Converting Daily data to Weekly data Sql Server 2005

• reubenraj

  SSChasing Mays

  Points: 651

  More actions

  July 28, 2012 at 7:18 am

  #1518870

  Sorry friend..

  I guess My computer is little just too hard to convince..

  Check this code. I'm generating Friday date and could you please check sClose is able to get the value on this date?

  DECLARE @year_start DATETIME

  SELECT @year_start= '03-JAN-2011'

  DECLARE @year_end DATETIME

  SELECT @year_end= '31-DEC-2011'

  ;WITH Date_range

  AS

  (

  SELECT @year_start Start_date, DATEADD(s,-1,@year_start+5) End_date

  UNION ALL

  SELECT Start_date + 7, DATEADD(s,-1,Start_date+7+5)

  FROM Date_range

  WHERE Start_date + 1 <=@year_end

  AND DATEADD(s,-1,Start_date+7+5) <=@year_end

  AND DATEPART(dw,Start_date)=2

  )

  SELECT symbol,Start_date as WeekDate,MAX(shigh) sHigh,

  MIN(sLow) sLow,

  MAX(CASE CONVERT(DATETIME,CONVERT(DATETIME,stimestamp))

  WHEN Start_date THEN sOpen END) AS sOpen,

  MAX(CASE CONVERT(DATETIME,CONVERT(DATETIME,stimestamp))

  WHEN End_date THEN sClose END) AS sClose,

  max(End_date) as FridayDate

  FROM usrstocks.NseData n,Date_range d

  WHERE n.stimestamp BETWEEN d.Start_date AND d.End_date

  GROUP BY symbol,Start_date

  order by symbol,Start_date

• reubenraj

  SSChasing Mays

  Points: 651

  More actions

  July 28, 2012 at 7:27 am

  #1518871

  Lokesh,

  I think i know why it is not working on my system. But I need your help.

  See The version of sql server which i'm using is 2005 and it seems you are using 2008. There is no data type "DATE" in 2005.

  Check the pic now and please notice the values of FridayDate it is showing 23 hrs 59 min and 59 sec originally the time will be 00 hrs 00min and 00 sec.

  

  For first record the value is being displayed.

  Here is the code:

  [code:"sql"]

  DECLARE @year_start DATETIME

  SELECT @year_start= '03-JAN-2011'

  DECLARE @year_end DATETIME

  SELECT @year_end= '31-DEC-2011'

  ;WITH Date_range

  AS

  (

  SELECT @year_start Start_date, DATEADD(dd,-1,@year_start+5) End_date

  UNION ALL

  SELECT Start_date + 7, DATEADD(s,-1,Start_date+7+5)

  FROM Date_range

  WHERE Start_date + 1 <=@year_end

  AND DATEADD(s,-1,Start_date+7+5) <=@year_end

  AND DATEPART(dw,Start_date)=2

  )

  SELECT symbol,Start_date as WeekDate,MAX(shigh) sHigh,

  MIN(sLow) sLow,

  MAX(CASE CONVERT(DATETIME,CONVERT(DATETIME,stimestamp))

  WHEN Start_date THEN sOpen END) AS sOpen,

  MAX(CASE CONVERT(DATETIME,CONVERT(DATETIME,stimestamp))

  WHEN End_date THEN sClose END) AS sClose,

  max(End_date) as FridayDate

  FROM usrstocks.NseData n,Date_range d

  WHERE n.stimestamp BETWEEN d.Start_date AND d.End_date

  GROUP BY symbol,Start_date

  order by symbol,Start_date

  [/code]

• reubenraj

  SSChasing Mays

  Points: 651

  More actions

  July 28, 2012 at 7:35 am

  #1518872

  Here I get the results. THANKS A LOT MATE.

  DECLARE @year_start DATETIME

  SELECT @year_start= '03-JAN-2011'

  DECLARE @year_end DATETIME

  SELECT @year_end= '31-DEC-2011'

  ;WITH Date_range

  AS

  (

  SELECT @year_start Start_date, DATEADD(dd,-1,@year_start+5) End_date

  UNION ALL

  SELECT Start_date + 7, DATEADD(dd,-1,Start_date+7+5)

  FROM Date_range

  WHERE Start_date + 1 <=@year_end

  AND DATEADD(dd,-1,Start_date+7+5) <=@year_end

  AND DATEPART(dw,Start_date)=2

  )

  SELECT symbol,Start_date as WeekDate,MAX(shigh) sHigh,

  MIN(sLow) sLow,

  MAX(CASE CONVERT(DATETIME,CONVERT(DATETIME,stimestamp))

  WHEN Start_date THEN sOpen END) AS sOpen,

  MAX(CASE CONVERT(DATETIME,CONVERT(DATETIME,stimestamp))

  WHEN End_date THEN sClose END) AS sClose,

  max(End_date) as FridayDate

  FROM usrstocks.NseData n,Date_range d

  WHERE n.stimestamp BETWEEN d.Start_date AND d.End_date

  GROUP BY symbol,Start_date

  order by symbol,Start_date

  All Credit Goes to you Lokesh. Thanks a lot a lot.. Can't just explain how good it feels.

  Amazing stuff.

  Regards,

  Reuben

• Lokesh Vij

  SSChampion

  Points: 10836

  More actions

  July 28, 2012 at 8:23 am

  #1518875

  All Credit Goes to you Lokesh. Thanks a lot a lot.. Can't just explain how good it feels.

  Amazing stuff.

  Really glad to know that I was helpful to you

  Feel free to get in touch with me

  Cheers

  Lokesh

  SQLPathy (Blogs)[/url]

  Follow me at twitter

  ~ Lokesh Vij
  

  ---

  Guidelines for quicker answers on T-SQL question[/url]
  Guidelines for answers on Performance questions

  Link to my Blog Post --> www.SQLPathy.com[/url]

  Follow me @Twitter