December 2, 2010 at 11:14 am
I have the following table with data.
CREATE TABLE #IdTable(
[RecId] [int] IDENTITY(1,1) NOT NULL,
[RecTime] [datetime] NOT NULL,
[IdNumber] [bigint] NULL,
[Location] [nvarchar](300) NULL,
[DeviceEsn] [int] NULL
)
INSERT INTO #IdTable ([RecTime],[IdNumber],[Location],[DeviceEsn]) VALUES ('2010/12/01 00:00:00', 1, 'A1', 100)
INSERT INTO #IdTable ([RecTime],[IdNumber],[Location],[DeviceEsn]) VALUES ('2010/12/01 00:01:01', 1, 'A2', 100)
INSERT INTO #IdTable ([RecTime],[IdNumber],[Location],[DeviceEsn]) VALUES ('2010/12/01 00:03:03', 1, 'A3', 100)
INSERT INTO #IdTable ([RecTime],[IdNumber],[Location],[DeviceEsn]) VALUES ('2010/12/01 00:05:00', 1, 'B', 101)
INSERT INTO #IdTable ([RecTime],[IdNumber],[Location],[DeviceEsn]) VALUES ('2010/12/01 00:10:00', 1, 'C1', 102)
INSERT INTO #IdTable ([RecTime],[IdNumber],[Location],[DeviceEsn]) VALUES ('2010/12/01 00:11:01', 1, 'C2', 102)
--SELECT * FROM #IdTable
I need to group the records by DeviceEsn with the enter time defined as the mininum time for the group and exit time is the maximum. The results should be filtered when the difference between enter and exit times is 2 minutes or less.
In a perfect world, I would like the result to look like:
DeviceEsn EnterTime EnterLocation ExitTime ExitLocation
100 2010-12-01 00:00:00 A1 2010-12-01 00:03:03 A3
This would also be acceptable:
DeviceEsn RecTime Location
100 2010-12-01 00:00:00 A1
100 2010-12-01 00:03:03 A3
I have tried using the HAVING clause to limit the results but I cannot figure out how to get location where the RecTime is at the minimum and maximum for the group.
SELECT [DeviceEsn],MIN([RecTime]),MAX([RecTime])
FROM #IdTable
WHERE [IdNumber] = 1
GROUP BY [DeviceEsn]
HAVING (DATEDIFF( mi, MIN([RecTime]), MAX([RecTime]) ) > 2)
I also tried adding ROW_NUMBER() column to the data so I can select the record when RecTime is at the minimum and maximum but I cannot figure out how to apply the time difference to the query.
SELECT t.[DeviceEsn],t.[RecTime],t.[Location]
FROM
(
SELECT
[DeviceEsn],[RecTime],[Location],
ROW_NUMBER() OVER( PARTITION BY [DeviceEsn] ORDER BY [RecTime] ASC ) AS [EnterRn],
ROW_NUMBER() OVER( PARTITION BY [DeviceEsn] ORDER BY [RecTime] DESC ) AS [ExitRn]
FROM #IdTable
WHERE ([IdNumber] = 1)
) t
WHERE (t.[EnterRn] = 1 OR t.[ExitRn] = 1)
ORDER BY t.[DeviceEsn] ASC, t.[RecTime] ASC
DeviceEsn RecTime Location
100 2010-12-01 00:00:00 A1
100 2010-12-01 00:03:03 A3
101 2010-12-01 00:05:00 B
102 2010-12-01 00:10:00 C1
102 2010-12-01 00:11:01 C2
I know I have probably made this more difficult that it should be. Any help and/or pointers will be appreciated.
Barry
December 2, 2010 at 11:35 am
I think what you're looking for is:
;WITH recGroups AS (
SELECT DeviceEsn,
Location,
recTime,
MIN(RecTime) OVER (PARTITION BY DeviceEsn) as minRecTime,
MAX(RecTime) OVER (PARTITION BY DeviceEsn) as maxRecTime
FROM #IdTable
WHERE IdNumber = 1
)
SELECT mn.deviceEsn,
mn.location AS enterLocation,
mx.location AS exitLocation,
mn.minRecTime AS enterTime,
mn.maxRecTime AS exitTime
FROM recGroups mn INNER JOIN
recGroups mx ON mn.deviceESN = mx.deviceESN
WHERE mn.recTime = mn.minRecTime
AND mx.recTime = mx.MaxRecTime
AND DATEDIFF(mi, mn.minRecTime, mn.maxRecTime) > 2
edit: formatting. You could also do this without the windowed aggregate functions by using an additional inner select or CTE to get the aggregate data, and then selecting the additional data points from the table.
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
December 2, 2010 at 12:20 pm
Wow, that's perfect.
Thank you
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply