Sum Query Help - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Sum Query Help

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-09-23 : 11:49:55

Hey Guys

Hope your well

I need some advice / Help

This is my query
select
[FDMSAccountNo],
[Fee_Sequence_Number],
month(Month_end_date) as Month_end_Date,
sum(Retail_amount) as total,
count ([Fee_Sequence_Number]) as CountFee
from [FDMS].[dbo].[Fact_Fee_History]
where [Fee_Sequence_Number] in ('42B','42C')
and Month_end_date between '2013-01-01' and '2013-08-01'
and FDMSAccountNo = '878231410886'
group by month(Month_end_date),[FDMSAccountNo],[Fee_Sequence_Number]
order by Month_end_Date desc

which produces the following results

FDMSAccountNo Fee_Sequence_Number Month_end_Date total CountFee
878231410886 42B 8 4.99 1
878231410886 42B 7 4.99 1
878231410886 42B 6 4.99 1
878231410886 42B 5 4.99 1
878231410886 42B 4 4.99 1
878231410886 42B 3 4.99 1
878231410886 42B 2 4.99 1
878231410886 42B 1 35.99 1
878231410886 42C 1 4.99 1

As you can see Month_End_date 1 has two Fee_Sequence_Number, 42c & 42B, is there anyway i can do an update so that the value of 42b gets added to 42c for month 1 and then all the other months
change the Fee_Sequence_Number number from 42b to 42c ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-23 : 12:21:34

[code]
select
[FDMSAccountNo],
CASE WHEN [Fee_Sequence_Number] = '42b' THEN '42c' ELSE [Fee_Sequence_Number] END AS [Fee_Sequence_Number],
month(Month_end_date) as Month_end_Date,
sum(Retail_amount) as total,
count ([Fee_Sequence_Number]) as CountFee
from [FDMS].[dbo].[Fact_Fee_History]
where [Fee_Sequence_Number] in ('42B','42C')
and Month_end_date between '2013-01-01' and '2013-08-01'
and FDMSAccountNo = '878231410886'
group by month(Month_end_date),[FDMSAccountNo],CASE WHEN [Fee_Sequence_Number] = '42b' THEN '42c' ELSE [Fee_Sequence_Number] END
order by Month_end_Date desc
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources