SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 finding missing dates
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pnasz
Yak Posting Veteran

97 Posts

Posted - 11/06/2010 :  02:07:27  Show Profile  Reply with Quote
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

Switzerland
746 Posts

Posted - 11/06/2010 :  05:33:14  Show Profile  Reply with Quote
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.
Go to Top of Page

pnasz
Yak Posting Veteran

97 Posts

Posted - 11/06/2010 :  08:43:51  Show Profile  Reply with Quote
can you give tell me how i write the query
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 11/06/2010 :  09:28:25  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 11/06/2010 :  09:28:55  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

pnasz
Yak Posting Veteran

97 Posts

Posted - 11/07/2010 :  02:05:57  Show Profile  Reply with Quote
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
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 11/07/2010 :  06:11:13  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

pnasz
Yak Posting Veteran

97 Posts

Posted - 11/07/2010 :  06:16:10  Show Profile  Reply with Quote
thanx a lot
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 11/07/2010 :  06:19:56  Show Profile  Visit webfred's Homepage  Reply with Quote
welcome


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000