help needed with the following SQL issue

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 needed with the following SQL issue

Author

Topic

raaj
Posting Yak Master

129 Posts

Posted - 2012-09-03 : 02:16:59

Hi Guys,

Declare @Temp Table
([Month] Varchar(10),
[Count] Int)

Insert into @Temp Values('Feb',10)
Insert into @Temp Values('Mar',20)
Insert into @Temp Values('Apr',30)
Insert into @Temp Values('May',40)

When I run the below statement
Select * from @Temp
I get the following result set :
Month Count
Feb 10
Mar 20
Apr 30
May 40

But, I want my resultset to look in the following way (i.e current month total should be addition with the previous months) :
Month Count
Feb 10
Mar 30
Apr 60
May 100

Can someone give any hints or clues how to achieve this please?
Thanks,
Raaj

stepson
Aged Yak Warrior

545 Posts

Posted - 2012-09-03 : 03:47:16

hi,
I can't use 'Feb' ...i put insteand '201202' ...

here is

Insert into @Temp Values('201202',10)
Insert into @Temp Values('201203',20)
Insert into @Temp Values('201204',30)
Insert into @Temp Values('201205',40)

select T.[Month], T.[Count] , coalesce(A.[count],0) as [Count2] from @temp T

outer apply (select sum([Count]) as [Count]
from @Temp
where [month]<=T.[month] )A

stepson
Aged Yak Warrior

545 Posts

Posted - 2012-09-03 : 03:58:31

you can use

update @temp
set [month]='2012/'+[month]+'/1'

and in where something like:

where cast ([month] as datetime) <=cast (T.[month] as datetime))A

sateeshGenpact
Starting Member

6 Posts

Posted - 2012-09-03 : 08:21:54

Hi Raaj,

you can use this way..

Declare @Temp Table
([Month] Varchar(10),
[Count] Int)

Insert into @Temp Values('Feb',10)
Insert into @Temp Values('Mar',20)
Insert into @Temp Values('Apr',30)
Insert into @Temp Values('May',40)

Select [Month],SUM([Count]) [Count] from

(Select datepart(m, convert(datetime, [Month]+' 1, 0')) Month_Number, [Month], [Count] from @Temp) T
where Month_Number <= (Select datepart(m, convert(datetime, [Month]+' 1, 0')) from @Temp a where [Month]=t.[Month])
group by [Month]

Sateesh

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-09-03 : 08:48:59

a running total like this is probably best done in your calling application layer.

Its one pass over the results and (unless you use a cheeky quirky update) in the db, it generally leads to triangle joins and slow queries.

Simply order by the date when passing the results back -- you are going to have to parse the output anyway so a simple running total doesn't add much overhead.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

sateeshGenpact
Starting Member

6 Posts

Posted - 2012-09-04 : 03:11:44

Ignore above post..

Declare @Temp Table
([Month] Varchar(10),
[Count] Int)

Insert into @Temp Values('Feb',10)
Insert into @Temp Values('Mar',20)
Insert into @Temp Values('Apr',30)
Insert into @Temp Values('May',40)

Select T.[Month],SUM([Count2]) [Count] from

(Select datepart(m, convert(datetime, [Month]+' 1, 0')) Month_Number, [Month], [Count] from @Temp) T

join (Select datepart(m, convert(datetime, [Month]+' 1, 0')) Month_Number, [Count] [Count2] from @Temp ) M

on T.Month_Number>=M.Month_Number

group by T.[Month]

Sateesh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources