Home T-SQL Case when with Using with Sql Two Column
Reply: 3

T-SQL Case when with Using with Sql Two Column

serenity
1#
serenity Published in 2018-02-13 22:09:55Z

First of all I would like to thank the friends who helped this complex and difficult query.

I have three tables

Table 1

 StaffId     FirstName       LastName   staffType
---------------------------------------
   1          Adam            Sorme      Student 
   2          Lara            Sandra     Teacher
   3          Jack            Jones      Student

Table 2

 GateId   GateName  
 ---------------------------------------
   1        frontDoor
   2        superDoor

Table 3

Id transitionDate     GateId  StaffId 
 ---------------------------------------
1  2018-01-1 08:00:00    1     1
2  2018-01-1 10:00:00    2     1
3  2018-01-1 20:00:00    2     1
4  2018-01-2 07:00:00    1     2
5  2018-01-2 10:00:00    1     3
6  2018-01-9 12:00:00    2     2

I want the first and last movements of students for each day. Value must be set to null if no movement is available between the specified dates

transitionDate> '2018-01-1 00:00:00 000' 
 and transitionDate< '2018-01-03 00:00:00 000'

OUTPUT:

  Id     Date    MinTransitionDate    MaxTransitionDate    FirstGateName LastGateName    StaffId    StaffType
  1   2018-01-01  2018-01-1 08:00:00 2018-01-1 20:00:00    frontDoor      superDoor         1         Student
  2   2018-01-01  null                null                  null           null             3         student
  3   2018-01-02  null                null                  null           null             1         student
  4   2018-01-02  2018-01-2 10:00:00  null                 frontDoor       null             3         student

The following query is partially working.

select 
q.*, 
g1.GateName as first_gate_name,
g2.GateName as last_gate_name
from 

(
select s.staffId, d.dte,


   min(t.transitionDate) as min_Date,
   max_Date= case when count(1)>1 then max(t.transitionDate) else null end,
   max(case when seqnum_asc = 1 then gateId end) as first_gateid,
   max(case when seqnum_desc = 1 then gateId end) as last_gateid
from (select s.* from Staff s where stafftype = 'Student') s cross join
 (select distinct cast(transitionDate as date) as dte from Transitions) d left join
 (select t.*,
         row_number() over (partition by StaffId, cast(transitionDate as date) order by transitionDate) as seqnum_asc,
         row_number() over (partition by StaffId, cast(transitionDate as date) order by transitionDate desc) as seqnum_desc
  from Transitions t
 ) t
 on cast(t.transitiondate as date) = d.dte and
    t.staffId = s.staffId and
    1 in (t.seqnum_asc, t.seqnum_desc)
group by s.staffId, d.dte
) q

left join Gates g1 on g1.gateId = q.first_gateid
left join Gates g2 on g2.gateId = q.last_gateid

see working demo

Problem : max_date in 4. row is empty. I want the value of last_gateid to be null. Can you help me?

screenshot 4. row https://cdn.pbrd.co/images/H7vyu31.png

Sanchit Anand
2#
Sanchit Anand Reply to 2018-02-13 22:32:26Z

A quick fix would be replacing this line

max(case when seqnum_desc = 1 then gateId end) as last_gateid

with

max(case when (seqnum_desc = 1 and seqnum_asc != 1) then t.gateId end) as last_gateid
Ahmad.Tr
3#
Ahmad.Tr Reply to 2018-02-13 22:39:00Z

It is obvious that some one helped you write the query and you gave him more explanation of the problem , what i did is tweaking the query you provided and used the same logic to give you the desired output although i would prefer to rewrite it in a more understandable way :

select 
q.*,
g1.GateName as first_gate_name,
g2.GateName as last_gate_name
from 

(
select s.staffId, d.dte,


   min(t.transitionDate) as min_Date,
   max_Date= case when count(1)>1 then max(t.transitionDate) else null end,
   max(case when seqnum_asc = 1 then gateId end) as first_gateid,
   case when count(1)>1 then MAX(gateId) end as last_gateid
from (select s.* from Staff s where stafftype = 'Student') s cross join
 (select distinct cast(transitionDate as date) as dte from Transitions) d left join
 (select t.*,
         row_number() over (partition by StaffId, cast(transitionDate as date) order by transitionDate) as seqnum_asc,
         row_number() over (partition by StaffId, cast(transitionDate as date) order by transitionDate desc) as seqnum_desc
  from Transitions t
 ) t
 on cast(t.transitiondate as date) = d.dte and
    t.staffId = s.staffId and
    1 in (t.seqnum_asc, t.seqnum_desc)
group by s.staffId, d.dte
) q

left join Gates g1 on g1.gateId = q.first_gateid
left join Gates g2 on g2.gateId = q.last_gateid
Ana
4#
Ana Reply to 2018-02-14 14:52:54Z
    IF OBJECT_ID('dbo.Staff') IS NOT NULL DROP TABLE Staff
   create table Staff (StaffId int, FirstName varchar(20), LastName 
    varchar(20), staffType varchar(20))
    insert into Staff values
    (1, 'Adam', 'Sorme', 'Student'), 
    (2, 'Lara', 'Sandra', 'Teacher'),
    (3, 'Jack', 'Jones', 'Student')

    IF OBJECT_ID('dbo.Gates') IS NOT NULL DROP TABLE Gates
    create table Gates (GateId int, GateName varchar(20)) 
    insert into Gates values
    (1, 'frontDoor'),
    (2, 'backDoor')

    IF OBJECT_ID('dbo.Transitions') IS NOT NULL DROP TABLE Transitions
    create table Transitions (Id int, transitionDate datetime, GateId int, 
     StaffId  int)
    insert into Transitions values
    (1, '2018-01-1 08:00:00', 1, 1),
    (2, '2018-01-1 10:00:00', 2, 1),
    (3, '2018-01-1 20:00:00', 2, 1),
    (4, '2018-01-2 07:00:00', 1, 2),
    (5, '2018-01-2 10:00:00', 2, 3),
    (6, '2018-01-9 12:00:00', 2, 2)


    --select * from Transitions 

DECLARE @Datefrom DATETIME = '2018-01-01'
DECLARE @DateTo DATETIME = '2018-01-03'

    ----1. If the transition table itself is not reliable meaning there 
   --could be imports and the maxID could be for a lesser datetime
    IF OBJECT_ID('tempdb..#Transitions') IS NOT NULL DROP TABLE 
    #Transitions
    SELECT *, [GateSortID] = RANK() OVER (PARTITION BY StaffID ORDER BY 
    StaffID,GateID,TransitionDate)
    INTO #Transitions
    FROM Transitions

    ----2.Based on above temp table get first and last dates
    IF OBJECT_ID('tempdb..#FirstandLastDates') IS NOT NULL DROP TABLE 
    #FirstandLastDates
    SELECT T.StaffId,
           [FirstDate] = FirstDate.transitionDate,
           [LastDate] = LastDate.transitionDate
    INTO #FirstandLastDates
    FROM #Transitions T
    JOIN 
    (
        SELECT TT.StaffId,
        [MinGateSortID] = MIN(GateSortID),
        TT.GateID,
        G.transitionDate
        FROM #Transitions TT
       JOIN Transitions G
          ON TT.StaffId = G.StaffId
          AND TT.Id = G.ID
          AND TT.GateId = G.GateId
       WHERE GateSortID = 1-- MIN VALUE WILL ALWAYS BE 1
      GROUP BY TT.StaffID,TT.GateID,G.transitionDate
 )FirstDate --lol!!
ON T.StaffID= FirstDate.StaffID
  LEFT JOIN 
  (
       SELECT TT.StaffId, TT.GateId, G.TransitionDate
        FROM #Transitions TT
        JOIN #Transitions G
              ON TT.StaffId = G.StaffId
              AND TT.Id = G.ID
              AND TT.GateId = G.GateId
        JOIN 
           (
               SELECT StaffID,
                       [MaxGateSortID] = MAX(GateSortID)
                FROM #Transitions TT
                WHERE GateSortID <> 1 -- SO THAT IF THE PERSON HAS NOT --CLOCKED OUT YET ONLY CLOCKED IN DO NOT CONSIDER THAT THE 
                --GATES ARE SAME
               GROUP BY StaffID
            ) LastGate1
              ON TT.StaffId = LastGate1.StaffId
              AND TT.GateSortID = LastGate1.MaxGateSortID
     )LastDate
            ON T.StaffId = LastDate.StaffId
      GROUP BY T.StaffId,FirstDate.transitionDate,LastDate.transitionDate

----3.Based on above temp table get first and last transition gates
 IF OBJECT_ID('tempdb..#FirstandLastGates') IS NOT NULL DROP TABLE 
  #FirstandLastGates
  SELECT T.StaffId,
        [FirstGate] = FirstGate.GateName,
        [LastGate] = LastGate.GateName
 INTO #FirstandLastGates
 FROM #Transitions T
JOIN 
(
    SELECT StaffID,
           [MinGateSortID] = MIN(GateSortID),
           TT.GateID,
           GateName
    FROM #Transitions TT
    JOIN Gates G
        ON TT.GateId = G.GateId
    WHERE GateSortID = 1-- MIN VALUE WILL ALWAYS BE 1
        --AND TT.transitionDate BETWEEN @Datefrom AND @DateTo
    GROUP BY StaffID,TT.GateID,GateName
)FirstGate
    ON T.StaffID= FirstGate.StaffID
LEFT JOIN 
(
    SELECT TT.StaffId, TT.GateId, G.GateName
    FROM #Transitions TT
    JOIN Gates G
        ON TT.GateId = G.GateId
    JOIN 
        (
            SELECT StaffID,
                [MaxGateSortID] = MAX(GateSortID)
            FROM #Transitions TT
            WHERE GateSortID <> 1 -- SO THAT IF THE PERSON HAS NOT CLOCKED 
           -- OUT YET ONLY CLOCKED IN DO NOT CONSIDER THAT THE GATES ARE --SAME
            --AND TT.transitionDate BETWEEN @Datefrom AND @DateTo
            GROUP BY StaffID
        ) LastGate1
        ON TT.StaffId = LastGate1.StaffId
        AND TT.GateSortID = LastGate1.MaxGateSortID
    )LastGate
         ON T.StaffId = LastGate.StaffId
 GROUP BY T.StaffId,FirstGate.GateName,LastGate.GateName

--MAIN OUTPUT
SELECT DISTINCT --t.Id, 
       --t.transitionDate, 
       [MinTransitionDate] = D.FirstDate, 
       [MaxTransitionDate] = D.LastDate, 
       [FirstGateName] = G.FirstGate, 
       [LastGateName] = G.LastGate, 
       T.[StaffId],
       S.StaffType
from Staff S
JOIN #Transitions T
    ON S.StaffId = T.StaffId
JOIN #FirstandLastDates D
    ON T.StaffId = D.StaffId
JOIN #FirstandLastGates G
    ON T.StaffId = G.StaffId
GROUP BY t.Id, D.FirstDate, D.LastDate, G.FirstGate,G.LastGate,T.[StaffId], 
S.StaffType
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.33654 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO