T-SQL help within a single table !!!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

T-SQL help within a single table !!!

Author

Topic

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-02-12 : 12:49:57

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

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-02-12 : 13:50:52

Can ayone help please????

Thanks a million....

Zee

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-12 : 14:40:49

This is untested and I have no idea how the performance will be but...who know, maybe it'll work :)


declare @saleshistory table 
(Date datetime
,StoreID varchar(10)
,ProductID varchar(10)
,Cost	money
,Price	money
,Units BigInt);

insert @salesHistory
select '2008-01-21 00:00:00', '041FCS', '134009', 0.00, 13.20, 1.00 union all
select '2008-01-14 00:00:00', '041FCS', '134009', 0.00, 13.20, 4.00 union all
select '2007-12-24 00:00:00', '041FCS', '134009', 0.00, 12.00, 3.00 union all
select '2007-12-17 00:00:00', '041FCS', '134009', 0.00, 12.00, 2.00 union all
select '2007-12-10 00:00:00', '041FCS', '134009', 6.47, 12.00, 2.00 union all
select '2007-12-03 00:00:00', '041FCS', '134009', 0.00, 12.00, 2.00 union all
select '2007-11-26 00:00:00', '041FCS', '134009', 5.00, 12.00, 3.00 union all
select '2007-11-19 00:00:00', '041FCS', '134009', 0.00, 12.00, 2.00 union all

select '2007-01-21 00:00:00', '041TCS', '234008', 0.0, 13.20, 1.00 union all
select '2007-01-14 00:00:00', '041TCS', '234008', 4.0, 13.20, 4.00 union all
select '2006-12-24 00:00:00', '041TCS', '234008', 5.0, 12.00, 3.00 union all

select '2007-02-21 00:00:00', '041TCS', '134555', 2.0, 13.20, 1.00 union all
select '2007-01-14 00:00:00', '041TCS', '134555', 0.0, 13.20, 4.00 union all
select '2006-12-24 00:00:00', '041TCS', '134555', 0.0, 12.00, 3.00


;with cost (storeid, productid, date, cost)
as
(
	select	StoreID
			,ProductID
			,max(Date)
			,convert(money, substring(max( convert(char(23), date, 121) + convert(varchar(20), cost) ), 23+1, 20))
	from	@salesHistory
	where	Cost > 0
	group by StoreID
			,ProductID
)
select	sh.date
		,sh.StoreID
		,sh.ProductID
		,Cost = case when isNull(sh.cost,0) = 0 and sh.date > c.date then c.cost else sh.cost end
		,sh.Price
		,sh.Units
from	@salesHistory sh
left join cost c 
	on c.storeid = sh.storeid
	and c.productid = sh.productid
order by sh.StoreID
		,sh.ProductID
		,sh.date desc


OUTPUT:
date                    StoreID    ProductID  Cost                  Price                 Units
----------------------- ---------- ---------- --------------------- --------------------- --------------------
2008-01-21 00:00:00.000 041FCS     134009     6.47                  13.20                 1
2008-01-14 00:00:00.000 041FCS     134009     6.47                  13.20                 4
2007-12-24 00:00:00.000 041FCS     134009     6.47                  12.00                 3
2007-12-17 00:00:00.000 041FCS     134009     6.47                  12.00                 2
2007-12-10 00:00:00.000 041FCS     134009     6.47                  12.00                 2
2007-12-03 00:00:00.000 041FCS     134009     0.00                  12.00                 2
2007-11-26 00:00:00.000 041FCS     134009     5.00                  12.00                 3
2007-11-19 00:00:00.000 041FCS     134009     0.00                  12.00                 2
2007-02-21 00:00:00.000 041TCS     134555     2.00                  13.20                 1
2007-01-14 00:00:00.000 041TCS     134555     0.00                  13.20                 4
2006-12-24 00:00:00.000 041TCS     134555     0.00                  12.00                 3
2007-01-21 00:00:00.000 041TCS     234008     4.00                  13.20                 1
2007-01-14 00:00:00.000 041TCS     234008     4.00                  13.20                 4
2006-12-24 00:00:00.000 041TCS     234008     5.00                  12.00                 3

EDIT:
I added your sample data just to make sure something worked.
Does Units need to be BIGINT?

Be One with the Optimizer
TG

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-02-12 : 16:50:08

TG,

Thanks for your help.
But how I can run the select part alone (without inserts) again the saleshistory table. I am struggling. Can you please help.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-12 : 17:02:34

Take just the SELECT part alone, change the table name from @salesHistory to SalesHistory (two places).

I used a table variable (with inserted data) to test the query.

Be One with the Optimizer
TG

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-02-12 : 17:17:26

TG,

When I am running the following, it give me an erorr: Msg 208, Level 16, State 1, Line 11
Invalid object name 'cost'.

Any ide what is wrong here?

