Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to group a range in query

Status
Not open for further replies.
Dec 13, 2002
109
GB
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top