DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: select distinct count on different dates

Threaded View

  1. #1
    Join Date
    Feb 2008
    Posts
    2

    select distinct count on different dates

    I want to select count of distinct requests for different statuses on different days like today, yesterday and day-before-yesterday from sample table, please guide me in this regard. Currently using following query where i get daily count.

    columns: req_Status, req_id,sent_date

    select req_status, count(distinct(req_id)) as REQ_TODAY, MIN(to_char(SENT_DATE,'DD-MON-YY - HH:MI:SS')) as Min_sent_date,
    MAX(to_char(SENT_DATE,'DD-MON-YY - HH:MI:SS')) as Max_sent_date,'1-NEW Command' Meaning_of_Status from sample
    where sent_date like sysdate
    and req_status=1 group by req_status
    union
    select req_status, count(distinct(req_id)) as REQ_TODAY, MIN(to_char(SENT_DATE,'DD-MON-YY - HH:MI:SS')) as Min_sent_date,
    MAX(to_char(SENT_DATE,'DD-MON-YY - HH:MI:SS')) as Max_sent_date,'2-old Command' Meaning_of_Status from sample
    where sent_date like sysdate
    and req_status=2 group by req_status
    union
    .
    .
    and so on... column req_status has values in range from 1 to 12

    output:

    REQ_STATUS REQ_TODAY MIN_SENT_DATE MAX_SENT_DATE MEANING_OF_STAT
    ------------- ------------ --------------------- --------------------- -------------------
    1 3 25-JUL-12 - 06:47:20 25-JUL-12 - 07:28:24 1-NEW Command
    2 3 25-JUL-12 - 06:47:20 25-JUL-12 - 08:07:10 2-old Command
    3 3 25-JUL-12 - 06:47:20 25-JUL-12 - 08:07:29 3-wrong Command
    .
    .
    so on for 12 REQ_STATUSes

    In this output I need two more columns that will depict count of requests for different statuses for last two days as follows :

    REQ_STATUS | REQ_CNT_DB4YEST | REQ_CNT_YEST | REQ_TODAY | MIN_SENT_DATE | MAX_SENT_DATE | MEANING_OF_STAT
    Last edited by ora600; 07-25-2012 at 12:34 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width