March 29, 2012 at 12:35 am
Hi All
SELECT EMPCODE,STANDARDGROSS,INCOMETAX,PFUNDCONT,COLOANDED,PFLOANDED,EIDADVANCEDED,
MCLOANDED,FLATRENTDED,EOBI,UNIONFEE,
case --1
when (DEDUCTIONALL-STANDARDGROSS)>0 then (actualgross- DEDUCTIONALL) end as Negative,
case --2
when (STANDARDGROSS-deductionall)<0 and pfloanbal=0 THEN
(STANDARDGROSS)- sum(INCOMETAX+PFUNDCONT+COLOANDED+EIDADVANCEDED+
MCLOANDED+FLATRENTDED+EOBI+UNIONFEE)+(DEDUCTIONALL-actualgross)
when (STANDARDGROSS-deductionall)<0 and coloanbal=0 THEN
(STANDARDGROSS)- sum(INCOMETAX+PFUNDCONT+PFLOANDED+EIDADVANCEDED+
MCLOANDED+FLATRENTDED+EOBI+UNIONFEE)+(DEDUCTIONALL-actualgross)
when (STANDARDGROSS-deductionall)<0 and eidadvancebal=0 then
(STANDARDGROSS)- sum(INCOMETAX+PFUNDCONT+COLOANDED+PFLOANDED+
MCLOANDED+FLATRENTDED+EOBI+UNIONFEE)+(DEDUCTIONALL-actualgross)
when (STANDARDGROSS-deductionall)<0 and pfloanbal>0 and coloanbal>0 and eidadvancebal>0 and Mcloanbal>0 THEN
(STANDARDGROSS)- sum(INCOMETAX+PFUNDCONT+COLOANDED+PFLOANDED+EIDADVANCEDED+
MCLOANDED+FLATRENTDED+EOBI+UNIONFEE)+(DEDUCTIONALL-actualgross)
when(actualgross)-(DEDUCTIONALL)>0 then (STANDARDGROSS)- sum(INCOMETAX+PFUNDCONT+COLOANDED+PFLOANDED+EIDADVANCEDED+
MCLOANDED+FLATRENTDED+EOBI+UNIONFEE)
when(actualgross)-(DEDUCTIONALL)<0 then (STANDARDGROSS)- sum(INCOMETAX+PFUNDCONT+COLOANDED+PFLOANDED+EIDADVANCEDED+
MCLOANDED+FLATRENTDED+EOBI+UNIONFEE)-(DEDUCTIONALL-actualgross) end as POSITION
FROM HRMSALARY
WHERE EMPCODE=2 ------between 51 and 100
group by HRMSALARY.EMPCODE,HRMSALARY.STANDARDGROSS,HRMSALARY.INCOMETAX,HRMSALARY.PFUNDCONT,
HRMSALARY.COLOANDED,HRMSALARY.PFLOANDED,HRMSALARY.EIDADVANCEDED,HRMSALARY.MCLOANDED,
HRMSALARY.FLATRENTDED,HRMSALARY.EOBI,HRMSALARY.UNIONFEE,HRMSALARY.DeductionAll,
HRMSALARY.ActualGross,LastMonthAdj,eobi,HRMSALARY.EidAdvanceBal,HRMSALARY.PFLoanBal,
HRMSALARY.CoLoanBal,HRMSALARY.MCLoanBal
problem In case -2, it just read first condition after when not others, Is any other Solution for above purpose--? Please
Regards.
March 29, 2012 at 7:44 am
Your question is very unclear. Can you try to explain it more clearly?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 29, 2012 at 7:58 am
The CASE expression will stop evaluating conditions as soon as it finds one that is true. If you want the other conditions to be evaluated every time, you need to put them in a separate CASE expression.
Hope that helps
John
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply