0

i am getting a total from my table by grouping date. For example if you look at below picture when i try to get values between '08.03.2013' AND '22.03.2013'. There are no data for 18.03.2013 and 20.03.2013.

But i need that date because i use highchart on front end and dont want to handle this on front end side.

Do you have any suggestion ?

Thanks

SELECT TO_CHAR(CR_DATE, 'DD') DAY, TO_CHAR(CR_DATE, 'MM') MONTH, TO_CHAR(CR_DATE, 'YY') YEAR, 
count(STAB_ID) AS total FROM paytrans 
WHERE acct_id = 1552 AND status = 'DND_FNSH' and CR_DATE > '08.03.2013' AND CR_DATE < '22.03.2013' 
GROUP BY TO_CHAR(CR_DATE, 'YY'), TO_CHAR(CR_DATE, 'DD'), TO_CHAR(CR_DATE, 'MM') 
ORDER BY year, month, day

enter image description here

[Edit for @art]

SELECT  TO_CHAR(CR_DATE-1, 'DD')  + LEVEL DAY, TO_CHAR(CR_DATE, 'MM')    MONTH, TO_CHAR(CR_DATE, 'YY')  YEAR,(cr_date-1) + LEVEL AS cr_date
  FROM   
(
SELECT
       To_Date('08.05.2013', 'DD.MM.YYYY')  end_date, 
       To_Date('08.04.2013', 'DD.MM.YYYY')  cr_date 
 FROM dual     
)
CONNECT BY LEVEL <= (end_date - cr_date)+1
Talha
  • 12,673
  • 5
  • 49
  • 68
  • make a [calendar](http://www.perpendulum.com/2012/06/calendar-table-script-for-oracle/) table and left join on to it – T I Mar 22 '13 at 12:39

1 Answers1

1

This is calendar as per your dates. You can compare your dates and calendar dates in your query:

SELECT cr_date
     , to_char(cr_date, 'DD')   curr_day
     , to_char(cr_date, 'MM')   curr_month
     , to_char(cr_date, 'YYYY') curr_year
 FROM
 (
 SELECT (cr_date-1) + LEVEL AS cr_date
   FROM 
   (
    SELECT To_Date('08.05.2013', 'DD.MM.YYYY')  end_date
         , To_Date('08.04.2013', 'DD.MM.YYYY')  cr_date 
      FROM dual
   )
   CONNECT BY LEVEL <= (end_date - cr_date)+1
 )
/

CR_DATE    CURR_DAY    CURR_MONTH    CURR_YEAR
-------------------------------------------------
4/8/2013     08         04          2013
4/9/2013     09         04          2013
4/10/2013    10         04          2013
....
....
4/30/2013    30         04          2013
5/1/2013     01         05          2013
5/2/2013     02         05          2013
...
...
5/8/2013     08          05         2013
Art
  • 5,616
  • 1
  • 20
  • 22
  • Thanks for your reply but there are problem when months are different To_Date('08.05.2013', 'DD.MM.YYYY') end_date, To_Date('08.04.2013', 'DD.MM.YYYY') cr_date – Talha Mar 22 '13 at 13:35
  • @talhakosen-What is different? I always test my queries... The query gives you all dates between start date April 8, 2013 ('08.04.2013') and end date May 8, 2013 (08.05.2013). It does not matter which dates you use, the query will work for any dates. Just make sure your formats and start/end dates are correct. See revised dates above. – Art Mar 22 '13 at 13:51
  • @talhakosen-Got it. It is just a matter of formatting. See revised answer please. – Art Mar 22 '13 at 14:39