Previous Years Report

• Luis Cazares

  SSC Guru

  Points: 183704

  More actions

  July 2, 2015 at 10:33 am

  #1809989

  gazy007 (7/2/2015)

  ---

  Thanks Luis, But I am not getting right numbers for 2015 and 2016 as we need to get for the whole year rather than getdate() or today. I hope I am making sense here.

  SUM(CASE WHEN YEAR(PeriodEnd) = YEAR(DATEADD(YY, 0,)))) THEN Value Else 0 End) as '2015'

  SUM(CASE WHEN YEAR(PeriodEnd) = YEAR(DATEADD(YY, 1,)))) THEN Value Else 0 End) as '2016'

  Thanks Luis.

  Why do you believe that you're getting numbers for just one day?

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• gazy007

  Default port

  Points: 1491

  More actions

  July 2, 2015 at 1:03 pm

  #1810041

  I am not getting any data when I add 2015 and 2016 line as it carries on for 3 minutes and then I cancel the query.

  I am not 100% sure what is causing the issue?

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  July 2, 2015 at 1:20 pm

  #1810047

  gazy007 (7/2/2015)

  ---

  I am not getting any data when I add 2015 and 2016 line as it carries on for 3 minutes and then I cancel the query.

  I am not 100% sure what is causing the issue?

  on june 11th...Luis gave you a very good example of how to post example data.....me thinks that if you follow this example and provide details of your expected results based on the example data, that you will very quickly get a tried and tested response.

  will save you (and others) a lot of time

  just my two cents 🙂

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• DonlSimpson

  SSCertifiable

  Points: 7043

  More actions

  July 2, 2015 at 2:01 pm

  #1810053

  gazy007 (7/2/2015)

  ---

  Thanks Luis, But I am not getting right numbers for 2015 and 2016 as we need to get for the whole year rather than getdate() or today. I hope I am making sense here.

  SUM(CASE WHEN YEAR(PeriodEnd) = YEAR(DATEADD(YY, 0,)))) THEN Value Else 0 End) as '2015'

  SUM(CASE WHEN YEAR(PeriodEnd) = YEAR(DATEADD(YY, 1,)))) THEN Value Else 0 End) as '2016'

  Thanks Luis.

  Since you are hard coding the column labels ('2015', '2016'), why don't you simplify the query with:

  SUM(CASE WHEN YEAR(PeriodEnd) = 2015 THEN Value Else 0 End) as '2015'

  Don Simpson
  

  ---

  
  I'm not sure about Heisenberg.