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

Author

Topic

erwine
Yak Posting Veteran

69 Posts

Posted - 2007-07-24 : 04:52:19

Hi SQL gurus,

I'm working on employee timesheet management. I want to check if the worker is working on the day before and after Public Holiday.
Note : If worker is not working today, there is no record for his/her timesheet today.

I have 2 tables:

--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 111, '20070630', 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, '20070726', 0900, 1800 UNION ALL
SELECT 111, '20070727', 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

I would like to get the end result like this:
---------------------------------------------
Public Holiday 1 Day Before 1 Day After
'20070701' Working Working
'20070708' Working Not Working
'20070715' Working Working
'20070722' Working Working
'20070729' Not working Working

I can't afford to use thirdparty script/code like vb.net because i have a very huge daily timesheet and i'm thinking of using stored procedure to solve this issue.

Anyone has ideas?

Thanks for your kind attention.
I look forward to your replies.

cheers,
erwine

... sql is fun...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-24 : 05:09:06

[code]SELECT h.[Date],
[1 DAY before] = CASE WHEN b.[Date] IS NOT NULL THEN 'Working' ELSE 'NOT Working' END,
[1 DAY after] = CASE WHEN a.[Date] IS NOT NULL THEN 'Working' ELSE 'NOT Working' END
FROM PUBLICHOLIDAY h
left JOIN TIMESHEET b ON b.[Date] = h.[Date] - 1
left JOIN TIMESHEET a ON a.[Date] = h.[Date] + 1
[/code]

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

erwine
Yak Posting Veteran

69 Posts

Posted - 2007-07-24 : 05:19:42

Wow..

It's working..thanks so much khtan.. but i tested using more than one employee and it's giving me weird result. Please take a look.

Thanks again.

Cheers,
erwine

... sql is fun...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-24 : 05:23:51

quote:
Originally posted by erwine

Wow..

It's working..thanks so much khtan.. but i tested using more than one employee and it's giving me weird result. Please take a look.

Thanks again.

Cheers,
erwine

... sql is fun...

Because i did not include the employee at all in the query.

SELECT	h.[Date],
	[1 DAY before]	= CASE WHEN b.[Date] IS NOT NULL THEN 'Working' ELSE 'NOT Working' END,
	[1 DAY after]	= CASE WHEN a.[Date] IS NOT NULL THEN 'Working' ELSE 'NOT Working' END
FROM	PUBLICHOLIDAY h
	left JOIN TIMESHEET  b	ON	b.[Date]	= h.[Date] - 1	AND	EmpID	= @EmpID
	left JOIN TIMESHEET  a	ON	a.[Date]	= h.[Date] + 1	AND	EmpID	= @EmpID

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

erwine
Yak Posting Veteran

69 Posts

Posted - 2007-07-24 : 05:24:23

Perhaps, what I need is query that can generate:

EmpID Date 1DayBefore 1DayAfter

100 '20070701' Working Working
100 '20070708' Working Not Working
100 '20070715' Working Working
100 '20070722' Working Working
100 '20070729' Working Working

111 '20070701' Working Working
111 '20070708' Working Not Working
111 '20070715' Working Working
111 '20070722' Working Working
111 '20070729' Not working Working

Thanks.

... sql is fun...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-24 : 05:30:54

You must have a EMP table right ?

SELECT	e.EmpID, h.[Date], 
	[1 DAY before]	= CASE WHEN b.[Date] IS NOT NULL THEN 'Working' ELSE 'NOT Working' END,
	[1 DAY after]	= CASE WHEN a.[Date] IS NOT NULL THEN 'Working' ELSE 'NOT Working' END
FROM	PUBLICHOLIDAY h
	CROSS JOIN EMP e
	left JOIN TIMESHEET  b	ON	b.[Date] = h.[Date] - 1	AND e.EmpID = b.EmpID
	left JOIN TIMESHEET  a	ON	a.[Date] = h.[Date] + 1 AND e.EmpID = a.EmpID
ORDER BY e.EmpID, h.[Date]

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

erwine
Yak Posting Veteran

69 Posts

Posted - 2007-07-24 : 05:38:20

yup..cross join. i learn something today.

thanks so much for your help.

cheers,
erwine

... sql is fun...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources