July 1, 2014 at 5:18 pm
I have two tables that I need to combine, they are as follows:
AccountCheckInDT
3289442/14/2014 15:11
3289442/14/2014 15:21
3289442/15/2014 15:24
3289442/15/2014 17:45
3289442/15/2014 23:58
3289442/16/2014 20:09
3289442/17/2014 11:27
3289442/19/2014 9:35
3289442/19/2014 9:41
AccountCheckOutDT
3289442/14/2014 16:18
3289442/16/2014 0:27
3289442/19/2014 9:28
3289442/19/2014 10:00
This is how I need the end result to look:
AccountCheckInDT CheckOutDT
3289442/14/2014 15:11 2/14/2014 16:18
3289442/14/2014 15:21
3289442/15/2014 15:24
3289442/15/2014 17:45
3289442/15/2014 23:58 2/16/2014 0:27
3289442/16/2014 20:09
3289442/17/2014 11:27
3289442/19/2014 9:35 2/19/2014 9:28
3289442/19/2014 9:41 2/19/2014 10:00
This is the best way that I know how to explain this and any help would be greatly appreciated. Sorry it is not lining up too well in preview but I hope it is understandable.
July 2, 2014 at 12:47 am
I'm new to the forum as well, but I've been SQLing a while. I hope that I can be of some help.
Looking at your desired result, I don't see the pattern of how you want to match the records between tables. Can you give a verbal rule for how you intend to combine these records?
For instance, "Each Check Out record should be matched with the Check In record which immediately precedes it in time."
This would give you a result like this:
Account CheckInDT CheckOutDT
328944 2014-02-14 15:11:00.000 NULL
328944 2014-02-14 15:21:00.000 2014-02-14 16:18:00.000
328944 2014-02-15 15:24:00.000 NULL
328944 2014-02-15 17:45:00.000 NULL
328944 2014-02-15 23:58:00.000 2014-02-16 00:27:00.000
328944 2014-02-16 20:09:00.000 NULL
328944 2014-02-17 11:27:00.000 2014-02-19 09:28:00.000
328944 2014-02-19 09:35:00.000 NULL
328944 2014-02-19 09:41:00.000 2014-02-19 10:00:00.000
I used a fairly straightforward script to produce this result:
CREATE TABLE [dbo].[CheckIn] ( Account int NOT NULL, CheckInDT datetime NOT NULL );
CREATE INDEX CLX_CheckIn ON [dbo].[CheckIn] ( Account, CheckInDT );
CREATE TABLE [dbo].[CheckOut] ( Account int NOT NULL, CheckOutDT datetime NOT NULL );
CREATE INDEX CLX_CheckOut ON [dbo].[CheckOut] ( Account, CheckOutDT );
INSERT INTO [dbo].[CheckIn] ( Account, CheckInDT )
VALUES ( 328944, '2/14/2014 15:11' )
, ( 328944, '2/14/2014 15:21' )
, ( 328944, '2/15/2014 15:24' )
, ( 328944, '2/15/2014 17:45' )
, ( 328944, '2/15/2014 23:58' )
, ( 328944, '2/16/2014 20:09' )
, ( 328944, '2/17/2014 11:27' )
, ( 328944, '2/19/2014 09:35' )
, ( 328944, '2/19/2014 09:41' );
INSERT INTO [dbo].[CheckOut] ( Account, CheckOutDT )
VALUES ( 328944, '2/14/2014 16:18' )
, ( 328944, '2/16/2014 00:27' )
, ( 328944, '2/19/2014 09:28' )
, ( 328944, '2/19/2014 10:00' );
SELECT i.Account, i.CheckInDT, o.CheckOutDT
FROM [dbo].[CheckIn] AS i
OUTER APPLY
(
-- Next record in checkout table
SELECT TOP 1 o.CheckOutDT
FROM [dbo].[CheckOut] AS o
WHERE o.Account = i.Account AND o.CheckOutDT > i.CheckInDT
AND NOT EXISTS
(
-- No record preceding it
SELECT 1 FROM [dbo].[CheckIn] AS i2
WHERE i2.Account = i.Account AND i2.CheckInDT > i.CheckInDT
AND i2.CheckInDT <= o.CheckOutDT
)
ORDER BY o.CheckOutDT
) AS o;
If I thought about it more and needed better performance, particularly at larger table sizes, I would start with using CTEs and ROW_NUMBER() functions, and then move to using indexed temp tables. With SQL Server 2014, I'd explore using window functions to look ahead or back by a single record.
July 2, 2014 at 2:57 am
My attempt at this one.
SELECT account,
checkindt,
CASE
WHEN rid = 1 THEN checkoutdt
ELSE NULL
END CheckOutDT
FROM (SELECT account,
Min(checkoutdt)checkoutdt,
checkindt,
Min(diff) diff,
Row_number()
OVER(
PARTITION BY Min(checkoutdt)
ORDER BY Min(checkoutdt), checkindt DESC)rid
FROM (SELECT i.account,
i.checkindt,
Datediff(hh, o.checkoutdt, i.checkindt)diff,
o.checkoutdt
FROM [dbo].[checkin] AS i
CROSS JOIN [dbo].[checkout] AS o
WHERE o.account = i.account
AND o.checkoutdt > i.checkindt) T
GROUP BY checkindt,account)T1
ORDER BY checkindt
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
July 2, 2014 at 7:39 am
Thank you both for your very quick solutions to my post. Both of them worked just as expected and returned the same results. As they say there is always more than one way to skin a cat and this is so true with any type of programming.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply