Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Totals for each month in a date range query


Totals for each month in a date range query

Author
Message
stewsterl
stewsterl
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
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')
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7001 Visits: 13559
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
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24207 Visits: 37978
And, based on the sample data provided, what should our expected results be so we know if our code is correct.

Cool
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)
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45121 Visits: 39921
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
stewsterl
stewsterl
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
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
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5963 Visits: 8312
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
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45121 Visits: 39921
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
stewsterl
stewsterl
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
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
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45121 Visits: 39921
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... :-P

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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
stewsterl
stewsterl
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 138
Awesome! thanks.. Going to read the article tonight.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search