Fill empty record - 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 2005 Forums

Transact-SQL (2005)

Fill empty record

Author

Topic

erwine
Yak Posting Veteran

69 Posts

Posted - 2007-11-22 : 01:07:15

Hi all,

Again I have problem in writing SQL query to get expected result. I have timesheet table :

CREATE TABLE Timesheet(EmpCode nvarchar(8), WDate date, Type nvarchar(2))

INSERT INTO Timesheet VALUES ('001','20070101','Full day')
INSERT INTO Timesheet VALUES ('001','20070102','Full day')
INSERT INTO Timesheet VALUES ('001','20070103','Full day')
INSERT INTO Timesheet VALUES ('001','20070104','Full day')
INSERT INTO Timesheet VALUES ('001','20070105','Half day')

CREATE TABLE Holiday (WDate date)
INSERT INTO Holiday VALUES ('20070101')
INSERT INTO Holiday VALUES ('20070102')
INSERT INTO Holiday VALUES ('20070107')
INSERT INTO Holiday VALUES ('20070114')
INSERT INTO Holiday VALUES ('20070121')
INSERT INTO Holiday VALUES ('20070128')

I would like to get all days within the month where employee is not coming to work on working day (based on holiday table):

INSERT INTO Timesheet VALUES ('001','20070101','Full day')
INSERT INTO Timesheet VALUES ('001','20070102','Full day')
INSERT INTO Timesheet VALUES ('001','20070103','Full day')
INSERT INTO Timesheet VALUES ('001','20070104','Full day')
INSERT INTO Timesheet VALUES ('001','20070105','Half day')
INSERT INTO Timesheet VALUES ('001','20070106','Full day')
INSERT INTO Timesheet VALUES ('001','20070108','Full day')
...

Notice that '20070107' is not included as it is public holiday. I'm not sure if there is such a function in F_TABLE_DATE. Please help.

Thanks.

... sql is fun...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-22 : 02:13:51

[code]SELECT DATE
FROM F_TABLE_DATE('20070101', '20070131') d
left JOIN @Holiday h
ON d.[DATE] = h.WDate
left JOIN @Timesheet t
ON d.[DATE] = t.WDate
WHERE h.WDate IS NULL
AND t.WDate IS NULL[/code]

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-22 : 02:15:19

if this is not what you expected, then please post the expected result

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

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-22 : 02:16:26

either one of these should work for you. Check the plan execution to see which suits you needs better.

Also it will be better performance wise if you create a permanent table from the function rather then using the function each time.

Also this query only considers m-f work days, remove that part of the query if you want to include weekends.


select * 
from
dbo.F_TABLE_DATE('01/01/2007','12/31/2007') a
	Left Join
 TimeSheet b
on a.date = b.Wdate
where 
 a.Day_Of_Week between 2 and 6
and b.Empcode is null
and not exists (select * from holiday aa where aa.wdate = a.Date)


--or

select * 
from
dbo.F_TABLE_DATE('01/01/2007','12/31/2007') a
where 
 a.Day_Of_Week between 2 and 6
and not exists (Select * from Timesheet bb where bb.wDate = a.Date)
and not exists (select * from holiday aa where aa.wdate = a.Date)

erwine
Yak Posting Veteran

69 Posts

Posted - 2007-11-22 : 02:51:57

Yes, both solutions are fine, but unfortunately TIMESHEET table has more than one employee.

CREATE TABLE Timesheet(EmpCode nvarchar(8), WDate date, Type nvarchar(2))

INSERT INTO Timesheet VALUES ('001','20070101','Full day')
INSERT INTO Timesheet VALUES ('001','20070102','Full day')
INSERT INTO Timesheet VALUES ('001','20070103','Full day')
INSERT INTO Timesheet VALUES ('001','20070104','Full day')
INSERT INTO Timesheet VALUES ('001','20070105','Half day')

INSERT INTO Timesheet VALUES ('002','20070101','Full day')
INSERT INTO Timesheet VALUES ('002','20070102','Full day')
INSERT INTO Timesheet VALUES ('002','20070103','Full day')
INSERT INTO Timesheet VALUES ('002','20070104','Full day')
INSERT INTO Timesheet VALUES ('002','20070105','Half day')
INSERT INTO Timesheet VALUES ('002','20070109','Half day')

And the solution is not going to work as it is per employee.

... sql is fun...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-22 : 02:57:45

quote:
And the solution is not going to work as it is per employee.

Because you never specified in your post !

SELECT	e.EmpCode , d.DATE
FROM	@Employee e
	CROSS JOIN F_TABLE_DATE('20070101', '20070131') d
	LEFT JOIN @Holiday h
	ON	d.[DATE]	= h.WDate
	LEFT JOIN @Timesheet t
	ON	e.EmpCode 	= t.EmpCode 
	AND	d.[DATE]	= t.WDate
WHERE	h.WDate	IS NULL
AND	t.WDate	IS NULL

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

erwine
Yak Posting Veteran

69 Posts

Posted - 2007-11-22 : 03:03:48

Lol..my apologies then..

hmm..very curious about this CROSS JOIN. I'll google it.
Thanks, man. you've been very helpful.

Cheers,
erwine

... sql is fun...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-22 : 03:10:42

google ? Just read the Books On Line

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

erwine
Yak Posting Veteran

69 Posts

Posted - 2007-11-22 : 03:24:00

Hmm..sorry to ask again.

I run the query for 3000+ employees and it takes very long time to complete. Is it expected? Or is there any better solution..opz..i would say faster solution to generate the same result?

Cheers,
erwine

... sql is fun...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-22 : 03:33:02

What is the date range you specified ? 1 month ?

For 1 month with 3000 employees, the cross join will result in 30 days x 3000 employees = 90000 records.

Do make sure you have proper index on the tables. Or you can try replace the F_TABLE_DATE with a permanent table.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources