Selecting a current and previous month's total

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Selecting a current and previous month's total

Author

Topic

bendertez
Yak Posting Veteran

94 Posts

Posted - 2010-09-27 : 07:40:09

Hi

I'm trying to select a current and a previous months total in the one query but i'm struggling to come up with the syntax.

my query is essentially

SELECT CustRef as [Cust Ref], TotalInvAmt as [Total This Month]
From Invoices
where InvoiceDate between '01AUG2010' and '31AUG2010'

I want to add last months total as an extra column to the query which would be

SELECT CustRef as [Cust Ref], TotalInvAmt as [Total Last Month]
From Invoices
where InvoiceDate between '01JULG2010' and '31JUL2010'

This months totals might include accounts that were not on last months totals so I would like to see all accounts.

Can this be done in the one query? leaving me with the fields:
Cust Ref, Total This Month, Total Last Month in the results.

Thanks in advance

Dave

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-09-27 : 08:18:56

Will this do?

SELECT custRef as [Cust Ref]
, SUM(CASE WHEN InvoiceDate between '01AUG2010' and '31AUG2010' THEN TotalInvAmt ELSE 0 END) as [Total This Month]
, SUM(CASE WHEN InvoiceDate between '01JUL2010' and '01JUL2010' THEN TotalInvAmt ELSE 0 END) as [Total This Month]
FROM invoices
GROUP BY custRef

Jim

Everyday I learn something that somebody else already knew

nithin11
Starting Member

6 Posts

Posted - 2010-09-27 : 08:43:52

Can you please help me?

QUERY1:

SELECT COUNT(T1.ID) AS T1_COUNT,convert(date,DATEADD(dd, 7-(DATEPART(dw, T1.TIME)), T1.TIME),101) AS T1_TIME FROM TABLE_1 AS T1,TABLE_2 AS T2 WHERE T2.ID=T1.ID AND T1.STATE != 'C' AND T1.TIME BETWEEN '2010-01-01 00:00:00' AND '2010-09-25 23:59:59' AND PRTY='F' GROUP BY convert(date,DATEADD(dd, 7-(DATEPART(dw, T1.TIME)), T1.TIME),101) ORDER BY T1_TIME DESC

GETTING RESULT:
T1_COUNT ---------- T1_TIME
3 ---------------- 2010-09-25
4 ---------------- 2010-09-18
2 ---------------- 2010-09-11

QUERY2:

SELECT COUNT(T1.ID) AS T2_COUNT,convert(date,DATEADD(dd, 7-(DATEPART(dw,T1.TIME)), T1.TIME),101) AS T2_TIME FROM TABLE_1 AS T1,TABLE_2 AS T2 WHERE T2.ID=T1.ID AND T1.STATE != 'C' AND T1.STATE ='E' AND T1.TIME BETWEEN '2010-01-01 00:00:00' AND '2010-09-25 23:59:59' AND PRTY='F' GROUP BY convert(date,DATEADD(dd, 7-(DATEPART(dw, T1.TIME)), T1.TIME),101) ORDER BY T2_TIME DESC

GETTING RESULT:
T2_COUNT ---------------- T2_TIME
1 ---------------- 2010-09-25
2 ---------------- 2010-09-18

Sample Query:
SELECT * FROM (
SELECT COUNT(T1.ID) AS T1_COUNT,convert(date,DATEADD(dd, 7-(DATEPART(dw, T1.TIME)), T1.TIME),101) AS T1_TIME FROM TABLE_1 AS T1,TABLE_2 AS T2 WHERE T2.ID=T1.ID AND T1.STATE != 'C' AND T1.TIME BETWEEN '2010-01-01 00:00:00' AND '2010-09-25 23:59:59' AND PRTY='F' GROUP BY convert(date,DATEADD(dd, 7-(DATEPART(dw, T1.TIME)), T1.TIME),101)
UNION
SELECT COUNT(T1.ID) AS T2_COUNT,convert(date,DATEADD(dd, 7-(DATEPART(dw,T1.TIME)), T1.TIME),101) AS T2_TIME FROM TABLE_1 AS T1,TABLE_2 AS T2 WHERE T2.ID=T1.ID AND T1.STATE != 'C' AND T1.STATE ='E' AND T1.TIME BETWEEN '2010-01-01 00:00:00' AND '2010-09-25 23:59:59' AND PRTY='F' GROUP BY convert(date,DATEADD(dd, 7-(DATEPART(dw, T1.TIME)), T1.TIME),101)) AS inLineView ORDER BY T1_TIME DESC

RESULT:
T1_COUNT ---------- T1_TIME
1 ---------------- 2010-09-25
3 ---------------- 2010-09-25
2 ---------------- 2010-09-18
4 ---------------- 2010-09-18
2 ---------------- 2010-09-11
(Please Give me idea or correct the sample Query to get Result like follows)
NEEDED RESULT:
T1_COUNT ----- T1_TIME ------ T2_COUNT ------ T2_TIME
1 ------------- 2010-09-25 ----- 3 ----------- 2010-09-25
2 ------------- 2010-09-18 ----- 4 ----------- 2010-09-18
2 ------------- 2010-09-11

Thanks,
Nithin

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-09-27 : 13:01:15

Provide sample table with data and clearly expected oupt, please.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-27 : 13:10:37

Nithin - STOP HIJACKING OTHER THREADS. You've already posted your question here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=150679

bendertez
Yak Posting Veteran

94 Posts

Posted - 2010-09-27 : 16:44:55

Hi Jimf

Unfortunately the query doesn't work, I think it would if I was counting the occurances of the data in the date range, but it is displaying all values and then everything else that is not in the date range as '0'.

I only want to see the data between within the date range and thn the previous months value for that data.

Data Example

Reference Inv__Date Total
BEF345453 01AUG2010 20.00
BEF345453 05JUL2010 30.00
BEF345453 01JUN2010 20.00
BEF345400 05JUL2010 60.00
BEF345400 01AUG2010 20.00
BEF345400 05JUN2010 10.00
BEF367402 05JUL2010 40.00
BEF367402 01AUG2010 10.00
BEF367402 05JUN2010 30.00

I want the query to be displayed below me if I used an August Parameter

Reference CurMnth LastMnth
BEF345453 20.00 30.00
BEF345400 20.00 60.00
BEF367400 10.00 40.00

Any help would be much appreciated.

Thanks again

Dave

Sachin.Nand

2937 Posts

Posted - 2010-09-28 : 06:15:50

quote:
Originally posted by jimf

Will this do?

SELECT custRef as [Cust Ref]
,SUM(CASE WHEN InvoiceDate between '01AUG2010' and '31AUG2010' THEN TotalInvAmt ELSE 0 END) as [Total This Month]
,SUM(CASE WHEN InvoiceDate between '01JUL2010' and '31JUL2010' THEN TotalInvAmt ELSE 0 END) as [Total This Month]
FROM invoices
GROUP BY custRef

Jim

Everyday I learn something that somebody else already knew

Jim has made a small type error.I have corrected it with the red part above.

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-09-28 : 06:23:40

quote:
Originally posted by bendertez

Hi Jimf

Unfortunately the query doesn't work, I think it would if I was counting the occurances of the data in the date range, but it is displaying all values and then everything else that is not in the date range as '0'.

I only want to see the data between within the date range and thn the previous months value for that data.

Data Example

Reference Inv__Date Total
BEF345453 01AUG2010 20.00
BEF345453 05JUL2010 30.00
BEF345453 01JUN2010 20.00
BEF345400 05JUL2010 60.00
BEF345400 01AUG2010 20.00
BEF345400 05JUN2010 10.00
BEF367402 05JUL2010 40.00
BEF367402 01AUG2010 10.00
BEF367402 05JUN2010 30.00

I want the query to be displayed below me if I used an August Parameter

Reference CurMnth LastMnth
BEF345453 20.00 30.00
BEF345400 20.00 60.00
BEF367400 10.00 40.00

Any help would be much appreciated.

Thanks again

Dave


declare @tbl as table(Reference varchar(20), Inv__Date datetime, Total int)
insert into @tbl
select 'BEF345453', '01-AUG-2010', 20.00 union
select 'BEF345453', '05-JUL-2010', 30.00 union
select 'BEF345453', '01-JUN-2010', 20.00 union
select 'BEF345400', '05-JUL-2010', 60.00 union
select 'BEF345400', '01-AUG-2010', 20.00 union
select 'BEF345400', '05-JUN-2010', 10.00 union
select 'BEF367402', '05-JUL-2010', 40.00 union
select 'BEF367402', '01-AUG-2010', 10.00 union
select 'BEF367402', '05-JUN-010', 30.00 

select * from @tbl

SELECT Reference as [Cust Ref]
, SUM(CASE WHEN Inv__Date between '01AUG2010' and '31AUG2010' THEN Total ELSE 0 END) as [Total This Month]
, SUM(CASE WHEN Inv__Date between '01JUL2010' and '31JUL2010' THEN Total ELSE 0 END) as [Total Last Month]
FROM @tbl
GROUP BY Reference

PBUH

bendertez
Yak Posting Veteran

94 Posts

Posted - 2010-09-28 : 06:50:38

Hi Sachin

Thanks for the reply

I'm not too sure about using the UNION as there are hundreds of rows in the table.

Would there be anyway around doing a UNION on each row?

Thanks

Dave

Sachin.Nand

2937 Posts

Posted - 2010-09-28 : 06:53:39

Union?
No it is use to populate some sample data so that o/p can be shown.You just need the below query


SELECT Reference as [Cust Ref]
, SUM(CASE WHEN Inv__Date between '01AUG2010' and '31AUG2010' THEN Total ELSE 0 END) as [Total This Month]
, SUM(CASE WHEN Inv__Date between '01JUL2010' and '31JUL2010' THEN Total ELSE 0 END) as [Total Last Month]
FROM yourtablename
GROUP BY Reference

PBUH

bendertez
Yak Posting Veteran

94 Posts

Posted - 2010-09-28 : 07:30:41

Hi Sachin

Thanks again for the reply.

I'll post you the exact query i'm running which is being run against the Invoice_Data view that I have created previously.

If I first run a couple of simple queries before the main query you might get a better idea of what i'm trying to achieve.

SELECT * from Invoice_Data Where invoice_date between '2010-07-01' and '2010-07-31'

This query returns about 5144 rows which is a correct figure

SELECT * from Invoice_Data Where invoice_date between '2010-08-01' and '2010-08-31'

This query returns about 5360 rows which is a correct figure

When I run the below query I get 9419 rows return which is incorrect as the majority of accounts have invoices in both July and August.

SELECT
Account_Num,
SUM(CASE WHEN invoice_date between '2010-07-01' and '2010-07-31' THEN Invoice_Total ELSE 0 END) as [Total_Last_Month],
SUM(CASE WHEN invoice_date between '2010-08-01' and '2010-08-31' THEN Invoice_Total ELSE 0 END) as [Total_This_Month]
FROM Invoice_Data
GROUP BY Account_Num

Can you think why this is returning such a different amount?

I expect there to be over the 5300 mark as there are some accounts that will be in one month and not the other.

Thanks again

Dave

bendertez
Yak Posting Veteran

94 Posts

Posted - 2010-09-29 : 05:18:02

Hi

Can anyone else help me with this query?

All I want is the Account_num, Total_This_Month, Total_last_Month

From the one table containing all the invoice info.

Any more advice would be much appreciated.

Thanks

Dave

Sachin.Nand

2937 Posts

Posted - 2010-09-29 : 05:35:31

Really its difficult to give you a solution without looking at all the data.You will have to find for yourself where is the result breaking.
How many records do this query give?


SELECT CustRef as [Cust Ref], SUM(TotalInvAmt) as [Total Last Month]
From Invoices
where InvoiceDate between '01JULG2010' and '31JUL2010'
group by CustRef

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources