How to calculate number of dates based on shift pattern

[robi212]

Hi Guys,

Does anyone of you know how to do this calculation. I need to calculate absences at work based on start and end date provided, but the problem is that is has to go with the shift pattern.

For example: Employee on sick from 01/03/2012 until 10/03/2012 but his shift pattern fall just into 3/4/5/8/9 03/2012 so he was absent just for
5 days but not 10 as "DateDiff" will give us. Is it possible to exclude his off days?

Please see attachement Date Fnction.zip for example Database wtih all relevent information needed.



Quick solutions needed if possible.

Regards Rob

[jzwp11]

Welcome to the forum!

First, you use the word date as a field name, the word date is a reserved word in Access, so it best not to use it as a field name. This link has a list of reserved words. Also, the date fields in your absence table are text data types. Shouldn't they be date/field data types?

SQL interprets all dates as being in the mm/dd/yyyy format, no matter how you have the dates formatted.

To get to your question, you will need a nested query to count the number of dates in the shiftpattern table that fall within the date range of the absence dates. The query will look something like this. I've highlighted the subquery in red.

SELECT tbl_Absence.[Employee ID], tbl_Absence.[Shift Pattern], tbl_Absence.[Start Date], tbl_Absence.[End Date], (SELECT Count([shiftpatterndate]) from allshiftpatterndates WHERE allshiftpatterndates.shiftpattern=tbl_Absence.[Shift Pattern] and [shiftpatterndate] between tbl_Absence.[Start Date] and tbl_Absence.[End Date]) AS NoDaysOff, DateDiff("d",tbl_Absence.[Start Date],tbl_Absence.[End Date])+1 AS daysinperiod, daysinperiod-nodaysoff AS NetSickDays
FROM tbl_Absence;

[robi212]

Thanks mate for quick replay I'll give it a go tomorrow. In terms of you question "Shouldn't they be date/field data types". Yes of course, I was just trying different ways of getting to the point where I wan't to be. I've tought that it might be possible to change dates to values do the queries and then revert it back to data,

[jzwp11]

There are some functions to convert between datatypes (i.e. cdate()), but I would think that you would want to be consistent throughout the database.

[RiVit]

You could use the Dcont function into the query
I send you the solution, if you have any problem use this SQL sentence to replace the Sql of any Query
SELECT tbl_Absence.[Employee ID], tbl_Absence.[Shift Pattern], tbl_Absence.[Start Date], tbl_Absence.[End Date], DateDiff("d",[Start Date],[End Date])+1-DCount("[Date_Blue]","BlueShiftPattern","[BlueShiftPattern]![Date_Blue] between #" & [tbl_Absence]![Start Date] & "# and #" & [tbl_Absence]![End Date] & "#") AS Expr1, -DCount("[Date_Blue]","BlueShiftPattern","[BlueShiftPattern]![Date_Blue] between #" & [tbl_Absence]![Start Date] & "# and #" & [tbl_Absence]![End Date] & "#") AS Expr2
FROM tbl_Absence;

Notice than I rename the variable Date to Date_Blue

Here is the solution



If you have any problems with the dates, use format fuction
Format(date_variable,"mm/dd/aaaa",2)

[robi212]

I've just tryed it and it works but only for 6 days, after 7,8,9 etc days of absence it's giving me big negative values.
Forr example for date from 01/01/2012 to 17/01/2012 it's giving me -81 days of absence.
Waht about RedShift Pattern should I do a different querry?

Plus what does "highlighted" does "+1"

You could use the Dcont function into the query
I send you the solution, if you have any problem use this SQL sentence to replace the Sql of any Query
SELECT tbl_Absence.[Employee ID], tbl_Absence.[Shift Pattern], tbl_Absence.[Start Date], tbl_Absence.[End Date], DateDiff("d",[Start Date],[End Date])+1-DCount("[Date_Blue]","BlueShiftPattern","[BlueShiftPattern]![Date_Blue] between #" & [tbl_Absence]![Start Date] & "# and #" & [tbl_Absence]![End Date] & "#") AS Expr1, -DCount("[Date_Blue]","BlueShiftPattern","[BlueShiftPattern]![Date_Blue] between #" & [tbl_Absence]![Start Date] & "# and #" & [tbl_Absence]![End Date] & "#") AS Expr2
FROM tbl_Absence;

