SQL Clone
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
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10539 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-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40278 Visits: 38567
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 Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88262 Visits: 41128
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
stewsterl
stewsterl
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 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
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12754 Visits: 8564
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88262 Visits: 41128
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
stewsterl
stewsterl
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 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 Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88262 Visits: 41128
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
stewsterl
stewsterl
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 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