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

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

    Re: Query to get the dates between days of months

    ‏2012-10-22T13:51:10Z  
    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

    Re: Query to get the dates between days of months

    ‏2012-10-23T05:22:29Z  
    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
    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

    Re: Query to get the dates between days of months

    ‏2012-10-24T15:29:00Z  
    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 .
    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