Hi, I have a table with the fields [belop, fra_dato, til_dato]
fra_dato and til_dato are datetime-fields.
I need to get the number of days between fra_dato and til_dato.
And the user also selects a input_date1 and input_date2.
An example: If theres a record with the values (500, 2003-11-01,2003-11-16) and the user enters input_date1=2003-10-31 and input_date2=2003-11-13, then it should count the number of days between 2003-11-01 and 2003-11-13. Thats why I use GREATEST and LEAST to only count on the days that the fra_dato to til_dato and input_date1 to input_date2 has in common. The number of days will then be multiplied with belop, which in this example is 500. 5 days makes 2500.
The IF() is used to only SUM() where the number of days is more than 0, so if the user here enters a date out of range, SUM should +0 instead of adding some strange negative numbers that it returns.
Now the problem is only count days that are NOT saturdays or sundays.
Thats why I have ROUND() /7 *5, but that doesnt work quite well. If the last day is saturday, it will still count it. Using WHERE on til_dato or fra_dato to test if any of them are saturdays or sundays with WEEKDAY() is only a bad idea for at least two reasons: 1. it only checks the starting and ending date, 2. If the ending date is a saturday, it doesnt count in the number of days between fra_dato and til_dato.
/7)*5),0)+1) * belop) ,
0)) AS kostnad,
hvor.value AS hvorValue
FROM hvor, media_kost
GROUP BY hvorValue
ORDER BY sortorder, hvorValue;
Can anyone help me how to only count the weekdays, and exclude the weekends (saturdays+sundays) ?
The first part is to assign the total number of days to a user variable to make the syntax simpler. The second part selects the weekday as a number (0..6 for Mon...Sun, respectively). The third part actually calculates the number of weekdays. It takes the total number of days and subtracts 2 for every full week. Then it takes the remaining days that do not make up a full week and uses a little logic to find how many of them are not weekdays. The logic is: If the start day plus the number of days left minus 4 is less than 0, then use 0 other wise use the least of 2 and the start day plus the number of days left minus 4.
I tested this on a few cases, check the attached file
Thanks for your reply. I tried your query, and it worked fine after some few alterings and adjustments.
My query now looks like this:
(SUM and GROUP BY is disabled at the moment, instead it lists every rows for each group)
@totdays:=LEAST(TO_DAYS(`til_dato`),TO_DAYS('2003-11-19'))-GREATEST(TO_DAYS(`fra_dato`),TO_DAYS('2003-11-01')) AS totdays,
@startday:=WEEKDAY(GREATEST(`fra_dato`,'2003-11-01')) AS startday,
@days:=@totdays - 2*FLOOR(@totdays/7) - IF(@startday + MOD(@totdays,7) - 4 < 0, 0, LEAST(@startday + MOD(@totdays,7) - 4, 2))
+ IF(@totdays + @startday < 6, 1, 1) As WeekDays, `sortorder`,
@days:=IF(@days>=0,@days,0) AS days,
@days*`belop` AS kost,
`hvor`.`value` AS hvorValue
FROM hvor, media_kost
ORDER BY `sortorder`, hvorValue LIMIT 10;
The problem now is that AND `hId` is required. hId refers to the hvorValue, which will be GROUP'ed BY. If AND hId=13 (or any other id) is there, it works fine. If I remove that line, all `days`-fields returns 1, which isnt right. (hId=13 refers to Altavista.no). Its also not possible to select more hId's, only one will be accepted. I've tried AND (hId=13 OR hId=14, etc) and AND hId IN (13,14,etc).