zeeshanbutt
asked on
Help with a T-Sql Query
Hi All,
I have a table called saleshistory with the following fields and types.
Date (datetime)
StoreID (varchar)
ProductID (varchar)
Cost(decimal)
Price(Decimal)
Units (BigInt)
The above saleshistory table has numerous distinct products, numerous distinct Outlets and numerous dictinct dates.
The saleshistory table holds the saleshistory information in a real retail world enviornment. So each record represents the units sold by each product per store per date with a particular price and cost.
The total number of records that this table holds is 5513828. Please note that the saleshistory table always has one record per store per date per product.
The Saleshistory records look like as follows (This is just an example. Each field is seperated by coma)
(Its sorted by date Desc order)
Date, StoreID, ProductID, Cost, Price,Units
2008-01-21 00:00:00 041FCS 134009 0.00 13.20 1.00
2008-01-14 00:00:00 041FCS 134009 0.00 13.20 4.00
2007-12-24 00:00:00 041FCS 134009 0.00 12.00 3.00
2007-12-17 00:00:00 041FCS 134009 0.00 12.00 2.00
2007-12-10 00:00:00 041FCS 134009 6.47 12.00 2.00
2007-12-03 00:00:00 041FCS 134009 0.00 12.00 2.00
2007-11-26 00:00:00 041FCS 134009 5.00 12.00 3.00
2007-11-19 00:00:00 041FCS 134009 0.00 12.00 2.00
Now in this table there are numerous instances where the cost is 0.
I want to get the non zero cost (where cost>0) on a latest date (if exsist) for each product per date per outlet and then apply this cost for the remaining dates available.
Look at the above example (Please note that its for same product & same outlet).
The non zero cost available on the latest date is $6.47 on '2007-12-10'. Now only from '2007-12-10' to ownwards I want to apply this cost in my SELECT statement, the price and units will always remain unchanged. So my SELECT should return the following:
Date, StoreID, ProductID, Cost, Price,Units
2008-01-21 00:00:00 041FCS 134009 6.47 13.20 1.00
2008-01-14 00:00:00 041FCS 134009 6.47 13.20 4.00
2007-12-24 00:00:00 041FCS 134009 6.47 12.00 3.00
2007-12-17 00:00:00 041FCS 134009 6.47 12.00 2.00
2007-12-10 00:00:00 041FCS 134009 6.47 12.00 2.00
2007-12-03 00:00:00 041FCS 134009 0.00 12.00 2.00
2007-11-26 00:00:00 041FCS 134009 5.00 12.00 3.00
2007-11-19 00:00:00 041FCS 134009 0.00 12.00 2.00
I want to do the same for each set of product per date per outlet.
Look at some another examples
Example 2; (make sure its same product & outlet)
Date, StoreID, ProductID, Cost, Price,Units
2007-01-21 00:00:00 041TCS 234008 0.0 13.20 1.00
2007-01-14 00:00:00 041TCS 234008 4.0 13.20 4.00
2006-12-24 00:00:00 041TCS 234008 5.0 12.00 3.00
Example 2 Should return:
Date, StoreID, ProductID, Cost, Price,Units
2007-01-21 00:00:00 041TCS 234008 4.0 13.20 1.00
2007-01-14 00:00:00 041TCS 234008 4.0 13.20 4.00
2006-12-24 00:00:00 041TCS 234008 5.0 12.00 3.00
In example 2 the non zero cost (4.0) on the latest date (2007-01-14) has been applied to the only other available date (2007-01-21).
Example3(make sure its same product & outlet):
Date, StoreID, ProductID, Cost, Price,Units
2007-02-21 00:00:00 041TCS 134555 2.0 13.20 1.00
2007-01-14 00:00:00 041TCS 134555 0.0 13.20 4.00
2006-12-24 00:00:00 041TCS 134555 0.0 12.00 3.00
Example 3 Should return:
Date, StoreID, ProductID, Cost, Price,Units
2007-02-21 00:00:00 041TCS 134555 2.0 13.20 1.00
2007-01-14 00:00:00 041TCS 134555 0.0 13.20 4.00
2006-12-24 00:00:00 041TCS 134555 0.0 12.00 3.00
Note in example 3 that the SELECT return everything same, because there is no zero cost for this product in this outlet.
In the end my SELECT should return all 5513828 records including cost applied (where applicable). The price and units always remin same.
How can I write a SELECT query for that.
Can someone please help me as soon as possible. Thanks a million for all your help.
Zee
I have a table called saleshistory with the following fields and types.
Date (datetime)
StoreID (varchar)
ProductID (varchar)
Cost(decimal)
Price(Decimal)
Units (BigInt)
The above saleshistory table has numerous distinct products, numerous distinct Outlets and numerous dictinct dates.
The saleshistory table holds the saleshistory information in a real retail world enviornment. So each record represents the units sold by each product per store per date with a particular price and cost.
The total number of records that this table holds is 5513828. Please note that the saleshistory table always has one record per store per date per product.
The Saleshistory records look like as follows (This is just an example. Each field is seperated by coma)
(Its sorted by date Desc order)
Date, StoreID, ProductID, Cost, Price,Units
2008-01-21 00:00:00 041FCS 134009 0.00 13.20 1.00
2008-01-14 00:00:00 041FCS 134009 0.00 13.20 4.00
2007-12-24 00:00:00 041FCS 134009 0.00 12.00 3.00
2007-12-17 00:00:00 041FCS 134009 0.00 12.00 2.00
2007-12-10 00:00:00 041FCS 134009 6.47 12.00 2.00
2007-12-03 00:00:00 041FCS 134009 0.00 12.00 2.00
2007-11-26 00:00:00 041FCS 134009 5.00 12.00 3.00
2007-11-19 00:00:00 041FCS 134009 0.00 12.00 2.00
Now in this table there are numerous instances where the cost is 0.
I want to get the non zero cost (where cost>0) on a latest date (if exsist) for each product per date per outlet and then apply this cost for the remaining dates available.
Look at the above example (Please note that its for same product & same outlet).
The non zero cost available on the latest date is $6.47 on '2007-12-10'. Now only from '2007-12-10' to ownwards I want to apply this cost in my SELECT statement, the price and units will always remain unchanged. So my SELECT should return the following:
Date, StoreID, ProductID, Cost, Price,Units
2008-01-21 00:00:00 041FCS 134009 6.47 13.20 1.00
2008-01-14 00:00:00 041FCS 134009 6.47 13.20 4.00
2007-12-24 00:00:00 041FCS 134009 6.47 12.00 3.00
2007-12-17 00:00:00 041FCS 134009 6.47 12.00 2.00
2007-12-10 00:00:00 041FCS 134009 6.47 12.00 2.00
2007-12-03 00:00:00 041FCS 134009 0.00 12.00 2.00
2007-11-26 00:00:00 041FCS 134009 5.00 12.00 3.00
2007-11-19 00:00:00 041FCS 134009 0.00 12.00 2.00
I want to do the same for each set of product per date per outlet.
Look at some another examples
Example 2; (make sure its same product & outlet)
Date, StoreID, ProductID, Cost, Price,Units
2007-01-21 00:00:00 041TCS 234008 0.0 13.20 1.00
2007-01-14 00:00:00 041TCS 234008 4.0 13.20 4.00
2006-12-24 00:00:00 041TCS 234008 5.0 12.00 3.00
Example 2 Should return:
Date, StoreID, ProductID, Cost, Price,Units
2007-01-21 00:00:00 041TCS 234008 4.0 13.20 1.00
2007-01-14 00:00:00 041TCS 234008 4.0 13.20 4.00
2006-12-24 00:00:00 041TCS 234008 5.0 12.00 3.00
In example 2 the non zero cost (4.0) on the latest date (2007-01-14) has been applied to the only other available date (2007-01-21).
Example3(make sure its same product & outlet):
Date, StoreID, ProductID, Cost, Price,Units
2007-02-21 00:00:00 041TCS 134555 2.0 13.20 1.00
2007-01-14 00:00:00 041TCS 134555 0.0 13.20 4.00
2006-12-24 00:00:00 041TCS 134555 0.0 12.00 3.00
Example 3 Should return:
Date, StoreID, ProductID, Cost, Price,Units
2007-02-21 00:00:00 041TCS 134555 2.0 13.20 1.00
2007-01-14 00:00:00 041TCS 134555 0.0 13.20 4.00
2006-12-24 00:00:00 041TCS 134555 0.0 12.00 3.00
Note in example 3 that the SELECT return everything same, because there is no zero cost for this product in this outlet.
In the end my SELECT should return all 5513828 records including cost applied (where applicable). The price and units always remin same.
How can I write a SELECT query for that.
Can someone please help me as soon as possible. Thanks a million for all your help.
Zee
The following questions seem to have fallen through the cracks and are now considered abandoned, please attend to them:
1 12/17/2007 500 How to Import an XML in a SQL Server 2005& Open SQL Server 2& &
2 10/20/2007 500 Maximum number of tables that a view can hold? Open MS SQL Server
3 10/19/2007 500 How to DTS a .DAT extension file in the data& Open MS SQL Server
4 07/05/2007 500 Intelligently update fields using another field !! Open Databases &
1 12/17/2007 500 How to Import an XML in a SQL Server 2005& Open SQL Server 2& &
2 10/20/2007 500 Maximum number of tables that a view can hold? Open MS SQL Server
3 10/19/2007 500 How to DTS a .DAT extension file in the data& Open MS SQL Server
4 07/05/2007 500 Intelligently update fields using another field !! Open Databases &
ASKER
I have attended all the open questions except this current one.
Can some please help.
Thanks,
Zee
Can some please help.
Thanks,
Zee
try this...
SELECT sh1.Date, sh1.StoreID, sh1.ProductID, IIF(sh1.Date>=sh2.Date,sh2.Cost,sh1.Cost) AS Cost, sh1.Price, sh1.Units
FROM saleshistory sh1
INNER JOIN saleshistory sh2 ON (sh2.StoreID=sh1.StoreID AND sh2.ProductID=sh1.ProductID)
WHERE sh2.Date IN (SELECT MAX(sh3.Date) FROM saleshistory sh3 WHERE sh3.Cost>0 AND sh3.StoreID=sh2.StoreID AND sh3.ProductID=sh2.ProductID)
AND sh2.Cost>0;
ASKER
mfsamuel,
Thanks for the reply.
But I am having syntex erorr. Can you please type the code in a regular text field correctly.
Thanks a million for your help.
Zee
Thanks for the reply.
But I am having syntex erorr. Can you please type the code in a regular text field correctly.
Thanks a million for your help.
Zee
sorry, iif is access. try this...
SELECT sh1.Date, sh1.StoreID, sh1.ProductID, CASE WHEN sh1.Date>=sh2.Date THEN sh2.Cost ELSE sh1.Cost END AS Cost, sh1.Price, sh1.Units
FROM saleshistory sh1
INNER JOIN saleshistory sh2 ON (sh2.StoreID=sh1.StoreID AND sh2.ProductID=sh1.ProductI
WHERE sh2.Date IN (SELECT MAX(sh3.Date) FROM saleshistory sh3 WHERE sh3.Cost>0 AND sh3.StoreID=sh2.StoreID AND sh3.ProductID=sh2.ProductI
AND sh2.Cost>0;
SELECT sh1.Date, sh1.StoreID, sh1.ProductID, CASE WHEN sh1.Date>=sh2.Date THEN sh2.Cost ELSE sh1.Cost END AS Cost, sh1.Price, sh1.Units
FROM saleshistory sh1
INNER JOIN saleshistory sh2 ON (sh2.StoreID=sh1.StoreID AND sh2.ProductID=sh1.ProductI
WHERE sh2.Date IN (SELECT MAX(sh3.Date) FROM saleshistory sh3 WHERE sh3.Cost>0 AND sh3.StoreID=sh2.StoreID AND sh3.ProductID=sh2.ProductI
AND sh2.Cost>0;
ASKER
mfsamuel,
For the examples that I put in my problem your script worked fine.
But when I am run it it against the entire saleshistory table it is returning 3570472 records instead of 5513828 (which is the total no. of records for the saleshistory table).
Any idea why?
I desperately need your help please.
Thanks a million
Zee
For the examples that I put in my problem your script worked fine.
But when I am run it it against the entire saleshistory table it is returning 3570472 records instead of 5513828 (which is the total no. of records for the saleshistory table).
Any idea why?
I desperately need your help please.
Thanks a million
Zee
What does this return?
SELECT sh1.Date, sh1.StoreID, sh1.ProductID, CASE WHEN sh1.Date>=sh2.Date THEN sh2.Cost ELSE sh1.Cost END AS Cost, sh1.Price, sh1.Units
FROM saleshistory sh1
LEFT JOIN saleshistory sh2 ON (sh2.StoreID=sh1.StoreID AND sh2.ProductID=sh1.ProductI
WHERE (sh2.Date IN (SELECT MAX(sh3.Date) FROM saleshistory sh3 WHERE sh3.Cost>0 AND sh3.StoreID=sh2.StoreID AND sh3.ProductID=sh2.ProductI
AND sh2.Cost>0)
OR sh2.Date IS NULL;
SELECT sh1.Date, sh1.StoreID, sh1.ProductID, CASE WHEN sh1.Date>=sh2.Date THEN sh2.Cost ELSE sh1.Cost END AS Cost, sh1.Price, sh1.Units
FROM saleshistory sh1
LEFT JOIN saleshistory sh2 ON (sh2.StoreID=sh1.StoreID AND sh2.ProductID=sh1.ProductI
WHERE (sh2.Date IN (SELECT MAX(sh3.Date) FROM saleshistory sh3 WHERE sh3.Cost>0 AND sh3.StoreID=sh2.StoreID AND sh3.ProductID=sh2.ProductI
AND sh2.Cost>0)
OR sh2.Date IS NULL;
sorry that won't work. give me one sec.,
What does this do? The most likely the problem is not all records have non-zero records.
SELECT sh1.Date, sh1.StoreID, sh1.ProductID, CASE WHEN sh1.Date>=sh2.Date THEN sh2.Cost ELSE sh1.Cost END AS Cost, sh1.Price, sh1.Units
FROM saleshistory sh1
INNER JOIN saleshistory sh2 ON (sh2.StoreID=sh1.StoreID AND sh2.ProductID=sh1.ProductI
WHERE (sh2.Date IN (SELECT MAX(sh3.Date) FROM saleshistory sh3 WHERE sh3.Cost>0 AND sh3.StoreID=sh2.StoreID AND sh3.ProductID=sh2.ProductI
AND sh2.Cost>0)
OR (sh2.Date IN (SELECT MAX(sh3.Date) FROM saleshistory sh3 WHERE sh3.Cost=0 AND sh3.StoreID=sh2.StoreID AND sh3.ProductID=sh2.ProductI
AND sh2.StoreID NOT IN (SELECT sh3.StoreID FROM saleshistory sh3 WHERE sh3.Cost>0 AND sh3.StoreID=sh2.StoreID AND sh3.ProductID=sh2.ProductI
SELECT sh1.Date, sh1.StoreID, sh1.ProductID, CASE WHEN sh1.Date>=sh2.Date THEN sh2.Cost ELSE sh1.Cost END AS Cost, sh1.Price, sh1.Units
FROM saleshistory sh1
INNER JOIN saleshistory sh2 ON (sh2.StoreID=sh1.StoreID AND sh2.ProductID=sh1.ProductI
WHERE (sh2.Date IN (SELECT MAX(sh3.Date) FROM saleshistory sh3 WHERE sh3.Cost>0 AND sh3.StoreID=sh2.StoreID AND sh3.ProductID=sh2.ProductI
AND sh2.Cost>0)
OR (sh2.Date IN (SELECT MAX(sh3.Date) FROM saleshistory sh3 WHERE sh3.Cost=0 AND sh3.StoreID=sh2.StoreID AND sh3.ProductID=sh2.ProductI
AND sh2.StoreID NOT IN (SELECT sh3.StoreID FROM saleshistory sh3 WHERE sh3.Cost>0 AND sh3.StoreID=sh2.StoreID AND sh3.ProductID=sh2.ProductI
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a million in advance....