Notice than I rename the variable Date to Date_Blue

Here is the solution



If you have any problems with the dates, use format fuction
Format(date_variable,"mm/dd/aaaa",2)

[robi212]

Would you be able to do it in the example attached as I've tryied byt can't get my head around it. Possibly that I'm not too good in sql.

Thanks in advance.

[RiVit]

I've just tryed it and it works but only for 6 days, after 7,8,9 etc days of absence it's giving me big negative values.
Forr example for date from 01/01/2012 to 17/01/2012 it's giving me -81 days of absence.
Waht about RedShift Pattern should I do a different querry?

Plus what does "highlighted" does "+1"

Ok, you have many problems that you have to solve:
1) You only need two tables: BlueShiftPattern and tbl_Absence
2) All the dates need to be defined as Date/Hour and format short date (Sorry the images are in spanish version)

3) The format (Text) that you capture de dates: dd/mm/yyyy, let me asume that you aren´t in US, I have the same problem, because ACCESS use de format mm/dd/yyyy, in this case, we have to use a fuction FORMAT, to change the date from dd/mm/yyyy to mm/dd/yyyy in the DCONT fuction.

With this changes, the solution is Ok.
Sorry I don't know what is the working day, the blue or the red.

first try to open and execute the solution that I send to you, if you have any problem, continue with the next option
Date Fnction New.zip

Once again open your query, change to view in sql, select al the text, remove it, y copy the next one
SELECT tbl_Absence.[Employee ID], tbl_Absence.[Shift Pattern], tbl_Absence.[Start Date], tbl_Absence.[End Date], DateDiff("d",[Start Date],[End Date])+1-DCount("[Date_Blue]","BlueShiftPattern","[BlueShiftPattern]![Date_Blue] between #" & Format([tbl_Absence]![Start Date],"mm/dd/yyyy",2) & "# and #" & Format([tbl_Absence]![End Date],"mm/dd/yyyy",2) & "#") AS Absents, -DCount("[Date_Blue]","BlueShiftPattern","[BlueShiftPattern]![Date_Blue] between #" & [tbl_Absence]![Start Date] & "# and #" & [tbl_Absence]![End Date] & "#") AS Blue_count
FROM tbl_Absence;

[robi212]

Ok, you have many problems that you have to solve:
1) You only need two tables: BlueShiftPattern and tbl_Absence
2) All the dates need to be defined as Date/Hour and format short date (Sorry the images are in spanish version)

3) The format (Text) that you capture de dates: dd/mm/yyyy, let me asume that you aren´t in US, I have the same problem, because ACCESS use de format mm/dd/yyyy, in this case, we have to use a fuction FORMAT, to change the date from dd/mm/yyyy to mm/dd/yyyy in the DCONT fuction.

With this changes, the solution is Ok.
Sorry I don't know what is the working day, the blue or the red.

first try to open and execute the solution that I send to you, if you have any problem, continue with the next option
Date Fnction New.zip

Once again open your query, change to view in sql, select al the text, remove it, y copy the next one
SELECT tbl_Absence.[Employee ID], tbl_Absence.[Shift Pattern], tbl_Absence.[Start Date], tbl_Absence.[End Date], DateDiff("d",[Start Date],[End Date])+1-DCount("[Date_Blue]","BlueShiftPattern","[BlueShiftPattern]![Date_Blue] between #" & Format([tbl_Absence]![Start Date],"mm/dd/yyyy",2) & "# and #" & Format([tbl_Absence]![End Date],"mm/dd/yyyy",2) & "#") AS Absents, -DCount("[Date_Blue]","BlueShiftPattern","[BlueShiftPattern]![Date_Blue] between #" & [tbl_Absence]![Start Date] & "# and #" & [tbl_Absence]![End Date] & "#") AS Blue_count
FROM tbl_Absence;

Many Thanks Rivit for the help. It was all about data format.

Kind Regards
Rob