(
select StoreID
,ProductID
,max(Date)
,convert(money, substring(max( convert(char(23), date, 121) + convert(varchar(20), cost) ), 23+1, 20))
from salesHistory
where Cost > 0
group by StoreID
,ProductID
)
select sh.date
,sh.StoreID
,sh.ProductID
,Cost = case when isNull(sh.cost,0) = 0 and sh.date > c.date then c.cost else sh.cost end
,sh.Price
,sh.Units
from salesHistory sh
left join cost c
on c.storeid = sh.storeid
and c.productid = sh.productid
order by sh.StoreID
,sh.ProductID
,sh.date desc

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-12 : 17:20:12

you are missing this above your statement:

;with cost (storeid, productid, date, cost)
as

EDIT:
I highlighted the SELECT portion in my first post in blue

Be One with the Optimizer
TG

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-02-12 : 19:23:01

Just need to ask one more thing. What if I want to insert the records of your select query in a temperoray table (for example; select * into #t from table) how can I do that in your query. The reason why I asked is that I have never work with a select query the way you are doing...so please let me know.

Thank you so much for your help.

Zee

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-12 : 19:46:01

[code]-- Prepare sample data to mimic your environment
DECLARE @Sample TABLE (Date DATETIME, StoreID CHAR(6), ProductID INT, Cost SMALLMONEY, Price SMALLMONEY, Units SMALLMONEY)

INSERT @Sample
SELECT '2008-01-21 00:00:00', '041FCS', '134009', 0.00, 13.20, 1.00 UNION ALL
SELECT '2008-01-14 00:00:00', '041FCS', '134009', 0.00, 13.20, 4.00 UNION ALL
SELECT '2007-12-24 00:00:00', '041FCS', '134009', 0.00, 12.00, 3.00 UNION ALL
SELECT '2007-12-17 00:00:00', '041FCS', '134009', 0.00, 12.00, 2.00 UNION ALL
SELECT '2007-12-10 00:00:00', '041FCS', '134009', 6.47, 12.00, 2.00 UNION ALL
SELECT '2007-12-03 00:00:00', '041FCS', '134009', 0.00, 12.00, 2.00 UNION ALL
SELECT '2007-11-26 00:00:00', '041FCS', '134009', 5.00, 12.00, 3.00 UNION ALL
SELECT '2007-11-19 00:00:00', '041FCS', '134009', 0.00, 12.00, 2.00 UNION ALL
SELECT '2007-01-21 00:00:00', '041TCS', '234008', 0.0, 13.20, 1.00 UNION ALL
SELECT '2007-01-14 00:00:00', '041TCS', '234008', 4.0, 13.20, 4.00 UNION ALL
SELECT '2006-12-24 00:00:00', '041TCS', '234008', 5.0, 12.00, 3.00 UNION ALL
SELECT '2007-02-21 00:00:00', '041TCS', '134555', 2.0, 13.20, 1.00 UNION ALL
SELECT '2007-01-14 00:00:00', '041TCS', '134555', 0.0, 13.20, 4.00 UNION ALL
SELECT '2006-12-24 00:00:00', '041TCS', '134555', 0.0, 12.00, 3.00

-- Show the expected output
SELECT s.Date,
s.StoreID,
s.ProductID,
CASE
WHEN s.Date >= d.Date AND s.Cost = 0.0 THEN d.Cost
ELSE s.Cost
END AS Cost,
s.Price,
s.Units
FROM @Sample AS s
INNER JOIN (
SELECT StoreID,
ProductID,
Cost,
Date,
ROW_NUMBER() OVER (PARTITION BY StoreID, ProductID ORDER BY Date DESC) AS RecID
FROM @Sample
WHERE Cost > 0.0
) AS d ON d.StoreID = s.StoreID
AND s.ProductID = d.ProductID
WHERE d.RecID = 1
ORDER BY s.StoreID,
s.ProductID,
s.Date DESC[/code]

E 12°55'05.25"
N 56°04'39.16"

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-02-12 : 21:46:59

Peso,

I run your SELECT against the saleshistory table. This time I am getting 3570472 rows only, it should be 5513828 rows which is what TG's method was returning.

Peso / TG can you please help?

Thank u so much for the help.

Zee

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-13 : 01:35:05

You have a working query from TG? Stick with that.
I doubt they differ in speed or performance.

SELECT		s.Date,
		s.StoreID,
		s.ProductID,
		CASE
			WHEN s.Date >= d.Date AND s.Cost = 0.0 THEN d.Cost
			ELSE s.Cost
		END AS Cost,
		s.Price,
		s.Units
FROM		@Sample AS s
LEFT JOIN	(
			SELECT		StoreID,
					ProductID,
					Cost,
					Date,
					ROW_NUMBER() OVER (PARTITION BY StoreID, ProductID ORDER BY Date DESC) AS RecID
			FROM		@Sample
			WHERE		Cost > 0.0
		) AS d ON d.StoreID = s.StoreID
			AND s.ProductID = d.ProductID
			AND d.RecID = 1
ORDER BY	s.StoreID,
		s.ProductID,
		s.Date DESC

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources