Crezzer7 Crezzer7 - 5 months ago 8
SQL Question

SQL Server - organising dates into a view from a table

Sorry for the broad title as I was unsure on how to word it but here is what I am after doing:

I have a table called

Scanner
and it contains a
ScannerID
(Primary Key),
StaffID
,
EventTime
and
Status
. This table records each time a scan occurs on a device and records it into this table on
EventTime
. The
StaffID
identifies who it is based on the cards details. Finally the
Status
counts up each time a scan is recorded based on the
StaffID
, for example:

ScannerID StaffID EventTime Status
1 1 2016-06-24 07:30:00 1
2 1 2016-06-24 13:00:00 2
3 1 2016-06-24 14:00:00 3
4 1 2016-06-24 17:00:00 4
5 2 2016-06-24 08:30:00 1
6 2 2016-06-24 17:30:00 2


Question
from this data I want to be able to block these together within a view like below, for example:

StaffID StartDate EndDate
1 2016-06-24 07:30:00 2016-06-24 13:00:00
1 2016-06-24 14:00:00 2016-06-24 17:00:00


this is based on odd numbers from
Status
being the StartDate, and Even Numbers from
Status
being the EndDate.

Additional Information


  • The Status starts from 1 each day, and increments based on the StaffID

  • Clarification: odd numbers are StartDate's and even are EndDate's. This is because the card is scanned an unknown quantity, but will always end on an Even number as they cannot leave the building without scanning out. That is the premises I am working on in my design



Is this possible to do, and if so how can I go about creating a view, alternativly what options are available to me with data like shown in the singular breakdown?

Thankyou for all help and advice on this matter

Answer

Try this...

CREATE TABLE #Scanning(StaffID INT, EventTime DATETIME, Status INT)

INSERT INTO #Scanning
VALUES  
    (1, '2016-06-24 07:30:00',  1)
   ,(1, '2016-06-24 13:00:00',  2)
   ,(1, '2016-06-24 14:00:00',  3)
   ,(1, '2016-06-24 17:00:00',  4)
   ,(1, '2016-06-25 07:30:00',  1)
   ,(1, '2016-06-25 13:00:00',  2)
   ,(1, '2016-06-25 14:00:00',  3)
   ,(1, '2016-06-25 17:00:00',  4)

SELECT  s1.StaffID, 
        s1.EventTime AS StartDate,
        s2.EventTime AS EndDate
FROM #Scanning s1
LEFT JOIN #Scanning s2 ON s1.StaffID = s2.StaffID
    AND s2.Status = s1.Status + 1
    AND CAST(s1.EventTime AS DATE) = CAST(s2.EventTime AS DATE) -- match for each day..
WHERE s1.Status%2 = 1       -- only rows with odd Numbers