December 24, 2008 at 1:14 am
I have thousands data which is include EmployeeID, Start and Finish. I just want to get FREE days whole my check dates.
Declare @myTable Table
(
IDint identity(1,1),
IDEmpint,
StartDateTime,
FinishDateTime
)
insert @myTable (IDEmp, Start, Finish) Values (1, '2009-01-01 00:00', '2009-01-01 09:00')
insert @myTable (IDEmp, Start, Finish) Values (1, '2009-01-01 10:00', '2009-01-02 01:00')
insert @myTable (IDEmp, Start, Finish) Values (1, '2009-01-20 23:00', '2009-01-24 01:00')
insert @myTable (IDEmp, Start, Finish) Values (1, '2009-01-10 00:00', '2009-01-11 23:59')
insert @myTable (IDEmp, Start, Finish) Values (2, '2009-01-01 12:00', '2009-01-01 18:00')
insert @myTable (IDEmp, Start, Finish) Values (2, '2009-01-01 23:00', '2009-01-02 01:00')
--There are busy days, How can I get FREE days
Select IDEmp, Start, Finish From @myTable where Start >= '2009-01-01' and Finish <= '2009-01-30'
Thanks.
December 24, 2008 at 2:05 am
what do you mean by free days.
"Keep Trying"
December 24, 2008 at 3:55 am
That s mean there is no activity dates. For example if we talk about IDEmp = 1
How can I get these result
2009-01-01 Busy
2009-01-02 Busy
2009-01-03 Free
2009-01-04 Free
...
2009-01-09 Free
2009-01-10 Busy
2009-01-11 Busy
2009-01-12 Free
...
2009-01-19 Free
2009-01-20 Busy
2009-01-24 Busy
2009-01-25 Free
...
2009-01-31 Free
December 24, 2008 at 6:03 am
Hi
Well iam going off now and will be on leave for a couple of days so cant go in detail.
The best way to do this is to use the tally table method.
go thru this link .
http://www.sqlservercentral.com/articles/TSQL/62867/
Build the required dates with this and then check on which on dates employee has worked or not.
Sorry cant help more...
"Keep Trying"
December 24, 2008 at 7:18 am
I think this does want you want:
[font="Courier New"]DECLARE @myTable TABLE
(
ID INT IDENTITY(1,1),
IDEmp INT,
Start DATETIME,
Finish DATETIME
)
INSERT @myTable (IDEmp, Start, Finish) VALUES (1, '2009-01-01 00:00', '2009-01-01 09:00')
INSERT @myTable (IDEmp, Start, Finish) VALUES (1, '2009-01-01 10:00', '2009-01-02 01:00')
INSERT @myTable (IDEmp, Start, Finish) VALUES (1, '2009-01-20 23:00', '2009-01-24 01:00')
INSERT @myTable (IDEmp, Start, Finish) VALUES (1, '2009-01-10 00:00', '2009-01-11 23:59')
INSERT @myTable (IDEmp, Start, Finish) VALUES (2, '2009-01-01 12:00', '2009-01-01 18:00')
INSERT @myTable (IDEmp, Start, Finish) VALUES (2, '2009-01-01 23:00', '2009-01-02 01:00')
-- build numbers table
SELECT TOP 500
IDENTITY(INT, 0,1) AS n
INTO #nums
FROM
sys.all_objects A,
sys.all_objects
/*distinct eliminates dupes where there are multiple
appointments in a single day*/
SELECT DISTINCT
dates.IDEmp,
dates.start_date AS date,
CASE
WHEN start IS NULL THEN 'free'
ELSE 'busy'
END AS status
FROM
/* derived table returns all dates in date range
for each employee */
(
SELECT
IDEmp,
DATEADD(DAY, n,MIN(Start)) AS start_date
FROM
#Nums,
@mytable
GROUP BY
n,
IDEmp
) AS dates LEFT JOIN
@myTable A ON
/* this join matches on any day, since hours are included you need to
remove them */
(dates.start_date = DATEADD(hour, -DATEPART(hour, A.start), A.start) OR
dates.start_date = DATEADD(hour, -DATEPART(hour, A.finish), A.finish))
WHERE
dates.start_date >= '2009-01-01' AND dates.start_date <= '2009-01-30'
DROP TABLE #nums[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply