[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

Count for Day of Current Month Show Zero Values

Please refer to a previous question I posted about counts per month.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/Cold_Fusion_Markup_Language/Q_26459915.html

I would like to do the same thing but count for each day of current month, and if there is a zero count, i would like that to show as well.

Here was the query the was worked out for count of month.

SELECT TO_CHAR(m, 'mm yyyy') AS themonth, COUNT(q.quote_id) AS numoflosses
    FROM     q_quotes q
         RIGHT JOIN
             (    SELECT TO_DATE('2009' || TO_CHAR(LEVEL, 'fm09'), 'yyyymm') m
                    FROM DUAL
              CONNECT BY LEVEL <= 12)
         ON q.quote_status_id IN (8, 10, 13)
        AND q.quote_dt >= m
        AND q.quote_dt < ADD_MONTHS(m, 1)
        AND q.quote_id NOT IN (  SELECT linked_quote_id FROM q_quote_links)
GROUP BY m
ORDER BY m ASC;
0
theideabulb
Asked:
theideabulb
1 Solution
 
slightwv (䄆 Netminder) Commented:
Off the top of my head change the connect by to something like:

SELECT TO_DATE('2009' || to_char(sysdate,'mm') || TO_CHAR(LEVEL, 'fm09'), 'yyyymmdd') m
FROM DUAL
CONNECT BY LEVEL <= to_number(to_char(last_day(sysdate),'dd'))
0
 
reitersCommented:
I've always resorted to getting the recordset and then looping over it and putting the results into a new handmade recordset and adding the missing ones myself.  I could never find a way to make a DB return a result where no results exist.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now