Post reply

Need help with year to date query for the past three years.

  • January 20, 2011 at 3:39 pm

    #232519

    I have a report that will compare this years sales numbers year-to-date with last years numbers year-to-date. Here is the query filter that I use....

    NOT BETWEEN DATEADD(Year, - 1, @end_date) AND DATEADD(d, - 1, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))

    Now I would like to expand the report to show this years numbers year-to-date and the last two years year-to-date. Any idea on how I would do that? Right now when I run the report it has all of 2009, 2010 year-to-date, and 2011 year-to-date. I don't know how to make it show 2009 year-to-date, 2010 year-to-date, and 2011 year-to-date. I had someone help me with the original report, and am not even positive that I can reuse what I've got. Maybe I need to redo the whole query filter? Thanks in advance. I'm kind of a noob when it comes to this.

  • January 20, 2011 at 8:53 pm

    #1276354

    Since you did not supply the table structure or data, I made up my table and populated it with my data:

    CREATE TABLE #T(Sales MONEY,DayofSale DATE)

    INSERT INTO #T(Sales,DayofSale)

    SELECT 100.10,'01-02-2009' UNION ALL

    SELECT 200.20 ,'01-03-2009' UNION ALL

    SELECT 300.30, '01-08-2009' UNION ALL

    SELECT 900.10,'01-15-2010' UNION ALL

    SELECT 800.20 ,'02-11-2010' UNION ALL

    SELECT 700.30, '01-18-2009' UNION ALL

    SELECT 90.00,'01-02-2011' UNION ALL

    SELECT 80.00 ,'02-01-2011' UNION ALL

    SELECT 70.30, '01-19-2011'

    And then executed this T-SQL

    SELECT SUM(Sales),DATEPART ( yyyy , DayofSale)

    FROM #T WHERE DayofSale <= '01-18-2011' GROUP BY DATEPART ( yyyy , DayofSale)

    Obtaining these results:

    (No column name)(No column name)

    1300.90 2009

    1700.30 2010

    90.00 2011

    Next time you post to a forum, please, please supply the table definition, sample data and desired results. Please refer to the first link in my signature box to learn how to do this easily and quickly. It will help those who want to help you to give you a tested result in the least amount of time.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • January 20, 2011 at 10:44 pm

    #1276380

    Well Done..BitBucket...

    Thats a great way to do it...!!

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply