October 22, 2014 at 2:52 pm
Guys I am having problems joining these two tables and returning the correct values. The issue is that i have a work order table and a revenue table. I only want to return the sum of the revenue when the revenue comes after the work order date. That is simple enough, but it gets tricky when there are multiple work orders for the same ID. for those instances, we only want the sum of the revenue if it shows up post the work order date and if it is before any other work order date. So ID 312187014 should only have the 9-5 revenue from below, not the 7/7 or 8/6 revenue because the 8/7 work order date is after those revenue dates and thus will not have any revenue tied to it because there is a 9/3 work order that ties to the 9/5 revenue. Additionally the 412100368 ID has a 7/7 work order that ties to the 7/26 revenue, and the 8/7 work order will tie to the 8/23 and 9/20 revenue
i would send some code that i tried, but i really can't figure this out....any help would be appreciated.
--===== Create the test table with
CREATE TABLE #workorder
(
Id varchar(20),
wo varchar(10),
wodate datetime,
amount float
)
GO
CREATE TABLE #revenue
(
ID varchar(20),
revdate datetime,
amount float
)
GO
INSERT INTO #workorder (Id, wo, wodate, amount)
values ('312187014','74266803', '2014-08-07 00:00:00.000', 1211.26)
INSERT INTO #workorder (Id, wo, wodate, amount)
values ('312187014','74299794', '2014-09-03 00:00:00.000', 4690.34)
INSERT INTO #workorder (Id, wo, wodate, amount)
values ('412100368','74198054', '2014-07-07 00:00:00.000', 3347.15)
INSERT INTO #workorder (Id, wo, wodate, amount)
values ('412100368','74284590', '2014-08-07 00:00:00.000', 2348.82)
INSERT INTO #revenue (ID, revdate, amount)
values ('312187014', '2014-07-07 00:00:00.000', 2511.00)
INSERT INTO #revenue (ID, revdate, amount)
values ('312187014', '2014-08-06 00:00:00.000', 2511.00)
INSERT INTO #revenue (ID, revdate, amount)
values ('312187014', '2014-09-05 00:00:00.000', 2511.00)
INSERT INTO #revenue (ID, revdate, amount)
values ('412100368', '2014-07-26 00:00:00.000', 3700.00)
INSERT INTO #revenue (ID, revdate, amount)
values ('412100368', '2014-08-23 00:00:00.000', 3700.00)
INSERT INTO #revenue (ID, revdate, amount)
values ('412100368', '2014-09-20 00:00:00.000', 3700.00)
select * from #revenue
select * from #workorder
work order REVENUE
Id wo wodate amountID Date AMOUNT
312187014742668032014-08-07 00:00:00.0001211.26NULL NULL NULL
312187014742997942014-09-03 00:00:00.0004690.343121870142014-09-05 00:00:00.0002511
412100368741980542014-07-07 00:00:00.0003347.154121003682014-07-26 00:00:00.0003700
412100368742845902014-08-07 00:00:00.0002348.824121003682014-08-23 00:00:00.0003700
412100368742845902014-08-07 00:00:00.0002348.824121003682014-09-20 00:00:00.0003700
October 22, 2014 at 3:36 pm
I'm sure that there's a more efficient solution in 2012 using LEAD() but I don't have access to a 2012 instance right now.
WITH wos AS(
SELECT Id,
wo,
wodate,
amount,
ROW_NUMBER() OVER( PARTITION BY Id ORDER BY wodate) rn
FROM #workorder
),
wos2 AS(
SELECT w1.Id,
w1.wo,
w1.wodate,
ISNULL( w2.wodate, '99991231') AS enddate,
w1.amount
FROM wos w1
LEFT
JOIN wos w2 ON w1.Id = w2.Id AND w1.rn = w2.rn - 1
)
SELECT *
FROM wos2 w
LEFT
JOIN #revenue r ON r.Id = w.ID
AND r.revdate BETWEEN w.wodate AND w.enddate;
October 22, 2014 at 3:44 pm
Using sqlfiddle, I was able to test this:
WITH wos AS(
SELECT Id,
wo,
wodate,
LEAD( wodate, 1, '99991231') OVER( PARTITION BY Id ORDER BY wodate) AS enddate,
amount
FROM #workorder w
)
SELECT *
FROM wos w
LEFT
JOIN #revenue r ON r.Id = w.ID
AND r.revdate BETWEEN w.wodate AND w.enddate
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply