Join by dates between each other

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 by dates between each other

Author

Topic

jshepler
Yak Posting Veteran

60 Posts

Posted - 2009-09-12 : 16:05:15

I'm working on an inventory system. I have two tables: one that holds inventory counts and another that holds delivieries. When the app shows the interface to build an order, I would like to present recent sales information. To help with that, I need to build a list of inventory/delivery pairs such that every inventory has a delivery associated with it. Every inventory row and delivery row needs to be in the result with nulls for any missing piece.

I hope I explained that well. I tried some searching but I have no idea what to search for. Here's some sample data and the expected results:

DECLARE @inventoryLogs TABLE (logId int identity(1,1), logDate smalldatetime)
INSERT INTO @inventoryLogs (logDate)
SELECT  '2009-09-07'
UNION SELECT '2009-09-14'
UNION SELECT '2009-09-21'
UNION SELECT '2009-09-28'

DECLARE @deliveryLogs TABLE (logId int identity(1,1), logDate smalldatetime)
INSERT INTO @deliveryLogs (logDate)
SELECT  '2009-09-01'
UNION SELECT '2009-09-08'
UNION SELECT '2009-09-22'

InventoryLogId  InventoryLogDate        DeliveryLogId   DeliveryLogDate
-------------   -------------------     -------------   -------------------
NULL            NULL                    1               2009-09-01 00:00:00
1               2009-09-07 00:00:00     2               2009-09-08 00:00:00
2               2009-09-14 00:00:00     NULL            NULL
3               2009-09-21 00:00:00     3               2009-09-22 00:00:00
4               2009-09-28 00:00:00     NULL            NULL

Can't depend on the logIds being in the proper order - must go by the dates. How can I join these tables such that the dates are basically between each other?

/jeff

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-13 : 06:02:43

[code]
DECLARE @inventoryLogs TABLE (logId int identity(1,1), logDate smalldatetime)
INSERT INTO @inventoryLogs (logDate)
SELECT '2009-09-07'
UNION SELECT '2009-09-14'
UNION SELECT '2009-09-21'
UNION SELECT '2009-09-28'

DECLARE @deliveryLogs TABLE (logId int identity(1,1), logDate smalldatetime)
INSERT INTO @deliveryLogs (logDate)
SELECT '2009-09-01'
UNION SELECT '2009-09-08'
UNION SELECT '2009-09-22'

; WITH
logs
AS
(
SELECT logId, logDate, logType, row_no = row_number() OVER ( ORDER BY logDate )
FROM
(
SELECT logId, logDate, logType = 'I'
FROM @inventoryLogs
UNION ALL
SELECT logId, logDate, logType = 'D'
FROM @deliveryLogs
) l
),
cte
AS
(
-- anchor
SELECT logId, logDate, logType, row_no, grp = 1
FROM logs
WHERE row_no = 1

UNION ALL

-- recursive
SELECT l.logId, l.logDate, l.logType, l.row_no,
grp = CASE WHEN l.logType = 'I'
OR l.logType = c.logType
THEN c.grp + 1
ELSE c.grp
END

FROM logs l
INNER JOIN cte c ON l.row_no = c.row_no + 1
)
SELECT logId = MAX(CASE WHEN logType = 'I' THEN logId END),
logDate = MAX(CASE WHEN logType = 'I' THEN logDate END),
logId = MAX(CASE WHEN logType = 'D' THEN logId END),
logDate = MAX(CASE WHEN logType = 'D' THEN logDate END)
FROM cte
GROUP BY grp

/*
logId logDate logId logDate
----------- ----------- ----------- -----------
NULL NULL 1 2009-09-01
1 2009-09-07 2 2009-09-08
2 2009-09-14 NULL NULL
3 2009-09-21 3 2009-09-22
4 2009-09-28 NULL NULL

(5 row(s) affected)
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-13 : 13:19:44

[code]
SELECT i.logId AS InventoryLogId,
i.logDate AS InventoryLogDate,
d.logId AS DeliveryLogId,
d.logDate AS DeliveryLogDate
FROM @deliveryLogs d
OUTER APPLY (SELECT TOP 1 logId,logDate
FROM @inventoryLogs
WHERE logDate< =d.logDate
ORDER BY logDate DESC)i
UNION ALL
SELECT i.logId,i.logDate,NULL,NULL
FROM @inventoryLogs i
OUTER APPLY (SELECT TOP 1 logId,logDate
FROM @deliveryLogs d
WHERE logDate> i.logDate
ORDER BY logDate)d
WHERE d.logId IS NULL
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources