SUM CASE - 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

SQL Server 2005 Forums

Transact-SQL (2005)

SUM CASE

Author

Topic

Jannette
Starting Member

26 Posts

Posted - 2011-08-26 : 06:56:43

I have the follwing SUM CASE statement that does not appear to be working, can you help with the syntax please.

Sum (Case When Payslip_P11.P11_NI_1a > 0 and Candidate.Active_YN in ('Y','N') Then Payslip.Gross_Pay Else 0 End) as Gross_Pay,

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-08-26 : 07:07:13

How exactly is it 'not working'......

Does it generate and error?
Does it generate the wrong results?

How could we tell?

Please give us a little more to work with. Ideally table definition(s) and the full query, If you can some sample data and the required result.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-26 : 07:19:53

see

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

for instructions on how to post a question

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

Jannette
Starting Member

26 Posts

Posted - 2011-08-26 : 07:40:20

There are no errors being produced, but the field Payslip.Gross_Pay is not showing the correct figure.

Scenario:
Week 7, P11_NI_1a = 0, Candidate Active= Y, Gross_Pay 221.76
Week 7, P11_NI_1a = 11, Candidate Active= Y, Gross_Pay 342.76
Value in Gross Pay should be 342.76

Week 7, P11_NI_1a = 0, Candidate Active= Y, Gross_Pay 666.45
Value in Gross Pay should be 666.45

=====================================================================
The full query is as follows:

SELECT '""',
Candidate.Candidate_Code,
Sum (Case When Payslip_P11.P11_NI_1a > 0 and Candidate.Active_YN in ('Y','N')
Then Payslip.Gross_Pay Else 0 End) as Gross_Pay,
Sum (Case When Payslip_P11.P11_NI_1a > 0 Then Payslip.Nett_Pay Else 0 End) as Nett_Pay,
Sum (Case When Payslip_P11.P11_NI_1a > 0 Then Payslip.NIable_Pay Else 0 End) as NIable_Pay,
Sum (Case When Payslip_P11.P11_NI_1a > 0 Then (Payslip.Gross_Pay - Payslip.NIable_Pay)
Else 0 End) as Non_NIable_Pay,
Sum(Case When Payslip_P11.P11_NI_1a > 0 Then Payslip.Taxable_Pay Else 0 End) as Taxable_Pay,
Sum(Payslip.Gross_Pay - Payslip.Taxable_Pay) as Non_Taxable_Pay,
Sum(Payslip.Tax_Deduction) as Total_Tax,
Sum(Payslip_P11.P11_NI_1g) as SSP,
Sum(Payslip_P11.P11_NI_1h) as SMP,
Sum(Payslip_P11.P11_NI_1k) as SAP,
Sum(Payslip_P11.P11_NI_1i) as SPP,
Sum(Payslip_P11.P11_NI_1l) as Student_Loan,
Sum(Payslip_P11.P11_Tax_9) as Tax_Credit,
0.00,
PAYE.P45_PTD, Sum(Payslip.Taxable_Pay),
PAYE.P45_TTD, Sum(Payslip.Tax_Deduction),
0.00, 0.00, 0.00,
0.00, 0.00,
Sum(Payslip.NIEE_Deduction)as EE, 0.00,
Sum(Payslip.NIER_Deduction) as ER,
Sum(Payslip_P11.P11_NI_1a),
Sum(Payslip_P11.P11_NI_1b),
Sum(Payslip_P11.P11_NI_1c),
Sum(Payslip_P11.P11_NI_1d),
Sum(Payslip_P11.P11_NI_1g),
Sum(Payslip_P11.P11_NI_1h),
0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00,
0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00,
0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00,
0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00,
0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00,
0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00,
0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00,
0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00,
0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00,
Sum(Payslip.NIEE_Deduction) as EE,
Sum(Payslip.NIER_Deduction) as ER,
Sum(Payslip.NIEE_Deduction + Payslip.NIER_Deduction),
Max(Payslip_P11.Reference_Period),
Payslip_P11.P11_Director_YN,
'""',
Person.Last_Name,
Min(Payslip_P11.Reference_Period),
Max(Payslip_P11.Reference_Period)

FROM Payslip INNER JOIN Payslip_P11 ON Payslip.Payslip_Id = Payslip_P11.Payslip_Id
INNER JOIN PAYE ON Payslip.PAYE_Id = PAYE.PAYE_Id
INNER JOIN Person ON PAYE.Person_Id = Person.Person_Id
INNER JOIN Candidate ON Person.Person_Id = Candidate.Person_Id

WHERE Payslip_P11.P11_NI_Table = 'A'
AND Payslip_P11.Reference_Year = 2011

GROUP BY Candidate.Candidate_Code, Payslip_P11.P11_Director_YN, Person.Last_Name, PAYE.P45_PTD, PAYE.P45_TTD

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-26 : 08:10:03

so is it always last value you want?

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

Jannette
Starting Member

26 Posts

Posted - 2011-08-26 : 08:30:50

Scenario:
Week 7, P11_NI_1a = 0, Candidate Active= Y, Gross_Pay 221.76
Week 7, P11_NI_1a = 11, Candidate Active= Y, Gross_Pay 342.76
Week 8, P11_NI_1a = 12, Candidate Active= Y, Gross_Pay 100.00

Value in Gross Pay should be 442.76

Week 7, P11_NI_1a = 0, Candidate Active= Y, Gross_Pay 666.45
Value in Gross Pay should be 666.45

=====================================================

Using the above scenario not necessarily.

Sorry I hope I am explaining myself

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-26 : 08:44:52

please explain in words wht you want

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

Jannette
Starting Member

26 Posts

Posted - 2011-08-30 : 09:16:05

I have resolved this issue now. Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources