October 3, 2016 at 6:37 am
I need to get a weekly count of records with Friday being the end of the week. So, the report will run on Friday and return the previous Friday through Thursday.
create table #Test
(
AccountNumber varchar(30),
DischargeDateTime datetime,
RegistrationDateTime datetime,
day_of_week char(1)
)
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090033386','2016-05-01 19:39:00.000','2016-05-01 15:58:23.000','1')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090034846','2016-05-02 20:52:00.000','2016-05-02 16:49:26.000','2')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090039309','2016-05-05 19:27:00.000','2016-05-05 16:22:29.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090041161','2016-05-07 19:42:00.000','2016-05-07 18:03:09.000','7')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090041177','2016-05-07 21:01:00.000','2016-05-07 18:57:16.000','7')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090041178','2016-05-07 21:38:00.000','2016-05-07 19:11:03.000','7')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090041190','2016-05-07 22:51:00.000','2016-05-07 20:19:13.000','7')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090041191','2016-05-08 00:48:00.000','2016-05-07 20:25:29.000','1')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090041196','2016-05-08 01:09:00.000','2016-05-07 20:49:56.000','1')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090042864','2016-05-09 19:57:00.000','2016-05-09 17:14:35.000','2')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090042918','2016-05-09 21:24:00.000','2016-05-09 18:13:13.000','2')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090042826','2016-05-09 22:05:00.000','2016-05-09 16:49:25.000','2')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090042911','2016-05-09 22:16:00.000','2016-05-09 18:01:06.000','2')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090043020','2016-05-10 00:00:00.000','2016-05-09 20:42:35.000','3')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090042934','2016-05-10 00:02:00.000','2016-05-09 18:54:37.000','3')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090043089','2016-05-10 00:43:00.000','2016-05-09 22:57:58.000','3')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090044390','2016-05-10 19:32:00.000','2016-05-10 17:07:01.000','3')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090046070','2016-05-12 01:47:00.000','2016-05-11 20:43:13.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090046071','2016-05-12 02:00:00.000','2016-05-11 20:48:35.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090047144','2016-05-12 18:45:00.000','2016-05-12 15:41:37.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090047329','2016-05-12 21:28:00.000','2016-05-12 18:59:29.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090047333','2016-05-12 21:52:00.000','2016-05-12 19:04:12.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090047414','2016-05-13 01:39:00.000','2016-05-12 21:13:18.000','6')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090048729','2016-05-13 22:09:00.000','2016-05-13 20:38:40.000','6')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090048741','2016-05-13 22:30:00.000','2016-05-13 21:22:09.000','6')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090048686','2016-05-13 22:53:00.000','2016-05-13 19:51:31.000','6')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090049083','2016-05-14 17:57:00.000','2016-05-14 16:32:19.000','7')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090049094','2016-05-14 18:52:00.000','2016-05-14 17:17:29.000','7')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090049355','2016-05-15 17:12:00.000','2016-05-15 15:43:40.000','1')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090049360','2016-05-15 17:59:00.000','2016-05-15 16:25:51.000','1')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090049375','2016-05-15 18:27:00.000','2016-05-15 17:09:08.000','1')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090049382','2016-05-15 21:16:00.000','2016-05-15 17:25:12.000','1')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090049441','2016-05-16 00:18:00.000','2016-05-15 22:45:33.000','2')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090049429','2016-05-16 00:19:00.000','2016-05-15 20:45:42.000','2')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090050478','2016-05-16 18:08:00.000','2016-05-16 15:11:47.000','2')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090050714','2016-05-16 21:17:00.000','2016-05-16 17:05:59.000','2')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090050895','2016-05-17 02:14:00.000','2016-05-16 22:10:01.000','3')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090053726','2016-05-18 21:14:00.000','2016-05-18 18:36:57.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090053670','2016-05-18 21:23:00.000','2016-05-18 17:07:43.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090053692','2016-05-18 21:37:00.000','2016-05-18 17:39:31.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090053700','2016-05-18 22:19:00.000','2016-05-18 17:50:59.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090055009','2016-05-19 18:45:00.000','2016-05-19 16:02:01.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090055061','2016-05-19 18:50:00.000','2016-05-19 16:37:00.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090055154','2016-05-19 21:54:00.000','2016-05-19 18:35:38.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090056314','2016-05-20 17:08:00.000','2016-05-20 15:29:06.000','6')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090056560','2016-05-21 00:20:00.000','2016-05-20 18:24:37.000','7')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090056990','2016-05-21 18:44:00.000','2016-05-21 16:14:05.000','7')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090057014','2016-05-21 21:28:00.000','2016-05-21 18:39:54.000','7')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090057240','2016-05-22 18:48:00.000','2016-05-22 16:59:05.000','1')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090057244','2016-05-22 19:20:00.000','2016-05-22 17:16:08.000','1')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090057280','2016-05-22 22:54:00.000','2016-05-22 19:09:58.000','1')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090059916','2016-05-24 21:48:00.000','2016-05-24 15:41:02.000','3')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090061586','2016-05-25 23:43:00.000','2016-05-25 20:11:09.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090062751','2016-05-26 17:14:00.000','2016-05-26 15:11:00.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090062871','2016-05-26 19:05:00.000','2016-05-26 16:14:53.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090062932','2016-05-26 19:12:00.000','2016-05-26 16:50:05.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090063101','2016-05-27 01:20:00.000','2016-05-26 21:39:46.000','6')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090064201','2016-05-27 18:35:00.000','2016-05-27 16:33:45.000','6')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090064240','2016-05-27 19:58:00.000','2016-05-27 17:08:54.000','6')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090064953','2016-05-29 16:46:00.000','2016-05-29 15:37:26.000','1')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090064947','2016-05-29 16:52:00.000','2016-05-29 15:19:38.000','1')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090064948','2016-05-29 17:49:00.000','2016-05-29 15:22:10.000','1')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090064944','2016-05-29 18:00:00.000','2016-05-29 15:10:11.000','1')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090064960','2016-05-29 18:56:00.000','2016-05-29 16:44:36.000','1')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090064955','2016-05-29 19:16:00.000','2016-05-29 15:52:19.000','1')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090065256','2016-05-30 20:29:00.000','2016-05-30 16:56:14.000','2')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090065286','2016-05-30 23:41:00.000','2016-05-30 18:22:48.000','2')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090066584','2016-05-31 22:10:00.000','2016-05-31 16:57:31.000','3')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090066617','2016-05-31 22:50:00.000','2016-05-31 17:37:49.000','3')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090068222','2016-06-01 22:24:00.000','2016-06-01 19:53:31.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090069469','2016-06-02 19:12:00.000','2016-06-02 15:31:16.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090069514','2016-06-02 19:25:00.000','2016-06-02 15:48:48.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090069707','2016-06-02 22:09:00.000','2016-06-02 18:58:05.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090071627','2016-06-05 18:10:00.000','2016-06-05 15:01:51.000','1')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090071716','2016-06-05 21:20:00.000','2016-06-05 19:20:25.000','1')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090073103','2016-06-06 23:32:00.000','2016-06-06 18:08:54.000','2')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090073129','2016-06-06 23:48:00.000','2016-06-06 18:45:49.000','2')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090073146','2016-06-07 01:07:00.000','2016-06-06 19:36:19.000','3')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090076129','2016-06-08 23:31:00.000','2016-06-08 18:51:39.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090077267','2016-06-09 16:47:00.000','2016-06-09 15:43:09.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090077436','2016-06-09 22:14:00.000','2016-06-09 17:53:59.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090077466','2016-06-09 23:21:00.000','2016-06-09 19:02:34.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090078638','2016-06-10 18:00:00.000','2016-06-10 16:19:50.000','6')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090078515','2016-06-10 18:14:00.000','2016-06-10 15:16:57.000','6')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090078678','2016-06-10 20:05:00.000','2016-06-10 16:52:33.000','6')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090078699','2016-06-10 21:53:00.000','2016-06-10 17:31:38.000','6')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090079172','2016-06-11 19:28:00.000','2016-06-11 15:51:18.000','7')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090079223','2016-06-11 23:39:00.000','2016-06-11 19:19:39.000','7')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090079244','2016-06-12 00:48:00.000','2016-06-11 20:38:42.000','1')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090079262','2016-06-12 01:08:00.000','2016-06-11 22:42:53.000','1')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090079455','2016-06-12 17:17:00.000','2016-06-12 15:17:05.000','1')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090079478','2016-06-12 17:57:00.000','2016-06-12 16:50:52.000','1')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090079473','2016-06-12 18:39:00.000','2016-06-12 16:13:44.000','1')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090082280','2016-06-14 21:18:00.000','2016-06-14 15:42:09.000','3')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090082471','2016-06-14 22:26:00.000','2016-06-14 17:32:38.000','3')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090082647','2016-06-15 01:06:00.000','2016-06-14 21:59:09.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090082646','2016-06-15 02:40:00.000','2016-06-14 21:34:57.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090083884','2016-06-15 18:06:00.000','2016-06-15 16:32:40.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090083968','2016-06-15 18:54:00.000','2016-06-15 18:09:25.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090086571','2016-06-17 19:43:00.000','2016-06-17 18:33:10.000','6')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090087032','2016-06-19 01:05:00.000','2016-06-18 22:23:33.000','1')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090087246','2016-06-19 19:57:00.000','2016-06-19 18:27:35.000','1')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090090038','2016-06-21 20:20:00.000','2016-06-21 16:44:40.000','3')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090090106','2016-06-21 20:45:00.000','2016-06-21 18:17:37.000','3')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090091596','2016-06-22 20:49:00.000','2016-06-22 16:29:50.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090093076','2016-06-23 21:55:00.000','2016-06-23 16:49:48.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090095103','2016-06-26 18:20:00.000','2016-06-26 16:02:15.000','1')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090095101','2016-06-26 19:24:00.000','2016-06-26 15:59:04.000','1')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090099722','2016-06-29 23:03:00.000','2016-06-29 20:45:06.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090099769','2016-06-29 23:55:00.000','2016-06-29 21:19:18.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090100931','2016-06-30 18:10:00.000','2016-06-30 16:34:23.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090105998','2016-07-06 22:34:00.000','2016-07-06 20:37:08.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090106000','2016-07-06 22:35:00.000','2016-07-06 20:44:36.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090105992','2016-07-07 00:35:00.000','2016-07-06 20:25:46.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090106035','2016-07-07 02:11:00.000','2016-07-06 21:51:28.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090106028','2016-07-07 02:20:00.000','2016-07-06 21:32:36.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090107440','2016-07-07 23:51:00.000','2016-07-07 19:14:40.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090108720','2016-07-08 22:16:00.000','2016-07-08 19:19:33.000','6')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090108719','2016-07-08 22:33:00.000','2016-07-08 19:09:43.000','6')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090108698','2016-07-08 23:05:00.000','2016-07-08 18:34:44.000','6')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090108811','2016-07-08 23:35:00.000','2016-07-08 21:01:07.000','6')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090109103','2016-07-09 21:45:00.000','2016-07-09 15:01:17.000','7')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090109384','2016-07-10 18:45:00.000','2016-07-10 16:39:45.000','1')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090109393','2016-07-10 20:38:00.000','2016-07-10 17:27:44.000','1')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090110802','2016-07-11 21:48:00.000','2016-07-11 18:57:00.000','2')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090110913','2016-07-11 23:41:00.000','2016-07-11 22:13:41.000','2')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090113306','2016-07-13 18:30:00.000','2016-07-13 16:02:40.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090113350','2016-07-13 20:45:00.000','2016-07-13 16:36:01.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090113572','2016-07-13 23:41:00.000','2016-07-13 20:59:16.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090116087','2016-07-15 23:13:00.000','2016-07-15 19:20:07.000','6')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090116468','2016-07-16 23:47:00.000','2016-07-16 21:09:19.000','7')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090116622','2016-07-17 16:50:00.000','2016-07-17 15:14:19.000','1')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090116634','2016-07-17 18:19:00.000','2016-07-17 15:53:25.000','1')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090116643','2016-07-17 19:00:00.000','2016-07-17 16:36:20.000','1')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090118066','2016-07-18 20:27:00.000','2016-07-18 18:06:47.000','2')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090118099','2016-07-18 22:39:00.000','2016-07-18 19:14:07.000','2')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090118100','2016-07-19 00:20:00.000','2016-07-18 19:18:11.000','3')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090120791','2016-07-20 19:50:00.000','2016-07-20 16:22:50.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090120765','2016-07-20 20:55:00.000','2016-07-20 16:07:38.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090123351','2016-07-22 16:51:00.000','2016-07-22 15:35:36.000','6')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090123453','2016-07-22 18:43:00.000','2016-07-22 17:00:05.000','6')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090123455','2016-07-22 19:03:00.000','2016-07-22 17:02:43.000','6')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090123464','2016-07-22 19:04:00.000','2016-07-22 17:15:34.000','6')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090123538','2016-07-22 22:05:00.000','2016-07-22 20:20:20.000','6')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090123547','2016-07-23 01:42:00.000','2016-07-22 21:09:45.000','7')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090123838','2016-07-23 18:37:00.000','2016-07-23 16:28:03.000','7')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090125416','2016-07-25 20:40:00.000','2016-07-25 17:00:52.000','2')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090126846','2016-07-26 19:37:00.000','2016-07-26 17:00:59.000','3')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090126969','2016-07-27 02:05:00.000','2016-07-26 19:53:23.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090128313','2016-07-27 20:02:00.000','2016-07-27 17:44:32.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090128288','2016-07-27 22:27:00.000','2016-07-27 17:22:22.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090129604','2016-07-29 03:01:00.000','2016-07-28 17:33:25.000','6')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090130970','2016-07-30 00:22:00.000','2016-07-29 19:40:10.000','7')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090133170','2016-08-02 00:06:00.000','2016-08-01 18:55:47.000','3')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090134713','2016-08-02 23:35:00.000','2016-08-02 21:04:30.000','3')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090138890','2016-08-05 20:04:00.000','2016-08-05 18:16:02.000','6')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090139249','2016-08-06 16:38:00.000','2016-08-06 15:20:39.000','7')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090139261','2016-08-06 18:33:00.000','2016-08-06 16:39:52.000','7')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090139502','2016-08-07 17:37:00.000','2016-08-07 15:22:13.000','1')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090139558','2016-08-07 22:51:00.000','2016-08-07 21:58:31.000','1')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090139547','2016-08-07 23:00:00.000','2016-08-07 19:55:27.000','1')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090140593','2016-08-08 17:59:00.000','2016-08-08 15:00:36.000','2')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090140951','2016-08-08 21:15:00.000','2016-08-08 19:10:51.000','2')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090142330','2016-08-09 23:27:00.000','2016-08-09 19:05:30.000','3')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090143716','2016-08-10 20:14:00.000','2016-08-10 17:50:07.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090143685','2016-08-10 20:30:00.000','2016-08-10 17:28:19.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090143741','2016-08-10 20:59:00.000','2016-08-10 18:34:45.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090143719','2016-08-10 21:30:00.000','2016-08-10 17:59:20.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090143735','2016-08-10 21:34:00.000','2016-08-10 18:26:36.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090143866','2016-08-10 22:59:00.000','2016-08-10 21:13:52.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090143868','2016-08-11 00:19:00.000','2016-08-10 22:00:09.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090143871','2016-08-11 00:24:00.000','2016-08-10 22:07:28.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090146675','2016-08-13 18:25:00.000','2016-08-13 16:44:30.000','7')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090146704','2016-08-13 23:10:00.000','2016-08-13 18:34:48.000','7')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090147988','2016-08-15 20:48:00.000','2016-08-15 15:12:15.000','2')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090148287','2016-08-15 23:56:00.000','2016-08-15 19:05:26.000','2')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090148383','2016-08-16 01:19:00.000','2016-08-15 22:39:34.000','3')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090149613','2016-08-17 00:41:00.000','2016-08-16 17:07:59.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090151067','2016-08-17 19:09:00.000','2016-08-17 16:47:29.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090151195','2016-08-17 21:37:00.000','2016-08-17 19:08:02.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090151200','2016-08-17 22:55:00.000','2016-08-17 19:11:32.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090151333','2016-08-18 00:32:00.000','2016-08-17 21:26:00.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090153665','2016-08-19 19:45:00.000','2016-08-19 16:15:30.000','6')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090153880','2016-08-20 00:48:00.000','2016-08-19 21:03:11.000','7')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090155707','2016-08-22 23:18:00.000','2016-08-22 16:53:38.000','2')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090155831','2016-08-23 01:39:00.000','2016-08-22 20:05:53.000','3')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090157040','2016-08-23 20:19:00.000','2016-08-23 16:45:35.000','3')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090157208','2016-08-24 03:40:00.000','2016-08-23 20:23:19.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090158268','2016-08-24 17:51:00.000','2016-08-24 15:26:19.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090158347','2016-08-24 18:17:00.000','2016-08-24 16:01:42.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090158277','2016-08-24 20:00:00.000','2016-08-24 15:30:21.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090158358','2016-08-24 21:15:00.000','2016-08-24 16:08:09.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090158498','2016-08-24 21:40:00.000','2016-08-24 18:04:41.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090158506','2016-08-24 22:14:00.000','2016-08-24 18:39:54.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090158521','2016-08-25 00:13:00.000','2016-08-24 19:39:56.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090158535','2016-08-25 00:20:00.000','2016-08-24 20:03:41.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090158581','2016-08-25 02:44:00.000','2016-08-24 22:09:57.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090159573','2016-08-25 18:10:00.000','2016-08-25 15:17:58.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090159623','2016-08-25 20:00:00.000','2016-08-25 15:41:27.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090159786','2016-08-25 20:15:00.000','2016-08-25 17:55:15.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090159788','2016-08-25 21:15:00.000','2016-08-25 17:59:16.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090161654','2016-08-28 23:17:00.000','2016-08-28 19:12:54.000','1')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090161687','2016-08-29 00:55:00.000','2016-08-28 20:56:55.000','2')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090162902','2016-08-29 17:20:00.000','2016-08-29 15:49:24.000','2')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090163063','2016-08-29 20:50:00.000','2016-08-29 17:40:44.000','2')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090162941','2016-08-29 21:46:00.000','2016-08-29 16:10:10.000','2')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090163103','2016-08-29 23:23:00.000','2016-08-29 19:15:20.000','2')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090163104','2016-08-29 23:47:00.000','2016-08-29 19:17:47.000','2')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090163178','2016-08-30 00:19:00.000','2016-08-29 21:20:32.000','3')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090163141','2016-08-30 00:45:00.000','2016-08-29 20:16:32.000','3')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090163182','2016-08-30 01:27:00.000','2016-08-29 21:45:13.000','3')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090163185','2016-08-30 02:40:00.000','2016-08-29 21:54:52.000','3')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090164357','2016-08-30 22:00:00.000','2016-08-30 16:55:12.000','3')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090165771','2016-08-31 19:36:00.000','2016-08-31 15:37:08.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090165950','2016-08-31 21:06:00.000','2016-08-31 17:37:24.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090166088','2016-08-31 22:31:00.000','2016-08-31 20:24:23.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090166057','2016-08-31 23:30:00.000','2016-08-31 20:00:03.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090166124','2016-08-31 23:41:00.000','2016-08-31 21:40:10.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090166130','2016-08-31 23:41:00.000','2016-08-31 22:26:52.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090168424','2016-09-02 17:50:00.000','2016-09-02 15:15:43.000','6')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090168574','2016-09-02 20:30:00.000','2016-09-02 16:46:59.000','6')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090168555','2016-09-02 21:15:00.000','2016-09-02 16:34:07.000','6')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090169072','2016-09-03 21:42:00.000','2016-09-03 20:32:16.000','7')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090169083','2016-09-03 23:54:00.000','2016-09-03 22:14:05.000','7')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090169237','2016-09-04 17:30:00.000','2016-09-04 15:14:59.000','1')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090170878','2016-09-06 18:34:00.000','2016-09-06 16:25:07.000','3')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090170801','2016-09-06 19:05:00.000','2016-09-06 15:49:50.000','3')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090171087','2016-09-06 21:23:00.000','2016-09-06 19:55:03.000','3')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090170980','2016-09-06 22:11:00.000','2016-09-06 17:35:13.000','3')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090170995','2016-09-06 22:12:00.000','2016-09-06 18:00:56.000','3')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090171175','2016-09-07 03:37:00.000','2016-09-06 22:52:06.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090172534','2016-09-07 22:23:00.000','2016-09-07 19:09:46.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090175200','2016-09-09 19:46:00.000','2016-09-09 16:19:56.000','6')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090175325','2016-09-09 22:33:00.000','2016-09-09 17:49:26.000','6')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090175454','2016-09-09 23:53:00.000','2016-09-09 20:54:02.000','6')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090175354','2016-09-10 00:21:00.000','2016-09-09 18:45:00.000','7')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090175457','2016-09-10 00:53:00.000','2016-09-09 20:59:48.000','7')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090175827','2016-09-10 20:55:00.000','2016-09-10 18:29:54.000','7')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090175867','2016-09-10 23:31:00.000','2016-09-10 21:31:37.000','7')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090176110','2016-09-11 19:52:00.000','2016-09-11 16:28:44.000','1')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090177306','2016-09-12 16:51:00.000','2016-09-12 16:03:23.000','2')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090177321','2016-09-12 20:14:00.000','2016-09-12 16:07:54.000','2')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090177442','2016-09-13 00:25:00.000','2016-09-12 17:20:23.000','3')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090178662','2016-09-13 19:25:00.000','2016-09-13 15:19:00.000','3')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090179056','2016-09-13 22:49:00.000','2016-09-13 20:22:39.000','3')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090180303','2016-09-14 21:29:00.000','2016-09-14 17:59:59.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090181600','2016-09-15 18:45:00.000','2016-09-15 16:26:24.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090181678','2016-09-15 19:01:00.000','2016-09-15 17:28:53.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090181670','2016-09-15 19:50:00.000','2016-09-15 17:22:20.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090181745','2016-09-15 20:30:00.000','2016-09-15 19:10:18.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090181813','2016-09-15 21:59:00.000','2016-09-15 20:11:58.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090181856','2016-09-15 22:42:00.000','2016-09-15 21:26:36.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090183016','2016-09-16 21:12:00.000','2016-09-16 19:22:10.000','6')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090183040','2016-09-16 22:17:00.000','2016-09-16 20:37:43.000','6')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090183041','2016-09-16 22:27:00.000','2016-09-16 20:40:30.000','6')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090183034','2016-09-16 22:31:00.000','2016-09-16 20:02:30.000','6')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090183052','2016-09-17 00:37:00.000','2016-09-16 21:33:16.000','7')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090185087','2016-09-19 18:10:00.000','2016-09-19 16:29:55.000','2')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090185062','2016-09-19 18:58:00.000','2016-09-19 16:14:18.000','2')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090185245','2016-09-19 21:49:00.000','2016-09-19 19:15:45.000','2')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090186617','2016-09-20 17:48:00.000','2016-09-20 16:45:53.000','3')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090186829','2016-09-20 23:53:00.000','2016-09-20 20:41:08.000','3')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090186846','2016-09-21 02:05:00.000','2016-09-20 21:28:03.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090188180','2016-09-22 00:16:00.000','2016-09-21 20:00:59.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090188211','2016-09-22 01:36:00.000','2016-09-21 20:54:58.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090189513','2016-09-22 20:50:00.000','2016-09-22 17:52:44.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090189530','2016-09-22 21:01:00.000','2016-09-22 18:09:57.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090189588','2016-09-22 21:23:00.000','2016-09-22 19:08:55.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090189542','2016-09-22 21:53:00.000','2016-09-22 18:14:17.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090189549','2016-09-22 22:18:00.000','2016-09-22 18:21:28.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090189550','2016-09-22 22:18:00.000','2016-09-22 18:22:30.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090189553','2016-09-22 22:18:00.000','2016-09-22 18:23:53.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090190939','2016-09-23 21:42:00.000','2016-09-23 19:42:30.000','6')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090191016','2016-09-24 00:07:00.000','2016-09-23 20:46:16.000','7')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090191072','2016-09-24 04:05:00.000','2016-09-23 22:35:58.000','7')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090191622','2016-09-25 20:33:00.000','2016-09-25 17:07:07.000','1')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090193016','2016-09-26 21:32:00.000','2016-09-26 17:07:42.000','2')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090193115','2016-09-26 23:25:00.000','2016-09-26 19:13:55.000','2')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090193013','2016-09-26 23:45:00.000','2016-09-26 17:05:46.000','2')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090193221','2016-09-26 23:51:00.000','2016-09-26 22:02:02.000','2')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090194344','2016-09-27 15:50:00.000','2016-09-27 15:09:51.000','3')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090194576','2016-09-28 00:01:00.000','2016-09-27 17:29:56.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090194771','2016-09-28 01:13:00.000','2016-09-27 22:34:01.000','4')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090197363','2016-09-29 18:22:00.000','2016-09-29 15:29:31.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090197528','2016-09-29 18:42:00.000','2016-09-29 17:08:51.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090197619','2016-09-29 21:09:00.000','2016-09-29 18:48:10.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090197582','2016-09-29 21:12:00.000','2016-09-29 18:15:44.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090197674','2016-09-29 23:45:00.000','2016-09-29 20:23:20.000','5')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090198688','2016-09-30 16:21:00.000','2016-09-30 15:04:07.000','6')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090198736','2016-09-30 16:27:00.000','2016-09-30 15:28:06.000','6')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090198860','2016-09-30 17:38:00.000','2016-09-30 17:01:29.000','6')
insert into #Test(AccountNumber, DischargeDateTime, RegistrationDateTime, day_of_week) values('F00090198923','2016-09-30 20:24:00.000','2016-09-30 18:22:42.000','6')
October 3, 2016 at 7:29 am
DECLARE @today DATETIME = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0); -- This is important to remove time from the equations
DECLARE @EndDate DATETIME = DATEADD(dd, -DATEPART(dw, @today) -1, @today);
DECLARE @StartDate DATETIME = DATEADD(dd, -7, @EndDate);
SELECT StartDate = @StartDate, EndDate = @EndDate;
SELECT
Regdate = CAST(t.RegistrationDateTime AS DATE)
, Registrations = COUNT(*)
FROM #Test AS t
WHERE t.RegistrationDateTime >= @StartDate
AND t.RegistrationDateTime < @EndDate
GROUP BY CAST(t.RegistrationDateTime AS DATE);
October 3, 2016 at 7:56 am
Are you trying to get an overall weekly count or count per day in a week? If you just need a weekly count (assuming discharge date based on day of week values) you could try this.
DECLARE @weekbefore DATETIME = '20160930'
SELECT COUNT(AccountNumber) AS NumOfDischarges
FROM #Test
WHERE DischargeDateTime BETWEEN DATEADD(dd, -7, @weekbefore) AND @weekbefore
October 3, 2016 at 9:17 am
I need to get a value for each week, ending with Friday, for the date range.
October 3, 2016 at 9:39 am
I was expecting to get a row for each Friday since the beginning of the data. Here is an example. I ran each of these weeks separately.
WeekEndingCount
5/13/2016 20
5/20/2016 21
5/27/2016 13
6/3/2016 16
6/10/2016 9
6/17/2016 17
6/24/2016 7
October 3, 2016 at 9:54 am
NineIron (10/3/2016)
I need to get a value for each week, ending with Friday, for the date range.
This is based on the RegistrationDate. You can modify it to work on DischargeDate if needed.
WITH cteWeeks AS (
SELECT WeekStart = DATEADD(dd, DATEDIFF(dd, 0, t.RegistrationDateTime -7), 0)
, WeekEnd = DATEADD(dd, DATEDIFF(dd, 0, t.RegistrationDateTime +1), 0)
FROM #Test AS t
WHERE t.day_of_week = '6'
)
SELECT cte.WeekStart
, tot.FirstReg
, tot.LastReg
, cte.WeekEnd
, tot.RegistrationCount
FROM cteWeeks AS cte
CROSS APPLY (
SELECT FirstReg = MIN(t2.RegistrationDateTime)
, LastReg = MAX(t2.RegistrationDateTime)
, RegistrationCount = COUNT(*)
FROM #Test AS t2
WHERE t2.RegistrationDateTime >= WeekStart
AND t2.RegistrationDateTime < WeekEnd
) AS tot
ORDER BY cte.WeekStart;
EDIT: Quoted wrong message
October 3, 2016 at 9:58 am
Here you go. If you don't feel comfortable changing DATEFIRST, you can do some modulo magic on DATEPART(dw,RegistrationDateTime) so that you get the right numbers.
set DATEFIRST 5 -- set first day of week to Friday
SELECT CAST(DATEADD(day,8-DATEPART(dw,RegistrationDateTime),RegistrationDateTime) AS date) AS WeekEnding,
COUNT(*) AS NoofRecords
FROM #Test
GROUP BY CAST(DATEADD(day,8-DATEPART(dw,RegistrationDateTime),RegistrationDateTime) AS date)
John
October 3, 2016 at 10:10 am
The results of this query show weeks ending for 5/14, 5/21, 5/28. None of these are Fridays. Also, there seem to be multiples. Any thoughts?
October 3, 2016 at 11:17 am
NineIron (10/3/2016)
The results of this query show weeks ending for 5/14, 5/21, 5/28. None of these are Fridays. Also, there seem to be multiples. Any thoughts?
Have you tried John's solution? Looks good to me and gets you what you expected.
October 3, 2016 at 11:30 am
It's been one of those days but, yes, this does look good.
October 3, 2016 at 11:30 am
Thanx. I think this will work.
October 3, 2016 at 2:39 pm
I think messing with DATEFIRST when you don't absolutely have to is just asking for issues later. The CROSS APPLYs are used just to simplify the main SELECT list and to make the code easier to follow/change.
SELECT WeekEnding, COUNT(*) AS Record_Count
FROM #Test t
CROSS APPLY (
SELECT 4 AS Friday /*0=Mon,...,6=Sun*/
) AS which_day_of_week
CROSS APPLY (
SELECT CAST(DATEADD(DAY, -DATEDIFF(DAY, Friday, t.RegistrationDateTime) % 7 + 7, t.RegistrationDateTime) AS date) AS WeekEnding
) AS assign_alias_names
GROUP BY WeekEnding
ORDER BY WeekEnding
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
October 4, 2016 at 4:02 am
Thanx.
October 4, 2016 at 4:28 am
Would you be able to explain this query for me? I hate to just copy and paste without understanding the code. Also, can this be applied to other calculations such as median? That's my next step. The user wants the median length of stay, LOS, for each week, as well as the number of visits. So, datediff(minute, RegistrationDateTime, DischargeDateTime) will give me the LOS for each week but, how do I plug that into this cross apply query?
October 4, 2016 at 4:59 am
Which bit are you struggling with - the aggregate function and GROUP BY, the CROSS APPLYs, the DATEADD and DATEDIFF, or something else?
Please confirm that you are indeed using SQL Server 2008 (which is the forum you've posted in). If you're on 2012 or later, the median query gets a lot simpler.
John
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply