April 17, 2011 at 9:21 pm
Hi All
Can somebody possibly provide some advice to help with a problem I have please?
I have a table where I store records for different areas on a daily basis. The sum of the time for each areas records for a day should = 1440 (minutes in a day). I am wanting to know what would be the most efficient way to determine periods where records for an area might be missing. The table looks something like this:
ID AreaPrdDate Start Stop A_Min E_Min
76751GTWR 2011-04-17 00:00:00 2011-04-17 07:00:00 2011-04-17 19:00:00 720 0
76750GTWR 2011-04-17 00:00:00 2011-04-17 06:04:00 2011-04-17 07:00:00 56 0
76685GTWR 2011-04-17 00:00:00 2011-04-17 03:43:00 2011-04-17 06:04:00 141 0
76678GTWR 2011-04-17 00:00:00 2011-04-17 01:30:00 2011-04-17 03:43:00 133 0
76676GTWR 2011-04-17 00:00:00 2011-04-17 01:09:00 2011-04-17 01:29:00 20 0
76659GTWR 2011-04-17 00:00:00 2011-04-16 19:42:00 2011-04-17 01:04:00 322 0
76657GTWR 2011-04-17 00:00:00 2011-04-16 19:30:00 2011-04-16 19:42:00 12 0
76649GTWR 2011-04-17 00:00:00 2011-04-16 19:00:00 2011-04-16 19:30:00 30 0
So I need to write a query which would return any missing time periods for each of a number of areas for a given PrdDate e.g. in the above example we are missing a record with a Start time of 2011-04-17 01:04:00 and an End time of 2011-04-17 01:09:00 and also missing a record with a Start time of 2011-04-17 01:29:00 and an End time of 2011-04-17 01:30:00.
I have done this once before for something similar and had to use 2 temporary tables and it was pretty messy. Just wanted to know if anyone might have some views on how better to produce the results I am am after.
Any help would be greatly appreciated.
Paul
April 17, 2011 at 11:21 pm
I would probably try to use CTEs, something like:
;WITH cte_TestTable
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY start) AS 'No',* FROM TestTable
)
SELECT a.id,a.Stop as StartTime ,b.StartTime as Stop FROM cte_TestTable a
INNER JOIN cte_TestTable b
ON a.No=b.No-1
WHERE b.No>1
AND a.Stop<>b.start
Please, note that it is based on the assumption that there are no overlapping time slots.
April 19, 2011 at 6:28 pm
Thanks ModestyBlaise84 I will give this a try.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply