How can I make this selection - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How can I make this selection

Author

Topic

anupalavila
Yak Posting Veteran

56 Posts

Posted - 2008-10-10 : 03:11:46

I have two tables named EmployeeAttendenceMaster(EmpId,LoginTime,LogoutTime) and EmployeeShiftsHolidays. I want to list the
attendence of an employee between two dates for example 1/9/2008 30/9/2008

How can I make a selection such that for all the dates between 1/9/2008 and 30/9/2008
if the date is in EmployeeAttendenceMaster select loginTime,logoutTime and Duration(LogoutTime-LoignTime)
if the date not in EmployeeAttendenceMaster and the date in EmployeeShiftsHolidays select HolidayDate and
if the date not in EmployeeAttendenceMaster and EmployeeShiftsHolidays mark the date as leave

How can I make this listing

Thanks in advance

Thanks and Regards
Anu Palavila

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-10 : 04:19:18

do you mean your number of columns in resultset needs to vary based on conditions?

anupalavila
Yak Posting Veteran

56 Posts

Posted - 2008-10-10 : 04:51:15

no I want to get the the resultset ie attendance for a month which contains all the dates of that month and whether present,Holiday and absent should list accordingly from 2 tables EmployeeAttendenceMaster and EmployeeShiftsHolidays ie

for all the dates between 1/9/2008 and 30/9/2008
if the date is in EmployeeAttendenceMaster select loginTime,logoutTime and Duration(LogoutTime-LoignTime)
if the date not in EmployeeAttendenceMaster and the date in EmployeeShiftsHolidays select HolidayDate and
if the date not in EmployeeAttendenceMaster and EmployeeShiftsHolidays mark the date as leave

Thanks and Regards
Anu Palavila

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-10 : 06:42:10

[code]DECLARE @Start datetime,@end datetime

SELECT @Start='1/9/2008',@End='30/9/2008'

SELECT DATEADD(dd,v.number,@Start) AS Date,
am.loginTime,am.logoutTime,
DATEDIFF(ss,am.loginTime,am.logoutTime) AS Duration,
sh.HolidayDate,
CASE WHEN am.DateField IS NULL AND sh.DateField IS NULL THEN 'Leave' ELSE 'Working' END
FROM master..spt_values v
LEFT JOIN EmployeeAttendenceMaster am
ON am.DateField=DATEADD(dd,v.number,@Start)
LEFT JOIN EmployeeShiftsHolidays sh
ON sh.DateField=DATEADD(dd,v.number,@Start)
WHERE v.type='p'
AND DATEADD(dd,v.number,@Start)<=@End[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources