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

T-SQL Case when with Using with Sql Two Column

user3163
1#
user3163 Published in May 21, 2018, 6:44 pm

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

You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO