Totals for each month in a date range query

  • Hello SQL gurus..

    I've been trying to figure out a way to get the total values of each month and year in a given date range.

    results like so:

    JanFeb Mar April May Jun JUL YEAR

    $23,000 $36,236.00 $36,236.00 $36,236.00 $36,236.00 $36,236.00 $2,008.00 2008

    $23,000 $36,236.00 $36,236.00 $36,236.00 $36,236.00 $36,236.00 $2,008.00 2009

    Below is my data structure and some values.

    CREATE TABLE RECEIVABLE_LINE

    (

    ROWID INT ,

    REFERENCE varchar(40) ,

    QTYdecimal(14, 4),

    AMOUNTdecimal(15, 2),

    INVOICE_IDvarchar(15),

    CUST_ORDER_IDvarchar(15),

    CUST_ORDER_LINE_NOsmallint

    )

    CREATE TABLE PART

    (

    ROWID INT ,

    ID varchar(30),

    PRODUCT_CODE varchar(15),

    USER_1 varchar(30)

    )

    CREATE TABLE RECEIVABLE

    (

    ROWID INT ,

    INVOICE_IDvarchar(15),

    INVOICE_DATEdatetime,

    TOTAL_AMOUNTdecimal(15, 2),

    CUSTOMER_IDvarchar(15)

    )

    CREATE TABLE CUST_ORDER_LINE

    (

    ROWID INT ,

    LINE_NO smallint ,

    CUST_ORDER_ID varchar(15),

    PART_ID varchar(30) ,

    UNIT_PRICE decimal(15, 6),

    TRADE_DISC_PERCENT decimal(6, 3)

    )

    INSERT INTO RECEIVABLE_LINE VALUES (18841, '6 x 10 Utility Angle- Black',1,899,'IN44254', 'S022724' ,1)

    INSERT INTO RECEIVABLE_LINE VALUES (18842, '7 x 12 Utility Angle- Black',1,299,'IN44255', 'S022725' ,1)

    INSERT INTO RECEIVABLE_LINE VALUES (18843, '5 x 8 Wood Side Kit',1,999,'IN44253', 'S022684' ,1)

    INSERT INTO RECEIVABLE_LINE VALUES (18844, '5 x 10 Tube Top Angle- Black',1,899,'IN44252', 'S022577' ,1)

    INSERT INTO RECEIVABLE_LINE VALUES (18845, '6 x 10 Utility Angle',1,699,'IN44227', 'S021149' ,1)

    INSERT INTO RECEIVABLE_LINE VALUES (18846, 'Utility Angle- Black',1,399,'IN44228', 'S021150' ,1)

    INSERT INTO RECEIVABLE_LINE VALUES (18847, 'Utility Angle- Black',1,299,'IN44226', 'S021107' ,1)

    INSERT INTO CUST_ORDER_LINE VALUES (52187, 1, 'S022724', 'ST7210UA-B', 2004.000000, 25.000 )

    INSERT INTO CUST_ORDER_LINE VALUES (82691, 1, 'S022725', 'ST7210UA-B', 904.000000, 25.000 )

    INSERT INTO CUST_ORDER_LINE VALUES (70936, 1, 'S022684', 'ST7210UA-B', 3104.000000, 25.000 )

    INSERT INTO CUST_ORDER_LINE VALUES (71766, 1, 'S022577', 'ST79184TE-B-140', 1104.000000, 25.000 )

    INSERT INTO CUST_ORDER_LINE VALUES (71766, 1, 'S021149', 'ST79184TE-B-140', 1104.000000, 25.000 )

    INSERT INTO CUST_ORDER_LINE VALUES (71766, 1, 'S021150', 'ST7210UA-B', 1104.000000, 25.000 )

    INSERT INTO CUST_ORDER_LINE VALUES (71766, 1, 'S021107', 'ST7210UA-B', 1104.000000, 25.000 )

    INSERT INTO RECEIVABLE VALUES (23101, 'IN44254', '2009-01-11', 1104.000000, 'I69TRA')

    INSERT INTO RECEIVABLE VALUES (34282, 'IN44255', '2009-01-15', 3204.000000, 'I69TRA')

    INSERT INTO RECEIVABLE VALUES (30163, 'IN44253', '2009-01-12', 2104.000000, 'I69TRA')

    INSERT INTO RECEIVABLE VALUES (30534, 'IN44252', '2009-02-15', 3364.000000, 'I69TRA')

    INSERT INTO RECEIVABLE VALUES (30535, 'IN44227', '2009-03-12', 5104.000000, 'I69TRA')

    INSERT INTO RECEIVABLE VALUES (30536, 'IN44228', '2009-03-20', 13104.000000, 'I69TRA')

    INSERT INTO RECEIVABLE VALUES (30537, 'IN44226', '2009-02-02', 4104.000000, 'I69TRA')

    INSERT INTO PART VALUES (1971,'ST7210UA-B', 'TRAILER', 'Landscape')

    INSERT INTO PART VALUES (1972,'ST7210UA-B', 'TRAILER', 'Landscape')

    INSERT INTO PART VALUES (1973,'ST7210UA-B', 'TRAILER', 'Landscape')

    INSERT INTO PART VALUES (1974,'ST79184TE-B-140', 'TRAILER', 'Landscape')

    INSERT INTO PART VALUES (1975,'ST8116DTS-B-140', 'TRAILER', 'Landscape')

    INSERT INTO PART VALUES (1976,'ST7210UA-B', 'TRAILER', 'Landscape')

    INSERT INTO PART VALUES (1977,'ST7210UA-B', 'TRAILER', 'Landscape')

  • What have you tried so far?

    I'd recommend reading the articles regarding CrossTab and DynamicCrossTab (if required) as referenced in my signature.

    Before doing so you probably should get the data in a format like Year, Month, Amount.



    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]

  • And, based on the sample data provided, what should our expected results be so we know if our code is correct.

  • stewsterl (11/25/2009)


    Jan Feb Mar April May Jun JUL YEAR

    $23,000 $36,236.00 $36,236.00 $36,236.00 $36,236.00 $36,236.00 $2,008.00 2008

    $23,000 $36,236.00 $36,236.00 $36,236.00 $36,236.00 $36,236.00 $2,008.00 2009

    Great post and thanks for all the data and table creation statements. I just don't know what you want to be included in the totals above.

    --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)

  • The results I'm looking for are the totals for each month of each year.

    Jan Feb Mar April May Jun JUL YEAR

    $23,000 $36,236.00 $36,236.00 $36,236.00 $36,236.00 $36,236.00 $2,008.00 2008

    $23,000 $36,236.00 $36,236.00 $36,236.00 $36,236.00 $36,236.00 $2,008.00 2009

  • If you know IN ADVANCE what date range you wish to deal with, then a fixed query such as below is perfect. If you don't, then I recommend you to search for dynamic crosstab like someone else suggested. Here is one such item: http://www.sommarskog.se/pivot_sp.sp, with some comments about that here: http://www.sommarskog.se/dynamic_sql.html#Crosstab

    CREATE TABLE #RECEIVABLE

    (

    ROWID INT ,

    INVOICE_ID varchar(15),

    INVOICE_DATE datetime,

    TOTAL_AMOUNT decimal(15, 2),

    CUSTOMER_ID varchar(15)

    )

    INSERT INTO #RECEIVABLE VALUES (23101, 'IN44254', '2009-01-11', 1104.000000, 'I69TRA')

    INSERT INTO #RECEIVABLE VALUES (34282, 'IN44255', '2009-01-15', 3204.000000, 'I69TRA')

    INSERT INTO #RECEIVABLE VALUES (30163, 'IN44253', '2009-01-12', 2104.000000, 'I69TRA')

    INSERT INTO #RECEIVABLE VALUES (30534, 'IN44252', '2009-02-15', 3364.000000, 'I69TRA')

    INSERT INTO #RECEIVABLE VALUES (30535, 'IN44227', '2009-03-12', 5104.000000, 'I69TRA')

    INSERT INTO #RECEIVABLE VALUES (30536, 'IN44228', '2009-03-20', 13104.000000, 'I69TRA')

    INSERT INTO #RECEIVABLE VALUES (30537, 'IN44226', '2009-02-02', 4104.000000, 'I69TRA')

    select sum(case when invoice_date between '1/1/2009' and '1/31/2009' then total_amount else 0 end) as 'jan2009',

    sum(case when invoice_date between '2/1/2009' and '2/28/2009' then total_amount else 0 end) as 'Feb2009',

    --etc for each month

    sum(case when year(invoice_date) = 2009 then total_amount else 0 end) as '2009'

    from #receivable

    jan2009 Feb2009 2009

    --------------------------------------- --------------------------------------- ---------------------------------------

    6412.00 7468.00 32088.00

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • stewsterl (11/30/2009)


    The results I'm looking for are the totals for each month of each year.

    Jan Feb Mar April May Jun JUL YEAR

    $23,000 $36,236.00 $36,236.00 $36,236.00 $36,236.00 $36,236.00 $2,008.00 2008

    $23,000 $36,236.00 $36,236.00 $36,236.00 $36,236.00 $36,236.00 $2,008.00 2009

    Yeah, I know that.... but from WHICH columns from WHICH tables? Everything in the quote qabove is a "total". Are you talking about the TOTAL_AMOUNT value from the Receivables table?

    --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)

  • sorry. yes TOTAL_AMOUNT from the TABLE RECEIVABLE.

    I was using the stored procedure below but I cannot use pivot tables because our ERP system requires 2000 compatibility (80).

    @CustomerID varchar(15),

    @BeginDate datetime,

    @EndDate datetime

    AS

    BEGIN

    ELECT *

    FROM (

    SELECT RECEIVABLE_LINE.REFERENCE AS 'DESCRIPTION', PART.USER_1 AS 'GROUP', RECEIVABLE_LINE.QTY,

    RECEIVABLE_LINE.AMOUNT, PART.ID, RECEIVABLE_LINE.INVOICE_ID,

    MONTH(RECEIVABLE.INVOICE_DATE) AS 'MONTH', RECEIVABLE.TOTAL_AMOUNT

    FROM PART AS PART INNER JOIN

    CUST_ORDER_LINE AS CUST_ORDER_LINE INNER JOIN

    RECEIVABLE_LINE AS RECEIVABLE_LINE INNER JOIN

    RECEIVABLE AS RECEIVABLE ON RECEIVABLE_LINE.INVOICE_ID = RECEIVABLE.INVOICE_ID ON

    CUST_ORDER_LINE.CUST_ORDER_ID = RECEIVABLE_LINE.CUST_ORDER_ID AND CUST_ORDER_LINE.LINE_NO = RECEIVABLE_LINE.CUST_ORDER_LINE_NO ON

    PART.ID = CUST_ORDER_LINE.PART_ID

    WHERE (RECEIVABLE.INVOICE_DATE >= @STARTDATE) AND (RECEIVABLE.INVOICE_DATE <= @ENDDATE) AND (PART.PRODUCT_CODE = 'TRAILER') AND

    (PART.ID NOT LIKE 'UPG%') AND (RECEIVABLE_LINE.QTY > $0) AND (RECEIVABLE_LINE.REFERENCE LIKE '[1-9]%') AND (RECEIVABLE.CUSTOMER_ID=@CUSTOMER_ID)) TableDate

    PIVOT (

    SUM (TOTAL_AMOUNT)

    FOR [MONTH] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) PivotTable

    END

  • stewsterl (12/2/2009)


    sorry. yes TOTAL_AMOUNT from the TABLE RECEIVABLE.

    I was using the stored procedure below but I cannot use pivot tables because our ERP system requires 2000 compatibility (80).

    Heh... not a problem. I can bang out a cross-tab tonight but take a look at the following URL... you might just be able to learn something new from the following article. I know the author... he's a pretty good guy and pretty much insane when it comes to performance on things like this... 😛

    http://www.sqlservercentral.com/articles/T-SQL/63681/

    --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)

  • Awesome! thanks.. Going to read the article tonight.

  • stewsterl (12/2/2009)


    Awesome! thanks.. Going to read the article tonight.

    Perfect. I love it when folks strive...

    --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)

  • Any luck with the query? I read the page of the link you sent. Not sure how to implement this in my project.

    SUCKS being a noooob at SQL. But I have leaned a ton from this site thus far.

    This is what I came up with:

    @CustomerID varchar(15),

    @Year varchar(15)

    AS

    BEGIN

    select

    sum(case when invoice_date between '1/1/'+@Year and '1/31/'+@Year then total_amount else 0 end) as 'Jan',

    sum(case when invoice_date between '2/1/'+@Year and '2/28/'+@Year then total_amount else 0 end) as 'Feb',

    sum(case when invoice_date between '3/1/'+@Year and '3/31/'+@Year then total_amount else 0 end) as 'Mar',

    sum(case when invoice_date between '4/1/'+@Year and '4/30/'+@Year then total_amount else 0 end) as 'Apr',

    sum(case when invoice_date between '5/1/'+@Year and '5/31/'+@Year then total_amount else 0 end) as 'May',

    sum(case when invoice_date between '6/1/'+@Year and '6/30/'+@Year then total_amount else 0 end) as 'Jun',

    sum(case when invoice_date between '7/1/'+@Year and '7/31/'+@Year then total_amount else 0 end) as 'Jul',

    sum(case when invoice_date between '8/1/'+@Year and '8/31/'+@Year then total_amount else 0 end) as 'Aug',

    sum(case when invoice_date between '9/1/'+@Year and '9/30/'+@Year then total_amount else 0 end) as 'Sep',

    sum(case when invoice_date between '10/1/'+@Year and '10/31/'+@Year then total_amount else 0 end) as 'Oct',

    sum(case when invoice_date between '11/1/'+@Year and '11/30/'+@Year then total_amount else 0 end) as 'Nov',

    sum(case when invoice_date between '12/1/'+@Year and '12/31/'+@Year then total_amount else 0 end) as 'Dec',

    sum(case when year(invoice_date) = 2009 then total_amount else 0 end) as 'Total'

    from receivable

    WHERE (RECEIVABLE.CUSTOMER_ID=@CustomerID)

    END

    This works fine if I send a year and a customerid. But the requirement is for a date range to be sent like I had in my other query.

    like feb 3 2007 - dec 3 2009

    Any help would be awesome.. thanks for the link which helped me get to the above query... Crawl-Walk-Run

  • You did real good especially for a first timer on such a thing! You've gotten yourself into just a little bit of a problem because you used BETWEEN instead of the more effective classic [font="Courier New"]Invoice_Date >= start of this month and Invoice_Date < start of next month[/font], but we'll hammer that out. I'm still at work. I'll wittle on this tonight.

    --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)

  • By the way... here's the next step in turning you from a newbie to an SQL monster... it's what I'll use tonight to solve your problem... heh... unless you beat me to it...

    http://www.sqlservercentral.com/articles/Crosstab/65048/

    ... the author of that article is a pretty good guy, too! 😛

    --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)

  • you could take what theSQLGuru wrote and make it generalizable for all years with Datepart functions.

    select sum(case when datepart(mm,Invoice_date)=1 then Total_Amount else 0 end ) as Jan,

    sum(case when datepart(mm,Invoice_date)=2 then Total_Amount else 0 end ) as Feb,

    -- etc

    Datepart(yy, Invoice_date) as Yr

    from #RECEIVABLE

    group by Datepart(yy, Invoice_date)

Viewing 15 posts - 1 through 14 (of 14 total)

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