Help to select correct cost price based on date

[Sweenster]

Hi, I have 3 tables which between them hold sales summary data, cost prices data and then dates/session info.

I need to pull everything from the sales table + work the actual cost of each sale (Summ.Volume * cost.CostPrice) *but* using the cost price that was applicable on the date the sale was made.

COST table has ProdID, Cost, ChangeDate
SALE table has ProdID, SessionID, Volume + others..
SESS table has SessionID, SessionDate

I have had a go but without much success. Any pointers would be greatly appreciated!

 

[gmmastros]

Can you post some sample data and expected results? It will probably help you get an answer quicker.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLBill]

Also, let us know what datatypes each column is. String values (char/varchar/nchar/nvarchar) are different than number values (int, decimal, etc). String values concatenate and number values add.

-SQLBill

Posting advice: FAQ481-4875

 

[Sweenster]

Hi, Here is some sample data....Apart from the dates everything is numeric

Sample from Sales Summary table (SALE)
SessID ItemID Volume Value SiteID
122 455 1 1.35 323
38 87 1 1.2 323
57 178 0 0 323
136 39 2 4.8 323

Sample from Sessions table (SESS)
SessionID SessionDate SiteID
122 05/01/2005 00:00 323
38 13/03/2006 00:00 323
397 01/05/2005 00:00 44
480 20/09/2005 00:00 544
550 20/09/2005 00:00 55

Sample from cost price table (COST)
ItemID Cost EffectDate
455 0.91 01/01/2005
455 1.04 12/03/2006
87 1.0175 01/01/2005
87 1.25 19/02/2006
87 1.19 13/03/2006

Desired end result...(basically SALE table + cost for each row)

SessID ItemID Volume Value SiteID SaleCost
122 455 1 1.35 323 0.91
38 87 1 1.2 323 1.04

 

[vongrunt]

> 38 87 1 1.2 323 [!]1.04[/!]

1.04 or 1.19?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[Sweenster]

sorry that was a typo. It should be 1.19
cheers

 

[vongrunt]

Try this:

select SA.*,
	(	select top 1 C.Cost 
		from COST C
		where C.ItemID=SA.ItemID and C.EffectDate <= S.SessionDate
		order by C.EffectDate desc
	) as SaleCost
from SALE SA
inner join SESS S on SA.SessID=S.SessionID

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[Sweenster]

This works OK for recent trading sessions but as we only started capturing cost price info on 1st Jan this year any sessions before this result in a cost price of NULL. Is it possible to get this to use the earliest cost price available for all session dates that are < C.EffDt?
(Have also added a join on site ID)

select top 100 SA.*,
( select top 1 C.Cost
from dbo.tbl_EPOS_Products_COS C
where C.ClarityCode=SA.ItemID --and C.EffDt <= S.SessDate
order by C.EffDt desc
) as SaleCost
from summline SA
inner join sessions S on SA.SessionID=S.SessionID
and s.siteid = sa.siteid

 

[vongrunt]

> Is it possible to get this to use the earliest cost price available for all session dates that are < C.EffDt?

I guess you mean "for all session dates that are < min(C.EffDT) for given ItemID"

This requires mixed logic:

a) show last captured price for ItemId before SessionDate
b) if such price does not exist, show first captured price for ItemId

IMHO b) is kind of fake but if you insist... temporal queries are always ugly, and this one is not an exception:

-- set ansi_warnings off
select SA.*, C.Cost as SaleCost
from  SALE SA
inner join SESS S on SA.SessID=S.SessionID
inner join
(	select SA.SessID, SA.ItemID, 
		isnull(max(case when S.SessionDate>= C.EffectDate then C.EffectDate end), min(C.EffectDate)) as EffectDate
	from SALE SA
	inner join SESS S on SA.SessID=S.SessionID
	left outer join COST C on C.ItemID=SA.ItemID 
	group by SA.SessID, SA.ItemID
) 
X on SA.SessID=X.SessID and SA.ItemID=X.ItemID
left outer join COST C on SA.ItemID=C.ItemID and X.EffectDate=C.EffectDate

Basically: derived table X calculates EffectDates for a) and b). if a) is NULL, b) takes place. Entire logic assumes two things:

- (SessID, ItemID) are unique in table SALE
- (ItemID, EffectDate) are unique in table COST

Note that query returns NULL SaleCost values if price is so far not captured at all.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[Sweenster]

That works a treat.
Thanks very much for your help.