Topic
3 replies Latest Post - ‏2012-10-24T15:29:00Z by SystemAdmin
SystemAdmin
SystemAdmin
17917 Posts
ACCEPTED ANSWER

Pinned topic Query to get the dates between days of months

‏2012-10-22T10:41:43Z |
Hey guys, If anyone can help on this

SELECT B.NAME, SUBSTR(A.TERRITORY,instr(TERRITORY,'-')+1), TO_CHAR(DATE(A.ROW_ADDED_DTTM),'DD-MON'), COUNT(B.NAME)
FROM REC1 A , REC2 B
WHERE A.ID = B.ID
AND B.ROLE = 'xxx'
AND A.FLAG = 'EE'
AND DATE(A.ROW_ADDED_DTTM) BETWEEN Current date - day(current date - 1 days) days AND Current date
GROUP BY B.NAME, SUBSTR( A.TERRITORY,instr(TERRITORY,'-')+1), TO_CHAR(DATE( A.ROW_ADDED_DTTM),'DD-MON')
ORDER BY 1 DESC, 2 DESC, 3 DESC

The o/p for this is

NAME 2 3 4
Christensen,Ryan Pacific Coast 18-OCT 2
Christensen,Ryan Pacific Coast 11-OCT 1

These are the dates where the conditions are satisfied, between 1st day of current month to current date.

What I want is whenever there is no data match I should get the data count as '0' for rest of the dates.

In this Eg the o/p should be

NAME 2 3 4
Christensen,Ryan Pacific Coast 18-OCT 2
Christensen,Ryan Pacific Coast 17-OCT 0
Christensen,Ryan Pacific Coast 16-OCT 0
.
.
.
.
.
Christensen,Ryan Pacific Coast 11-OCT 1
Christensen,Ryan Pacific Coast 10-OCT 0
Christensen,Ryan Pacific Coast 09-OCT 0
Christensen,Ryan Pacific Coast 08-OCT 0
..
.
.
..

And so on .

