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.

And heres how my current query looks like:

SELECT
SUM(
IF(
((ROUND(((
(LEAST(TO_DAYS(til_dato),TO_DAYS('2003-11-19'))
-GREATEST(TO_DAYS(fra_dato),TO_DAYS('2003-11-01')))
/7)*5),0)+1) * belop)>0 ,

((ROUND((( (LEAST(TO_DAYS(til_dato),TO_DAYS('2003-11-19'))
-GREATEST(TO_DAYS(fra_dato),TO_DAYS('2003-11-01')))
/7)*5),0)+1) * belop) ,
0)) AS kostnad,
hvor.value AS hvorValue
FROM hvor, media_kost
WHERE media_kost.mId=hvor.hId
AND media_kost.dager=1
AND hvor.active=1
GROUP BY hvorValue
ORDER BY sortorder, hvorValue;


Can anyone help me how to only count the weekdays, and exclude the weekends (saturdays+sundays) ?

Use this partial query for the number of days:

@ttldays:=LEAST(TO_DAYS(til_dato),TO_DAYS('2003-11-19'))+1
-GREATEST(TO_DAYS(fra_dato),TO_DAYS('2003-11-01')),
@startday:=WEEKDAY(GREATEST(fra_dato,"2003-11-01")),
@ttldays - 2*FLOOR(@ttldays/7) - IF(@startday + MOD(@ttldays,7) - 4 < 0, 0,
LEAST(@startday + MOD(@ttldays,7) - 4, 2)) As WeekDays

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

Oops, there is a case that my code did not handle. You need to add a one if there are only week days in the range. The Code for this is:
+ IF(@ttldays + @startday < 6, 1, 0)

Just add that right before "As WeekDays"

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)

SELECT
@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
WHERE `media_kost`.`mId`=`hvor`.`hId`
AND `media_kost`.`dager`=1
AND `hvor`.`active`=1
AND `hId`=13
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).

Here's the result with AND hId=13: http://sql-servers.com/nopaste/?show=1305

Heres the result without AND hId=13: http://sql-servers.com/nopaste/?show=1306