Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Totals for each month in a date range query Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, November 25, 2009 3:08 PM
 Grasshopper Group: General Forum Members Last Login: Tuesday, April 2, 2013 1:18 PM Points: 20, Visits: 138
 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: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 2009Below is my data structure and some values.CREATE TABLE RECEIVABLE_LINE(ROWID INT ,REFERENCE varchar(40) ,QTY decimal(14, 4),AMOUNT decimal(15, 2),INVOICE_ID varchar(15),CUST_ORDER_ID varchar(15),CUST_ORDER_LINE_NO smallint)CREATE TABLE PART(ROWID INT ,ID varchar(30),PRODUCT_CODE varchar(15),USER_1 varchar(30))CREATE TABLE RECEIVABLE(ROWID INT ,INVOICE_ID varchar(15),INVOICE_DATE datetime,TOTAL_AMOUNT decimal(15, 2),CUSTOMER_ID varchar(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')
Post #824969
 Posted Wednesday, November 25, 2009 3:44 PM
 SSCertifiable Group: General Forum Members Last Login: Wednesday, February 10, 2016 11:50 AM Points: 6,897, Visits: 13,559
 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. LutzA pessimist is an optimist with experience. How to get fast answers to your question How to post performance related questionsLinks for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #824989
 Posted Wednesday, November 25, 2009 7:37 PM
 SSC-Insane Group: General Forum Members Last Login: Yesterday @ 10:24 PM Points: 23,522, Visits: 37,760
 And, based on the sample data provided, what should our expected results be so we know if our code is correct.
Post #825013
 Posted Wednesday, November 25, 2009 10:12 PM
 SSC-Forever Group: General Forum Members Last Login: Yesterday @ 11:30 PM Points: 42,081, Visits: 39,471
 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 2009Great 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." Helpful Links:How to post code problemsHow to post performance problems
Post #825041
 Posted Monday, November 30, 2009 7:45 AM
 Grasshopper Group: General Forum Members Last Login: Tuesday, April 2, 2013 1:18 PM Points: 20, Visits: 138
 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
Post #826210
 Posted Tuesday, December 1, 2009 7:56 AM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 2:12 PM Points: 5,667, Visits: 8,193
 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 monthsum(case when year(invoice_date) = 2009 then total_amount else 0 end) as '2009'from #receivablejan2009 Feb2009 2009--------------------------------------- --------------------------------------- ---------------------------------------6412.00 7468.00 32088.00` Best,Kevin G. BolesSQL Server ConsultantSQL MVP 2007-2012TheSQLGuru at GMail
Post #826702
 Posted Tuesday, December 1, 2009 11:43 AM
 SSC-Forever Group: General Forum Members Last Login: Yesterday @ 11:30 PM Points: 42,081, Visits: 39,471
 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 2009Yeah, 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." Helpful Links:How to post code problemsHow to post performance problems
Post #826892
 Posted Wednesday, December 2, 2009 11:26 AM
 Grasshopper Group: General Forum Members Last Login: Tuesday, April 2, 2013 1:18 PM Points: 20, Visits: 138
 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 datetimeASBEGIN 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_AMOUNTFROM 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_IDWHERE (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)) TableDatePIVOT (SUM (TOTAL_AMOUNT)FOR [MONTH] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) PivotTableEND
Post #827699
 Posted Wednesday, December 2, 2009 2:13 PM
 SSC-Forever Group: General Forum Members Last Login: Yesterday @ 11:30 PM Points: 42,081, Visits: 39,471
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #827788
 Posted Wednesday, December 2, 2009 2:53 PM
 Grasshopper Group: General Forum Members Last Login: Tuesday, April 2, 2013 1:18 PM Points: 20, Visits: 138
 Awesome! thanks.. Going to read the article tonight.
Post #827822

 Permissions