April 9, 2020 at 1:29 pm
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
April 9, 2020 at 2:47 pm
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
April 9, 2020 at 3:29 pm
drop table if exists #tblRecorddetails;
go
create 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') WorkShift
from
#tblRecorddetails
group 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') WorkShift
from
#tblRecorddetails
group 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)
You must be logged in to reply to this topic. Login to reply