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
zeeshanbuttAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zeeshanbuttAuthor Commented:
Can anyone help please????

Thanks a million in advance....
0
Anthony PerkinsCommented:
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 & 
0
zeeshanbuttAuthor Commented:
I have attended all the open questions except this current one.

Can some please help.


Thanks,

Zee
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

mfsamuelCommented:
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;

Open in new window

0
zeeshanbuttAuthor Commented:
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
0
mfsamuelCommented:
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.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;
0
zeeshanbuttAuthor Commented:
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
0
mfsamuelCommented:
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.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)
OR sh2.Date IS NULL;
0
mfsamuelCommented:
sorry that won't work.  give me one sec.,
0
mfsamuelCommented:
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.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)
OR (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 sh3.Date=sh1.Date)
AND sh2.StoreID NOT IN (SELECT sh3.StoreID FROM saleshistory sh3 WHERE sh3.Cost>0 AND sh3.StoreID=sh2.StoreID AND sh3.ProductID=sh2.ProductID));
0
mfsamuelCommented:
this one does it I think.  note this requires distinct Date, StoreId, ProductID

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.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)
OR (sh2.StoreID NOT IN (SELECT sh3.StoreID FROM saleshistory sh3 WHERE sh3.Cost>0 AND sh3.StoreID=sh2.StoreID AND sh3.ProductID=sh2.ProductID)
AND sh1.Date=sh2.Date);
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.