Post reply

sum by each quarter

  • September 14, 2010 at 10:53 am

    #223833

    Hi,

    I ahve a period table like this.

    SELECT [PeriodID]

    ,[StartDate]

    ,[EndDate]

    ,[StartMonth]

    ,[EndMonth]

    ,[PeriodDesc]

    FROM [Report].[refPeriod]

    GO

    Data in the table is like this

    PeriodIDStartDateEndDateStartMonthEndMonthPeriodDesc

    1 2009-07-01 00:00:00.000 2009-09-30 23:59:59.000200907200909 Q1 2009

    Jul - Sep

    2 2009-10-01 00:00:00.0002009-12-31 23:59:59.000 200910 200912 Q2 2009

    Oct - Dec

    3 2010-01-01 00:00:00.0002010-03-31 23:59:59.000 201001 201003 Q3 2009

    Jan - Mar

    4 2010-04-01 00:00:00.0002010-06-30 23:59:59.000 201004 201006 Q4 2009

    Apr - Jun

    5 2010-07-01 00:00:00.0002010-09-30 23:59:59.000 201007 201009 Q1 2010

    Jul - Sep

    When a user selectsthe quarter then I need nned have sum for that quarter and follwed by four preios quarters

    My function now is somewhat like this

    CREATE FUNCTION [dbo].[GetDisposition](@iQuarter int)

    RETURNS TABLE

    AS

    RETURN(

    SELECT dispositiondesc,

    CASE WHEN typeofservice IN ('Sales', 'Service') THEN UPPER(businesschannel) ELSE UPPER(typeofservice) END typeoftransaction,

    CASE WHEN typeofservice IN ('Sales', 'Service') THEN 'SALES AND SERVICES' ELSE 'CLAIMS' END GeneralType, count(*) AS 'tCount'

    FROM

    sample.sampledisposition a JOIN sample.refDisposition b ON (a.disposition = b.disposition) JOIN sample.sampleinclude c ON (a.sampleid = c.sampleid)

    WHERE a.disposition <> 2 and a. DispositionDate between

    (select (dateadd(m,-0,startdate)) as 'Quarter Start Date' from [Report].[refPeriod] where periodid = @iQuarter) and

    (select EndDate as 'Quarter End Date' from[Report].[refPeriod] where periodid = @iQuarter)

    GROUP BY dispositiondesc, CASE WHEN typeofservice IN ('Sales', 'Service') THEN UPPER(businesschannel) ELSE UPPER(typeofservice) END,

    CASE WHEN typeofservice IN ('Sales', 'Service') THEN 'SALES AND SERVICES' ELSE 'CLAIMS' END

    This display only the selected quatrter sum

    . but I need for the selected quarter and also previous forur quarters sum.

    Can any one help me with this.

    Thank you

    thank you

  • September 14, 2010 at 11:05 am

    #1220848

    change:

    periodid = @iQuarter

    to:

    periodid between @iQuarter-4 and @iQuarter

    You might also want to look at the first link in my signature for how to HELP US HELP YOU get faster, better, tested answers.

    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

  • September 14, 2010 at 1:21 pm

    #1220925

    Seems like a related issue posted here...


    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]

  • September 14, 2010 at 4:00 pm

    #1221044

    I GOT THIS ONE.

    THANKS FOR REPLYING

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

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