Join Nightmare - SQL Server Forums

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)

Join Nightmare

Author

Topic

ArieB
Starting Member

9 Posts

Posted - 2007-07-18 : 04:36:17

I Have these two table I need to join.

tblMovements
SKU Units TranDate
ABC 1 23/05/2007
ABC 1 18/06/2007

tblPriceChange
SKU ChangeDate Price
ABC 20/02/2007 400
ABC 14/04/2007 350
ABC 22/05/2007 300
ABC 17/06/2007 250

Select m.SKU,Sum(m.Units), Sum(m.Units*p.Price)
From tblMovements m join tblPriceChange p on
m.SKU=p.SKU and ...
Group by m.SKU

this would return
ABC 2 550

At the first transaction date in tblMovements the Price was 300,
the secon was at 250.
I need to stick the right price on each of the tblMovements row..

This is a real headache, please help...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-18 : 05:31:49

[code]-- Prepare sample data
set dateformat dmy

declare @Movements table (SKU varchar(3), Units int, TranDate datetime)

insert @movements
select 'ABC', 1, '23/05/2007' union all
select 'ABC', 1, '18/06/2007'

declare @PriceChange table (SKU varchar(3), ChangeDate datetime, Price int)

insert @pricechange
select 'ABC', '20/02/2007', 400 union all
select 'ABC', '14/04/2007', 350 union all
select 'ABC', '22/05/2007', 300 union all
select 'ABC', '17/06/2007', 250

-- Show the result
SELECT m.SKU,
SUM(m.Units) AS Units,
SUM(d.Price) AS Price
FROM @Movements AS m
INNER JOIN (
SELECT SKU,
ChangeDate,
Price,
(SELECT MIN(pc2.ChangeDate) FROM @PriceChange AS pc2 WHERE pc2.SKU = pc1.SKU AND pc2.ChangeDate > pc1.ChangeDate) AS dt2
FROM @PriceChange AS pc1
) AS d ON d.SKU = m.SKU
AND d.ChangeDate <= m.TranDate
AND ISNULL(d.dt2, m.TranDate) >= m.TranDate
GROUP BY m.SKU
ORDER BY m.SKU[/code]

Peter Larsson
Helsingborg, Sweden

ArieB
Starting Member

9 Posts

Posted - 2007-07-18 : 08:01:07

Wow, thanks a lot Peter.
I goota delve into it to understand what you've done.
But it sure works...
Thanks a million...
It's amazing how you can learn so much from one post.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources