Home » SQL & PL/SQL » SQL & PL/SQL » Counts for all dates in a given month
Counts for all dates in a given month [message #379230] Mon, 05 January 2009 10:00 Go to next message
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 #379231 is a reply to message #379230] Mon, 05 January 2009 10:04 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
SEARCH this forum for "calendar"
Re: Counts for all dates in a given month [message #379232 is a reply to message #379230] Mon, 05 January 2009 10:08 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous message
flyboy
Messages: 1832
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.
Previous Topic: Creating primary key
Next Topic: SQL problem
Goto Forum:
  


Current Time: Fri Dec 09 19:05:38 CST 2016

Total time taken to generate the page: 0.12307 seconds