Case when 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

Case when Help

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-09-19 : 11:28:20

Hey Guys

Really random one

Aim -> IF [Fee_Sequence_Number] = 42b or 42c and both Fee_Sequence_Number are within the same [Month_end_date], then sum the two retail amounts together. The sum calculation should only apply if the Fee_Sequence_Number months are greater than >='2013-01-01'

My query is

SELECT TOP 1000 [FDMSAccountNo]
,[Fee_Sequence_Number]
,Description
,[Month_end_date]
,[Retail_amount],
--into #test ]
SUM(case when [Fee_Sequence_Number] = '42b' then SUM([Fee_Sequence_Number] ='42C') end as test)
FROM [FDMS].[dbo].[Fact_Fee_History]
INNER JOIN Dim_Fee_Codes ON Fact_Fee_History.Fee_Sequence_Number = Dim_Fee_Codes.Fee_Code
where [Fee_Sequence_Number] in ('42B','42C')
and Month_end_date between '2013-01-01' and '2013-08-01'
and FDMSAccountNo = '878212486889'

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-09-19 : 11:33:55

Please post sample data and expected output. Here are some links to help you post your question so we can help you better:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Are we to assume that the query you posted doesn't work?

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-09-19 : 11:34:59

For example

FDMSAccountNo Fee_Sequence_Number Description Month_end_date Retail_amount
878212486889 42B PCI DSS MANAGEMENT FEE 2013-07-01 4.99
878212486889 42C PCI DSS MANAGEMENT PM 2013-07-01 4.99
878212486889 42B PCI DSS MANAGEMENT FEE 2013-06-01 4.99

as you can see that 42b and 42c has appeared within the same month of july. i want to combine the sales of both these fee_sequences so i have one line which says july 9.98

sigmas
Posting Yak Master

172 Posts

Posted - 2013-09-19 : 11:41:44

select month(Month_end_date), sum(Retail_amount) as total
from table
group by month(Month_end_date)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-09-19 : 12:08:06

Sigmas is on the right track. You may want to also group by Year if you have data that spans more than one year.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources