April 7, 2012 at 12:54 am
Hi all
I have employees Table which canting 155 Records. I am prepared a advance payment report which is showing the Nett Post ion of payable amount. For this I less all types of loan's installment such as Festival Advance,Company Loan ,PF Loan. Motorcycle loan etc. From Actual Gross Salary . In the following Query I set a check which is when last installment of any type of loan then ignore it,but the case condition just read first condition not others and I want to read all others condition for all 155 Employees in one case. Hope some body help me. Or tell me any other solution for this.
case
-- when wages in Negative and one loan kind is zero
when (STANDARDGROSS-deductionall)<0 and (coloanBal=0) or (pfloanbal=0) or (eidadvancebal=0) or (Mcloanbal=0)
THEN (STANDARDGROSS)- sum(INCOMETAX+PFUNDCONT+FLATRENTDED+EOBI+UNIONFEE+(DEDUCTIONALl-actualgross))
--- when Wages Amount not in negative but one loan Type is Zero
when (STANDARDGROSS-deductionall)>0 and (pfloanbal=0) and coloanbal=0 and eidadvancebal=0 and Mcloanbal=0
THEN (STANDARDGROSS)- sum(INCOMETAX+PFUNDCONT+FLATRENTDED+EOBI+UNIONFEE)
-- when loan type not zero
when (STANDARDGROSS-deductionall)<0 or pfloanbal>0 or coloanbal>0 or eidadvancebal>0 or Mcloanbal>0
THEN (STANDARDGROSS)- sum(INCOMETAX+PFUNDCONT+COLOANDED+PFLOANDED+EIDADVANCEDED+
MCLOANDED+FLATRENTDED+EOBI+UNIONFEE)+(DEDUCTIONALL-actualgross)
--when no loan
when(actualgross)-(DEDUCTIONALL)>0 then (STANDARDGROSS)- sum(INCOMETAX+PFUNDCONT+COLOANDED+
PFLOANDED+EIDADVANCEDED+MCLOANDED+FLATRENTDED+EOBI+UNIONFEE)
--- when Nett Position coming in negative
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 between 01 and 155
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,
RMSALARY.ActualGross,LastMonthAdj,eobi,HRMSALARY.EidAdvanceBal,HRMSALARY.PFLoanBal,
RMSALARY.CoLoanBal,HRMSALARY.MCLoanBal
April 7, 2012 at 3:07 am
Per definition a CASE function will return the result based on the first match.
There's very little we can do. The requirement doesn't make much sense to me.
Please provide table def and some ready to use sample data together with the expected result to demonstrate what you're trying to achieve.
As a side note:
INCOMETAX+PFUNDCONT+FLATRENTDED+EOBI+UNIONFEE is used in each CASE condition. It might be easier to read and understand, if this constant calculation would be excluded from the CASE function.
April 9, 2012 at 7:43 am
As Lutz already said, if you want some real help you will need to provide some real information (ddl, sample data etc)...
Taking a shot in the dark it looks like your case statements are not doing what you think they are.
case
-- when wages in Negative and one loan kind is zero
when (STANDARDGROSS-deductionall)<0 and (coloanBal=0) or (pfloanbal=0) or (eidadvancebal=0) or (Mcloanbal=0)
Your comments and your code contradict each other here. This will evaluate to true if the calculation < 0 OR any of the balance fields are 0.
I think you missed some parenthesis.
case
-- when wages in Negative and one loan kind is zero
when (STANDARDGROSS-deductionall)<0 and (coloanBal = 0 or pfloanbal = 0 or eidadvancebal = 0 or Mcloanbal = 0)
But without some more information it is just shooting blind.
_______________________________________________________________
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/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply