Generate Dates not in table
-
Hi
My really problem
I work on program that calculate Employee's attendance
and I have
table have (Employee's name, In time, Out time)
that i just record the attendance's day
and table with (Employee's name, Vacation's Day)
so i want to get a report that have
(Date, state of day, Employee's name)
between two dates
Note: state of day must be (Attendance, Vacation)
Thanks
-
declare @attendance table
(
employee_id char(3),
in_time datetime not null,
out_time datetime not null,
count_to_date as convert(datetime,convert(varchar(12),in_time,101)),
hours_worked as datediff(minute,in_time,out_time)
)
declare @holidays table
(
holiday_date datetime not null,
descriptor varchar(50),
primary key(holiday_date,descriptor)
)
insert @holidays
select '12/25/2006','Christmas'
insert @attendance(employee_id,in_time,out_time)
select '111','2006-03-25 09:11:07.077','2006-03-25 10:11:07.077'
UNION
select '111','2006-12-25 23:11:07.077','2006-12-26 06:37:07.077'
select a.employee_id,
case when h.descriptor is null
then 'Attendance'
else 'Vacation' end as State,
count_to_date,
hours_worked/60.00 hours
from @attendance a
left outer join @holidays h
on a.count_to_date = h.holiday_date
Mathew J Kulangara
sqladventures.blogspot.com -
OK, follow-up question here, that relates to something I've been doing.
As I read this code, it will end up listing the dates for which the employee has a record, and whether that date was 'Attendance' or 'Vacation.'
So, how can I list ALL dates within a month, and whether the employee was in 'Attendance,' 'Vacation,' or 'Unscheduled,' i.e., for which there is no record found? It seems to me like there ought to be an easy way to do this. But, the only ways I've some up with are to either create a table simply listing the dates of the month, or to create a loop to go through the days and test each one.
In my case, I'm attempting to generate uptime reports from a ticketing system, and I need to be able to indicate how many minutes we had a critical problem, a non-critical problem, or no problem. Tickets overlap, so I can't simply sum the outage times. And, theoretically, there should be lots of minutes during which there is no problem. But, other than a very clunky loop to test each minute, I can't come up with a solid solution.
-
So, how can I list ALL dates within a month... DECLARE @start datetime, @end datetime, @days int
SET @start = '20060301'
SET @end = '20060331'
SET @days = DATEDIFF(day,@start,@end)
SELECT e.[id],d.[Date],
CASE
WHEN a.[id] IS NOT NULL THEN 'Attendance'
WHEN v.[id] IS NOT NULL THEN 'Vacation'
ELSE 'Unscheduled'
END
FROM [Employee] e
CROSS JOIN (SELECT n.number FROM [Numbers] n n.number BETWEEN 0 AND @days) d
LEFT OUTER JOIN [Attendance] a ON a.[id] = e.[id] AND a.[Date] = d.[Date]
LEFT OUTER JOIN [Vacation] v ON v.[id] = e.[id] AND v.[Date] = d.[Date]
In my case, I'm attempting to generate uptime DECLARE @start datetime, @end datetime, @minutes int
SET @start = '20060301'
SET @end = '20060331'
SET @minutes = select DATEDIFF(minute,@start,@end+1)
SELECT [ProblemType], COUNT(*)
FROM (SELECT DISTINCT t.[ProblemType], D.[Date]
FROM [Tickets] t
INNER JOIN (SELECT DATEADD(minute,n.number,@start) AS [Date]
FROM [Numbers] n WHERE n.number BETWEEN 0 AND @minutes) d
ON d.[Date] >= t.[Start] AND d.[Date] <= t.[End]) c
GROUP BY [ProblemType]
ORDER BY [ProblemType]
Both queries use a generic numbers table
Far away is close at hand in the images of elsewhere.
Anon. -
or
DECLARE @start datetime, @end datetime, @minutes int
SET @start = '20060301'
SET @end = '20060331'
SET @minutes = select DATEDIFF(minute,@start,@end+1)
SELECT [ProblemType], COUNT(*)
FROM (SELECT DISTINCT COALESCE(t.[ProblemType],'No Problem') AS [ProblemType], D.[Date]
FROM (SELECT DATEADD(minute,n.number,@start) AS [Date]
FROM [Numbers] n WHERE n.number BETWEEN 0 AND @minutes) d
LEFT OUTER JOIN [Tickets] t
ON d.[Date] >= t.[Start] AND d.[Date] <= t.[End]) c
GROUP BY [ProblemType]
ORDER BY [ProblemType]
Far away is close at hand in the images of elsewhere.
Anon. -
Both queries use a generic numbers table
Could you clarify this? Is this just a single-column table with a list of the positive integers?
-
Yes and needs to contain sufficient numbers for the range required
e.g. 0 to 30 for max days on 1 month
or 0 to 44639 for max minutes in a month
etc.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply