locked
How can I find the remaining Hours, Please help RRS feed

  • Question

  • I have the following table  and Records. From the following record , I want to find remaining hrs entitled for Holiday after they have taken. Please help me with suggested sqls to get the remaining holiday

    (Empid int,
     Empname varchar(max)
     HolidayEntitled dec(5,2))
    
    Create table tblHoliday
    (empid int,
    dateHoliday datetime,
    Descr  varchar(max),
    HolidayHrs dec(5,2))
    
    insert into TblEmp
    tblEmp
    (Empid,EmpName,HolidayEntitled )
    Values
    (1,'Employee1',210.30),   // 
    (2,'Employee2' 210.45),
    (3,'Employee3'230.30)
    
    Insert into tblHoliday
    (Empid,EmpName,Descr , HolidayHrs )
    Values
    (1, '01-01-2019','NewYear' 7.30),
    (2, '01-01-2019','NewYear' 7.30),
    (3, '01-01-2019','NewYear' 7.30),
    (1, '10-02-2019','Holiday' 7.45),
    (2, '10-03-2019','Holiday' 7.50),
    (3, '10-03-2019','Holiday' 1.59),
    (1, '01-05-2019','Holiday' 8.00),
    (2, '10-05-2019','Holiday' 7.15),
    (3, '15-05-2019','Holiday' 3.15)

    Output should be 

    With Thanks

    Pol


    polachan

    Wednesday, March 27, 2019 10:50 PM

All replies

  • Please test your script before you post. Thanks.

    create table TblEmp(Empid int,
     Empname varchar(max),
     HolidayEntitled dec(5,2))
    
    Create table tblHoliday
    (empid int,
    dateHoliday datetime,
    Descr  varchar(max),
    HolidayHrs dec(5,2))
    
    insert into TblEmp
     
    (Empid,EmpName,HolidayEntitled )
    Values
    (1,'Employee1',210.30),    
    (2,'Employee2', 210.45),
    (3,'Employee3',230.30)
    
    Insert into tblHoliday
    (Empid,dateHoliday,Descr , HolidayHrs )
    Values
    (1, '01-01-2019','NewYear', 7.30),
    (2, '01-01-2019','NewYear', 7.30),
    (3, '01-01-2019','NewYear', 7.30),
    (1, '10-02-2019','Holiday', 7.45),
    (2, '10-03-2019','Holiday', 7.50),
    (3, '10-03-2019','Holiday', 1.59),
    (1, '01-05-2019','Holiday', 8.00),
    (2, '10-05-2019','Holiday' ,7.15),
    (3, '12-05-2019','Holiday', 3.15)
    
    select h.empid,Empname,dateHoliday,Descr,HolidayEntitled,HolidayHrs
     , HolidayEntitled-sum(HolidayHrs) Over(PARTITION BY h.Empid ORDER BY dateHoliday ) RemainingHolidayHrs
     from tblHoliday h left join TblEmp e on h.empid=e.Empid
    order by 1
    
    
    drop table TblEmp,tblHoliday

    Thursday, March 28, 2019 2:24 AM
  • Hi polachan,

    According to your description, I think you could try below query to see whether it woks or not

    create table #TblEmp
    (Empid int,
     Empname varchar(max),
     HolidayEntitled dec(5,2))
    
     insert into #TblEmp
    
    Values
    (1,'Employee1',210.30),   
    (2,'Employee2', 210.45),
    (3,'Employee3',230.30)
    
    Create  table #tblHoliday
    (empid int,
    dateHoliday datetime,
    Descr  varchar(max),
    HolidayHrs dec(5,2))
    
    
    
    Insert into #tblHoliday
    Values
    (1, '01-01-2019','NewYear' ,7.30),
    (2, '01-01-2019','NewYear', 7.30),
    (3, '01-01-2019','NewYear', 7.30),
    (1, '02-10-2019','Holiday', 7.45),
    (2, '03-10-2019','Holiday', 7.50),
    (3, '03-10-2019','Holiday', 1.59),
    (1, '05-01-2019','Holiday' ,8.00),
    (2, '05-10-2019','Holiday', 7.15),
    (3, '05-15-2019','Holiday', 3.15)
    
    
    select h.empid,t.Empname,h.dateHoliday,h.Descr,t.HolidayEntitled,h.HolidayHrs  ,
    
    cast(round((ROUND(t.HolidayEntitled,0,1)*60+(t.HolidayEntitled%1)*100 -sum(ROUND(h.HolidayHrs,0,1)*60+(h.HolidayHrs%1)*100) over (partition by h.empid order by h.empid,h.dateHoliday) )/60,0,1)  
    +(((ROUND(t.HolidayEntitled,0,1)*60+(t.HolidayEntitled%1)*100 -sum(ROUND(h.HolidayHrs,0,1)*60+(h.HolidayHrs%1)*100) over (partition by h.empid order by h.empid,h.dateHoliday) )/60)%1 )*0.6 as decimal(10,2) )as remaining 
    from #tblHoliday h left join #TblEmp t on h.empid=t.Empid

    You could replace my #table with your table

    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, March 28, 2019 2:59 AM
  • Add this sample data (an employee that hasn't taken vacation)
    insert into #TblEmp Values (5,'Employee1',210.30)

    www.sqltopia.com

    Thursday, March 28, 2019 9:07 AM
  • Add this sample data (an employee that hasn't taken vacation)
    insert into #TblEmp Values (5,'Employee1',210.30)

    www.sqltopia.com

    Thursday, March 28, 2019 9:08 AM
  • -- swePeso
    WITH cteData
    AS (
    	SELECT		e.EmpID AS EmployeeID,
    			e.EmpName AS EmployeeName,
    			e.HolidayEntitled,
    			60 * FLOOR(e.HolidayEntitled) + (100 * e.HolidayEntitled) % 100 AS EntitledMinutes,
    			h.Descr,
    			h.DateHoliday,
    			h.HolidayHrs,
    			SUM(60 * FLOOR(ISNULL(h.HolidayHrs, 0)) + (100 * ISNULL(h.HolidayHrs, 0)) % 100) OVER (PARTITION BY e.EmpID ORDER BY h.DateHoliday) AS HolidayMinutes
    	FROM		#tblEmp  AS e
    	LEFT JOIN	#tblHoliday AS h ON h.EmpID = e.EmpID
    )
    SELECT		EmployeeID,
    		EmployeeName,
    		DateHoliday AS [Date],
    		Descr,
    		HolidayEntitled,
    		HolidayHrs,
    		CAST(FLOOR((EntitledMinutes - HolidayMinutes) / 60E) + ((EntitledMinutes - HolidayMinutes) % 60) / 100 AS DECIMAL(5, 2)) AS RemainingHolidayHrs
    FROM		cteData
    ORDER BY	EmployeeID,
    		DateHoliday;
    


    www.sqltopia.com

    Thursday, March 28, 2019 9:08 AM