Is this possible? - 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

Transact-SQL (2000)

Is this possible?

Author

Topic

patshaw
Posting Yak Master

177 Posts

Posted - 2007-06-04 : 11:39:53

I have 2 tables:

Table 1 - EmployeeIDs
ID EmpID
1 109664
2 109665
3 109666

Table 2 - Timesheets
ID Employee [Date]
1 109664 11/05/2007
2 109664 12/05/2007
3 109664 13/05/2007
4 109665 11/05/2007
5 109665 12/05/2007
6 109666 12/05/2007

Taken over a specific date range (eg. 11/05 to 13/05) using BETWEEN, I need to find which employees have not made an entry into the Timesheets table, but I also need to return the Date that the missing entry occured.

Is this possible or am I overlooking something obvious?

Thanks.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-04 : 11:44:22

You need a tally table to give you the dates - you can then left join to that to get the missing entries.
You can use the distinct dates in Timesheets if you want to risk not alerting if no one enters a timesheet

select e.EmpID, d.date
from EmployeeIDs e
join (select distinct Date from Timesheets) d
left join TimeSheets t
on e.EmpID = t.EmpID
and d.Date = t.Date
where d.Date between @startdate and @enddate
and t.EmpID is null

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-05 : 01:07:19

Select a.Id,
a.EmpId,
b.[date],
'Timesheet Present?' = case when c.[date] is Null then 'No' else 'Yes' end
from EmployeeIds a cross join
(select [date] = dateadd(dd, number,@start)
from master..spt_values x
where name is null and number <= (datepart(dd,@end) - datepart(day, @start)))
as b left join Timesheets c
on a.empid = c.empid and b.[date] = c.[date]
order by a.empid, b.[date]

--------------------------------------------------
S.Ahamed

patshaw
Posting Yak Master

177 Posts

Posted - 2007-06-05 : 05:52:00

Thanks for your help guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources