July 7, 2005 at 1:04 am
I'm a beginner at SQL server. I have a question:
---------
I have Employee table.
Employee table
--------------
EmpNO DayOfWork HoursWorked Holiday Code
1 07/04/2005 0 GovernmentHoilday(GH)
2 07/04/2005 0 GovernmentHoilday(GH)
3 07/04/2005 0 GovernmentHoilday(GH)
1 07/05/2005 8 Null
2 07/05/2005 8 Null
3 07/05/2005 8 Null
1 07/06/2005 8 Null
2 07/06/2005 8 Null
3 07/06/2005 0 Sick(S)
......
.....
3 07/30/2005 8 Null
--------------------------------------------------------
Report from 07/04/2005 to 07/30/2005
-----------------------------------
EmpNO EmpName HoursWorked HolidayCOde
1 A 152hrs GH
2 B 152hrs GH
3 C 148hrs Sick(S)
---------------------------------------------------------
The Employee details for each Employee will be entered into table every day.This is a program in ASP.net which retrives data from SQL Server database into excelsheets.So when an user enters the 1st date staring on monday and 2nd date ending on sunday.This dates will be used for report generation of emplyees.It generates the total number of hours worked for that range of dates entered.If we consider from july 4th(monday) to july 30th(Sunday)we will have 20 entries for each employee in the Employee database.So my aim is to count all the holliday codes for each employee and give the output in report.I am able to count Total number of hours including leaves.
I want the output like the below
--------------------------------------------------------------
Report from 07/04/2005 to 07/30/2005
-----------------------------------
EmpNO EmpName HoursWorked HolidayCOde
1 A 152hrs GH=8HR
2 B 152hrs GH=8hrs
3 C 140hrs GH=8HR,S=16HR.
I am not able to count the holiday code as mentioned above. How can I code to get the result I wanted.
-------------------
I'm wondering if any experts out there could help, Thanks in advance.
-alice
July 8, 2005 at 8:08 am
would something like this work for you?
Create Table Employee(
EmpNO int,
DayOfWork datetime,
HoursWorked int,
[Holiday Code] varchar (30)
)
insert into Employee( EmpNO, DayOfWork, HoursWorked, [Holiday Code]) VALUES(1, '07/04/2005', 0, 'GovernmentHoilday(GH)')
insert into Employee( EmpNO, DayOfWork, HoursWorked, [Holiday Code]) VALUES(2, '07/04/2005', 0, 'GovernmentHoilday(GH)')
insert into Employee( EmpNO, DayOfWork, HoursWorked, [Holiday Code]) VALUES(3, '07/04/2005', 0, 'GovernmentHoilday(GH)')
insert into Employee( EmpNO, DayOfWork, HoursWorked, [Holiday Code]) VALUES(1, '07/05/2005', 8, Null)
insert into Employee( EmpNO, DayOfWork, HoursWorked, [Holiday Code]) VALUES(2, '07/05/2005', 8, Null)
insert into Employee( EmpNO, DayOfWork, HoursWorked, [Holiday Code]) VALUES(3, '07/05/2005', 8, Null)
insert into Employee( EmpNO, DayOfWork, HoursWorked, [Holiday Code]) VALUES(1, '07/06/2005', 8, Null)
insert into Employee( EmpNO, DayOfWork, HoursWorked, [Holiday Code]) VALUES(2, '07/06/2005', 8, Null)
insert into Employee( EmpNO, DayOfWork, HoursWorked, [Holiday Code]) VALUES(3, '07/06/2005', 0, 'Sick(S)')
select EmpNO,[Holiday Code], count([Holiday Code])as HolidayCount from Employee group by EmpNO,[Holiday Code]
select
a.EmpNo,
isnull((select sum(HoursWorked) from Employee where isnull([Holiday Code],'Normal Hours')='Normal Hours' and EmpNO = A.EmpNO),0) as NormalHours,
isnull((select sum(HoursWorked) from Employee where isnull([Holiday Code],'Normal Hours')='GovernmentHoilday(GH)' and EmpNO = A.EmpNO) ,0) as GovHoliday,
isnull((select sum(HoursWorked) from Employee where isnull([Holiday Code],'Normal Hours')='Sick(S)' and EmpNO = A.EmpNO),0) as SickHours
from employee A group by a.empno order by A.empno
or to include the hours paid for holiday/sick:
select
a.EmpNo,
isnull((select sum(HoursWorked) from Employee where isnull([Holiday Code],'Normal Hours')='Normal Hours' and EmpNO = A.EmpNO),0) as NormalHours,
isnull((select count(HoursWorked) from Employee where isnull([Holiday Code],'Normal Hours')='GovernmentHoilday(GH)' and EmpNO = A.EmpNO) ,0) * 8 as GovHoliday,
isnull((select count(HoursWorked) from Employee where isnull([Holiday Code],'Normal Hours')='Sick(S)' and EmpNO = A.EmpNO),0) * 8 as SickHours
from employee A group by a.empno order by A.empno
results:
EmpNo NormalHours GovHoliday SickHours
----------- ----------- ----------- -----------
1 16 8 0
2 16 8 0
3 8 8 8
Lowell
July 8, 2005 at 11:41 pm
Hi,
Thanks a lot for your response!! I'll try this and let you know if any problems..
Regards,
Alice.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply