Indexing Joined Columns
-
Can you post the table definitions of the tables that hold the information about the work scheduled and the work actually performed? And maybe some test data. I got a strange feeling there should be a better way to deal with this problem.
Regarding the usage of the temp table vs 'physical' tables. There is not much of a difference since the temp table is going to be created as a physical table in the tempdb anyway and the server is going to do the cleaning for you.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font] -
OK, time and attendance matching.
Done it once, and it did not involve any temp tables at all.
You're trying to use procedural language approaches instead of set-based T-SQL way.
You don't need to break records from original tables at all.
You just need to match them and record the result.
If you want you may have separate table containing a record for every minute for a week, but it must be static table to be matched with both scheduled and planned events.
_____________
Code for TallyGenerator -
Pop, I agree with Serqiy... I don't think you need to break the source information into "by the minute" rows. If you could post some T/A rows for a couple of employees and their "schedule" rows, it would be a big help in determining a solution for you...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hey, any new techniques you could share would be awesome. You actually helped me come up with the one I'm using on another thread. Anyway, here goes. For each day, an employee could have one or more shifts they were scheduled and one or more shifts they actually worked.
They could have been scheduled from 1:00-2:00 and worked from 1:00-2:00. This is 60 minutes 'WorkScheduled'.
They could have been Scheduled 1:15-2:00 and Worked 1:03-2:05. This would be 17 minutes they had WorkNotScheduled (the time outside of their Scheduled time 1:03-1:15 and 2:00-2:05) and 45 minutes WorkScheduled (1:15-2:00).
They could also have been Scheduled from 1:00-2:00 and Worked from 1:32-2:00. This would be 32 minutes 'ScheduledNotWorked' (1:00-1:32) and 28 Minutes 'WorkScheduled' (1:32-2:00).
To add to this there can be days where they have all schedules and no work actually punched in and all punch data and no corresponding schedule data. Any other combinations could be true. For example, they were scheduled 1:00-4:00, 5:00-7:00, and 11:00-12:00. They actually worked 8:15-12:00. So the 3 conditions 'WorkScheduled', 'WorkNotScheduled', and 'ScheduledNotWorked' would have to be calculated for that day. This would be 'WorkScheduled'=60 (11:00-12:00), WorkNotScheduled=165 minutes (8:15-11:00), ScheduledNotWorked=300 (1:00-4:00 and 5:00-7:00).
The script I'm using is in this thread and I can repost if anybody would like. The data is in the form:
Schedules (PersonNum varchar(15), ShiftStartDate DATETIME, ShiftEndDate DATETIME)
TimePunch (PersonNum varchar(15), PunchInDate DATETIME, PunchOutDate DATETIME)
Any Questions just let me know. I'm fascinated by people's problem solving approaches. Anything new I can learn is great!
-
Thanks, Pop... I'll see what I can do... bet lot's of other folks jump on this one, as well. It's an interesting and classic problem...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Yes, I'm sure many others have encountered similar data issues as well. I'd be very interested in their methodology to solve this issue. Appreciate how helpful you've been. Thanks.
-Pat
-
SELECT ISNULL(S.PersonNum, T.PersonNum),
COUNT(S.ID - T.ID) as WorkedOnSchedule, -- if any one is NULL row will not be counted
COUNT S.ID as Scheduled,
COUNT T.ID as Worked
FROM dbo.AllMinutesInSchedule A -- table containing all minutes which can be used in schedule
LEFT JOIN dbo.Schedules S ON S.ShiftStartDate A.MinuteStart
LEFT JOIN dbo.TimePunch T ON S.PersonNum = T.PersonNum AND T.PunchInDate A.MinuteStart
WHERE S.PersonNum IS NOT NULL OR T.PersonNum IS NOT NULL
I believe you can get everything you want from here.
_____________
Code for TallyGenerator -
I am assuming you will be looking for this information based on a date range and this date range might be much smaller then the entire span off dates you have to keep in the AllMinutesInSchedule table - a year worth of minutes is over half a milion records.
In this case you should preload the records from the AllMinutesInSchedule into a temp table and use the temp table for the select instead. And you may want to created a clustered index on it to let the SQL Server know that the data is sorted.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font] -
All minutes in a schedule is every minute for every day of the year? not just per day?
-
Serqiy, just making sure I'm following correctly, when you say S.ID, T.ID...where would these values come from? From Schedule and TimePunch data or elsewhere? I'm really interested in your solution. Thanks.
-
Well, if you shifts and worked hours do not span days and you pull this info for a single day only then you don't need the minutes for each day of the year (or the life span of application) your but you will have to change Sergiy's code to deal with the date only portion separately and time portion separately.
The way I read his code the ...Minutes... table holds each minute for each date you dealing with.
And regarding the question you posted for Sergyi: yes, the S.ID and T.ID are coming from the Schedule and TimePunch tables
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font] -
well the time punch and schedule data is broken into separate days, so the days don't overlap. I can probably just use the 1440 i minute bucket table I have. There data in the tables is:
Schedules (personnum(15), ShiftStartDate DATETIME, ShiftEndDate DATETIME)
TimePunch (personNum varchar(15), PunnchInDate DATETIME, PunchOutDate DATETIME)
That's why I'm wondering what he's using as S.ID, T.ID?
-
Yes, you are right that code does not look right, which is surprising for Sergyi's code.
The ID should be coming from the Minutes tables not from the Schedule or TimePunch tables.
I think this code needs to be rewritten and you may have to UNION 3 separate statements to get the time worked on schedule, worked outside the schedule and scheduled not worked.
I am on my way out of the office right now so I don't have time to look more carefully into this. What could help is some insert statements with sample data so I can test any code I come up with.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font] -
Spent some time at lunch working on this. What I have works, sort of. There are 2 things that can occur that create anomolies in my code that I didn't have time to work out. These anomolies occur when there are 2 or more TimePunch records that satisfy a single Schedule record or there are 2 or more Schedule records that satisfy a single TimePunch record. Here is my code for those who wish to play with it:
create table dbo.Schedules (
PersonNum varchar(15),
ShiftStartDate datetime,
ShiftEndDate datetime
)
go
create table dbo.TimePunch (
PersonNum varchar(15),
PunchInDate datetime,
PunchOutDate datetime
)
go
insert into dbo.Schedules values('1', '2007-06-20 01:00', '2007-06-20 04:00')
insert into dbo.Schedules values('1', '2007-06-20 05:00', '2007-06-20 07:00')
insert into dbo.Schedules values('1', '2007-06-20 11:00', '2007-06-20 12:00')
insert into dbo.Schedules values('2', '2007-06-20 04:00', '2007-06-20 08:00')
insert into dbo.Schedules values('3', '2007-06-20 01:00', '2007-06-20 04:00')
insert into dbo.Schedules values('4', '2007-06-20 01:00', '2007-06-20 04:00')
insert into dbo.Schedules values('5', '2007-06-20 01:00', '2007-06-20 04:00')
insert into dbo.Schedules values('6', '2007-06-20 08:00', '2007-06-20 12:00')
--insert into dbo.Schedules values('7', '2007-06-20 04:00', '2007-06-20 12:00')
--insert into dbo.Schedules values('8', '2007-06-20 02:00', '2007-06-20 04:00')
--insert into dbo.Schedules values('8', '2007-06-20 08:00', '2007-06-20 12:00')
go
insert into dbo.TimePunch values('1', '2007-06-20 08:15', '2007-06-20 12:00')
insert into dbo.TimePunch values('1', '2007-06-20 13:00', '2007-06-20 15:00')
insert into dbo.TimePunch values('2', '2007-06-20 03:00', '2007-06-20 09:00')
insert into dbo.TimePunch values('3', '2007-06-20 01:15', '2007-06-20 04:00')
insert into dbo.TimePunch values('4', '2007-06-20 01:00', '2007-06-20 03:30')
insert into dbo.TimePunch values('5', '2007-06-20 01:00', '2007-06-20 04:00')
insert into dbo.TimePunch values('6', '2007-06-20 13:00', '2007-06-20 17:00')
--insert into dbo.TimePunch values('7', '2007-06-20 05:00', '2007-06-20 08:00')
--insert into dbo.TimePunch values('7', '2007-06-20 09:00', '2007-06-20 11:00')
--insert into dbo.TimePunch values('8', '2007-06-20 03:00', '2007-06-20 11:00')
go
--select * from dbo.Schedules
--select * from dbo.TimePunch
--go
select
coalesce(Schedules.PersonNum, TimePunch.PersonNum) as PersonNum,
Schedules.ShiftStartDate,
Schedules.ShiftEndDate,
TimePunch.PunchInDate,
TimePunch.PunchOutDate,
case when Schedules.ShiftStartDate <= coalesce(TimePunch.PunchInDate, Schedules.ShiftStartDate)
then datediff(mi,Schedules.ShiftStartDate, coalesce(TimePunch.PunchInDate,Schedules.ShiftEndDate)) +
datediff(mi,case when TimePunch.PunchOutDate > Schedules.ShiftEndDate then Schedules.ShiftEndDate else coalesce(TimePunch.PunchOutDate,Schedules.ShiftEndDate) end, Schedules.ShiftEndDate)
else 0 end ScheduledNotWorked,
case when (TimePunch.PunchInDate between Schedules.ShiftStartDate and Schedules.ShiftEndDate or
TimePunch.PunchInDate < coalesce(Schedules.ShiftStartDate, TimePunch.PunchInDate))
then datediff(mi, case when TimePunch.PunchInDate < Schedules.ShiftStartDate then Schedules.ShiftStartDate else TimePunch.PunchInDate end,
case when TimePunch.PunchOutDate > Schedules.ShiftEndDate then Schedules.ShiftEndDate else TimePunch.PunchOutDate end)
else 0 end WorkScheduled,
case when TimePunch.PunchInDate <= coalesce(Schedules.ShiftStartDate, TimePunch.PunchInDate) or
TimePunch.PunchOutDate >= coalesce(Schedules.ShiftEndDate, TimePunch.PunchOutDate)
then datediff(mi, case when TimePunch.PunchInDate > coalesce(Schedules.ShiftStartDate, TimePunch.PunchInDate) then Schedules.ShiftStartDate else TimePunch.PunchInDate end, coalesce(Schedules.ShiftStartDate, TimePunch.PunchOutDate)) +
datediff(mi, coalesce(Schedules.ShiftEndDate, TimePunch.PunchOutDate), case when TimePunch.PunchOutDate < coalesce(Schedules.ShiftEndDate, TimePunch.PunchOutDate) then coalesce(Schedules.ShiftEndDate, TimePunch.PunchOutDate) else TimePunch.PunchOutDate end)
else 0 end WorkNotScheduled
from
dbo.Schedules
full outer join dbo.TimePunch
on (Schedules.PersonNum = TimePunch.PersonNum and
((TimePunch.PunchInDate between Schedules.ShiftStartDate and Schedules.ShiftEndDate or
TimePunch.PunchOutDate between Schedules.ShiftStartDate and Schedules.ShiftEndDate) or
(Schedules.ShiftStartDate between TimePunch.PunchInDate and TimePunch.PunchOutDate or
Schedules.ShiftEndDate between TimePunch.PunchInDate and TimePunch.PunchOutDate)))
order by
coalesce(Schedules.PersonNum, TimePunch.PersonNum)
go
drop table dbo.Schedules
drop table dbo.TimePunch
go
-
Every minute in a period you are working with.
If you match daily T&A reports - it's for a day.
If weekly - for a week.
And "ID" means any not nullable column in the table.
Ideally some IDENTITY column, but it does not really matter. Point is this column must be NOT NULL.
_____________
Code for TallyGenerator
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply