ronaldmacdonald
MIS
Hello
I have a query that returns the number of working days between two dates from two oracle tables. That bit seems ok. When I run the query it list each rows equivalent working days which is ok.
So the code is:
SELECT (TRUNC(DATABASE.TABLEA.CERTDATE) - TRUNC(DATABASE.TABLEA.COMPDATE)) - (TRUNC(DATABASE.TABLEA.CERTDATE, 'D')
- TRUNC(DATABASE.TABLEA.COMPDATE, 'D')) / 7 * 2 + 1 AS WDAYS
FROM DATABASE.TABLEB, DATABASE.TABLEA
WHERE DATABASE.TABLEB.PRIMKEY = DATABASE.TABLEA.PPRIMKEY AND (DATABASE.TABLEA.CERTDATE >= :startdate) AND
(DATABASE.TABLEA.CERTDATE <= :enddate) AND (DATABASE.TABLEA.CERTDATE IS NOT NULL) AND ((TRUNC(DATABASE.TABLEA.CERTDATE)
- TRUNC(DATABASE.TABLEA.COMPDATE)) - (TRUNC(DATABASE.TABLEA.CERTDATE, 'D') - TRUNC(DATABASE.TABLEA.COMPDATE, 'D')) / 7 * 2 + 1 > - 1)
ORDER BY WDAYS
The results look like:
WDAYS
-----
3
3
3
2
2
2
1
1
1
I am counting the number of rows returned so in the above example this would be 9.
I want to group WDAYS into groups like
WDAYS
1 -2 = 6
3 = 3
It is the latter part I am struggling with. Could I use a subquery perhaps on the results from the query code I have posted?
Ronaldmacdonald
I have a query that returns the number of working days between two dates from two oracle tables. That bit seems ok. When I run the query it list each rows equivalent working days which is ok.
So the code is:
SELECT (TRUNC(DATABASE.TABLEA.CERTDATE) - TRUNC(DATABASE.TABLEA.COMPDATE)) - (TRUNC(DATABASE.TABLEA.CERTDATE, 'D')
- TRUNC(DATABASE.TABLEA.COMPDATE, 'D')) / 7 * 2 + 1 AS WDAYS
FROM DATABASE.TABLEB, DATABASE.TABLEA
WHERE DATABASE.TABLEB.PRIMKEY = DATABASE.TABLEA.PPRIMKEY AND (DATABASE.TABLEA.CERTDATE >= :startdate) AND
(DATABASE.TABLEA.CERTDATE <= :enddate) AND (DATABASE.TABLEA.CERTDATE IS NOT NULL) AND ((TRUNC(DATABASE.TABLEA.CERTDATE)
- TRUNC(DATABASE.TABLEA.COMPDATE)) - (TRUNC(DATABASE.TABLEA.CERTDATE, 'D') - TRUNC(DATABASE.TABLEA.COMPDATE, 'D')) / 7 * 2 + 1 > - 1)
ORDER BY WDAYS
The results look like:
WDAYS
-----
3
3
3
2
2
2
1
1
1
I am counting the number of rows returned so in the above example this would be 9.
I want to group WDAYS into groups like
WDAYS
1 -2 = 6
3 = 3
It is the latter part I am struggling with. Could I use a subquery perhaps on the results from the query code I have posted?
Ronaldmacdonald