finding missing dates - 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

General SQL Server Forums

New to SQL Server Programming

finding missing dates

Author

Topic

pnasz
Posting Yak Master

101 Posts

Posted - 2010-11-06 : 02:07:27

I have a table with the Timein and timeout record of all employees

empid entrydate timein timeout
1 1/1/2010 10 05

1 2/1/2010 10 05

i want to find the missing dates when emp has not entered timein timeout

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-11-06 : 05:33:14

you need to have table with dates and than query out all the missing dates; meaning to get all the dates out of your dates table that are not present in table with TimeIn|TimeOut_table_of_employees table.

pnasz
Posting Yak Master

101 Posts

Posted - 2010-11-06 : 08:43:51

can you give tell me how i write the query

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-06 : 09:28:25

Here is an example adapted from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=96787

-- We need test table
declare @tblInOut table(empid int, entrydate datetime, timein varchar(10), [timeout] varchar(10))

-- We need test data
Insert into @tblInOut 
select 1,'20100101','10','05' union all
select 1,'20100102','10','05'

-- We need variables for a date range to search in
Declare @startdate datetime
Declare @enddate datetime

-- Setting the date range to search in for missing dates
Set @startdate='20100101'
Set @enddate='20100131'

-- The solution (just replace @tblInOut by your real table name
Select dateadd(day,number,@startdate) as missingDate from master.dbo.spt_values 
where master.dbo.spt_values.type='p' and dateadd(day,number,@startdate)<=@enddate
and dateadd(day,number,@startdate) not in 
(select entrydate from @tblInOut)

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-06 : 09:28:55

Here is an example adapted from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=96787

-- We need test table
declare @tblInOut table(empid int, entrydate datetime, timein varchar(10), [timeout] varchar(10))

-- We need test data
Insert into @tblInOut 
select 1,'20100101','10','05' union all
select 1,'20100102','10','05'

-- We need variables for a date range to search in
Declare @startdate datetime
Declare @enddate datetime

-- Setting the date range to search in for missing dates
Set @startdate='20100101'
Set @enddate='20100131'

-- The solution (just replace @tblInOut by your real table name
Select dateadd(day,number,@startdate) as missingDate from master.dbo.spt_values 
where master.dbo.spt_values.type='p' and dateadd(day,number,@startdate)<=@enddate
and dateadd(day,number,@startdate) not in 
(select entrydate from @tblInOut)

No, you're never too old to Yak'n'Roll if you're too young to die.

pnasz
Posting Yak Master

101 Posts

Posted - 2010-11-07 : 02:05:57

thanx for ur help it working but what if there are different employee .
this is done for only empno 1 what about if i am having empno 2,3,4 etc

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-07 : 06:11:13

For a certain employee you change the last line
(select entrydate from @tblInOut)
to
(select entrydate from @tblInOut where empid = 2)
so you will get the missing dates for empid 2

No, you're never too old to Yak'n'Roll if you're too young to die.

pnasz
Posting Yak Master

101 Posts

Posted - 2010-11-07 : 06:16:10

thanx a lot

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-07 : 06:19:56

welcome