Case When Sum - 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 Sum

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-10-25 : 05:53:30

HI all

Aim
To calculate hst_sales_amt_r12 when the scheme = visa and the credit _debit = credit

SELECT
Dim_Outlet.FDMSAccountNo,
sum(Fact_Financial_History_2.hst_sales_amt_R12) As Sales,
sum(Fact_Financial_History_2.hst_sales_tran_R12)As Trans,
[Credit_Debit],
[Scheme],
case when
sum(hst_sales_amt_R12) when scheme = 'visa' and Credit_Debit = 'Credit' end as Visa_Credit
FROM Fact_Financial_History_2
INNER JOIN Dim_Interchange_Tier_2 ON Fact_Financial_History_2.Plan_Key = Dim_Interchange_Tier_2.Plan_Code
INNER JOIN Dim_Outlet ON Fact_Financial_History_2.hst_merchnum = Dim_Outlet.FDMSAccountNo_First9
where FDMSAccountNo = '878216362888'
group by
Dim_Outlet.FDMSAccountNo,
[Credit_Debit],
[Scheme]

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-10-25 : 07:22:51

--When Condition Is Written Two Times And U Can Miss The 'Then' Condition .

SELECT
Dim_Outlet.FDMSAccountNo,
sum(Fact_Financial_History_2.hst_sales_amt_R12) As Sales,
sum(Fact_Financial_History_2.hst_sales_tran_R12)As Trans,
[Credit_Debit],
[Scheme],
case when
sum(hst_sales_amt_R12) when [scheme] = 'visa' and [Credit_Debit] = 'Credit' end as Visa_Credit
FROM Fact_Financial_History_2
INNER JOIN Dim_Interchange_Tier_2 ON Fact_Financial_History_2.Plan_Key = Dim_Interchange_Tier_2.Plan_Code
INNER JOIN Dim_Outlet ON Fact_Financial_History_2.hst_merchnum = Dim_Outlet.FDMSAccountNo_First9
where FDMSAccountNo = '878216362888'
group by
Dim_Outlet.FDMSAccountNo,
[Credit_Debit],
[Scheme]

veeranjaneyulu

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-10-25 : 07:24:28

HI VeeranjaneyuluAnnapureddy

i get the following error message when using your logic

Error message is'Msg 4145, Level 15, State 1, Line 8
An expression of non-boolean type specified in a context where a condition is expected, near 'when'.'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-25 : 08:35:10

[code]
SELECT
Dim_Outlet.FDMSAccountNo,
sum(Fact_Financial_History_2.hst_sales_amt_R12) As Sales,
sum(Fact_Financial_History_2.hst_sales_tran_R12)As Trans,
[Credit_Debit],
[Scheme],
sum(case when scheme = 'visa' and Credit_Debit = 'Credit' then hst_sales_amt_R12 else 0 end) as Visa_Credit
FROM Fact_Financial_History_2
INNER JOIN Dim_Interchange_Tier_2 ON Fact_Financial_History_2.Plan_Key = Dim_Interchange_Tier_2.Plan_Code
INNER JOIN Dim_Outlet ON Fact_Financial_History_2.hst_merchnum = Dim_Outlet.FDMSAccountNo_First9
where FDMSAccountNo = '878216362888'
group by
Dim_Outlet.FDMSAccountNo,
[Credit_Debit],
[Scheme]
[/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