November 17, 2010 at 5:58 pm
Basic problem is to calculate Gaps over a range of days and sum up a metric of 60 minute gaps per day per clinic.
The code below creates my auxiliary table of times in 10 minute increments over a 24 hour day as well as an Appt table.
I'm creating my gaps succesfully (as far as I can tell) but I'm losing the granularity I need (which clinicID and which Day) for each row.
The resulting output I eventually would like to see would be something like
ClinicID aDate Blocks*
1 20101115 9
1 20101116 3
2 20101115 5
2 20101116 4
*not actual values
To see the base behaivour just insert the rows for 20101115 and run the code as is. This will show you the Gaps for a single day regardless of clinic or provider
USE tempdb
IF OBJECT_ID('#times') IS NOT NULL
DROP TABLE #times;
CREATE TABLE #times
(
id Int,
hr Int
)
Insert into #times values(1,0)
Insert into #times values(2,10)
Insert into #times values(3,20)
Insert into #times values(4,30)
Insert into #times values(5,40)
Insert into #times values(6,50)
Insert into #times values(7,100)
Insert into #times values(8,110)
Insert into #times values(9,120)
Insert into #times values(10,130)
Insert into #times values(11,140)
Insert into #times values(12,150)
Insert into #times values(13,200)
Insert into #times values(14,210)
Insert into #times values(15,220)
Insert into #times values(16,230)
Insert into #times values(17,240)
Insert into #times values(18,250)
Insert into #times values(19,300)
Insert into #times values(20,310)
Insert into #times values(21,320)
Insert into #times values(22,330)
Insert into #times values(23,340)
Insert into #times values(24,350)
Insert into #times values(25,400)
Insert into #times values(26,410)
Insert into #times values(27,420)
Insert into #times values(28,430)
Insert into #times values(29,440)
Insert into #times values(30,450)
Insert into #times values(31,500)
Insert into #times values(32,510)
Insert into #times values(33,520)
Insert into #times values(34,530)
Insert into #times values(35,540)
Insert into #times values(36,550)
Insert into #times values(37,600)
Insert into #times values(38,610)
Insert into #times values(39,620)
Insert into #times values(40,630)
Insert into #times values(41,640)
Insert into #times values(42,650)
Insert into #times values(43,700)
Insert into #times values(44,710)
Insert into #times values(45,720)
Insert into #times values(46,730)
Insert into #times values(47,740)
Insert into #times values(48,750)
Insert into #times values(49,800)
Insert into #times values(50,810)
Insert into #times values(51,820)
Insert into #times values(52,830)
Insert into #times values(53,840)
Insert into #times values(54,850)
Insert into #times values(55,900)
Insert into #times values(56,910)
Insert into #times values(57,920)
Insert into #times values(58,930)
Insert into #times values(59,940)
Insert into #times values(60,950)
Insert into #times values(61,1000)
Insert into #times values(62,1010)
Insert into #times values(63,1020)
Insert into #times values(64,1030)
Insert into #times values(65,1040)
Insert into #times values(66,1050)
Insert into #times values(67,1100)
Insert into #times values(68,1110)
Insert into #times values(69,1120)
Insert into #times values(70,1130)
Insert into #times values(71,1140)
Insert into #times values(72,1150)
Insert into #times values(73,1200)
Insert into #times values(74,1210)
Insert into #times values(75,1220)
Insert into #times values(76,1230)
Insert into #times values(77,1240)
Insert into #times values(78,1250)
Insert into #times values(79,1300)
Insert into #times values(80,1310)
Insert into #times values(81,1320)
Insert into #times values(82,1330)
Insert into #times values(83,1340)
Insert into #times values(84,1350)
Insert into #times values(85,1400)
Insert into #times values(86,1410)
Insert into #times values(87,1420)
Insert into #times values(88,1430)
Insert into #times values(89,1440)
Insert into #times values(90,1450)
Insert into #times values(91,1500)
Insert into #times values(92,1510)
Insert into #times values(93,1520)
Insert into #times values(94,1530)
Insert into #times values(95,1540)
Insert into #times values(96,1550)
Insert into #times values(97,1600)
Insert into #times values(98,1610)
Insert into #times values(99,1620)
Insert into #times values(100,1630)
Insert into #times values(101,1640)
Insert into #times values(102,1650)
Insert into #times values(103,1700)
Insert into #times values(104,1710)
Insert into #times values(105,1720)
Insert into #times values(106,1730)
Insert into #times values(107,1740)
Insert into #times values(108,1750)
Insert into #times values(109,1800)
Insert into #times values(110,1810)
Insert into #times values(111,1820)
Insert into #times values(112,1830)
Insert into #times values(113,1840)
Insert into #times values(114,1850)
Insert into #times values(115,1900)
Insert into #times values(116,1910)
Insert into #times values(117,1920)
Insert into #times values(118,1930)
Insert into #times values(119,1940)
Insert into #times values(120,1950)
Insert into #times values(121,2000)
Insert into #times values(122,2010)
Insert into #times values(123,2020)
Insert into #times values(124,2030)
Insert into #times values(125,2040)
Insert into #times values(126,2050)
Insert into #times values(127,2100)
Insert into #times values(128,2110)
Insert into #times values(129,2120)
Insert into #times values(130,2130)
Insert into #times values(131,2140)
Insert into #times values(132,2150)
Insert into #times values(133,2200)
Insert into #times values(134,2210)
Insert into #times values(135,2220)
Insert into #times values(136,2230)
Insert into #times values(137,2240)
Insert into #times values(138,2250)
Insert into #times values(139,2300)
Insert into #times values(140,2310)
Insert into #times values(141,2320)
Insert into #times values(142,2330)
Insert into #times values(143,2340)
Insert into #times values(144,2350)
USE tempdb
IF OBJECT_ID('#Appts') IS NOT NULL
DROP TABLE #appts;
CREATE TABLE #Appts
(
startT int,
endT int,
id int,
aDate int,
clinicID int
)
Insert into #Appts(startT,endT,id,aDate,clinicID) values(0,430,1,20101115,1)
Insert into #Appts(startT,endT,id,aDate,clinicID) values(700,730,1,20101115,1)
Insert into #Appts(startT,endT,id,aDate,clinicID) values(700,830,22,20101115,1)
Insert into #Appts(startT,endT,id,aDate,clinicID) values(900,940,23,20101115,1)
Insert into #Appts(startT,endT,id,aDate,clinicID) values(1000,1100,8,20101115,2)
Insert into #Appts(startT,endT,id,aDate,clinicID) values(1300,1330,15,20101115,2)
Insert into #Appts(startT,endT,id,aDate,clinicID) values(1400,1450,2,20101115,1)
Insert into #Appts(startT,endT,id,aDate,clinicID) values(1500,1530,6,20101115,1)
Insert into #Appts(startT,endT,id,aDate,clinicID) values(1430,1630,14,20101115,2)
Insert into #Appts(startT,endT,id,aDate,clinicID) values(0,430,1,20101115,1)
---NEXT DAY ROWS (DONT INSERT THESE IF STUDYING BASE BEHAIVOUR)
Insert into #Appts(startT,endT,id,aDate,clinicID) values(710,750,1,20101116,2)
Insert into #Appts(startT,endT,id,aDate,clinicID) values(600,630,22,20101116,2)
Insert into #Appts(startT,endT,id,aDate,clinicID) values(900,1000,23,20101116,1)
Insert into #Appts(startT,endT,id,aDate,clinicID) values(1020,1110,8,20101116,2)
Insert into #Appts(startT,endT,id,aDate,clinicID) values(1340,1350,15,20101116,1)
Insert into #Appts(startT,endT,id,aDate,clinicID) values(1400,1450,2,20101116,2)
Insert into #Appts(startT,endT,id,aDate,clinicID) values(1500,1530,6,20101116,1)
Insert into #Appts(startT,endT,id,aDate,clinicID) values(1430,1630,14,20101116,1)
-----------------------------------------------------------------
WITH CTE1 AS
(
SELECT startT,endT,#Appts.id as apptID,#times.hr,#times.id as idx
FROM #Appts
CROSS APPLY
(
SELECT #times.id as idx,hr
FROM #times
WHERE #times.hr >= #Appts.startT AND #times.hr <= #Appts.endT
) AS T
FULL OUTER JOIN #times on T.idx = #times.id
WHERE #appts.id IS NULL
),
CTE2 AS
(
SELECT
MIN(idx)-1 AS start_range,
MAX(idx)+1 AS end_range,
(select hr from #times where #times.id=MIN(idx)-1) as start_time,
isnull((select hr from #times where #times.id=MAX(idx)+1),2400) as end_time,
((MAX(idx)+1)-(MIN(idx)-1)) *10 as mins,
(((MAX(idx)+1)-(MIN(idx)-1)) *10) /60 as blocks
FROM
(Select idx,idx-ROW_NUMBER() OVER (order by idx) as grp FROM CTE1) as D
GROUP BY grp
)
SELECT * FROM CTE2
November 17, 2010 at 7:49 pm
Blocks = # of hours for the gap?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 17, 2010 at 7:51 pm
Yes, Blocks = mins/60
So a gap of 150 minutes should equal 2 blocks
Here are the results if you just consider APPTs for 20101115 regardless of any other factors
start_range end_range start_time end_time mins blocks
----------- ----------- ----------- ----------- ----------- -----------
28 43 430 700 150 2
52 55 830 900 30 0
59 61 940 1000 20 0
67 79 1100 1300 120 2
82 85 1330 1400 30 0
100 145 1630 2400 450 7
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply