InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 01:01:38 GMT20http://www.sqlservercentral.com/Forums/Topic824969-149-1.aspxyou could take what theSQLGuru wrote and make it generalizable for all years with Datepart functions. [code="sql"]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 #RECEIVABLEgroup by Datepart(yy, Invoice_date)[/code]Fri, 04 Dec 2009 10:01:30 GMTkeith.gerritsenhttp://www.sqlservercentral.com/Forums/Topic824969-149-1.aspxBy 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...[url]http://www.sqlservercentral.com/articles/Crosstab/65048/[/url]... the author of that article is a pretty good guy, too! :-PThu, 03 Dec 2009 14:59:52 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic824969-149-1.aspxYou 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.Thu, 03 Dec 2009 14:55:33 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic824969-149-1.aspxAny 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)ASBEGIN 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 receivableWHERE (RECEIVABLE.CUSTOMER_ID=@CustomerID)ENDThis 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 2009Any help would be awesome.. thanks for the link which helped me get to the above query... Crawl-Walk-RunThu, 03 Dec 2009 08:29:47 GMTstewsterlhttp://www.sqlservercentral.com/Forums/Topic824969-149-1.aspx[quote][b]stewsterl (12/2/2009)[/b][hr]Awesome! thanks.. Going to read the article tonight.[/quote]Perfect. I love it when folks strive...Wed, 02 Dec 2009 17:54:04 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic824969-149-1.aspxAwesome! thanks.. Going to read the article tonight.Wed, 02 Dec 2009 14:53:29 GMTstewsterlhttp://www.sqlservercentral.com/Forums/Topic824969-149-1.aspx[quote][b]stewsterl (12/2/2009)[/b][hr]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). [/quote]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... :-P[url]http://www.sqlservercentral.com/articles/T-SQL/63681/[/url]Wed, 02 Dec 2009 14:13:23 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic824969-149-1.aspxsorry. 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])) PivotTableENDWed, 02 Dec 2009 11:26:20 GMTstewsterlhttp://www.sqlservercentral.com/Forums/Topic824969-149-1.aspx[quote][b]stewsterl (11/30/2009)[/b][hr]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[/quote]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?Tue, 01 Dec 2009 11:43:16 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic824969-149-1.aspxIf 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[code="sql"]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[/code]Tue, 01 Dec 2009 07:56:44 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic824969-149-1.aspxThe 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 2009Mon, 30 Nov 2009 07:45:12 GMTstewsterlhttp://www.sqlservercentral.com/Forums/Topic824969-149-1.aspx[quote][b]stewsterl (11/25/2009)[/b][hr]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[/quote]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.Wed, 25 Nov 2009 22:12:34 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic824969-149-1.aspxAnd, based on the sample data provided, what should our expected results be so we know if our code is correct.Wed, 25 Nov 2009 19:37:25 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic824969-149-1.aspxWhat 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.Wed, 25 Nov 2009 15:44:26 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic824969-149-1.aspxHello 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')Wed, 25 Nov 2009 15:08:25 GMTstewsterl