Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Totals for each month in a date range query Expand / Collapse
Author
Message
Posted Wednesday, November 25, 2009 3:08 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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 2009


Below 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

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, July 21, 2014 1:11 PM
Points: 7,019, Visits: 12,909
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #824989
Posted Wednesday, November 25, 2009 7:37 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:59 PM
Points: 23,000, Visits: 31,482
And, based on the sample data provided, what should our expected results be so we know if our code is correct.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #825013
Posted Wednesday, November 25, 2009 10:12 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:19 PM
Points: 36,724, Visits: 31,173
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #825041
Posted Monday, November 30, 2009 7:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 8:36 AM
Points: 4,319, Visits: 6,112
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 at GMail
Post #826702
Posted Tuesday, December 1, 2009 11:43 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:19 PM
Points: 36,724, Visits: 31,173
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #826892
Posted Wednesday, December 2, 2009 11:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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 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
Post #827699
Posted Wednesday, December 2, 2009 2:13 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:19 PM
Points: 36,724, Visits: 31,173
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #827788
Posted Wednesday, December 2, 2009 2:53 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse