How to get Median length of stay grouped by YearMonth

Question

How to get Median length of stay grouped by YearMonth

NineIron

SSChampion

Points: 12529
More actions
December 1, 2015 at 7:59 am

#322941

This first bit of code is how I can get the median from my data but, I need to group it by YearMonth.
select
(
(select MAX(LOSInMinutes) from
(select top 50 percent LOSInMinutes
from #T
where LOSInMinutes>0
order by LOSInMinutes) a)
+
(select MIN(LOSInMinutes) from
(select top 50 percent LOSInMinutes
from #T
where LOSInMinutes>0
order by LOSInMinutes desc) b)
) / 2 AS 'Median'
create table #TEST
(
AccountNumber,
UnitNumber,
ErServiceDateTime,
ErDepartDateTime,
LOSInMinutes,
YearMonth
)
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73853923','000009','2015-01-20 13:53:00.000','2015-01-20 15:57:00.000',124,'2015-01')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73315549','000009','2014-12-09 10:52:00.000','2014-12-09 11:56:00.000',64,'2014-12')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75062893','000009','2015-04-18 10:28:00.000','2015-04-18 11:02:00.000',34,'2015-04')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76552487','000010','2015-08-02 11:21:00.000','2015-08-02 11:56:00.000',35,'2015-08')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73531373','000083','2014-12-26 17:50:00.000','2014-12-26 21:40:00.000',230,'2014-12')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75004303','000171','2015-04-14 18:05:00.000','2015-04-14 22:30:00.000',265,'2015-04')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74906885','000284','2015-04-07 20:44:00.000','2015-04-08 01:28:00.000',284,'2015-04')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76996903','000314','2015-09-02 12:56:00.000','2015-09-02 14:05:00.000',69,'2015-09')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76348936','000329','2015-07-18 23:40:00.000','2015-07-19 05:28:00.000',348,'2015-07')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75529597','000329','2015-05-21 05:36:00.000','2015-05-21 06:56:00.000',80,'2015-05')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74554466','000329','2015-03-14 11:07:00.000','2015-03-15 07:47:00.000',1240,'2015-03')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75064152','000329','2015-04-18 14:56:00.000','2015-04-18 16:50:00.000',114,'2015-04')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75234848','000329','2015-04-30 20:51:00.000','2015-05-01 17:48:00.000',1257,'2015-04')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75363311','000329','2015-05-08 20:53:00.000','2015-05-09 04:26:00.000',453,'2015-05')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75962118','000449','2015-06-19 22:34:00.000','2015-06-20 09:50:00.000',676,'2015-06')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75862636','000449','2015-06-13 10:39:00.000','2015-06-13 15:52:00.000',313,'2015-06')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75651406','000449','2015-05-30 18:47:00.000','2015-05-31 10:22:00.000',935,'2015-05')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73407362','000536','2014-12-16 10:33:00.000','2014-12-16 13:26:00.000',173,'2014-12')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73821410','000837','2015-01-17 20:22:00.000','2015-01-18 14:10:00.000',1068,'2015-01')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76004901','000966','2015-06-23 17:15:00.000','2015-06-23 21:50:00.000',275,'2015-06')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '77033751','000966','2015-09-04 14:47:00.000','2015-09-04 23:57:00.000',550,'2015-09')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76549699','000966','2015-08-01 12:43:00.000','2015-08-01 15:24:00.000',161,'2015-08')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '77229302','001013','2015-09-20 16:15:00.000','2015-09-20 18:34:00.000',139,'2015-09')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74723055','001042','2015-03-26 14:20:00.000','2015-03-26 19:23:00.000',303,'2015-03')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74685528','001042','2015-03-24 13:45:00.000','2015-03-24 17:59:00.000',254,'2015-03')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75037724','001097','2015-04-16 16:26:00.000','2015-04-17 00:17:00.000',471,'2015-04')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75309210','001097','2015-05-05 19:33:00.000','2015-05-06 11:35:00.000',962,'2015-05')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74288898','001131','2015-02-24 17:47:00.000','2015-02-24 23:40:00.000',353,'2015-02')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '77250743','001173','2015-09-21 21:41:00.000','2015-09-22 14:10:00.000',989,'2015-09')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76648123','001294','2015-08-09 12:16:00.000','2015-08-09 14:38:00.000',142,'2015-08')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74956815','001422','2015-04-10 16:34:00.000','2015-04-10 21:35:00.000',301,'2015-04')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74450407','001514','2015-03-07 06:24:00.000','2015-03-07 08:00:00.000',96,'2015-03')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72678052','001608','2014-10-22 22:37:00.000','2014-10-23 04:03:00.000',326,'2014-10')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73459214','001608','2014-12-19 11:14:00.000','2014-12-19 16:25:00.000',311,'2014-12')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73402930','001612','2014-12-16 07:21:00.000','2014-12-16 12:42:00.000',321,'2014-12')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75347707','001612','2015-05-08 08:11:00.000','2015-05-08 09:15:00.000',64,'2015-05')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76254460','001629','2015-07-12 07:55:00.000','2015-07-12 12:19:00.000',264,'2015-07')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76058814','001868','2015-06-26 23:46:00.000','2015-06-27 00:27:00.000',41,'2015-06')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74942946','002109','2015-04-09 23:26:00.000','2015-04-10 14:46:00.000',920,'2015-04')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73599717','002117','2015-01-01 14:27:00.000','2015-01-01 18:08:00.000',221,'2015-01')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75886977','002117','2015-06-15 21:19:00.000','2015-06-15 22:45:00.000',86,'2015-06')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75988222','002117','2015-06-22 21:49:00.000','2015-06-23 00:55:00.000',186,'2015-06')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76406154','002235','2015-07-22 17:59:00.000','2015-07-23 09:50:00.000',951,'2015-07')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74666442','002235','2015-03-23 14:15:00.000','2015-03-23 19:28:00.000',313,'2015-03')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74573495','002235','2015-03-16 15:51:00.000','2015-03-17 12:29:00.000',1238,'2015-03')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76103674','002235','2015-06-30 15:55:00.000','2015-06-30 22:52:00.000',417,'2015-06')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73540801','002260','2014-12-29 06:01:00.000','2014-12-29 10:05:00.000',244,'2014-12')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73825466','002260','2015-01-19 04:23:00.000','2015-01-19 12:13:00.000',470,'2015-01')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73877750','002260','2015-01-21 16:04:00.000','2015-01-22 10:15:00.000',1091,'2015-01')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74858708','002260','2015-04-03 18:03:00.000','2015-04-05 15:46:00.000',2743,'2015-04')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72429800','002260','2014-10-04 10:38:00.000','2014-10-04 12:50:00.000',132,'2014-10')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75080875','002260','2015-04-20 18:20:00.000','2015-04-21 07:08:00.000',768,'2015-04')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74689663','002260','2015-03-24 16:52:00.000','2015-03-25 02:41:00.000',589,'2015-03')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73721300','002260','2015-01-11 09:19:00.000','2015-01-11 11:35:00.000',136,'2015-01')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73383102','002260','2014-12-14 16:05:00.000','2014-12-14 23:14:00.000',429,'2014-12')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76088730','002260','2015-06-30 06:11:00.000','2015-06-30 12:08:00.000',357,'2015-06')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74102701','002279','2015-02-09 12:47:00.000','2015-02-10 20:37:00.000',1910,'2015-02')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75355840','002279','2015-05-08 12:39:00.000','2015-05-08 20:55:00.000',496,'2015-05')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72790227','002389','2014-10-30 14:01:00.000','2014-10-30 17:20:00.000',199,'2014-10')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74595758','002415','2015-03-18 06:41:00.000','2015-03-18 08:30:00.000',109,'2015-03')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72431472','002425','2014-10-04 16:26:00.000','2014-10-04 18:29:00.000',123,'2014-10')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74008186','002425','2015-02-01 07:57:00.000','2015-02-01 09:40:00.000',103,'2015-02')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76181101','002440','2015-07-06 20:18:00.000','2015-07-07 03:32:00.000',434,'2015-07')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73827698','002611','2015-01-19 09:47:00.000','2015-01-19 17:50:00.000',483,'2015-01')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74890903','002611','2015-04-07 09:44:00.000','2015-04-07 14:25:00.000',281,'2015-04')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72419553','002611','2014-10-03 12:22:00.000','2014-10-03 15:45:00.000',203,'2014-10')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72502451','002611','2014-10-09 14:08:00.000','2014-10-10 14:10:00.000',1442,'2014-10')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72963628','002611','2014-11-12 04:36:00.000','2014-11-12 08:00:00.000',204,'2014-11')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73520789','002611','2014-12-25 20:46:00.000','2014-12-26 09:00:00.000',734,'2014-12')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74175287','002611','2015-02-16 06:03:00.000','2015-02-17 16:10:00.000',2047,'2015-02')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74509126','002611','2015-03-11 14:13:00.000','2015-03-11 16:19:00.000',126,'2015-03')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72797132','002611','2014-10-30 22:49:00.000','2014-10-31 04:33:00.000',344,'2014-10')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73384087','002611','2014-12-14 23:22:00.000','2014-12-15 13:45:00.000',863,'2014-12')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76062633','002611','2015-06-27 15:37:00.000','2015-06-27 21:01:00.000',324,'2015-06')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '77003959','002611','2015-09-02 18:55:00.000','2015-09-03 14:00:00.000',1145,'2015-09')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76380660','002611','2015-07-21 13:21:00.000','2015-07-21 21:30:00.000',489,'2015-07')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76588899','002616','2015-08-04 18:28:00.000','2015-08-04 22:47:00.000',259,'2015-08')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75723601','002616','2015-06-03 18:17:00.000','2015-06-03 19:20:00.000',63,'2015-06')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75082312','002687','2015-04-21 06:14:00.000','2015-04-21 06:19:00.000',5,'2015-04')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74098207','002754','2015-02-08 19:22:00.000','2015-02-09 19:25:00.000',1443,'2015-02')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72928920','002754','2014-11-08 16:39:00.000','2014-11-08 23:34:00.000',415,'2014-11')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74370918','002754','2015-03-02 13:42:00.000','2015-03-02 18:25:00.000',283,'2015-03')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73621542','002758','2015-01-03 15:52:00.000','2015-01-03 19:37:00.000',225,'2015-01')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76696691','002758','2015-08-12 14:55:00.000','2015-08-12 18:09:00.000',194,'2015-08')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73667852','002758','2015-01-07 10:55:00.000','2015-01-07 12:00:00.000',65,'2015-01')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '77276041','002772','2015-09-23 12:26:00.000','2015-09-23 15:44:00.000',198,'2015-09')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72930436','002772','2014-11-09 10:35:00.000','2014-11-09 16:53:00.000',378,'2014-11')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73519900','002772','2014-12-25 13:17:00.000','2014-12-25 16:16:00.000',179,'2014-12')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75479107','002829','2015-05-18 11:35:00.000','2015-05-18 13:30:00.000',115,'2015-05')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72449371','003137','2014-10-06 14:52:00.000','2014-10-06 21:53:00.000',421,'2014-10')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73214793','003137','2014-12-01 19:56:00.000','2014-12-02 02:11:00.000',375,'2014-12')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74852017','003137','2015-04-03 13:03:00.000','2015-04-03 16:29:00.000',206,'2015-04')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75783655','003137','2015-06-08 15:57:00.000','2015-06-08 23:35:00.000',458,'2015-06')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73962811','003242','2015-01-29 11:30:00.000','2015-01-29 23:00:00.000',690,'2015-01')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74028951','003242','2015-02-03 13:37:00.000','2015-02-04 15:10:00.000',1533,'2015-02')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75159251','003266','2015-04-26 17:15:00.000','2015-04-27 15:19:00.000',1324,'2015-04')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '77202480','003266','2015-09-17 16:56:00.000','2015-09-17 22:15:00.000',319,'2015-09')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73967511','003266','2015-01-29 14:46:00.000','2015-01-30 21:45:00.000',1859,'2015-01')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73916739','003266','2015-01-23 18:40:00.000','2015-01-23 23:51:00.000',311,'2015-01')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75865813','003266','2015-06-14 09:14:00.000','2015-06-14 13:35:00.000',261,'2015-06')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76928819','003266','2015-08-28 16:56:00.000','2015-08-28 23:05:00.000',369,'2015-08')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72419596','003502','2014-10-03 12:32:00.000','2014-10-03 17:37:00.000',305,'2014-10')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73470277','003521','2014-12-20 09:37:00.000','2014-12-20 10:08:00.000',31,'2014-12')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74392202','003664','2015-03-03 15:45:00.000','2015-03-03 23:15:00.000',450,'2015-03')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76704236','003698','2015-08-13 05:35:00.000','2015-08-13 08:33:00.000',178,'2015-08')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74748031','003829','2015-03-28 12:51:00.000','2015-03-28 16:06:00.000',195,'2015-03')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73420971','003829','2014-12-16 23:58:00.000','2014-12-17 16:28:00.000',990,'2014-12')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75118872','003829','2015-04-22 22:09:00.000','2015-04-23 16:21:00.000',1092,'2015-04')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74249337','003829','2015-02-21 08:14:00.000','2015-02-21 14:20:00.000',366,'2015-02')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75566964','003847','2015-05-23 11:46:00.000','2015-05-23 13:05:00.000',79,'2015-05')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73622956','003874','2015-01-04 08:23:00.000','2015-01-04 10:10:00.000',107,'2015-01')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74096174','003901','2015-02-08 08:31:00.000','2015-02-08 10:40:00.000',129,'2015-02')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73680395','003931','2015-01-07 18:19:00.000','2015-01-07 18:50:00.000',31,'2015-01')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74174507','003982','2015-02-15 16:34:00.000','2015-02-15 18:46:00.000',132,'2015-02')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73471478','004202','2014-12-20 11:52:00.000','2014-12-20 16:46:00.000',294,'2014-12')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73291658','004202','2014-12-07 13:00:00.000','2014-12-07 15:50:00.000',170,'2014-12')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74866018','004202','2015-04-05 12:04:00.000','2015-04-05 22:17:00.000',613,'2015-04')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75367546','004202','2015-05-09 18:41:00.000','2015-05-09 21:30:00.000',169,'2015-05')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72960548','004204','2014-11-11 15:24:00.000','2014-11-11 18:03:00.000',159,'2014-11')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73114021','004263','2014-11-22 19:39:00.000','2014-11-22 20:56:00.000',77,'2014-11')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73210216','004263','2014-12-01 15:31:00.000','2014-12-01 18:05:00.000',154,'2014-12')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74752225','004263','2015-03-29 14:45:00.000','2015-03-29 20:10:00.000',325,'2015-03')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75366736','004536','2015-05-09 13:16:00.000','2015-05-09 15:53:00.000',157,'2015-05')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76215308','004547','2015-07-08 21:04:00.000','2015-07-09 02:30:00.000',326,'2015-07')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72810511','004608','2014-10-31 16:24:00.000','2014-10-31 23:03:00.000',399,'2014-10')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74136397','004640','2015-02-11 18:07:00.000','2015-02-13 22:15:00.000',3128,'2015-02')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73906920','004640','2015-01-23 11:27:00.000','2015-01-23 15:45:00.000',258,'2015-01')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75651708','004708','2015-05-30 20:34:00.000','2015-05-30 21:50:00.000',76,'2015-05')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74280439','004856','2015-02-24 11:37:00.000','2015-02-24 19:02:00.000',445,'2015-02')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76637181','004856','2015-08-07 15:15:00.000','2015-08-07 21:16:00.000',361,'2015-08')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76063834','004938','2015-06-28 01:09:00.000','2015-06-28 15:08:00.000',839,'2015-06')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76726981','004938','2015-08-14 11:13:00.000','2015-08-14 14:59:00.000',226,'2015-08')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75554435','005053','2015-05-22 10:57:00.000','2015-05-22 17:35:00.000',398,'2015-05')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73780284','005200','2015-01-14 20:58:00.000','2015-01-15 09:05:00.000',727,'2015-01')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74963048','005375','2015-04-11 11:48:00.000','2015-04-11 13:20:00.000',92,'2015-04')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75567545','005396','2015-05-23 12:29:00.000','2015-05-23 18:55:00.000',386,'2015-05')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75564236','005428','2015-05-22 22:53:00.000','2015-05-22 23:30:00.000',37,'2015-05')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75569190','005428','2015-05-23 22:38:00.000','2015-05-24 05:15:00.000',397,'2015-05')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75411383','005428','2015-05-13 07:30:00.000','2015-05-13 10:34:00.000',184,'2015-05')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76798834','005428','2015-08-19 19:51:00.000','2015-08-19 23:19:00.000',208,'2015-08')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72786963','005666','2014-10-30 12:06:00.000','2014-10-30 14:20:00.000',134,'2014-10')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73041465','005713','2014-11-17 20:02:00.000','2014-11-17 23:00:00.000',178,'2014-11')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75573502','005714','2015-05-25 10:09:00.000','2015-05-25 13:52:00.000',223,'2015-05')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '77220488','005714','2015-09-18 19:37:00.000','2015-09-19 02:30:00.000',413,'2015-09')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76913951','005734','2015-08-27 20:28:00.000','2015-08-28 04:58:00.000',510,'2015-08')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75028369','005777','2015-04-16 10:28:00.000','2015-04-16 18:00:00.000',452,'2015-04')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72677021','005795','2014-10-22 19:24:00.000','2014-10-23 15:35:00.000',1211,'2014-10')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74036368','005795','2015-02-03 20:57:00.000','2015-02-04 07:15:00.000',618,'2015-02')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76151431','005798','2015-07-02 19:29:00.000','2015-07-02 22:59:00.000',210,'2015-07')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75463979','005837','2015-05-15 19:53:00.000','2015-05-15 22:59:00.000',186,'2015-05')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73616492','005888','2015-01-02 17:53:00.000','2015-01-02 21:21:00.000',208,'2015-01')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75808992','006119','2015-06-10 00:37:00.000','2015-06-10 05:27:00.000',290,'2015-06')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76405174','006119','2015-07-22 16:37:00.000','2015-07-23 02:05:00.000',568,'2015-07')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73721342','006119','2015-01-11 09:23:00.000','2015-01-11 10:40:00.000',77,'2015-01')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74437591','006123','2015-03-06 10:25:00.000','2015-03-06 16:18:00.000',353,'2015-03')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73979528','006184','2015-01-30 11:08:00.000','2015-02-01 10:30:00.000',2842,'2015-01')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72476026','006184','2014-10-08 09:06:00.000','2014-10-08 17:31:00.000',505,'2014-10')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75348070','006184','2015-05-08 08:30:00.000','2015-05-08 09:48:00.000',78,'2015-05')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72678281','006184','2014-10-23 03:18:00.000','2014-10-23 11:15:00.000',477,'2014-10')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72562321','006185','2014-10-15 10:09:00.000','2014-10-15 12:55:00.000',166,'2014-10')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74741703','006288','2015-03-27 16:01:00.000','2015-03-27 19:21:00.000',200,'2015-03')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73441838','006288','2014-12-18 11:03:00.000','2014-12-18 12:10:00.000',67,'2014-12')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73783844','006288','2015-01-15 09:31:00.000','2015-01-15 11:40:00.000',129,'2015-01')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75487541','006288','2015-05-18 15:38:00.000','2015-05-18 17:36:00.000',118,'2015-05')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73720541','006310','2015-01-11 00:14:00.000','2015-01-11 13:20:00.000',786,'2015-01')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74683495','006310','2015-03-24 12:02:00.000','2015-03-24 18:30:00.000',388,'2015-03')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73474451','006310','2014-12-21 12:32:00.000','2014-12-21 19:18:00.000',406,'2014-12')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74134947','006310','2015-02-11 16:35:00.000','2015-02-12 10:55:00.000',1100,'2015-02')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72763424','006310','2014-10-29 06:49:00.000','2014-10-29 11:22:00.000',273,'2014-10')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75284969','006310','2015-05-04 15:51:00.000','2015-05-04 23:40:00.000',469,'2015-05')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73264383','006310','2014-12-04 16:05:00.000','2014-12-05 00:15:00.000',490,'2014-12')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74833438','006310','2015-04-02 13:25:00.000','2015-04-02 22:18:00.000',533,'2015-04')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74272797','006391','2015-02-23 20:08:00.000','2015-02-23 22:52:00.000',164,'2015-02')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75160844','006462','2015-04-27 08:00:00.000','2015-04-27 15:44:00.000',464,'2015-04')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73719364','006462','2015-01-10 19:17:00.000','2015-01-11 11:30:00.000',973,'2015-01')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74068936','006474','2015-02-05 15:39:00.000','2015-02-07 19:18:00.000',3099,'2015-02')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72530837','006486','2014-10-12 17:58:00.000','2014-10-13 02:26:00.000',508,'2014-10')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76157235','006577','2015-07-04 07:33:00.000','2015-07-04 10:48:00.000',195,'2015-07')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74452809','006581','2015-03-07 10:57:00.000','2015-03-07 14:30:00.000',213,'2015-03')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73249201','006602','2014-12-03 18:43:00.000','2014-12-03 23:00:00.000',257,'2014-12')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73502497','006602','2014-12-23 12:52:00.000','2014-12-23 21:52:00.000',540,'2014-12')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73319269','006602','2014-12-09 13:04:00.000','2014-12-09 18:53:00.000',349,'2014-12')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76248338','006624','2015-07-10 19:48:00.000','2015-07-11 03:12:00.000',444,'2015-07')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '77169903','006707','2015-09-16 09:32:00.000','2015-09-16 15:00:00.000',328,'2015-09')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72895975','006709','2014-11-06 11:07:00.000','2014-11-06 18:13:00.000',426,'2014-11')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76154554','006709','2015-07-03 10:59:00.000','2015-07-03 18:30:00.000',451,'2015-07')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72604008','006783','2014-10-17 13:03:00.000','2014-10-17 14:05:00.000',62,'2014-10')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75470274','006918','2015-05-17 09:49:00.000','2015-05-18 11:14:00.000',1525,'2015-05')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74644350','007378','2015-03-20 16:51:00.000','2015-03-21 05:15:00.000',744,'2015-03')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75840136','007378','2015-06-11 15:29:00.000','2015-06-11 20:47:00.000',318,'2015-06')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76479666','007378','2015-07-28 14:41:00.000','2015-07-28 17:25:00.000',164,'2015-07')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76888051','007546','2015-08-26 13:32:00.000','2015-08-26 13:49:00.000',17,'2015-08')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72957865','007620','2014-11-11 12:23:00.000','2014-11-11 15:05:00.000',162,'2014-11')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74873791','007620','2015-04-06 10:34:00.000','2015-04-06 14:06:00.000',212,'2015-04')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74573002','007620','2015-03-16 15:32:00.000','2015-03-16 18:00:00.000',148,'2015-03')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73284422','007702','2014-12-05 18:47:00.000','2014-12-06 01:45:00.000',418,'2014-12')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75767790','007747','2015-06-07 20:19:00.000','2015-06-07 23:00:00.000',161,'2015-06')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74852548','007798','2015-04-03 13:42:00.000','2015-04-03 22:35:00.000',533,'2015-04')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76587795','007839','2015-08-04 17:05:00.000','2015-08-04 21:20:00.000',255,'2015-08')
insert into TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75968914','007856','2015-06-21 16:56:00.000','2015-06-21 19:24:00.000',148,'2015-06')

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Jacob Wilkins One Orange Chip Points: 27972 More actions · Answer 1

Your table definition lacked datatypes, and the INSERTs were into TEST, not #TEST, but I cleaned that up to get the following for the test data:

CREATE TABLE #TEST

(

AccountNumber VARCHAR(20) ,

UnitNumber VARCHAR(20) ,

ErServiceDateTime DATETIME ,

ErDepartDateTime DATETIME ,

LOSInMinutes INT ,

YearMonth CHAR(7)

);

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73853923','000009','2015-01-20 13:53:00.000','2015-01-20 15:57:00.000',124,'2015-01')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73315549','000009','2014-12-09 10:52:00.000','2014-12-09 11:56:00.000',64,'2014-12')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75062893','000009','2015-04-18 10:28:00.000','2015-04-18 11:02:00.000',34,'2015-04')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76552487','000010','2015-08-02 11:21:00.000','2015-08-02 11:56:00.000',35,'2015-08')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73531373','000083','2014-12-26 17:50:00.000','2014-12-26 21:40:00.000',230,'2014-12')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75004303','000171','2015-04-14 18:05:00.000','2015-04-14 22:30:00.000',265,'2015-04')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74906885','000284','2015-04-07 20:44:00.000','2015-04-08 01:28:00.000',284,'2015-04')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76996903','000314','2015-09-02 12:56:00.000','2015-09-02 14:05:00.000',69,'2015-09')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76348936','000329','2015-07-18 23:40:00.000','2015-07-19 05:28:00.000',348,'2015-07')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75529597','000329','2015-05-21 05:36:00.000','2015-05-21 06:56:00.000',80,'2015-05')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74554466','000329','2015-03-14 11:07:00.000','2015-03-15 07:47:00.000',1240,'2015-03')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75064152','000329','2015-04-18 14:56:00.000','2015-04-18 16:50:00.000',114,'2015-04')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75234848','000329','2015-04-30 20:51:00.000','2015-05-01 17:48:00.000',1257,'2015-04')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75363311','000329','2015-05-08 20:53:00.000','2015-05-09 04:26:00.000',453,'2015-05')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75962118','000449','2015-06-19 22:34:00.000','2015-06-20 09:50:00.000',676,'2015-06')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75862636','000449','2015-06-13 10:39:00.000','2015-06-13 15:52:00.000',313,'2015-06')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75651406','000449','2015-05-30 18:47:00.000','2015-05-31 10:22:00.000',935,'2015-05')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73407362','000536','2014-12-16 10:33:00.000','2014-12-16 13:26:00.000',173,'2014-12')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73821410','000837','2015-01-17 20:22:00.000','2015-01-18 14:10:00.000',1068,'2015-01')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76004901','000966','2015-06-23 17:15:00.000','2015-06-23 21:50:00.000',275,'2015-06')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '77033751','000966','2015-09-04 14:47:00.000','2015-09-04 23:57:00.000',550,'2015-09')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76549699','000966','2015-08-01 12:43:00.000','2015-08-01 15:24:00.000',161,'2015-08')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '77229302','001013','2015-09-20 16:15:00.000','2015-09-20 18:34:00.000',139,'2015-09')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74723055','001042','2015-03-26 14:20:00.000','2015-03-26 19:23:00.000',303,'2015-03')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74685528','001042','2015-03-24 13:45:00.000','2015-03-24 17:59:00.000',254,'2015-03')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75037724','001097','2015-04-16 16:26:00.000','2015-04-17 00:17:00.000',471,'2015-04')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75309210','001097','2015-05-05 19:33:00.000','2015-05-06 11:35:00.000',962,'2015-05')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74288898','001131','2015-02-24 17:47:00.000','2015-02-24 23:40:00.000',353,'2015-02')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '77250743','001173','2015-09-21 21:41:00.000','2015-09-22 14:10:00.000',989,'2015-09')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76648123','001294','2015-08-09 12:16:00.000','2015-08-09 14:38:00.000',142,'2015-08')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74956815','001422','2015-04-10 16:34:00.000','2015-04-10 21:35:00.000',301,'2015-04')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74450407','001514','2015-03-07 06:24:00.000','2015-03-07 08:00:00.000',96,'2015-03')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72678052','001608','2014-10-22 22:37:00.000','2014-10-23 04:03:00.000',326,'2014-10')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73459214','001608','2014-12-19 11:14:00.000','2014-12-19 16:25:00.000',311,'2014-12')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73402930','001612','2014-12-16 07:21:00.000','2014-12-16 12:42:00.000',321,'2014-12')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75347707','001612','2015-05-08 08:11:00.000','2015-05-08 09:15:00.000',64,'2015-05')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76254460','001629','2015-07-12 07:55:00.000','2015-07-12 12:19:00.000',264,'2015-07')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76058814','001868','2015-06-26 23:46:00.000','2015-06-27 00:27:00.000',41,'2015-06')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74942946','002109','2015-04-09 23:26:00.000','2015-04-10 14:46:00.000',920,'2015-04')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73599717','002117','2015-01-01 14:27:00.000','2015-01-01 18:08:00.000',221,'2015-01')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75886977','002117','2015-06-15 21:19:00.000','2015-06-15 22:45:00.000',86,'2015-06')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75988222','002117','2015-06-22 21:49:00.000','2015-06-23 00:55:00.000',186,'2015-06')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76406154','002235','2015-07-22 17:59:00.000','2015-07-23 09:50:00.000',951,'2015-07')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74666442','002235','2015-03-23 14:15:00.000','2015-03-23 19:28:00.000',313,'2015-03')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74573495','002235','2015-03-16 15:51:00.000','2015-03-17 12:29:00.000',1238,'2015-03')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76103674','002235','2015-06-30 15:55:00.000','2015-06-30 22:52:00.000',417,'2015-06')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73540801','002260','2014-12-29 06:01:00.000','2014-12-29 10:05:00.000',244,'2014-12')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73825466','002260','2015-01-19 04:23:00.000','2015-01-19 12:13:00.000',470,'2015-01')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73877750','002260','2015-01-21 16:04:00.000','2015-01-22 10:15:00.000',1091,'2015-01')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74858708','002260','2015-04-03 18:03:00.000','2015-04-05 15:46:00.000',2743,'2015-04')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72429800','002260','2014-10-04 10:38:00.000','2014-10-04 12:50:00.000',132,'2014-10')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75080875','002260','2015-04-20 18:20:00.000','2015-04-21 07:08:00.000',768,'2015-04')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74689663','002260','2015-03-24 16:52:00.000','2015-03-25 02:41:00.000',589,'2015-03')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73721300','002260','2015-01-11 09:19:00.000','2015-01-11 11:35:00.000',136,'2015-01')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73383102','002260','2014-12-14 16:05:00.000','2014-12-14 23:14:00.000',429,'2014-12')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76088730','002260','2015-06-30 06:11:00.000','2015-06-30 12:08:00.000',357,'2015-06')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74102701','002279','2015-02-09 12:47:00.000','2015-02-10 20:37:00.000',1910,'2015-02')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75355840','002279','2015-05-08 12:39:00.000','2015-05-08 20:55:00.000',496,'2015-05')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72790227','002389','2014-10-30 14:01:00.000','2014-10-30 17:20:00.000',199,'2014-10')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74595758','002415','2015-03-18 06:41:00.000','2015-03-18 08:30:00.000',109,'2015-03')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72431472','002425','2014-10-04 16:26:00.000','2014-10-04 18:29:00.000',123,'2014-10')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74008186','002425','2015-02-01 07:57:00.000','2015-02-01 09:40:00.000',103,'2015-02')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76181101','002440','2015-07-06 20:18:00.000','2015-07-07 03:32:00.000',434,'2015-07')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73827698','002611','2015-01-19 09:47:00.000','2015-01-19 17:50:00.000',483,'2015-01')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74890903','002611','2015-04-07 09:44:00.000','2015-04-07 14:25:00.000',281,'2015-04')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72419553','002611','2014-10-03 12:22:00.000','2014-10-03 15:45:00.000',203,'2014-10')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72502451','002611','2014-10-09 14:08:00.000','2014-10-10 14:10:00.000',1442,'2014-10')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72963628','002611','2014-11-12 04:36:00.000','2014-11-12 08:00:00.000',204,'2014-11')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73520789','002611','2014-12-25 20:46:00.000','2014-12-26 09:00:00.000',734,'2014-12')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74175287','002611','2015-02-16 06:03:00.000','2015-02-17 16:10:00.000',2047,'2015-02')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74509126','002611','2015-03-11 14:13:00.000','2015-03-11 16:19:00.000',126,'2015-03')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72797132','002611','2014-10-30 22:49:00.000','2014-10-31 04:33:00.000',344,'2014-10')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73384087','002611','2014-12-14 23:22:00.000','2014-12-15 13:45:00.000',863,'2014-12')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76062633','002611','2015-06-27 15:37:00.000','2015-06-27 21:01:00.000',324,'2015-06')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '77003959','002611','2015-09-02 18:55:00.000','2015-09-03 14:00:00.000',1145,'2015-09')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76380660','002611','2015-07-21 13:21:00.000','2015-07-21 21:30:00.000',489,'2015-07')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76588899','002616','2015-08-04 18:28:00.000','2015-08-04 22:47:00.000',259,'2015-08')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75723601','002616','2015-06-03 18:17:00.000','2015-06-03 19:20:00.000',63,'2015-06')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75082312','002687','2015-04-21 06:14:00.000','2015-04-21 06:19:00.000',5,'2015-04')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74098207','002754','2015-02-08 19:22:00.000','2015-02-09 19:25:00.000',1443,'2015-02')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72928920','002754','2014-11-08 16:39:00.000','2014-11-08 23:34:00.000',415,'2014-11')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74370918','002754','2015-03-02 13:42:00.000','2015-03-02 18:25:00.000',283,'2015-03')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73621542','002758','2015-01-03 15:52:00.000','2015-01-03 19:37:00.000',225,'2015-01')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76696691','002758','2015-08-12 14:55:00.000','2015-08-12 18:09:00.000',194,'2015-08')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73667852','002758','2015-01-07 10:55:00.000','2015-01-07 12:00:00.000',65,'2015-01')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '77276041','002772','2015-09-23 12:26:00.000','2015-09-23 15:44:00.000',198,'2015-09')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72930436','002772','2014-11-09 10:35:00.000','2014-11-09 16:53:00.000',378,'2014-11')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73519900','002772','2014-12-25 13:17:00.000','2014-12-25 16:16:00.000',179,'2014-12')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75479107','002829','2015-05-18 11:35:00.000','2015-05-18 13:30:00.000',115,'2015-05')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72449371','003137','2014-10-06 14:52:00.000','2014-10-06 21:53:00.000',421,'2014-10')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73214793','003137','2014-12-01 19:56:00.000','2014-12-02 02:11:00.000',375,'2014-12')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74852017','003137','2015-04-03 13:03:00.000','2015-04-03 16:29:00.000',206,'2015-04')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75783655','003137','2015-06-08 15:57:00.000','2015-06-08 23:35:00.000',458,'2015-06')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73962811','003242','2015-01-29 11:30:00.000','2015-01-29 23:00:00.000',690,'2015-01')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74028951','003242','2015-02-03 13:37:00.000','2015-02-04 15:10:00.000',1533,'2015-02')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75159251','003266','2015-04-26 17:15:00.000','2015-04-27 15:19:00.000',1324,'2015-04')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '77202480','003266','2015-09-17 16:56:00.000','2015-09-17 22:15:00.000',319,'2015-09')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73967511','003266','2015-01-29 14:46:00.000','2015-01-30 21:45:00.000',1859,'2015-01')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73916739','003266','2015-01-23 18:40:00.000','2015-01-23 23:51:00.000',311,'2015-01')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75865813','003266','2015-06-14 09:14:00.000','2015-06-14 13:35:00.000',261,'2015-06')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76928819','003266','2015-08-28 16:56:00.000','2015-08-28 23:05:00.000',369,'2015-08')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72419596','003502','2014-10-03 12:32:00.000','2014-10-03 17:37:00.000',305,'2014-10')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73470277','003521','2014-12-20 09:37:00.000','2014-12-20 10:08:00.000',31,'2014-12')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74392202','003664','2015-03-03 15:45:00.000','2015-03-03 23:15:00.000',450,'2015-03')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76704236','003698','2015-08-13 05:35:00.000','2015-08-13 08:33:00.000',178,'2015-08')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74748031','003829','2015-03-28 12:51:00.000','2015-03-28 16:06:00.000',195,'2015-03')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73420971','003829','2014-12-16 23:58:00.000','2014-12-17 16:28:00.000',990,'2014-12')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75118872','003829','2015-04-22 22:09:00.000','2015-04-23 16:21:00.000',1092,'2015-04')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74249337','003829','2015-02-21 08:14:00.000','2015-02-21 14:20:00.000',366,'2015-02')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75566964','003847','2015-05-23 11:46:00.000','2015-05-23 13:05:00.000',79,'2015-05')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73622956','003874','2015-01-04 08:23:00.000','2015-01-04 10:10:00.000',107,'2015-01')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74096174','003901','2015-02-08 08:31:00.000','2015-02-08 10:40:00.000',129,'2015-02')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73680395','003931','2015-01-07 18:19:00.000','2015-01-07 18:50:00.000',31,'2015-01')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74174507','003982','2015-02-15 16:34:00.000','2015-02-15 18:46:00.000',132,'2015-02')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73471478','004202','2014-12-20 11:52:00.000','2014-12-20 16:46:00.000',294,'2014-12')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73291658','004202','2014-12-07 13:00:00.000','2014-12-07 15:50:00.000',170,'2014-12')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74866018','004202','2015-04-05 12:04:00.000','2015-04-05 22:17:00.000',613,'2015-04')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75367546','004202','2015-05-09 18:41:00.000','2015-05-09 21:30:00.000',169,'2015-05')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72960548','004204','2014-11-11 15:24:00.000','2014-11-11 18:03:00.000',159,'2014-11')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73114021','004263','2014-11-22 19:39:00.000','2014-11-22 20:56:00.000',77,'2014-11')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73210216','004263','2014-12-01 15:31:00.000','2014-12-01 18:05:00.000',154,'2014-12')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74752225','004263','2015-03-29 14:45:00.000','2015-03-29 20:10:00.000',325,'2015-03')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75366736','004536','2015-05-09 13:16:00.000','2015-05-09 15:53:00.000',157,'2015-05')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76215308','004547','2015-07-08 21:04:00.000','2015-07-09 02:30:00.000',326,'2015-07')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72810511','004608','2014-10-31 16:24:00.000','2014-10-31 23:03:00.000',399,'2014-10')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74136397','004640','2015-02-11 18:07:00.000','2015-02-13 22:15:00.000',3128,'2015-02')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73906920','004640','2015-01-23 11:27:00.000','2015-01-23 15:45:00.000',258,'2015-01')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75651708','004708','2015-05-30 20:34:00.000','2015-05-30 21:50:00.000',76,'2015-05')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74280439','004856','2015-02-24 11:37:00.000','2015-02-24 19:02:00.000',445,'2015-02')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76637181','004856','2015-08-07 15:15:00.000','2015-08-07 21:16:00.000',361,'2015-08')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76063834','004938','2015-06-28 01:09:00.000','2015-06-28 15:08:00.000',839,'2015-06')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76726981','004938','2015-08-14 11:13:00.000','2015-08-14 14:59:00.000',226,'2015-08')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75554435','005053','2015-05-22 10:57:00.000','2015-05-22 17:35:00.000',398,'2015-05')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73780284','005200','2015-01-14 20:58:00.000','2015-01-15 09:05:00.000',727,'2015-01')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74963048','005375','2015-04-11 11:48:00.000','2015-04-11 13:20:00.000',92,'2015-04')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75567545','005396','2015-05-23 12:29:00.000','2015-05-23 18:55:00.000',386,'2015-05')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75564236','005428','2015-05-22 22:53:00.000','2015-05-22 23:30:00.000',37,'2015-05')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75569190','005428','2015-05-23 22:38:00.000','2015-05-24 05:15:00.000',397,'2015-05')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75411383','005428','2015-05-13 07:30:00.000','2015-05-13 10:34:00.000',184,'2015-05')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76798834','005428','2015-08-19 19:51:00.000','2015-08-19 23:19:00.000',208,'2015-08')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72786963','005666','2014-10-30 12:06:00.000','2014-10-30 14:20:00.000',134,'2014-10')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73041465','005713','2014-11-17 20:02:00.000','2014-11-17 23:00:00.000',178,'2014-11')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75573502','005714','2015-05-25 10:09:00.000','2015-05-25 13:52:00.000',223,'2015-05')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '77220488','005714','2015-09-18 19:37:00.000','2015-09-19 02:30:00.000',413,'2015-09')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76913951','005734','2015-08-27 20:28:00.000','2015-08-28 04:58:00.000',510,'2015-08')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75028369','005777','2015-04-16 10:28:00.000','2015-04-16 18:00:00.000',452,'2015-04')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72677021','005795','2014-10-22 19:24:00.000','2014-10-23 15:35:00.000',1211,'2014-10')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74036368','005795','2015-02-03 20:57:00.000','2015-02-04 07:15:00.000',618,'2015-02')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76151431','005798','2015-07-02 19:29:00.000','2015-07-02 22:59:00.000',210,'2015-07')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75463979','005837','2015-05-15 19:53:00.000','2015-05-15 22:59:00.000',186,'2015-05')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73616492','005888','2015-01-02 17:53:00.000','2015-01-02 21:21:00.000',208,'2015-01')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75808992','006119','2015-06-10 00:37:00.000','2015-06-10 05:27:00.000',290,'2015-06')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76405174','006119','2015-07-22 16:37:00.000','2015-07-23 02:05:00.000',568,'2015-07')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73721342','006119','2015-01-11 09:23:00.000','2015-01-11 10:40:00.000',77,'2015-01')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74437591','006123','2015-03-06 10:25:00.000','2015-03-06 16:18:00.000',353,'2015-03')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73979528','006184','2015-01-30 11:08:00.000','2015-02-01 10:30:00.000',2842,'2015-01')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72476026','006184','2014-10-08 09:06:00.000','2014-10-08 17:31:00.000',505,'2014-10')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75348070','006184','2015-05-08 08:30:00.000','2015-05-08 09:48:00.000',78,'2015-05')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72678281','006184','2014-10-23 03:18:00.000','2014-10-23 11:15:00.000',477,'2014-10')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72562321','006185','2014-10-15 10:09:00.000','2014-10-15 12:55:00.000',166,'2014-10')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74741703','006288','2015-03-27 16:01:00.000','2015-03-27 19:21:00.000',200,'2015-03')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73441838','006288','2014-12-18 11:03:00.000','2014-12-18 12:10:00.000',67,'2014-12')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73783844','006288','2015-01-15 09:31:00.000','2015-01-15 11:40:00.000',129,'2015-01')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75487541','006288','2015-05-18 15:38:00.000','2015-05-18 17:36:00.000',118,'2015-05')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73720541','006310','2015-01-11 00:14:00.000','2015-01-11 13:20:00.000',786,'2015-01')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74683495','006310','2015-03-24 12:02:00.000','2015-03-24 18:30:00.000',388,'2015-03')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73474451','006310','2014-12-21 12:32:00.000','2014-12-21 19:18:00.000',406,'2014-12')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74134947','006310','2015-02-11 16:35:00.000','2015-02-12 10:55:00.000',1100,'2015-02')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72763424','006310','2014-10-29 06:49:00.000','2014-10-29 11:22:00.000',273,'2014-10')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75284969','006310','2015-05-04 15:51:00.000','2015-05-04 23:40:00.000',469,'2015-05')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73264383','006310','2014-12-04 16:05:00.000','2014-12-05 00:15:00.000',490,'2014-12')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74833438','006310','2015-04-02 13:25:00.000','2015-04-02 22:18:00.000',533,'2015-04')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74272797','006391','2015-02-23 20:08:00.000','2015-02-23 22:52:00.000',164,'2015-02')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75160844','006462','2015-04-27 08:00:00.000','2015-04-27 15:44:00.000',464,'2015-04')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73719364','006462','2015-01-10 19:17:00.000','2015-01-11 11:30:00.000',973,'2015-01')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74068936','006474','2015-02-05 15:39:00.000','2015-02-07 19:18:00.000',3099,'2015-02')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72530837','006486','2014-10-12 17:58:00.000','2014-10-13 02:26:00.000',508,'2014-10')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76157235','006577','2015-07-04 07:33:00.000','2015-07-04 10:48:00.000',195,'2015-07')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74452809','006581','2015-03-07 10:57:00.000','2015-03-07 14:30:00.000',213,'2015-03')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73249201','006602','2014-12-03 18:43:00.000','2014-12-03 23:00:00.000',257,'2014-12')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73502497','006602','2014-12-23 12:52:00.000','2014-12-23 21:52:00.000',540,'2014-12')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73319269','006602','2014-12-09 13:04:00.000','2014-12-09 18:53:00.000',349,'2014-12')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76248338','006624','2015-07-10 19:48:00.000','2015-07-11 03:12:00.000',444,'2015-07')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '77169903','006707','2015-09-16 09:32:00.000','2015-09-16 15:00:00.000',328,'2015-09')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72895975','006709','2014-11-06 11:07:00.000','2014-11-06 18:13:00.000',426,'2014-11')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76154554','006709','2015-07-03 10:59:00.000','2015-07-03 18:30:00.000',451,'2015-07')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72604008','006783','2014-10-17 13:03:00.000','2014-10-17 14:05:00.000',62,'2014-10')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75470274','006918','2015-05-17 09:49:00.000','2015-05-18 11:14:00.000',1525,'2015-05')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74644350','007378','2015-03-20 16:51:00.000','2015-03-21 05:15:00.000',744,'2015-03')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75840136','007378','2015-06-11 15:29:00.000','2015-06-11 20:47:00.000',318,'2015-06')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76479666','007378','2015-07-28 14:41:00.000','2015-07-28 17:25:00.000',164,'2015-07')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76888051','007546','2015-08-26 13:32:00.000','2015-08-26 13:49:00.000',17,'2015-08')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '72957865','007620','2014-11-11 12:23:00.000','2014-11-11 15:05:00.000',162,'2014-11')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74873791','007620','2015-04-06 10:34:00.000','2015-04-06 14:06:00.000',212,'2015-04')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74573002','007620','2015-03-16 15:32:00.000','2015-03-16 18:00:00.000',148,'2015-03')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '73284422','007702','2014-12-05 18:47:00.000','2014-12-06 01:45:00.000',418,'2014-12')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75767790','007747','2015-06-07 20:19:00.000','2015-06-07 23:00:00.000',161,'2015-06')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '74852548','007798','2015-04-03 13:42:00.000','2015-04-03 22:35:00.000',533,'2015-04')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '76587795','007839','2015-08-04 17:05:00.000','2015-08-04 21:20:00.000',255,'2015-08')

INSERT INTO #TEST(AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, LOSInMinutes, YearMonth) values( '75968914','007856','2015-06-21 16:56:00.000','2015-06-21 19:24:00.000',148,'2015-06')

The following was the first of the 2008-compatible median methods that my fingers started typing, and it at least shows how what you're wanting to do can be done. You might want to look at http://sqlperformance.com/2012/08/t-sql-queries/median to see if any of the other methods are more efficient when adapted for GROUP BY like the below code:

--You can use this to manually confirm the results

--of the median query

SELECT *

FROM #TEST

ORDER BY YearMonth ASC ,

LOSInMinutes ASC;

--The median query itself

WITH Medians

AS ( SELECT Ascending_RN = ROW_NUMBER() OVER ( PARTITION BY YearMonth ORDER BY LOSInMinutes ASC ) ,

Descending_RN = ROW_NUMBER() OVER ( PARTITION BY YearMonth ORDER BY LOSInMinutes DESC ) ,

YearMonth ,

LOSInMinutes

FROM #TEST

)

SELECT AVG(LOSInMinutes*1.0) ,

YearMonth

FROM Medians

WHERE Medians.Ascending_RN BETWEEN Medians.Descending_RN-1 AND Medians.Descending_RN+1

GROUP BY Medians.YearMonth;

DROP TABLE #TEST;

Cheers!

NineIron SSChampion Points: 12529 More actions · Answer 2

NineIron

SSChampion

Points: 12529

December 1, 2015 at 9:03 am

#1843235

Thanx. Sorry about the data types and stuff.

NineIron SSChampion Points: 12529 More actions · Answer 3

NineIron

SSChampion

Points: 12529

December 1, 2015 at 9:23 am

#1843247

Oops. Not all of the YearMonths show up.

Jacob Wilkins One Orange Chip Points: 27972 More actions · Answer 4

NineIron (12/1/2015)
Oops. Not all of the YearMonths show up.

Do you mean the result of the median query I posted with that sample data is missing rows for some YearMonths?

I see the months from 2014-10 to 2015-09 in the #TEST table, and one row for each of those in the output of the median query.

I'm probably misunderstanding what you're saying (maybe you mean you're missing rows when you run a similar query against your actual data?), but I just wanted to clarify.

Cheers!

GroverVivek SSCommitted Points: 1665 More actions · Answer 5

MedianYearMonth

315.500000 2014-10

191.0000002014-11

311.0000002014-12

284.5000002015-01

618.0000002015-02

303.0000002015-03

458.0000002015-04

185.0000002015-05

290.0000002015-06

391.0000002015-07

208.0000002015-08

328.0000002015-09

Regards
VG

NineIron SSChampion Points: 12529 More actions · Answer 6

My actual data has records from October, 2014 thru September, 2015. Our Fiscal Year. When I run the query I only get.............

(No column name)YearMonth

200.0000002014-12

216.0000002015-01

207.0000002015-04

196.0000002015-06

195.0000002015-08

199.0000002015-09

Jacob Wilkins One Orange Chip Points: 27972 More actions · Answer 7

That's the same date range covered by the test data, so I'm still not 100% clear. You're seeing correct results when run against the test data, but incorrect results when run against the actual data?

If that's the case, I'd have to see the actual data that's causing a problem, as well as the query you're running.

Cheers!

NineIron SSChampion Points: 12529 More actions · Answer 8

That's bizarre. Should it matter if there are LOSInMinutes=0?

NineIron SSChampion Points: 12529 More actions · Answer 9

I was able to get correct results with the TEST data but, not my actual. When I sort using your select query, I get records for each month.

Jacob Wilkins One Orange Chip Points: 27972 More actions · Answer 10

It's probably due to some funky behavior with the two ROW_NUMBER() functions and ties in the data set (I remember there being some funkiness there).

What's the result of the following query against the actual data (for one of the missing YearMonth values)?

SELECT Ascending_RN = ROW_NUMBER() OVER ( PARTITION BY YearMonth ORDER BY LOSInMinutes ASC ) ,

Descending_RN = ROW_NUMBER() OVER ( PARTITION BY YearMonth ORDER BY LOSInMinutes DESC ) ,

YearMonth ,

LOSInMinutes

FROM #TEST

WHERE YearMonth='2015-02'

You might want to use the same approach but with the 2005_B method from that article I cited. It should be free of any problems with ties.

Cheers!

NineIron SSChampion Points: 12529 More actions · Answer 11

There are 64K+ records. Mind if I attach a spreadsheet?

Here's the query. Basically, a copy of yours. I just changed #TEST to #T, the actual data. I also added the code to create #T. Funny but, if I exclude zero minutes, the 2014-12 and 2015-04 records show up. If I comment out that line of code, it excludes 2014-12 and 2015-04 records but, returns all the other months.

WITH Medians

AS

(

SELECT Ascending_RN = ROW_NUMBER() OVER ( PARTITION BY YearMonth ORDER BY LOSInMinutes ASC ) ,

Descending_RN = ROW_NUMBER() OVER ( PARTITION BY YearMonth ORDER BY LOSInMinutes DESC ) ,

YearMonth ,

LOSInMinutes

FROM #T

)

SELECT AVG(LOSInMinutes*1.0), YearMonth

FROM Medians

WHERE Medians.Ascending_RN BETWEEN Medians.Descending_RN-1 AND Medians.Descending_RN+1

GROUP BY Medians.YearMonth;

select AccountNumber, UnitNumber, ErServiceDateTime, ErDepartDateTime, datediff(minute,ErServiceDateTime, ErDepartDateTime) as 'LOSInMinutes',

convert(varchar(7), ErServiceDateTime, 121) as 'YearMonth'

into #T

from AbstractData

where ErServiceDateTime>='2014-10-01' and ErServiceDateTime<'2015-10-01'

and ErServiceDateTime is not null

and ErDepartDateTime is not null

--and datediff(minute,ErServiceDateTime, ErDepartDateTime)>0

NineIron SSChampion Points: 12529 More actions · Answer 12

It returns records. I'm going to try the other approach, as you suggest.

Jacob Wilkins One Orange Chip Points: 27972 More actions · Answer 13

I know it would return rows. The suspicion is that ties would mess with the relationship between the row number sequences and we would see that no rows had the required relationship between ascending and descending row numbers (the BETWEEN from the WHERE clause).

If there are too many rows in the result set to post, that's understandable; if you could post the result set, even as a spreadsheet, I would be interested in confirming/disconfirming that hypothesis.

Either way, I'll probably try to put together my own sample to confirm the behavior.

Cheers!

NineIron SSChampion Points: 12529 More actions · Answer 14

I've attached the actual data. Thanx again for your help.