complicated timesheet query 2 - 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)

complicated timesheet query 2

Author

Topic

erwine
Yak Posting Veteran

69 Posts

Posted - 2007-07-25 : 05:07:55

Hi SQL gurus,

I have another issue regarding to worker's timesheet:
There are 2 tables, timesheet table and publicholiday table. I need to get the first 6 dates within the month where the worker did not come to work on working day.

--This table contains timesheet of Employee 111
--No record means not working
CREATE TABLE TIMESHEET (EmpID int, Date datetime, TimeIn nvarchar(4), TimeOut nvarchar(4))
GO
INSERT INTO TIMESHEET
SELECT 100, '20070701', 0900, 1800 UNION ALL
SELECT 100, '20070702', 0900, 1800 UNION ALL
SELECT 100, '20070703', 0900, 1800 UNION ALL
SELECT 100, '20070704', 0900, 1800 UNION ALL

SELECT 111, '20070701', 0900, 1800 UNION ALL
SELECT 111, '20070702', 0900, 1800 UNION ALL
SELECT 111, '20070703', 0900, 1800 UNION ALL
SELECT 111, '20070704', 0900, 1800 UNION ALL
SELECT 111, '20070705', 0900, 1800 UNION ALL
SELECT 111, '20070706', 0900, 1800 UNION ALL
SELECT 111, '20070707', 0900, 1800 UNION ALL
SELECT 111, '20070708', 0900, 1800 UNION ALL
SELECT 111, '20070710', 0900, 1800 UNION ALL
SELECT 111, '20070711', 0900, 1800 UNION ALL
SELECT 111, '20070712', 0900, 1800 UNION ALL
SELECT 111, '20070714', 0900, 1800 UNION ALL
SELECT 111, '20070715', 0900, 1800 UNION ALL
SELECT 111, '20070716', 0900, 1800 UNION ALL
SELECT 111, '20070717', 0900, 1800 UNION ALL
SELECT 111, '20070718', 0900, 1800 UNION ALL
SELECT 111, '20070719', 0900, 1800 UNION ALL
SELECT 111, '20070720', 0900, 1800 UNION ALL
SELECT 111, '20070721', 0900, 1800 UNION ALL
SELECT 111, '20070722', 0900, 1800 UNION ALL
SELECT 111, '20070723', 0900, 1800 UNION ALL
SELECT 111, '20070724', 0900, 1800 UNION ALL
SELECT 111, '20070725', 0900, 1800 UNION ALL
SELECT 111, '20070729', 0900, 1800 UNION ALL
SELECT 111, '20070730', 0900, 1800 UNION ALL
SELECT 111, '20070731', 0900, 1800
GO

--This table contains the dates of public holiday during the month.
--In my case, sunday is public holiday.
CREATE TABLE PUBLICHOLIDAY (Month nvarchar(10), Date datetime)
GO
INSERT INTO PUBLICHOLIDAY
SELECT 'July', '20070701' UNION ALL
SELECT 'July', '20070708' UNION ALL
SELECT 'July', '20070715' UNION ALL
SELECT 'July', '20070722' UNION ALL
SELECT 'July', '20070729'
GO

The expected result :

empid absentdate
100 '20070705'
100 '20070706'
100 '20070707'
100 '20070709'
100 '20070710'
100 '20070711'

111 '20070709'
111 '20070713'
111 '20070726'
111 '20070727'
111 '20070728'

any ideas, anyone?
thanks for your attention.

cheers,
erwine

... sql is fun...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-25 : 05:18:27

[code]SELECT e.EmpID, d.[DATE], h.[Date]
FROM F_TABLE_DATE('2007-07-01', '2007-07-31') d
CROSS JOIN EMP e
left JOIN PUBLICHOLIDAY h
ON d.[DATE] = h.[Date]
left JOIN TIMESHEET t
ON e.EmpID = t.EmpID
AND d.[DATE] = t.[Date]
WHERE h.[Date] IS NULL
AND t.EmpID IS NULL[/code]

KH
[spoiler]Time is always against us[/spoiler]

erwine
Yak Posting Veteran

69 Posts

Posted - 2007-07-25 : 05:30:09

hi,

you again khtan. thanks.

is F_TABLE_DATE is user-defined function?
how do you return a table of one full month? any new feature in 2005? or it's just a loop from startdate to enddate (parameters)?

cheers,
erwine

... sql is fun...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-25 : 08:25:15

Sorry, i assume everybody in SQL Team knows about the F_TABLE_DATE