Nested Dateadd function for Date range.... any ideas?

• shalaran

  Valued Member

  Points: 63

  More actions

  March 9, 2015 at 8:00 pm

  #298403

  .j

• Sean Lange

  SSC Guru

  Points: 286602

  More actions

  March 9, 2015 at 8:57 pm

  #1782761

  shalaran (3/9/2015)

  ---

  Hi,

  I'm working on a tsql query to return a column with the summed time units for each week. However, I'm only getting a summed total at Week6, and am out of ideas... Could anyone point me in the right direction? Any assistance is greatly appreciate and thank you in advance!

  Week1 = ISNULL(SUM(CASE WHEN tdate Between DATEADD(Week,-4,DateColumn) AND DATEADD(DAY,-1,DATEADD(WEEK,-3,DateColumn) THEN (Hours) END),0 ),

  Week2 = ISNULL(SUM(CASE WHEN tdate BETWEEN DATEADD(WEEK,-3,DateColumn) AND DATEADD(DAY,-1,DATEADD(WEEK,-2,DateColumn)) THEN (Hours) END),0 ),

  Week3 = ISNULL(SUM(CASE WHEN tdate BETWEEN DATEADD(WEEK,-2,DateColumn) AND DATEADD(DAY,-1,DATEADD(WEEK,-1,DateColumn)) THEN (Hours) END),0 ),

  Week4= ISNULL(SUM(CASE WHEN tdate BETWEEN DATEADD(Week,-1,DateColumn) AND DateColumn THEN (Hours) END),0 )

  Hi and welcome to the forums. It is impossible to say what may be the problem from this brief snippet. Please take a few minutes and read the first article referenced in my signature for best practices when posting questions.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• Jeff Moden

  SSC Guru

  Points: 1004588

  More actions

  March 9, 2015 at 9:53 pm

  #1782763

  shalaran (3/9/2015)

  ---

  Hi,

  I'm working on a tsql query to return a column with the summed time units for each week. However, I'm only getting a summed total at Week6, and am out of ideas... Could anyone point me in the right direction? Any assistance is greatly appreciate and thank you in advance!

  Week1 = ISNULL(SUM(CASE WHEN tdate Between DATEADD(Week,-4,DateColumn) AND DATEADD(DAY,-1,DATEADD(WEEK,-3,DateColumn) THEN (Hours) END),0 ),

  Week2 = ISNULL(SUM(CASE WHEN tdate BETWEEN DATEADD(WEEK,-3,DateColumn) AND DATEADD(DAY,-1,DATEADD(WEEK,-2,DateColumn)) THEN (Hours) END),0 ),

  Week3 = ISNULL(SUM(CASE WHEN tdate BETWEEN DATEADD(WEEK,-2,DateColumn) AND DATEADD(DAY,-1,DATEADD(WEEK,-1,DateColumn)) THEN (Hours) END),0 ),

  Week4= ISNULL(SUM(CASE WHEN tdate BETWEEN DATEADD(Week,-1,DateColumn) AND DateColumn THEN (Hours) END),0 )

  Week 6? Where's that at in the code above??? Also, would you please post the complete code and see the link that Sean suggested?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)