| Counts for all dates in a given month [message #379230] |
Mon, 05 January 2009 10:00  |
Hez_66
Messages: 3
Registered: November 2005
Location: Belfast
|
Junior Member |
|
|
Hi,
I have a table that has a number or records recorded against a date, possibly up to 48 records against one date.
What I need to do is count the number of records against each date, including bringing back zero for any missing dates.
I need this done for a month from sysdate - 1, i.e. for to-day it would be counts for 1st, 2nd, 3rd and 4th of January.
I have managed to get the code that brings back counts for those dates that are in the table, but I now need to expand this for zeros for missing dates.
Code so far is
SELECT intv_val_set_id, TRUNC(intv_val_dttm), COUNT(intv_val_set_id)
FROM ci_intv_val
WHERE TRUNC(intv_val_dttm) IN (SELECT TO_CHAR(TRUNC(sysdate,'MM') + LEVEL - 1) day
FROM dual
CONNECT BY level <= TRUNC(sysdate - 1)- TRUNC(sysdate,'MM') + 1)
GROUP BY intv_val_set_id,TRUNC(intv_val_dttm)
;
Any help would be much appreciated
|
|
|
|
|
|
| Re: Counts for all dates in a given month [message #379232 is a reply to message #379230] |
Mon, 05 January 2009 10:08  |
Hez_66
Messages: 3
Registered: November 2005
Location: Belfast
|
Junior Member |
|
|
Hi,
From the search I'm assuming you're referring to creating a table with the required dates and then matching against this.
This is on a live environment and I can't/don't want to create tables, would prefer to do this purely via the SELECT statement if possible.
Heather
|
|
|
|
| Re: Counts for all dates in a given month [message #379233 is a reply to message #379232] |
Mon, 05 January 2009 10:26 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
| Hez_66 wrote on Mon, 05 January 2009 17:08 | Hi,
From the search I'm assuming you're referring to creating a table with the required dates and then matching against this.
This is on a live environment and I can't/don't want to create tables, would prefer to do this purely via the SELECT statement if possible.
Heather
|
There are many examples of dynamic calendar generation here.
In addition, Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).
Regards
Michel
[Updated on: Mon, 05 January 2009 10:27] Report message to a moderator |
|
|
|
| Re: Counts for all dates in a given month [message #379234 is a reply to message #379230] |
Mon, 05 January 2009 10:51 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
SELECT intv_val_set_id, TRUNC(intv_val_dttm), COUNT(intv_val_set_id)
FROM ci_intv_val
WHERE TRUNC(intv_val_dttm) IN (SELECT TO_CHAR(TRUNC(sysdate,'MM') + LEVEL - 1) day
FROM dual
CONNECT BY level <= TRUNC(sysdate - 1)- TRUNC(sysdate,'MM') + 1)
GROUP BY intv_val_set_id,TRUNC(intv_val_dttm)
;
There is no need to use SELECT and the IN clause, you may use simple "BETWEEN TRUNC(sysdate,'MM') AND TRUNC(sysdate) - 1". Why are you using the TO_CHAR function? It only leads to unnecessary implicit conversions, and, as you do not use format mask, it is not very safe. What is the data type of INTV_VAL_DTTM? And are you aware, that when using TRUNC on that column, Oracle cannot use index on that column (unless it is functional - but it is better to adjust the SELECT statement).
On the other hand, the resultset of the subquery in the IN clause may (after correcting the result data type) be easily used as the table you need and do not want to create.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
