June 19, 2014 at 1:25 am
Hi,
We have an application management planning in which we must manage:
First
- Daily attendance of resources
- For each day ranges not present resources
Then the absence of resources (day, start time, end time)
and other things ...
It should then count the hours of attendance theoretical resources daily and recorded hours (theoretical - absences)
Currently we have calculations that work but take time: 15mn but it will take 1 hour at the end of the year ...
the structure of the database
to calculate the hours of attendance we have to subtract for each day the beaches of downtime -> theoretical hours
We must verify that the absences are not recorded on beaches of unavailability deduct only the actual minutes absences
ARNAUD is working the 06/19/2014
He can't work on
00-8:00
12:00 p.m. to 1:00 p.m.
10:00 p.m. to 0:00
He's absent from 11:45 a.m. to 1:15 p.m. on 19/06/2014
Time theoretical presence = 13h
Time = real presence-13h 30min = 12:30
Table Present_Day_Resource (Id_resource int not null, present_day smalldatetime not null) contains 500.000
Table Resource_No_Present (Id_resource int not null, present_day smalldatetime not null, start_minute int not null, end_minute int not null) contains 1.500.000 lines
Table Resource_absence (Id_resource int not null, absence_day smalldatetime not null, start_minute int not null, end_minute int not null) contains 200.000 lines
Actually the best's SQL time is to have procedure to calculate for one day for one resource and do it for the 500.000 day !
We tried to work with sets but it degrades processing times
Have any ideas ?
Arno
June 19, 2014 at 1:44 am
can you please supply table create / sample data insert scripts and the expected results for the sample data.
pls try and cover all eventualities .....eg do some people work overnight
thanks
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 19, 2014 at 2:39 am
this script contains one example
Regards
Arno
June 19, 2014 at 4:08 am
a.guillaume (6/19/2014)
this script contains one exampleRegards
Arno
thanks for the script...can you please confirm what results you are expecting from this data...just take a single day as an example
thanks
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 19, 2014 at 6:24 am
Day NbMinTheo Théo minus absence
2013-10-01 450 450
2013-10-02 450 450
2013-10-03 450 450
2013-10-04 450 450
2013-10-14 450 0
2013-10-15 450 0
2013-10-16 450 0
2013-10-17 450 0
2013-10-18 450 0
2013-11-12 450 360
2013-11-13 450 420
2013-11-14 450 0
2013-11-15 450 0
2013-12-09 450 405
2013-12-10 450 435
2013-12-11 450 435
Regards
June 20, 2014 at 2:46 am
a.guillaume (6/19/2014)
Day NbMinTheo Théo minus absence2013-12-09 450 405
Hi ...can you explain how you get the result for 2013-12-09?....should it be 450/390?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 20, 2014 at 4:10 am
hi,
the resource was not present for this '09/12/2013'
0 to 495
720 to 780
1005 to 1440
so, he has 1440- (495+60+435) = 450
He's absent during the time 510 to 555
so he's really present
0 to 495
510 to 555
720 to 780
1005 to 1440
1440 - (495+60+435+45) = 405
--------------
An another case to :
the resource was not present for
0 to 500
700 to 800
1040 to 1440
he has 1440- (500+100+400) = 440 theorical
He's absent during the time 600 to 900
so he's not present for
0 to 500
600 to 900
1040 to 1440
he's really present 240 mn for this day
Regards
June 21, 2014 at 10:02 am
can we assume that if [Resource_ABSENCE].[Id_Raison_absence] = 1 then the user is absent all day and therefore .... Théo minus absence = 0
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 21, 2014 at 12:46 pm
Currently we have calculations that work but take time: 15mn but it will take 1 hour at the end of the year ...
had a longer look at this...but cant resolve the logic entirely....perhaps if you can post what you are currently doing then things may appear a little clearer 🙂
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 21, 2014 at 2:35 pm
a.guillaume (6/19/2014)
ARNAUD is working the 06/19/2014He can't work on
00-8:00
12:00 p.m. to 1:00 p.m.
10:00 p.m. to 0:00
He's absent from 11:45 a.m. to 1:15 p.m. on 19/06/2014
Heh... I'd hate to work for you folks. You cut out and extra half hour of time present in that example.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 21, 2014 at 2:56 pm
Jeff Moden (6/21/2014)
a.guillaume (6/19/2014)
ARNAUD is working the 06/19/2014He can't work on
00-8:00
12:00 p.m. to 1:00 p.m.
10:00 p.m. to 0:00
He's absent from 11:45 a.m. to 1:15 p.m. on 19/06/2014
Heh... I'd hate to work for you folks. You cut out and extra half hour of time present in that example.
Jeff...
I think what OP wants is:
ARNAUD should be approved absent from 12:00 p.m. to 1:00 p.m. (lunch?)
but actually absent from 11:45 a.m. to 1:15 p.m.....
so therefore he was AWOL(!) 11:45am to 12:00 pm and also from 1:00pm to 1:15pm.....2 * 15mins = 30 mins
does that make sense?????
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 21, 2014 at 3:54 pm
J Livingston SQL (6/21/2014)
Jeff Moden (6/21/2014)
a.guillaume (6/19/2014)
ARNAUD is working the 06/19/2014He can't work on
00-8:00
12:00 p.m. to 1:00 p.m.
10:00 p.m. to 0:00
He's absent from 11:45 a.m. to 1:15 p.m. on 19/06/2014
Heh... I'd hate to work for you folks. You cut out and extra half hour of time present in that example.
Jeff...
I think what OP wants is:
ARNAUD should be approved absent from 12:00 p.m. to 1:00 p.m. (lunch?)
but actually absent from 11:45 a.m. to 1:15 p.m.....
so therefore he was AWOL(!) 11:45am to 12:00 pm and also from 1:00pm to 1:15pm.....2 * 15mins = 30 mins
does that make sense?????
What you said makes sense. What the OP wrote left more than one possibility open. Thanks for the clarification.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 22, 2014 at 11:56 pm
Hi,
ARNAUD should be approved absent from 12:00 p.m. to 1:00 p.m. (lunch?)
but actually absent from 11:45 a.m. to 1:15 p.m.....
so therefore he was AWOL(!) 11:45am to 12:00 pm and also from 1:00pm to 1:15pm.....2 * 15mins = 30 mins
that's exactly the sense
The ID_Raison_absence is a link for a table with the type of absense to do statistics
Regards
June 23, 2014 at 10:57 am
a.guillaume (6/22/2014)
Hi,ARNAUD should be approved absent from 12:00 p.m. to 1:00 p.m. (lunch?)
but actually absent from 11:45 a.m. to 1:15 p.m.....
so therefore he was AWOL(!) 11:45am to 12:00 pm and also from 1:00pm to 1:15pm.....2 * 15mins = 30 mins
that's exactly the sense
The ID_Raison_absence is a link for a table with the type of absense to do statistics
Regards
ok...think I understand now
can you please share what you are currently running...
Currently we have calculations that work but take time: 15mn but it will take 1 hour at the end of the year ...
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 23, 2014 at 11:47 pm
hi,
every day, the statistics are calculated
- Tables of statistics are truncated
- We calculated for the entire base hours of attendance, hours of absences, financial allocations are made on these days ....
To calculate the hours and lack of presence it takes 15 minutes
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply