| View previous topic :: View next topic |
| Author |
Message |
hagar
Beginner
Joined: 26 Jan 2010
Posts: 2
Topics: 1
Location: Columbia, South Carolina
|
 Posted: Fri Mar 12, 2010 11:18 am Post subject: Calculate # of weekend days between 2 date ranges |
 |
|
| Just wondering if there is a DB2 query available for doing this. I need to calculate the number of weekend days between 2 dates, eg between 2009-12-28 and 2010-01-05. I searched this site (and the libs at work) using DAYOFWEEK criteria, but did not find what I am looking for. Not looking for Cobol code, just a simple DB2 query. |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Fri Mar 12, 2010 12:36 pm Post subject: |
|
|
hagar,
Weekend days as in saturday and sunday? Since your date range is only 1 week , it would always be 2 right?
Is your date range more than a week? |
|
| Back to top |
|
|
hagar
Beginner
Joined: 26 Jan 2010
Posts: 2
Topics: 1
Location: Columbia, South Carolina
|
| Posted: Fri Mar 12, 2010 1:37 pm Post subject: |
|
|
| That's just an example, date range can be any 2 dates, and I want to calculate the weekend dates in the range. I have another utility to calculate holidays. |
|
| Back to top |
|
|
CraigG
Intermediate
Joined: 02 May 2007
Posts: 202
Topics: 0
Location: Viginia, USA
|
| Posted: Fri Mar 12, 2010 3:54 pm Post subject: |
|
|
I had to come up with calculations for workdays so for a Monday thru Friday work week
| Code: |
weekend_days = days(begin-date) - days(end-date) - weekdays(begin-date, end-date)
where
CREATE FUNCTION WEEKDAYS (BDATE DATE, EDATE DATE)
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURNS DEC(9,0)
RETURN
CASE ((DAYS(EDATE + 1 DAY) - DAYS(BDATE)) +
ABS(DAYS(EDATE + 1 DAY) - DAYS(BDATE))) WHEN 0 THEN -1
ELSE
(FLOOR((DAYS(EDATE) - DAYS(BDATE) + 1) / 7) * 5)
+ (CASE DAYOFWEEK(BDATE)
WHEN 1 THEN CASE MOD(
DAYS(EDATE) - DAYS(BDATE) + 1,7)
WHEN 0 THEN 0
WHEN 1 THEN 0
WHEN 2 THEN 1
WHEN 3 THEN 2
WHEN 4 THEN 3
WHEN 5 THEN 4
WHEN 6 THEN 5
END
WHEN 2 THEN CASE MOD(
DAYS(EDATE) - DAYS(BDATE) + 1,7)
WHEN 0 THEN 0
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
WHEN 4 THEN 4
WHEN 5 THEN 5
WHEN 6 THEN 5
END
WHEN 3 THEN CASE MOD(
DAYS(EDATE) - DAYS(BDATE) + 1,7)
WHEN 0 THEN 0
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
WHEN 4 THEN 4
WHEN 5 THEN 4
WHEN 6 THEN 4
END
WHEN 4 THEN CASE MOD(
DAYS(EDATE) - DAYS(BDATE) + 1,7)
WHEN 0 THEN 0
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
WHEN 4 THEN 3
WHEN 5 THEN 3
WHEN 6 THEN 4
END
WHEN 5 THEN CASE MOD(
DAYS(EDATE) - DAYS(BDATE) + 1,7)
WHEN 0 THEN 0
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 2
WHEN 4 THEN 2
WHEN 5 THEN 3
WHEN 6 THEN 4
END
WHEN 6 THEN CASE MOD(
DAYS(EDATE) - DAYS(BDATE) + 1,7)
WHEN 0 THEN 0
WHEN 1 THEN 1
WHEN 2 THEN 1
WHEN 3 THEN 1
WHEN 4 THEN 2
WHEN 5 THEN 3
WHEN 6 THEN 4
END
WHEN 7 THEN CASE MOD(
DAYS(EDATE) - DAYS(BDATE) + 1,7)
WHEN 0 THEN 0
WHEN 1 THEN 0
WHEN 2 THEN 0
WHEN 3 THEN 1
WHEN 4 THEN 2
WHEN 5 THEN 3
WHEN 6 THEN 4
END
END)
END;
COMMIT;
GRANT EXECUTE ON FUNCTION WEEKDAYS TO PUBLIC;
|
|
|
| Back to top |
|
|
CraigG
Intermediate
Joined: 02 May 2007
Posts: 202
Topics: 0
Location: Viginia, USA
|
| Posted: Fri Mar 12, 2010 6:04 pm Post subject: |
|
|
One little mistake
weekend_days = days(begin-date) - days(end-date) - weekdays(begin-date, end-date)
should be
weekend_days = days(end-date) - days(begin-date) - weekdays(begin-date, end-date) |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Fri Mar 12, 2010 6:29 pm Post subject: |
|
|
hagar,
The following SQL (semi tested - modified from existing recursive query) will find all the Saturdays and Sundays between a given range. I gave the range from 2009-12-28 to 2010-12-31
| Code: |
WITH TEMP (WEEKEND1,WEEKEND2) AS
((SELECT DATE(NEXT_DAY('2009-12-28','SAT'))
,DATE(NEXT_DAY('2009-12-28','SUN'))
FROM SYSIBM.SYSDUMMY1)
UNION ALL
(SELECT DATE(NEXT_DAY(B.WEEKEND1,'SAT'))
,DATE(NEXT_DAY(B.WEEKEND2,'SUN'))
FROM SYSIBM.SYSDUMMY1 A
,TEMP B
WHERE B.WEEKEND1 < '2010-12-31'
OR B.WEEKEND2 < '2010-12-31' ))
SELECT *
FROM TEMP
WHERE WEEKEND1 < '2010-12-31'
AND WEEKEND2 < '2010-12-31'
; |
The output will be something like this
| Code: |
WEEKEND1 WEEKEND2
---------+---------+---
2010-01-02 2010-01-03
2010-01-09 2010-01-10
2010-01-16 2010-01-17
2010-01-23 2010-01-24
2010-01-30 2010-01-31
2010-02-06 2010-02-07
2010-02-13 2010-02-14
2010-02-20 2010-02-21
...
2010-12-18 2010-12-19
2010-12-25 2010-12-26
|
Craig, I think your sql can be simplified
Kolusu |
|
| Back to top |
|
|
Dibakar
Advanced
Joined: 02 Dec 2002
Posts: 700
Topics: 63
Location: USA
|
| Posted: Mon Mar 15, 2010 3:55 pm Post subject: |
|
|
hagar,
if you are only interested in the count then you can use following query -
| Code: |
SELECT ((DAYS(NEXT_DAY(DATE(DAYS('2010-01-05')-7),'SAT')) -
DAYS(NEXT_DAY(DATE(DAYS('2009-12-28')-1),'SAT')))/7 + 1)
+
((DAYS(NEXT_DAY(DATE(DAYS('2010-01-05')-7),'SUN')) -
DAYS(NEXT_DAY(DATE(DAYS('2009-12-28')-1),'SUN')))/7 + 1)
FROM SYSIBM.SYSDUMMY1
;
|
kolusu, your SQL will need modification if the range starts from a Sunday or ends in a Saturday |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Mon Mar 15, 2010 5:08 pm Post subject: |
|
|
| Dibakar wrote: | hagar,
if you are only interested in the count then you can use following query -
|
How would you modify if the date range is beyond a week? The begin date is 2009-12-28 and end date is 2010-05-31?
| Quote: |
kolusu, your SQL will need modification if the range starts from a Sunday or ends in a Saturday |
If it is inclusive of the begin and end day then yes we need to account for them too.
Kolusu |
|
| Back to top |
|
|
Dibakar
Advanced
Joined: 02 Dec 2002
Posts: 700
Topics: 63
Location: USA
|
| Posted: Mon Mar 15, 2010 5:18 pm Post subject: |
|
|
| Quote: |
How would you modify if the date range is beyond a week? The begin date is 2009-12-28 and end date is 2010-05-31?
|
Same query works, unless I am missing something. Here is the result for above range -
| Code: |
SELECT ((DAYS(NEXT_DAY(DATE(DAYS('2010-05-31')-7),'SAT')) -
DAYS(NEXT_DAY(DATE(DAYS('2009-12-28')-1),'SAT')))/7 + 1)
+
((DAYS(NEXT_DAY(DATE(DAYS('2010-05-31')-7),'SUN')) -
DAYS(NEXT_DAY(DATE(DAYS('2009-12-28')-1),'SUN')))/7 + 1)
FROM SYSIBM.SYSDUMMY1
;
---------+---------+---------+---------+---------+---------+--------
---------+---------+---------+---------+---------+---------+--------
44
|
Correction: Corrected date in the qiery |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
