Need a sql query, which can be run in ssms, to produce the output as illustrated in the attached spreadsheet breakouts. I have included a text file with the script to create the sampled data as well as the outcome spreadsheet illustration.
The scenario:
Multiple mail clerks deliver mail throughout our campus.
We have begin locating them by RFID
When they are at any workstation, our application stores:
The Mail clerk's ID
The workstation identifier
a date-time stamp
Example MailClerk 1 arrives at workstation 1 at 10:00am and he stays until 10:05 am at that workstation
Our data table would receive approximately 3 record inserts, as follows,
HitID 001 MailClerkID: 1 WorkStation:1 DateTmeOfHit: 10:00
HitID 004 MailClerkID:1 WorkStation:1 DateTimeOfHit: 10:02
HitID 007 MailClerkID:1 WorkStation:1 DateTimeOfHit: 10:05
HitID 011 MailClerkID:1 WorkStation:2 DateTimeOfHit: 10:08
At HitID 011 - he has moved to a different workstation, but for Hits001 through Hits007, he was at the SAME STATION, and that is the blocks-of-time-at-same-station we are looking for.
The attached spreadsheet has worksheets for the raw source data, a breakout by 3 different mail clerks, the color-coded blocks of time at each mail station for these 3 clerks, and the [DesiredReportOutput] worksheet.
IMPORTANT: the data in the table in the DESIREDREPORTOUTPUT table is the ONLY output WE NEED
Hi,
I have ready the query you need. The query is giving same desire result as your expectation. I can give you demo now. Let me know for demo before select any developer.
I can show result with your raw data as below:
MailClerkId WorkStation firsthit lasthit duration
2 2 2016-01-25 06:04:00.000 2016-01-25 06:08:00.000 4 Minutes
2 1 2016-01-25 06:22:00.000 2016-01-25 06:28:00.000 6 Minutes
2 2 2016-01-25 06:30:00.000 2016-01-25 06:36:00.000 6 Minutes
3 12 2016-01-25 06:21:00.000 2016-01-25 06:25:00.000 4 Minutes
3 17 2016-01-25 06:29:00.000 2016-01-25 06:35:00.000 6 Minutes
4 34 2016-01-25 06:25:00.000 2016-01-25 06:29:00.000 4 Minutes
4 35 2016-01-25 06:31:00.000 2016-01-25 06:35:00.000 4 Minutes
4 38 2016-01-25 06:39:00.000 2016-01-25 06:45:00.000 6 Minutes
Thanks
Daya