# Query Required this scenario

• Hai,

One company multiple employees working data entry operators.Employees working two different  shifts. First shift time 10AM to 6PM .Second shift time 8PM to 6AM .

my requirement , date wise use wise  data entry count for both the shifts. i am facing problem second shits count. Because date changed in night 12'o clock.

create table #tblRecorddetails ( Recordid int,Userid char(3),Createddate datetime)

insert into #tblRecorddetails values(1,'U01','2020-04-07 13:01:21.817')

insert into #tblRecorddetails values(2,'U02','2020-04-07 13:23:21.817')

insert into #tblRecorddetails values(3,'U01','2020-04-07 14:34:21.817')

insert into #tblRecorddetails values(4,'U01','2020-04-07 13:12:21.817')

insert into #tblRecorddetails values(5,'U03','2020-04-07 15:34:21.817')

insert into #tblRecorddetails values(6,'U03','2020-04-07 15:23:21.817')

insert into #tblRecorddetails values(7,'U01','2020-04-07 14:34:21.817')

insert into #tblRecorddetails values(8,'U04','2020-04-07 17:43:21.817')

insert into #tblRecorddetails values(9,'U11','2020-04-07 19:01:21.817')

insert into #tblRecorddetails values(10,'U12','2020-04-07 19:23:21.817')

insert into #tblRecorddetails values(11,'U11','2020-04-07 20:34:21.817')

insert into #tblRecorddetails values(12,'U11','2020-04-08 01:12:21.817')

insert into #tblRecorddetails values(13,'U13','2020-04-08 02:34:21.817')

insert into #tblRecorddetails values(14,'U13','2020-04-08 01:23:21.817')

insert into #tblRecorddetails values(15,'U11','2020-04-08 04:34:21.817')

insert into #tblRecorddetails values(16,'U14','2020-04-08 01:43:21.817')

I need following result

"ScanDate", "USERID","DeliveryCnt","Shift"

"2020-04-07", "U01","4","First shift"

"2020-04-07", "U02","1","First shift"

"2020-04-07", "U03","2","First shift"

"2020-04-07", "U04","1","First shift"

"2020-04-07", "U11","3","Second Shift"

"2020-04-07", "U12","1","Second Shift"

"2020-04-07", "U13","2","Second Shift"

"2020-04-07", "U14","1","Second Shift"

How to achieve this above output.

Thank you

• What have you tried?

I really can't tell what the data in your table represents.  If the table and its columns had meaningful names, that would be more helpful.  But if your problem is the date change, why not subtract 8 hours from every value of Createddate?

John

• `drop table if exists #tblRecorddetails;gocreate table #tblRecorddetails(  Recordid          int,  Userid            char(3),  Createddate       datetime);insert #tblRecorddetails values(1,'U01','2020-04-07 13:01:21.817'),(2,'U02','2020-04-07 13:23:21.817'),(3,'U01','2020-04-07 14:34:21.817'),(4,'U01','2020-04-07 13:12:21.817'),(5,'U03','2020-04-07 15:34:21.817'),(6,'U03','2020-04-07 15:23:21.817'),(7,'U01','2020-04-07 14:34:21.817'),(8,'U04','2020-04-07 17:43:21.817'),(9,'U11','2020-04-07 19:01:21.817'),(10,'U12','2020-04-07 19:23:21.817'),(11,'U11','2020-04-07 20:34:21.817'),(12,'U11','2020-04-08 01:12:21.817'),(13,'U13','2020-04-08 02:34:21.817'),(14,'U13','2020-04-08 01:23:21.817'),(15,'U11','2020-04-08 04:34:21.817'),(16,'U14','2020-04-08 01:43:21.817');declare  @first_shift_start_time      time='10:00:00', /* 10AM */  @first_shift_end_time        time='18:00:00', /*  6PM */  @second_shift_start_time     time='20:00:00', /*  8PM */  @second_shift_end_time       time='06:00:00'; /*  6AM *//* keep date created */select  cast(Createddate as date) ScanDate,  USERID,  count(*) DeliveryCnt,  iif(cast(Createddate as time) between @first_shift_start_time and @first_shift_end_time, 'First Shift', 'Second Shift') WorkShiftfrom  #tblRecorddetailsgroup by  cast(Createddate as date),  USERID,  iif(cast(Createddate as time) between @first_shift_start_time and @first_shift_end_time, 'First Shift', 'Second Shift');/* attribute to prior day */select  iif(cast(Createddate as time)<@second_shift_end_time, cast(dateadd(dd, -1, Createddate) as date), cast(Createddate as date)) ScanDate,  USERID,  count(*) DeliveryCnt,  iif(cast(Createddate as time) between @first_shift_start_time and @first_shift_end_time, 'First Shift', 'Second Shift') WorkShiftfrom  #tblRecorddetailsgroup by  iif(cast(Createddate as time)<@second_shift_end_time, cast(dateadd(dd, -1, Createddate) as date), cast(Createddate as date)),  USERID,  iif(cast(Createddate as time) between @first_shift_start_time and @first_shift_end_time, 'First Shift', 'Second Shift');`

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Viewing 3 posts - 1 through 2 (of 2 total)