| How to get a date dependend product price [message #374868] |
Mon, 09 July 2001 05:11  |
Chris
Messages: 128
Registered: November 1998
|
Senior Member |
|
|
Hi there,
I have got a urgend problem.
1. One Table with Customer_No,Country, Start_Date_of_Price_Validity,Price
2. Table with Customer_No,Country, Purchase_date, Quantity and some more columns.
In the first table could be only one price for a country, or more e.g. 11/1999 -> 100 USD, 5/2000 -> 150 USD.
If I now check the cost of a product I would like to know if a customer bought a product in 3/2000 how much 5 pices would cost.
e.g 11/1999 - 5/2000 = 5*100 USD and 10 pices in 1/2001 -->
5/2000 - Today (Getdate()) = 10 * 150.
I don't know how the syntax in SQL should look like but it must be the same syntax like the Excelfunction VLOOKUP.
Please could some1 help me
|
|
|
|
| Re: How to get a date dependend product price [message #374943 is a reply to message #374868] |
Thu, 12 July 2001 11:35  |
Christian
Messages: 15
Registered: February 2001
|
Junior Member |
|
|
Thank you for your help so far!
I don't need a product ID because it is only one Product called Transaction.
GTI = CustomerNo
I tryed it this way:
SELECT dbo.Transaction_Cost.GTI, dbo.Trades.Country,
dbo.Trades.No_of_Trades AS Sum_No_of_Trades,
dbo.Transaction_Cost.Cost, CONVERT(money,
dbo.Trades.No_of_Trades)
* dbo.Transaction_Cost.Cost AS Gesamt
FROM dbo.Transaction_Cost INNER JOIN
dbo.Trades ON
dbo.Transaction_Cost.GTI = dbo.Trades.GTI AND
dbo.Transaction_Cost.Country = dbo.Trades.Country
WHERE (dbo.Transaction_Cost.Startdate =
(SELECT MAX(Transaction_Cost.Startdate)
AS CostDate
FROM dbo.Transaction_Cost, dbo.Trades
WHERE (dbo.Transaction_Cost.GTI = 21739) AND
(dbo.Transaction_Cost.Country = dbo.Trades.Country)
AND
(dbo.Transaction_Cost.Startdate <= dbo.Trades.Ultimo_Entry_Date)
AND
(dbo.Trades.Ultimo_Entry_Date = CONVERT(DATETIME,
'2001-01-31 00:00:00', 102)))) AND
(dbo.Trades.Ultimo_Entry_Date = CONVERT(DATETIME,
'2001-01-31 00:00:00', 102)) AND
(dbo.Transaction_Cost.GTI = 21739)
Or Month and year dependend
################################################
SELECT dbo.Transaction_Cost.GTI, dbo.Trades.Country,
dbo.Trades.No_of_Trades AS Sum_No_of_Trades,
dbo.Transaction_Cost.Cost, CONVERT(money,
dbo.Trades.No_of_Trades)
* dbo.Transaction_Cost.Cost AS Gesamt
FROM dbo.Transaction_Cost INNER JOIN
dbo.Trades ON
dbo.Transaction_Cost.GTI = dbo.Trades.GTI AND
dbo.Transaction_Cost.Country = dbo.Trades.Country
WHERE (dbo.Transaction_Cost.Startdate =
(SELECT MAX(Transaction_Cost.Startdate)
AS CostDate
FROM dbo.Transaction_Cost, dbo.Trades
WHERE (dbo.Transaction_Cost.GTI = 21739) AND
(dbo.Transaction_Cost.Country = dbo.Trades.Country)
AND
(dbo.Transaction_Cost.Startdate <= dbo.Trades.Ultimo_Entry_Date)
AND ((DATEPART(Mm, dbo.Trades.Ultimo_Entry_Date)
= 1) AND (DATEPART(yy,
dbo.Trades.Ultimo_Entry_Date) = 2001)))) AND
(dbo.Transaction_Cost.GTI = 21739) AND (DATEPART(Mm,
dbo.Trades.Ultimo_Entry_Date) = 1) AND (DATEPART(yy,
dbo.Trades.Ultimo_Entry_Date) = 2001)
################################################
OK It funktions nice for January and February but if I come to March only one country shows up.
In the Transaction_cost table is for each Customer and each country an costentry e.g.
GTI | Country | Cost | Cur | Starddate
123 | Germany | 10 | EUR | 11/1/99
123 | Germany | 20 | EUR | 1/1/00
123 | Germany | 30 | EUR | 3/1/00
123 | Germany | 40 | EUR | 10/1/00
123 | Germany | 50 | EUR | 1/1/01
123 | USA___ | 15 | EUR | 1/1/01
123 | Italy__ | 10 | EUR | 11/1/99
123 | Italy__ | 50 | EUR | 1/1/01
999 | Italy__ | 50 | EUR | 1/1/01
Now I woud like to know if transaktions take place on one day, a month, quater of a year, year and between two dates, which is the correct cost per transaction (T) per country to calculate with
for example if a customer made 3 T in 1/00, 4 T in 2/00 and 9 T in 3/00 (Germany)
5 T each mont in USA and 10 T each mont in Italy
the calculation for a quater should be
Germany | USA | Italy
+ 3 * 20 | 5 * 15 | 10 * 50
+ 4 * 20 | 5 * 15 | 10 * 50
+ 9 * 30 | 5 * 15 | 10 * 50
---------------------------
410 + 225 + 1500 = 2135
If a use the select statement from above january shows up ok also if I use february but because only germany has an entry in march only germany will show up in march.
If I use a select statement for the first Quarter of a year, also only germany will show up and !!! with the cost of march for each month
Should I build up a function or how to tell SQL to use the correct Cost per Country to its dependent date for each customer
The problem is in the beginning I don't know in how many different countrys a customer how many transactions will make
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
