[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 155
  • Last Modified:

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
0
zeeshanbutt
Asked:
zeeshanbutt
  • 6
  • 4
1 Solution
 
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now