Post reply

Return average from last 12 month with 0 possible

  • June 1, 2011 at 11:21 am

    #240768

    I have the following query and am a little stuck on figuring out how to return a 0 if there is no data for a given month.

    select avg(resolvetime) as xResolveTime, (datename(month,[createdon]) + datename(year,[createdon])) as 'xMonths'

    from Cases

    where accountidname like '%AccountName%' and createdon > dateadd(month,-11,GetDate())

    group by (datename(month,[createdon]) + datename(year,[createdon])) ORDER BY MIN(createdon)

    This will return the average resolution time for a month but will not show a month that has no data.

    Ideas?

    Thanks

  • June 1, 2011 at 11:25 am

    #1332647

    Build a calendar table, do myquery right join calendar where calendar between @start and @end.

    Then you'll be able to use isnull()

  • June 1, 2011 at 11:26 am

    #1332649

    Do you mind providing an example?

  • June 1, 2011 at 11:30 am

    #1332657

    DevNate (6/1/2011)

    Do you mind providing an example?

    What's unclear?

    A calendar table has all the dates relevant to your project.

    Select all the data in the range of date of the query from the calendar that way you have data for all the months. Outer join that to your current result set on the date or month then use ISNULL for the missing data.

    Google has tones of scripts to build such table.

  • June 1, 2011 at 11:31 am

    #1332659

    DevNate (6/1/2011)

    Do you mind providing an example?

    Nope. Do you mind providing table definitions (DDL, aka CREATE TABLE statements), sample data (aka INSERT statements) and expected results based upon that sample data? Help us help you by doing so... see the first link in my signature for how to do so! (Remember, we're all volunteers here, so helping us helps you!)

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • June 1, 2011 at 1:59 pm

    #1332771

    I'll use a smaller set of data. Say I have the a table of Cases with the following columns. Id, Date, Name. What I would like to do is gather the total number of cases in each month for the last year. If there are no cases for the month, display it as 0.

  • June 1, 2011 at 3:35 pm

    #1332839

    DevNate (6/1/2011)

    I'll use a smaller set of data. Say I have the a table of Cases with the following columns. Id, Date, Name. What I would like to do is gather the total number of cases in each month for the last year. If there are no cases for the month, display it as 0.

    select count(*) from OP where OP_ReadWaynesPostAboutProvidingDDLAndSampleDate = 1

    (0 row(s) affected)

    _______________________________________________________________

    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/

  • June 1, 2011 at 3:46 pm

    #1332843

    I'm confident this link will help you getting started on the calendar table concept.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 8 posts - 1 through 7 (of 7 total)

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