Hi,
I have a problem with working out some dates.
I have a query that has a start date field and a number of days field.
I know i can create another field that could provide the return date (DATEADD function) by adding the number of days to the start date.
However the problem I have is that i need to discount the weekends from the return date.
For example if the start date was a wednesday and the number of days was 3 the datadd sum would give a return date of saturday when in reality it should be monday.
I am not sure if i am making sense but if anyone out there has any ideas it would be more than welcome.
Andrew
What is generally being referred to here is CALENDAR days and WORKING days
The .Net system revolves around calendars, which do not support the notion of working days.
You have to also check if holiday days of any sort have an impact (Bank holidays, Christmas etc). If this is the case, thse change from year to year, so you might need to keep a database of these special days
Assuming that you only want weekdays to be part of the calculation, then creating your own class might be the answer. You cannot inherit from DateTime, which is sealed.
Here is an (UNTESTED) example of what you might do - it isn't the only solution you might consider. however, use it as a starting point to get you thinking
publicclassWeekdayDateTime
{
staticTimeSpan DateDiff(DateTime startDate,DateTime endDate)
{
// move start to MOnday if weekend
if (startDate.DayOfWeek ==DayOfWeek.Saturday)
startDate.AddDays(2);
elseif (startDate.DayOfWeek ==DayOfWeek.Sunday)
startDate.AddDays(1);
// move end date to Friday if weekend
if (endDate.DayOfWeek ==DayOfWeek.Sunday)
endDate.AddDays(-2);
elseif (endDate.DayOfWeek ==DayOfWeek.Saturday)
endDate.AddDays(-1);
// get duration including rest of weekends
TimeSpan ts = endDate.Subtract(startDate);
//if less than a week (including mmoving weekends in initial process, then return diff
if (startDate.AddDays(5) <= endDate)
return ts;
// must be one or more weekends to remove
// complete weeks to consider
int completeWeeks = ts.Days / 7;
// 2 week end days for every weekday
TimeSpan weekendTime =newTimeSpan(completeWeeks * 2, 0, 0, 0, 0);
// remove weekends
return ts.Subtract(weekendTime);
}
}
|||
Thanks for that sbyard,
Though what you have said is of great interest, i maybe should clarify a bit more on what i am actually trying to do.
I am trying to use the DATEADD function in an sql query, all my data is stored in a table for holidays. It has the start date of the holiday and the number of days.
My ultimate goal is to provide a datagrid webpage that shows people who are currently on holiday.
For example if someone takes the 10th of September of for 4 days and i run the page on the 11th or 12th they will be included in the list.
My logic at the moment is if i can get the query itself to show the start date and the return date with DATEADD i can get the webpage to filter the datagrid based on those two dates.
I may be going down the wrong road for this but at the moment it is the best idea i have got.
Of course any suggestions are openly accepted.
Andrew
|||
Working type calendars are always a lot of work as there is a lot to consider.
Let’s consider data volume against speed and ease of use.
If you record every day for every user in a table and whether they are working (user id, date, dayTypeId), then each user has 365 rows per year (366 leap year). Each user is created with the public holidays already set, and it is easy to add/remove holiday days by setting the dayTypeId field. This links to a table where the Id is described as (e.g.) 1=working day, 2=public holiday, 3=annual leave, 4 = TOIL, etc.
This sounds like a lot of data, but you can archive old data off after <n> years, and records are only 12 bytes each – about 4k per person per year – hardly taxing to the system - and you can easily index all the fields to quickly select data how you want.
The above method is easy to query and you not only get the data you want, but the type of holiday and who did exactly what in one place
If you store holiday days only, you use a lot less data volume, but you simply move the work into processing complexity – as you are finding! So ease of use is reduced – especially when someone asks you to extend the system with different types of day off, which the first model is easy to change.
I suggest starting with my initial option above, and if you can shoot it down with another method, then go that way. I would suggest simplicity every time.
|||
SELECTDATEADD(d,CASEDATEPART(dw,StartDate)WHEN 7THEN 2WHEN 1THEN 1ELSE 0END,StartDate)+(DATEPART(dw,DATEADD(d,CASEDATEPART(dw,StartDate)WHEN 7THEN 2WHEN 1THEN 1ELSE 0END,StartDate))-2+Duration)%5+((DATEPART(dw,DATEADD(d,CASEDATEPART(dw,StartDate)WHEN 7THEN 2WHEN 1THEN 1ELSE 0END,StartDate))-2+Duration)/5)*7-(DATEPART(dw,DATEADD(d,CASEDATEPART(dw,StartDate)WHEN 7THEN 2WHEN 1THEN 1ELSE 0END,StartDate))-2) As ReturnDate
FROM {YourTable}
of course that assumes your fields are named StartDate (datetime) and Duration (int).
|||
That is the point sbyard is trying to make but the user did not understand it can get both complicated and convoluted. The link below is for the US but I am sure there maybe a UK version or sbyard could have one handy.
http://www.smart.net/~mmontes/ushols.html
http://www.timeanddate.com/worldclock/full.html
|||Of course, what I gave does not account for Holdays, and your logic is going to start to get really complicated if your have multiple holidays that are consecutive, or the duration does not count holidays that may occur during this "Holiday". In which case, it'll get many times more complicated. What I gave is fairly crude. It's what DATEADD(dw,Duration,StartDate) would return *IF* it actually added weekdays (Although reading the help it would seem to indicate it would, it doesn't unfortunately -- What is referred to as "weekday" really should have been called "DayOfWeek" which doesn't make a lot of sense for dateadd).|||
With my simple record-per-day method, you can also have holiday types that are full day, half day, etc, if you do not want to record hours worked.
However, I suggest you might want to add a small decimal field xx.xx to allow accurate quarter hours to be captured as a persons working day (obviously set to zero for full holiday days) - if this appropriate for your project
You now also have a way of tracking worked time for little extra effort - you might even want to extend the system into being a timesheet/holiday package.
Of course, there is over-time and other time types to consider, as well as flexi-time, etc., etc.
If your application is multi-country, you will also need to extend the public-holiday table with a country field (linked to a country table), and link each user to a country - but don'e even ask about more migrant workers who travel from office to office and might be not-working of different public holidays in different countries!
The water looks lovely lad, but by 'eck it's fair deep - as they say in my adopted county here in England.
|||
Motley,
I am sorry for the time it has taken for me to reply but your answer was exactly what i was looking for.
Thanks very much.
I know it doesnt take in to account holidays but i will save that task for another day.
Regards
Andrew
