SQL Query on Start & End Dates

• rajreddys

  Grasshopper

  Points: 22

  More actions

  May 21, 2017 at 5:26 pm

  #337942

  Hello, I have a requirement to display data with end date as start date in the next row:

  Emp_ID         Hour_Type            Start_Date                     Hours                            End_Date

  100                 REG                06/01/2016 8:00 AM             4                            06/01/2016 12:00 PM
  100                 SL                   06/01/2016 12:00 PM            4                            06/01/2016  04:00 PM
  100                 OT                   06/01/2016 04:00 PM           2                             06/01/2016  6:00 PM

• Alan Burstein

  SSC Guru

  Points: 61152

  More actions

  May 21, 2017 at 7:18 pm

  #1943493

  I did this on my phone and cant test this but i think this is what you need.

  With yourData (rn, Emp_ID, Hour_Type, Start_Date, Hours, End_Date
  AS
  (
  SELECT
   1, 100, 'REG', '06/01/2016 8:00 AM', 4, '06/01/2016 12:00 PM' UNION ALL
  SELECT
  2, 100, 'SL', '06/01/2016 12:00 PM', 4, '06/01/2016 04:00 PM' UNION ALL
   SELECT
    3, 100, 'OT', '06/01/2016 04:00 PM', 2, '06/01/2016 6:00 PM'
  )
  SELECT
  a.Emp_ID,
  a.Hour_Type,
  a.Start_Date,
  a.Hours,
  End_Date = b.Start_Date
  FROM yourData a
  JOIN yourDats b ON a.rn = b.rn-1;

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001

• Thom A

  SSC Guru

  Points: 99143

  More actions

  May 22, 2017 at 2:00 am

  #1943506

  Alan.B - Sunday, May 21, 2017 7:18 PM

  I did this on my phone and cant test this but i think this is what you need.

  With yourData (rn, Emp_ID, Hour_Type, Start_Date, Hours, End_Date
  AS
  (
  SELECT
   1, 100, 'REG', '06/01/2016 8:00 AM', 4, '06/01/2016 12:00 PM' UNION ALL
  SELECT
  2, 100, 'SL', '06/01/2016 12:00 PM', 4, '06/01/2016 04:00 PM' UNION ALL
   SELECT
    3, 100, 'OT', '06/01/2016 04:00 PM', 2, '06/01/2016 6:00 PM'
  )
  SELECT
  a.Emp_ID,
  a.Hour_Type,
  a.Start_Date,
  a.Hours,
  End_Date = b.Start_Date
  FROM yourData a
  JOIN yourDats b ON a.rn = b.rn-1;

  Ahh, the joys of trying to type SQL on your phone.

  There were a few typoes I've corrected for you, but also made it a LEFT JOIN, so that the final row displays, and added an ISNULL. I've also seperated the RN into a different WITH statement, so the OP can see how to generate it. 🙂
  WITH yourData (Emp_ID, Hour_Type, Start_Date, Hours, End_Date)
AS
(
  SELECT 100, 'REG', CAST('06/01/2016 8:00 AM' AS datetime), 4, CAST('06/01/2016 12:00 PM' AS datetime) UNION ALL
  SELECT 100, 'SL', CAST('06/01/2016 12:00 PM' AS datetime), 4, CAST('06/01/2016 04:00 PM' AS datetime) UNION ALL
  SELECT 100, 'OT', CAST('06/01/2016 04:00 PM' AS datetime), 2, CAST('06/01/2016 6:00 PM' AS datetime)
),
RN AS (
  SELECT ROW_NUMBER() OVER (ORDER BY Start_date ASC, End_date ASC) AS RN, *
  FROM yourData
)
SELECT a.Emp_ID,
       a.Hour_Type,
       a.Start_Date,
       a.Hours,
       ISNULL(b.Start_Date,a.End_Date) AS End_Date
FROM RN a
    LEFT JOIN RN b ON a.rn = b.rn-1;


  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Ed Wagner

  SSC Guru

  Points: 287026

  More actions

  May 22, 2017 at 2:54 pm

  #1943661

  Alan.B - Sunday, May 21, 2017 7:18 PM

  I did this on my phone and cant test this but i think this is what you need.

  With yourData (rn, Emp_ID, Hour_Type, Start_Date, Hours, End_Date
  AS
  (
  SELECT
   1, 100, 'REG', '06/01/2016 8:00 AM', 4, '06/01/2016 12:00 PM' UNION ALL
  SELECT
  2, 100, 'SL', '06/01/2016 12:00 PM', 4, '06/01/2016 04:00 PM' UNION ALL
   SELECT
    3, 100, 'OT', '06/01/2016 04:00 PM', 2, '06/01/2016 6:00 PM'
  )
  SELECT
  a.Emp_ID,
  a.Hour_Type,
  a.Start_Date,
  a.Hours,
  End_Date = b.Start_Date
  FROM yourData a
  JOIN yourDats b ON a.rn = b.rn-1;

  You wrote code using your phone?  You're a braver man than I. 😉  I filled in your missing ) and fixed the joined name.

  WITH yourData(rn, Emp_ID, Hour_Type, Start_Date, Hours, End_Date) AS (
SELECT 1, 100, 'REG', '06/01/2016 8:00 AM', 4, '06/01/2016 12:00 PM' UNION ALL
SELECT 2, 100, 'SL', '06/01/2016 12:00 PM', 4, '06/01/2016 04:00 PM' UNION ALL
SELECT 3, 100, 'OT', '06/01/2016 04:00 PM', 2, '06/01/2016 6:00 PM'
)
SELECT a.Emp_ID,
  a.Hour_Type,
  a.Start_Date,
  a.Hours,
  End_Date = b.Start_Date
FROM yourData a
  JOIN yourData b ON a.rn = b.rn - 1;

  Tally Tables - Performance Personified
  String Splitting with True Performance
  Best practices on how to ask questions