Search code examples
sqloracle-sqldevelopersql-scripts

How to get values between two dates from a table which have no data some days

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
Solution

  • 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