locked
To generate the sql to find employee who are not working for the given date RRS feed

  • Question

  • I have the following table and  its record,  I want to create an sql if the  dayoffHrs >0 or Employee Hrs =0  for the date given, then it should be  returned as 'Yes'

    For example

    declare @Date datetime

    set @Date = '04-17-2019'

    Select  EmpId,  'Yes'  as DayOffYes

    from dayoffhrs where DayOffHrs>0 and Attdate = @Date

    Union

    Select EmpID, 'Yes' as DayOffYes

    from Employee where    Case when   DATEPART(dw,@Date ) = 4  and   WedHrs>0  Or DATEPART(dw,@Date ) = 3 and TueHrs >0 

    Please  help, I have given the table structure.

    Many Thanks

    Pol

    Create table Employee
    (Empid int, EmpName varchar(max), MonHrs decimal(5,2), TueHrs decimal(5,2), WedHrs decimal(5,2) , ThuHrs decimal(5,2), FriHrs decimal(5,2), SatHrs decimal(5,2))
    
    Empid    EmpName   MonHrs     TueHrs      WedHrs         ThuHrs          FriHrs          SatHrs
    20        XXXX       8           8          0              8               0                0
    13        YYYY       8           0          8              8               8                8
    15        ZZZZ       9           9          0              9               9                9              
    
    
    
    
    Create table DayOff
    (Empid int,  attdate datetime, dayoffHrs(5,2))
    
    
    
    Record
    EmpID      Attdate              DayOffHrs
    20         01-01-2019            8
    20         07-07-2019            8
    13         10-06-2019            7.30
    13         13-06-2019            7.30
    15         02-01-2019            9.00
    15         08-01-2019            4.30

     



    polachan


    • Edited by polachan Wednesday, April 17, 2019 11:25 AM
    Wednesday, April 17, 2019 11:21 AM

All replies

  • declare @Date datetime

    set @Date = '04-17-2019'

    If exists (

    Select   * 

    from dayoffhrs join employee  on dayoffhrs.empid=employee.empid

       where DayOffHrs>0 and Attdate = @Date

    and 1=Case when   DATEPART(dw,@Date ) = 4  and   

            WedHrs>0  Or DATEPART(dw,@Date ) = 3 and TueHrs >0 

        then 1 else 0 end 

    )

    begin 

    print 'Yes' 

    end


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, April 17, 2019 11:51 AM
    Answerer
  • I have the following table and  its record,  I want to create an sql if the  dayoffHrs >0 or Employee Hrs =0  for the date given, then it should be  returned as 'Yes'

    For example

    declare @Date datetime

    set @Date = '04-17-2019'

    Select  EmpId,  'Yes'  as DayOffYes

    from dayoffhrs where DayOffHrs>0 and Attdate = @Date

    Union

    Select EmpID, 'Yes' as DayOffYes

    from Employee where    Case when   DATEPART(dw,@Date ) = 4  and   WedHrs>0  Or DATEPART(dw,@Date ) = 3 and TueHrs >0 

    Please  help, I have given the table structure.

    Many Thanks

    Pol

    Create table Employee
    (Empid int, EmpName varchar(max), MonHrs decimal(5,2), TueHrs decimal(5,2), WedHrs decimal(5,2) , ThuHrs decimal(5,2), FriHrs decimal(5,2), SatHrs decimal(5,2))
    
    Empid    EmpName   MonHrs     TueHrs      WedHrs         ThuHrs          FriHrs          SatHrs
    20        XXXX       8           8          0              8               0                0
    13        YYYY       8           0          8              8               8                8
    15        ZZZZ       9           9          0              9               9                9              
    
    
    
    
    Create table DayOff
    (Empid int,  attdate datetime, dayoffHrs(5,2))
    
    
    
    Record
    EmpID      Attdate              DayOffHrs
    20         01-01-2019            8
    20         07-07-2019            8
    13         10-06-2019            7.30
    13         13-06-2019            7.30
    15         02-01-2019            9.00
    15         08-01-2019            4.30

     



    polachan


    this you mean?

    SELECT Empid,'Yes'
    FROM
    (
    SELECT Empid,Col,Val
    FROM Employee e
    UNPIVOT(Val FOR Col IN ( MonHrs,     TueHrs   ,   WedHrs      ,   ThuHrs   ,       FriHrs ,         SatHrs))u
    )r
    WHERE Val = 0
    AND LEFT(DATENAME(dw,@date),3) = REPLACE(Col,'Hrs','')
    
    UNION
    
    SELECT Empid,'Yes'
    FROM DayOff
    WHERE dayOfHrs >0
    AND attdate = @date


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, April 17, 2019 12:09 PM
  • Please post the outputs you expect based on your sample data.

    A Fan of SSIS, SSRS and SSAS

    Wednesday, April 17, 2019 1:22 PM
  • HI polachan

    DECLARE @Date DATE;
    DECLARE @column NVARCHAR(50);
    DECLARE @sql NVARCHAR(MAX);
    SET @Date = '04-17-2019';
    SET @column = LEFT(DATEname(dw, @Date), 3) + 'Hrs';
    SET @sql = N'SELECT Empid,''Yes''
    FROM Employee e where ' + @column + ' > 0
    
    UNION
    
    SELECT Empid,''Yes''
    FROM DayOff
    WHERE dayOffHrs >0
    AND attdate = ''' + CAST(@date AS NVARCHAR(20)) + '''';
    EXEC sp_executesql 
         @SQL;

    Hope it can help you.

     

    Best Regards,

    Natig


    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. 


    Wednesday, April 17, 2019 1:26 PM
  • SELECT d.*, 
    	CASE WHEN d.dayoffHrs > 0 OR  
    		CASE 
    			WHEN DATEPART(dw, d.attdate) = 2 THEN e.MonHrs
    			WHEN DATEPART(dw, d.attdate) = 3 THEN e.TueHrs
    			WHEN DATEPART(dw, d.attdate) = 4 THEN e.WedHrs
    			WHEN DATEPART(dw, d.attdate) = 5 THEN e.ThuHrs
    			WHEN DATEPART(dw, d.attdate) = 6 THEN e.FriHrs
    			WHEN DATEPART(dw, d.attdate) = 7 THEN e.SatHrs
    			ELSE 0
    		END = 0
    	THEN 'Yes'
    	ELSE 'No' 
    	END AS DayOffYes
    FROM DayOff AS d
    INNER JOIN Employee AS e ON e.Empid = d.Empid
    WHERE d.attdate = @date;



    A Fan of SSIS, SSRS and SSAS

    Wednesday, April 17, 2019 1:37 PM
  • Hi  polachan, 

    If possible, could you please inform me your expecting output? Then I will help you more correctly.

    You could refer to below expression to see whether it works or not

    Create table Employee
    (Empid int, EmpName varchar(max), MonHrs decimal(5,2), TueHrs decimal(5,2), WedHrs decimal(5,2) , ThuHrs decimal(5,2), FriHrs decimal(5,2), SatHrs decimal(5,2))
    insert into Employee values 
    
    (20,        'XXXX'  ,     8,           8 ,         0,              8,               0,                0),
    (13,        'YYYY'  ,     8 ,          0,          8,              8,               8,                8),
    (15,        'ZZZZ' ,      9,           9,          0,              9 ,              9,                9 )             
    
    
    
    
    Create table DayOff
    (Empid int,  attdate datetime, dayoffHrs decimal(5,2))
    
    insert into DayOFF VALUES 
    
    
    (20 ,        '01-01-2019' ,           8),
    (20 ,        '07-07-2019' ,           8),
    (13,         '06-10-2019',            7.30),
    (13,         '06-13-2019' ,           7.30),
    (15,         '01-02-2019' ,           9.00),
    (15  ,       '01-08-2019' ,           4.30)
     declare @date date='2019/4/16'
     SELECT a.*, b.attdate,b.dayoffHrs, 
     case when (DATEPART(dw,@Date ) = 4  and   WedHrs>0 ) Or (DATEPART(dw,@Date ) = 3 and TueHrs >0) or (b.attdate=@date and b.dayoffHrs>0) then 'Y' else 'N' end  as dayoffyes  
     FROM Employee a LEFT JOIN Dayoff b on a.Empid=b.Empid
     -- select  DATEPART(dw,@Date ) 
    
     DROP table DayOff
     DROP table Employee
    Best Regards,
    Zoe Zhi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 18, 2019 7:25 AM