Solved

Case When Result

Posted on 2013-01-18
11
329 Views
Last Modified: 2013-01-20
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,StartingDate,EndingDate)

            , Casual=Case When JoiningDate<=b.StartingDate and RegsinedOn is null then c.Casual else
                         Case When JoiningDate<=b.StartingDate and RegsinedOn>=EndingDate then c.Casual else
                         Case When JoiningDate<=b.StartingDate and RegsinedOn<=EndingDate then Round(c.Casual/365*(DATEDIFF(d,  b.StartingDate, RegsinedOn)),0) else
                         Case When JoiningDate>=b.StartingDate 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
0
Comment
Question by:Mehram
  • 3
  • 3
  • 2
  • +2
11 Comments
 
LVL 11

Expert Comment

by:Simone B
ID: 38794602
You actually have 4 nested CASE statements in your query, where only one is required. If you remove the unnecessary nesting, you get this:

Casual=Case When JoiningDate<=b.StartingDate and RegsinedOn is null then c.Casual  
                          When JoiningDate<=b.StartingDate and RegsinedOn>=EndingDate then c.Casual  
                          When JoiningDate<=b.StartingDate and RegsinedOn<=EndingDate then Round(c.Casual/365*(DATEDIFF(d,  b.StartingDate, RegsinedOn)),0)  
                          When JoiningDate>=b.StartingDate and RegsinedOn is null then (DATEDIFF(d,  JoiningDate, EndingDate)) else 0 end 

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.StartingDate and RegsinedOn is null then (DATEDIFF(d,  JoiningDate, EndingDate))

That's why you're getting the DATEDIFF calculated in that column instead of 0.
0
 

Author Comment

by:Mehram
ID: 38796329
sir, result is still same. please correct my code
0
 

Author Comment

by:Mehram
ID: 38796632
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,StartingDate,EndingDate)
            , 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
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38796750
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
0
 

Author Comment

by:Mehram
ID: 38796761
sir, I'm working on it since yesterday and getting same result. is there anyother way to do that?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38796788
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?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38797836
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.
0
 
LVL 39

Accepted Solution

by:
appari earned 250 total points
ID: 38799636
i think you need to add one more condition as follows:

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,StartingDate,EndingDate)
            , 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 between startingDAte and EndingDate and RegsinedOn Is null Then 2                    
                    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
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 250 total points
ID: 38799665
@appari, think that still returns a 2

Maybe the bold is better as :

When JoiningDate between startingDAte and EndingDate and RegsinedOn Is null Then 0

-- or given the less than and greater than, then probably more accurate to do

When JoiningDate > startingDAte and JoiningDate < EndingDate and RegsinedOn Is null Then 0

Open in new window


Seems to match the expected result of 0 for those highlighted rows...

And yes, that is exactly the type of thing I meant about "additional conditions" needing to be checked.
0
 
LVL 39

Expert Comment

by:appari
ID: 38799684
i think the condition should be

When JoiningDate > startingDAte and JoiningDate > EndingDate and RegsinedOn Is null Then 0          
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38799716
*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
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now