Home T-SQL Max date and min date with value single row
Reply: 0

T-SQL Max date and min date with value single row

user10276
1#
user10276 Published in September 21, 2018, 8:17 am

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
share|improve this question
  • 5
    Can you include what you have tried, and any issues you are having – Matthew Evans Feb 13 at 8:07
  • also include your ddls and insert scripts. – Pawan Kumar Feb 13 at 8:09
  • is that Front door is for entry (First movement)and Backdoor is for Exit (Last movement)..? – Mahesh.K Feb 13 at 8:21
  • @MatthewEvans I do not get the result I want. – serenity Feb 13 at 8:28
  • @Mahesh.K no rule. it can be a different gate name – serenity Feb 13 at 8:32

3 Answers 3

active oldest votes
up vote 3 down vote accepted
You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO