Mehram
asked on
Case When Result
Dear Experts,
I am using below mentioned query to assign leave days to employee according to the company policy.
The result of this query of last column (Casual) should be zero in first five rows while the last row value is correct. I’m not been able to understand where I am doing wrong and not getting proper result. Would anyone see and guide me please how to correct it.
Rgds.
Iqbal
Declare @CC Varchar(2)
Set @CC='01'
;with ETC as
(Select Casual=Sum(Casual), Earned=Sum(Earned), Sick=Sum(Sick), MAternity=Sum(Maternity) from
(Select Casual=Case When OnAccount='Casual' then Days else 0 end
,Earned=Case When OnAccount='Earned' then Days else 0 end
,Sick=Case When OnAccount='Sick' then Days else 0 end
,Maternity=Case When OnAccount='Maternity' then Days else 0 end
from mtLeavePolicy a Where a.cc=@CC)x)
Select a.TransNo, WorkingYear, JoiningDate, RegsinedOn, StartingDate, EndingDate, Days=(DATEDIFF(d, JoiningDate, EndingDate))
, Dayss=DateDiff(d,StartingD ate,Ending Date)
, Casual=Case When JoiningDate<=b.StartingDat e and RegsinedOn is null then c.Casual else
Case When JoiningDate<=b.StartingDat e and RegsinedOn>=EndingDate then c.Casual else
Case When JoiningDate<=b.StartingDat e and RegsinedOn<=EndingDate then Round(c.Casual/365*(DATEDI FF(d, b.StartingDate, RegsinedOn)),0) else
Case When JoiningDate>=b.StartingDat e and RegsinedOn is null then (DATEDIFF(d, JoiningDate, EndingDate)) else 0 end end end end
from Emp_Info a
Cross Join mtYear b
Cross Join ETC c
Where a.CC=@CC and a.Branch='KHI' and a.EmpCode='00311'
Order by a.JoiningDate
Capture.JPG
I am using below mentioned query to assign leave days to employee according to the company policy.
The result of this query of last column (Casual) should be zero in first five rows while the last row value is correct. I’m not been able to understand where I am doing wrong and not getting proper result. Would anyone see and guide me please how to correct it.
Rgds.
Iqbal
Declare @CC Varchar(2)
Set @CC='01'
;with ETC as
(Select Casual=Sum(Casual), Earned=Sum(Earned), Sick=Sum(Sick), MAternity=Sum(Maternity) from
(Select Casual=Case When OnAccount='Casual' then Days else 0 end
,Earned=Case When OnAccount='Earned' then Days else 0 end
,Sick=Case When OnAccount='Sick' then Days else 0 end
,Maternity=Case When OnAccount='Maternity' then Days else 0 end
from mtLeavePolicy a Where a.cc=@CC)x)
Select a.TransNo, WorkingYear, JoiningDate, RegsinedOn, StartingDate, EndingDate, Days=(DATEDIFF(d, JoiningDate, EndingDate))
, Dayss=DateDiff(d,StartingD
, Casual=Case When JoiningDate<=b.StartingDat
Case When JoiningDate<=b.StartingDat
Case When JoiningDate<=b.StartingDat
Case When JoiningDate>=b.StartingDat
from Emp_Info a
Cross Join mtYear b
Cross Join ETC c
Where a.CC=@CC and a.Branch='KHI' and a.EmpCode='00311'
Order by a.JoiningDate
Capture.JPG
ASKER
sir, result is still same. please correct my code
ASKER
I have changed my query and also added resigned employee in list to get proper result. The query is running for the first to employee (00001 & 00008) perfectly. But the result of last employee 00311 is not correct. The serial # 11 to 14 should be 0 instead of display 2.
Please help and correct my query.
Declare @CC Varchar(2)
Set @CC='01'
;with ETC as
(Select Casual=Sum(Casual), Earned=Sum(Earned), Sick=Sum(Sick), MAternity=Sum(Maternity) from
(Select Casual=Case When OnAccount='Casual' then Days else 0 end
,Earned=Case When OnAccount='Earned' then Days else 0 end
,Sick=Case When OnAccount='Sick' then Days else 0 end
,Maternity=Case When OnAccount='Maternity' then Days else 0 end
from mtLeavePolicy a Where a.cc=@CC)x)
Select a.TransNo, WorkingYear, a.EmpCode, JoiningDate, RegsinedOn, StartingDate, EndingDate
, Dayss=DateDiff(d,StartingD ate,Ending Date)
, Case when JoiningDate < StartingDate and RegsinedOn is null then c.casual
When JoiningDate < startingDAte and RegsinedOn > EndingDate then c.Casual
When JoiningDate < startingDAte and RegsinedOn < EndingDate then 1
When JoiningDate > startingDAte and RegsinedOn Is null Then 2 else 0 end
from Emp_Info a
Cross Join mtYear b
Cross Join ETC c
Where a.CC=@CC and a.Branch='KHI' and (a.EmpCode='00008' or a.EmpCode='00001' or a.EmpCode='00311')
Order by a.EmpCode
Capture.JPG
Please help and correct my query.
Declare @CC Varchar(2)
Set @CC='01'
;with ETC as
(Select Casual=Sum(Casual), Earned=Sum(Earned), Sick=Sum(Sick), MAternity=Sum(Maternity) from
(Select Casual=Case When OnAccount='Casual' then Days else 0 end
,Earned=Case When OnAccount='Earned' then Days else 0 end
,Sick=Case When OnAccount='Sick' then Days else 0 end
,Maternity=Case When OnAccount='Maternity' then Days else 0 end
from mtLeavePolicy a Where a.cc=@CC)x)
Select a.TransNo, WorkingYear, a.EmpCode, JoiningDate, RegsinedOn, StartingDate, EndingDate
, Dayss=DateDiff(d,StartingD
, Case when JoiningDate < StartingDate and RegsinedOn is null then c.casual
When JoiningDate < startingDAte and RegsinedOn > EndingDate then c.Casual
When JoiningDate < startingDAte and RegsinedOn < EndingDate then 1
When JoiningDate > startingDAte and RegsinedOn Is null Then 2 else 0 end
from Emp_Info a
Cross Join mtYear b
Cross Join ETC c
Where a.CC=@CC and a.Branch='KHI' and (a.EmpCode='00008' or a.EmpCode='00001' or a.EmpCode='00311')
Order by a.EmpCode
Capture.JPG
The serial # 11 to 14 should be 0 instead of display 2.
> When JoiningDate > startingDAte and RegsinedOn Is null Then 2 else 0 end
actually, looking at this expression, they shall indeed all 4 return 2, as joining date > startdate and regsinedOn is null.
I don't see how this should be different?
please double-check your data and expected results
> When JoiningDate > startingDAte and RegsinedOn Is null Then 2 else 0 end
actually, looking at this expression, they shall indeed all 4 return 2, as joining date > startdate and regsinedOn is null.
I don't see how this should be different?
please double-check your data and expected results
ASKER
sir, I'm working on it since yesterday and getting same result. is there anyother way to do that?
to do what?
please clarify what is "wrong"? I understand the formula you wrote, compare to the data, and I would return the same results...
in other words: WHY should it return 0 there?
I presume you are comparing the wrong fields?
please clarify what is "wrong"? I understand the formula you wrote, compare to the data, and I would return the same results...
in other words: WHY should it return 0 there?
I presume you are comparing the wrong fields?
Looking at the image of the query results, I have to agree with angelIII and a bit confused by your requirement.
for 00311 then joining date is in fact > starting date and regsinedOn is in fact NULL
it is the ONLY test where joining date is > starting date, so, maybe there are other conditions that need to be checked ?
Maybe if you descibe what that last column should be showing (not with code, just explanation) then we might be able to help decipher that case statement.
But so far, the results from your query looks quite correct given the T-SQL code.
for 00311 then joining date is in fact > starting date and regsinedOn is in fact NULL
it is the ONLY test where joining date is > starting date, so, maybe there are other conditions that need to be checked ?
Maybe if you descibe what that last column should be showing (not with code, just explanation) then we might be able to help decipher that case statement.
But so far, the results from your query looks quite correct given the T-SQL code.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
i think the condition should be
When JoiningDate > startingDAte and JoiningDate > EndingDate and RegsinedOn Is null Then 0
When JoiningDate > startingDAte and JoiningDate > EndingDate and RegsinedOn Is null Then 0
*laughing* yep... Or even
when JoiningDate > Endingdate and regsinedon is null then 0
But, think it is an "extra" condition that currently isnt catered for... We'll only know once we hear back from Mehram
when JoiningDate > Endingdate and regsinedon is null then 0
But, think it is an "extra" condition that currently isnt catered for... We'll only know once we hear back from Mehram
Open in new window
Looking at the data you have in your result, RegsinedOn is always NULL, and JoiningDate is always >= StartingDate, therefore the last option applies:
When JoiningDate>=b.StartingDat
That's why you're getting the DATEDIFF calculated in that column instead of 0.