Thanks
Updated on 2012-10-24T15:29:00Z at 2012-10-24T15:29:00Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    17917 Posts
    ACCEPTED ANSWER

    Re: Query to get the dates between days of months

    ‏2012-10-22T13:51:10Z  in response to SystemAdmin
    Hi there,

    I think you should reformulate your question.

    Even when I can try to figure out what your purpose is I would like to have a much more detailed example, including the actual data and DDL of the tables involved here. That way I would have a clear picture and you will get (hopefully) a more accurate and valuable answer.

    So, please if you do not mind elaborate a little bit more on this.

    Thanks,

    LooW
    • SystemAdmin
      SystemAdmin
      17917 Posts
      ACCEPTED ANSWER

      Re: Query to get the dates between days of months

      ‏2012-10-23T05:22:29Z  in response to SystemAdmin
      Sure.
      I have the below query

      SELECT B.NAME, SUBSTR(A.TERRITORY,instr(TERRITORY,'-')+1), TO_CHAR(DATE(A.ROW_ADDED_DTTM),'DD-MON'), COUNT(B.NAME)
      FROM REC1 A , REC2 B
      WHERE A.ID = B.ID
      AND B.ROLE = 'xxx'
      AND A.FLAG = 'EE'
      AND DATE(A.ROW_ADDED_DTTM) BETWEEN Current date - day(current date - 1 days) days AND Current date
      GROUP BY B.NAME, SUBSTR( A.TERRITORY,instr(TERRITORY,'-')+1), TO_CHAR(DATE( A.ROW_ADDED_DTTM),'DD-MON')
      ORDER BY 1 DESC, 2 DESC, 3 DESC

      For which the O/p is
      NAME 2 3 4
      Christensen,Ryan Pacific Coast 18-OCT 2
      Christensen,Ryan Pacific Coast 11-OCT 1

      Whenever I run this query, the o/p I want is from 1st day of current month to Current Date (e.g Say if I run it on 23rd Oct, it should geve me the o/p from 1st Oct to 23rd.)

      Now,the o/p should be such that for those dates where there is data, should appear as it is(i.e As above 2 Rows) and for those dates where there is no data, it should populate the rows with count 0 (E.g 16th Oct). But it should display rows for all the rows from 1st day to Current Date.

      I hope this clarifies the scenario.
      NAME 2 3 4
      Christensen,Ryan Pacific Coast 18-OCT 2
      Christensen,Ryan Pacific Coast 17-OCT 0
      Christensen,Ryan Pacific Coast 16-OCT 0
      .
      .
      .
      .
      .
      Christensen,Ryan Pacific Coast 11-OCT 1
      Christensen,Ryan Pacific Coast 10-OCT 0
      Christensen,Ryan Pacific Coast 09-OCT 0
      Christensen,Ryan Pacific Coast 08-OCT 0
      ..
      .
      .
      ..

      And so on .
      • SystemAdmin
        SystemAdmin
        17917 Posts
        ACCEPTED ANSWER

        Re: Query to get the dates between days of months

        ‏2012-10-24T15:29:00Z  in response to SystemAdmin
        Hi,

        Your answer is pretty much the same than the first time you posted, it just clarifies little your purpose.

        Nevertheless try with this:

        Create an auxiliary table to store the dates as follows:

        
        CREATE TABLE DATES (D DATE NOT NULL)@   INSERT INTO DATES VALUES(to_date(
        '24-OCT-2012', 
        'DD-MON-YYYY'))@ INSERT INTO DATES VALUES(to_date(
        '23-OCT-2012', 
        'DD-MON-YYYY'))@ INSERT INTO DATES VALUES(to_date(
        '22-OCT-2012', 
        'DD-MON-YYYY'))@ INSERT INTO DATES VALUES(to_date(
        '21-OCT-2012', 
        'DD-MON-YYYY'))@ INSERT INTO DATES VALUES(to_date(
        '20-OCT-2012', 
        'DD-MON-YYYY'))@ INSERT INTO DATES VALUES(to_date(
        '19-OCT-2012', 
        'DD-MON-YYYY'))@ INSERT INTO DATES VALUES(to_date(
        '18-OCT-2012', 
        'DD-MON-YYYY'))@ INSERT INTO DATES VALUES(to_date(
        '17-OCT-2012', 
        'DD-MON-YYYY'))@ INSERT INTO DATES VALUES(to_date(
        '16-OCT-2012', 
        'DD-MON-YYYY'))@ INSERT INTO DATES VALUES(to_date(
        '15-OCT-2012', 
        'DD-MON-YYYY'))@ INSERT INTO DATES VALUES(to_date(
        '14-OCT-2012', 
        'DD-MON-YYYY'))@ INSERT INTO DATES VALUES(to_date(
        '13-OCT-2012', 
        'DD-MON-YYYY'))@ INSERT INTO DATES VALUES(to_date(
        '12-OCT-2012', 
        'DD-MON-YYYY'))@ INSERT INTO DATES VALUES(to_date(
        '11-OCT-2012', 
        'DD-MON-YYYY'))@ INSERT INTO DATES VALUES(to_date(
        '10-OCT-2012', 
        'DD-MON-YYYY'))@ INSERT INTO DATES VALUES(to_date(
        '09-OCT-2012', 
        'DD-MON-YYYY'))@ INSERT INTO DATES VALUES(to_date(
        '08-OCT-2012', 
        'DD-MON-YYYY'))@ INSERT INTO DATES VALUES(to_date(
        '07-OCT-2012', 
        'DD-MON-YYYY'))@ INSERT INTO DATES VALUES(to_date(
        '06-OCT-2012', 
        'DD-MON-YYYY'))@ INSERT INTO DATES VALUES(to_date(
        '05-OCT-2012', 
        'DD-MON-YYYY'))@ INSERT INTO DATES VALUES(to_date(
        '04-OCT-2012', 
        'DD-MON-YYYY'))@ INSERT INTO DATES VALUES(to_date(
        '03-OCT-2012', 
        'DD-MON-YYYY'))@ INSERT INTO DATES VALUES(to_date(
        '02-OCT-2012', 
        'DD-MON-YYYY'))@ INSERT INTO DATES VALUES(to_date(
        '01-OCT-2012', 
        'DD-MON-YYYY'))@ INSERT INTO DATES VALUES(to_date(
        '30-SEP-2012', 
        'DD-MON-YYYY'))@
        


        Then execute AND TEST this query with the test data set that you did not posted:

        
        SELECT NAME, SUBSTR(TERRITORY,instr(TERRITORY,
        '-')+1), TO_CHAR(DATE(D),
        'DD-MON'), SUM(VAL) FROM ( SELECT B.NAME, A.TERRITORY, D.D, SUM(CASE WHEN D.D=A.ROW_ADDED_DTTM THEN 1 ELSE 0 END) VAL FROM DATES D, REC1 A, REC2 B WHERE A.ID=B.ID AND B.ROLE = 
        'xxx' AND A.FLAG = 
        'EE' AND DATE(A.ROW_ADDED_DTTM) BETWEEN Current date - day(current date - 1 days) days AND Current date AND DATE(D.D) BETWEEN Current date - day(current date - 1 days) days AND Current date GROUP BY D.D, B.NAME, A.TERRITORY, A.ROW_ADDED_DTTM ) GROUP BY NAME, SUBSTR(TERRITORY,instr(TERRITORY,
        '-')+1), TO_CHAR(DATE(D),
        'DD-MON') ORDER BY 1 DESC , 2 DESC , 3 DESC @
        


        Just for your future references when posting to a forum, the quality of your answer will be dependent on the quality of your question and information provided with it.

        Good luck,

        LooW