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