Help! Sum of Previous month and Current month

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 2008 Forums

Transact-SQL (2008)

Help! Sum of Previous month and Current month

Author

Topic

wongek
Starting Member

4 Posts

Posted - 2012-10-23 : 21:32:27

Need help to retrieve the Total of previous month in another column

Year Month YearMonth Total Count Average
2011 11 2011 November 1568.5 10 156.85
2011 12 2011 December 1174 8 146.75
2012 1 2012 January 1166.5 8 145.8125
2012 2 2012 February 1168 8 146
2012 3 2012 March 1156 8 144.5
2012 4 2012 April 1455 10 145.5

Eg. (add another 2 column as below)

Previous Month Previous Total
10 0
11 1568.5
12 1174
1 1166.5
2 1168
3 1156

My current query as below:(Sorry, I'm not good in sql, please advice)

Declare @EndDate as Date
Declare @PreviousMonth as nvarchar(20)
set @StartDate = 'November 2011'
set @EndDate = 'April 2012'

select DATEPART(YYYY,Date), DATEPART(MM,Date), DATENAME(yy,date) + ' ' + DATENAME(mm,date) as [YearMonth],
SUM([Unit Price]) as Total,
COUNT(Datename(yy,date) + Datename(mm,date)) as Count,
SUM([Unit Price])/COUNT(Datename(yy,date) + Datename(mm,date)) as Average
from [Material Pricing]
where
DATENAME(mm,date) + ' ' + DATENAME(yy,date) >= @StartDate AND
DATENAME(mm,date) + ' ' + DATENAME(yy,date) <= @EndDate
group by DATEPART(YYYY,Date), DATEPART(MM,Date), DATENAME(yy,date) + ' ' + DATENAME(mm,date)
order by DATEPART(YYYY,Date), DATEPART(MM,Date), DATENAME(yy,date) + ' ' + DATENAME(mm,date)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-23 : 21:40:56

why are you storing year, month etc separately. Storing them in a single date field would have made matters easier. Anyways here you go


Declare @EndDate as Date
Declare @startDate as date
set @StartDate = '20111101'
set @EndDate='20120430'

;With Temp
AS
(SELECT DATEADD(mm,[Month]-1,DATEADD(yy,[Year]-1900,0)) AS dateval,YearMonth ,Total, Count, Average
      FROM table)

SELECT t.*,
COALESCE(t1.Month,0) AS PrevMonth,
COALESCE(t1.Total,0) AS PrevTotal
FROM Temp t
OUTER APPLY (SELECT Total,Month
             FROM Temp
             WHERE dateval = DATEADD(mm,-1,t.dateval)
            )t1



if you've gaps in your data (ie data missing for in between months you might have to make a small tweak as below in last SELECT

...

SELECT t.*,
COALESCE(t1.Month,0) AS PrevMonth,
COALESCE(t1.Total,0) AS PrevTotal
FROM Temp t
OUTER APPLY (SELECT TOP 1 Total,Month
             FROM Temp
             WHERE dateval < t.dateval
             ORDER BY dateval DESC
            )t1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wongek
Starting Member

4 Posts

Posted - 2012-10-23 : 22:02:56

Thank you for reply. I'm terribly sorry, I provide the wrong info. Below is the actual table data. The previous that i post was the result i got from my previous query.

The reason i put in the date in separate column, so that i can order it correctly in a report.

By the way, how do i sort the data and paste it here nice and neat so that you guys would not suffering by looking at the data?

Title Date Region Item Type Currency Unit Price Sources Remarks ID UOM Source
November 2011 2011-11-04 00:00:00.000 1 1 1 296 1 ?OPC Bulk Cement 6 BULK NULL
November 2011 2011-11-08 00:00:00.000 1 1 1 280 1 ?OPC Bulk Cement 7 BULK NULL
November 2011 2011-11-15 00:00:00.000 1 1 1 280 1 ?OPC Bulk Cement 8 BULK NULL
November 2011 2011-11-21 00:00:00.000 1 1 1 280 1 ?OPC Bulk Cement 9 BULK NULL
November 2011 2011-11-29 00:00:00.000 1 1 1 280 1 ?OPC Bulk Cement 10 BULK NULL
December 2011 2011-12-05 00:00:00.000 1 1 1 256 1 ?OPC Bulk Cement 11 BULK NULL
December 2011 2011-12-13 00:00:00.000 1 1 1 256 1 ?OPC Bulk Cement 12 BULK NULL
December 2011 2011-12-19 00:00:00.000 1 1 1 280 1 ?OPC Bulk Cement 13 BULK NULL
December 2011 2011-12-27 00:00:00.000 1 1 1 260 1 ?OPC Bulk Cement 14 BULK NULL
January 2012 2012-01-03 00:00:00.000 1 1 1 260 1 ?OPC Bulk Cement 15 BULK NULL
January 2012 2012-01-09 00:00:00.000 1 1 1 260 1 ?OPC Bulk Cement 16 BULK NULL
January 2012 2012-01-17 00:00:00.000 1 1 1 260 1 ?OPC Bulk Cement 17 BULK NULL
January 2012 2012-01-30 00:00:00.000 1 1 1 260 1 ?OPC Bulk Cement 18 BULK NULL
February 2012 2012-02-03 00:00:00.000 1 1 1 260 1 ?OPC Bulk Cement 19 BULK NULL
February 2012 2012-02-13 00:00:00.000 1 1 1 260 1 ?OPC Bulk Cement 20 BULK NULL
February 2012 2012-02-20 00:00:00.000 1 1 1 260 1 ?OPC Bulk Cement 21 BULK NULL
February 2012 2012-02-28 00:00:00.000 1 1 1 260 1 ?OPC Bulk Cement 22 BULK NULL
March 2012 2012-03-05 00:00:00.000 1 1 1 257 1 ?OPC Bulk Cement 23 BULK NULL
March 2012 2012-03-12 00:00:00.000 1 1 1 257 1 ?OPC Bulk Cement 24 BULK NULL
March 2012 2012-03-19 00:00:00.000 1 1 1 257 1 ?OPC Bulk Cement 25 BULK NULL
March 2012 2012-03-26 00:00:00.000 1 1 1 257 1 ?OPC Bulk Cement 26 BULK NULL
April 2012 2012-04-04 00:00:00.000 1 1 1 257 1 ?OPC Bulk Cement 27 BULK NULL
April 2012 2012-04-09 00:00:00.000 1 1 1 257 1 ?OPC Bulk Cement 28 BULK NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-23 : 22:21:56

quote:
Originally posted by wongek

Thank you for reply. I'm terribly sorry, I provide the wrong info. Below is the actual table data. The previous that i post was the result i got from my previous query.

The reason i put in the date in separate column, so that i can order it correctly in a report.

By the way, how do i sort the data and paste it here nice and neat so that you guys would not suffering by looking at the data?

Title Date Region Item Type Currency Unit Price Sources Remarks ID UOM Source
November 2011 2011-11-04 00:00:00.000 1 1 1 296 1 ?OPC Bulk Cement 6 BULK NULL
November 2011 2011-11-08 00:00:00.000 1 1 1 280 1 ?OPC Bulk Cement 7 BULK NULL
November 2011 2011-11-15 00:00:00.000 1 1 1 280 1 ?OPC Bulk Cement 8 BULK NULL
November 2011 2011-11-21 00:00:00.000 1 1 1 280 1 ?OPC Bulk Cement 9 BULK NULL
November 2011 2011-11-29 00:00:00.000 1 1 1 280 1 ?OPC Bulk Cement 10 BULK NULL
December 2011 2011-12-05 00:00:00.000 1 1 1 256 1 ?OPC Bulk Cement 11 BULK NULL
December 2011 2011-12-13 00:00:00.000 1 1 1 256 1 ?OPC Bulk Cement 12 BULK NULL
December 2011 2011-12-19 00:00:00.000 1 1 1 280 1 ?OPC Bulk Cement 13 BULK NULL
December 2011 2011-12-27 00:00:00.000 1 1 1 260 1 ?OPC Bulk Cement 14 BULK NULL
January 2012 2012-01-03 00:00:00.000 1 1 1 260 1 ?OPC Bulk Cement 15 BULK NULL
January 2012 2012-01-09 00:00:00.000 1 1 1 260 1 ?OPC Bulk Cement 16 BULK NULL
January 2012 2012-01-17 00:00:00.000 1 1 1 260 1 ?OPC Bulk Cement 17 BULK NULL
January 2012 2012-01-30 00:00:00.000 1 1 1 260 1 ?OPC Bulk Cement 18 BULK NULL
February 2012 2012-02-03 00:00:00.000 1 1 1 260 1 ?OPC Bulk Cement 19 BULK NULL
February 2012 2012-02-13 00:00:00.000 1 1 1 260 1 ?OPC Bulk Cement 20 BULK NULL
February 2012 2012-02-20 00:00:00.000 1 1 1 260 1 ?OPC Bulk Cement 21 BULK NULL
February 2012 2012-02-28 00:00:00.000 1 1 1 260 1 ?OPC Bulk Cement 22 BULK NULL
March 2012 2012-03-05 00:00:00.000 1 1 1 257 1 ?OPC Bulk Cement 23 BULK NULL
March 2012 2012-03-12 00:00:00.000 1 1 1 257 1 ?OPC Bulk Cement 24 BULK NULL
March 2012 2012-03-19 00:00:00.000 1 1 1 257 1 ?OPC Bulk Cement 25 BULK NULL
March 2012 2012-03-26 00:00:00.000 1 1 1 257 1 ?OPC Bulk Cement 26 BULK NULL
April 2012 2012-04-04 00:00:00.000 1 1 1 257 1 ?OPC Bulk Cement 27 BULK NULL
April 2012 2012-04-09 00:00:00.000 1 1 1 257 1 ?OPC Bulk Cement 28 BULK NULL

please post data within code tags and also give expected output

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-10-23 : 23:04:16

quote:
By the way, how do i sort the data and paste it here nice and neat so that you guys would not suffering by looking at the data?

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

KH
[spoiler]Time is always against us[/spoiler]

wongek
Starting Member

4 Posts

Posted - 2012-10-23 : 23:44:38

This is the current table:

Title	  Date	Region	Item Type	Currency	Unit Price	Sources	Remarks	ID	UOM	Source
Nov-11	04/11/2011 0:00	1	1	1	296	1	?OPC Bulk Cement	6	BULK	NULL
Nov-11	08/11/2011 0:00	1	1	1	280	1	?OPC Bulk Cement	7	BULK	NULL
Nov-11	15/11/2011 0:00	1	1	1	280	1	?OPC Bulk Cement	8	BULK	NULL
Nov-11	21/11/2011 0:00	1	1	1	280	1	?OPC Bulk Cement	9	BULK	NULL
Nov-11	29/11/2011 0:00	1	1	1	280	1	?OPC Bulk Cement	10	BULK	NULL
Dec-11	05/12/2011 0:00	1	1	1	256	1	?OPC Bulk Cement	11	BULK	NULL
Dec-11	13/12/2011 0:00	1	1	1	256	1	?OPC Bulk Cement	12	BULK	NULL
Dec-11	19/12/2011 0:00	1	1	1	280	1	?OPC Bulk Cement	13	BULK	NULL
Dec-11	27/12/2011 0:00	1	1	1	260	1	?OPC Bulk Cement	14	BULK	NULL
Jan-12	03/01/2012 0:00	1	1	1	260	1	?OPC Bulk Cement	15	BULK	NULL
Jan-12	09/01/2012 0:00	1	1	1	260	1	?OPC Bulk Cement	16	BULK	NULL
Jan-12	17/01/2012 0:00	1	1	1	260	1	?OPC Bulk Cement	17	BULK	NULL
Jan-12	30/01/2012 0:00	1	1	1	260	1	?OPC Bulk Cement	18	BULK	NULL
Feb-12	03/02/2012 0:00	1	1	1	260	1	?OPC Bulk Cement	19	BULK	NULL
Feb-12	13/02/2012 0:00	1	1	1	260	1	?OPC Bulk Cement	20	BULK	NULL
Feb-12	20/02/2012 0:00	1	1	1	260	1	?OPC Bulk Cement	21	BULK	NULL
Feb-12	28/02/2012 0:00	1	1	1	260	1	?OPC Bulk Cement	22	BULK	NULL
Mar-12	05/03/2012 0:00	1	1	1	257	1	?OPC Bulk Cement	23	BULK	NULL
Mar-12	12/03/2012 0:00	1	1	1	257	1	?OPC Bulk Cement	24	BULK	NULL
Mar-12	19/03/2012 0:00	1	1	1	257	1	?OPC Bulk Cement	25	BULK	NULL
Mar-12	26/03/2012 0:00	1	1	1	257	1	?OPC Bulk Cement	26	BULK	NULL
Apr-12	04/04/2012 0:00	1	1	1	257	1	?OPC Bulk Cement	27	BULK	NULL
Apr-12	09/04/2012 0:00	1	1	1	257	1	?OPC Bulk Cement	28	BULK	NULL

This is expected Result (Current query Missing the Previous Month)


Year	Month	Year Month	Total	Count	Average	Previous Month Total
2011	11	2011 November	1568.5	10	156.85	0
2011	12	2011 December	1174	8	146.75	1568.5
2012	1	2012 January	1166.5	8	145.8125	1174
2012	2	2012 February	1168	8	146	1166.5
2012	3	2012 March	1156	8	144.5	1168
2012	4	2012 April	1455	10	145.5	1156

My Query so far:

Declare @EndDate as Date
Declare @PreviousMonth as nvarchar(20)
set @StartDate = 'November 2011'
set @EndDate = 'April 2012'

select DATEPART(YYYY,Date), DATEPART(MM,Date), DATENAME(yy,date) + ' ' + DATENAME(mm,date) as [YearMonth],
SUM([Unit Price]) as Total,
COUNT(Datename(yy,date) + Datename(mm,date)) as Count,
SUM([Unit Price])/COUNT(Datename(yy,date) + Datename(mm,date)) as Average
from [Material Pricing]
where
DATENAME(mm,date) + ' ' + DATENAME(yy,date) >= @StartDate AND
DATENAME(mm,date) + ' ' + DATENAME(yy,date) <= @EndDate
group by DATEPART(YYYY,Date), DATEPART(MM,Date), DATENAME(yy,date) + ' ' + DATENAME(mm,date)
order by DATEPART(YYYY,Date), DATEPART(MM,Date), DATENAME(yy,date) + ' ' + DATENAME(mm,date)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-24 : 02:10:21

[code]
Declare @EndDate as Date
Declare @startDate as date
set @StartDate = '20111101'
set @EndDate='20120430'

;With Temp
AS
(SELECT Title,YEAR(Date) AS [year],MONTH([Date]) AS [Month],SUM(Price*1.0) AS Total,COUNT(*) AS [Count],AVG(Price*1.0) AS Average
FROM table
WHERE Date >=@StartDate
AND Date < @EndDate+1
GROUP BY Title,YEAR(Date),MONTH([Date]))

SELECT t.*,
COALESCE(t1.Month,0) AS PrevMonth,
COALESCE(t1.Total,0) AS PrevTotal
FROM Temp t
OUTER APPLY (SELECT TOP 1 Total,Month
FROM Temp
WHERE [Year]*100 + [Month] < t.[Year]*100 + t.[Month]
ORDER BY [Year]*100 + [Month] DESC
)t1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wongek
Starting Member

4 Posts

Posted - 2012-10-29 : 05:48:01

Thank you Visakh16, but how do I order by Year, Month?

Currently it shows:


2009	1	293.5	2	146.75	 0	0
2010	1	273	2	136.5	 12	278
2011	1	292	2	146	 12	292
2012	1	1166.5	8	145.8125 12	1174
2009	2	293.5	2	146.75	 1	293.5
2010	2	267	2	133.5	 1	273
2011	2	292	2	146	 1	292
2012	2	1168	8	146	 1	1166.5
2009	3	289.5	2	144.75	 2	293.5

I wanted to show:


2009	1	293.5	2	146.75	0	0
2009	2	293.5	2	146.75	1	293.5
2009	3	289.5	2	144.75	2	293.5
2010	1	273	2	136.5	12	278
2010	2	267	2	133.5	1	273
2011	1	292	2	146	12	292
2011	2	292	2	146	1	292
2012	1	1166.5	8	145.8125 12	1174	
2012	2	1168	8	146	1	1166.5

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-29 : 07:33:39

Add this part at the end of query

ORDER BY [year],[Month]

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources