Need to Split a Date Column into 2 solumn as Start Date and End Date

• AdityaSingh

  SSC Journeyman

  Points: 89

  More actions

  March 6, 2014 at 3:12 am

  #100548

  I need help over this thing. I am working on a table which looks like

  Date Dateofyear

  2014-01-01 1

  2014-01-02 2

  2014-01-03 3

  2014-01-04 4

  2014-01-05 5

  . .

  ..

  ..

  2014-12-31 365

  Now i need to Run a query on this table to show me result after taking input from user (store proc ) if user enters 2 then

  StartDate EndDate

  2014/01/01 2014/07/01

  2014-07-02 2014-12-31

  and if user enter 5 then

  StartDate EndDate

  2014/01/01 2014-03-14

  2014-03-15 2014-05-26

  2014-05-27 2014-08-07

  2014-08-08 2014-10-19

  2014-10-19 2014-12-31

  I hope I made it clear; kindly Help me over this.

  Alot of thanks in Advance ;

  please

• Koen Verbeeck

  SSC Guru

  Points: 259105

  More actions

  March 6, 2014 at 4:26 am

  #1694609

  This can easily be achieved by using NTILE.

  DECLARE @parts INT = 5;

  SELECT

  StartDate= MIN([Date])

  ,EndDate= MAX([Date])

  FROM

  (

  SELECT

  [Date]

  ,Parts = NTILE(@parts) OVER (ORDER BY [DateOfYear])

  FROM [dbo].[myDateTable]

  WHERE [Year] = 2014

  ) tmp

  GROUP BY Parts;

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP