Results 1 to 4 of 4

Thread: days and dates

  1. #1
    Join Date
    Nov 2003
    Posts
    3

    Unanswered: days and dates

    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) ?

  2. #2
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137

    Re: days and dates

    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
    Attached Files Attached Files

  3. #3
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137
    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"

  4. #4
    Join Date
    Nov 2003
    Posts
    3
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •