| very urgent..help in very simple sql statement [message #20995] |
Fri, 05 July 2002 03:14  |
ksr
Messages: 112
Registered: January 2002
|
Senior Member |
|
|
SELECT count(r.REQUEST) request,to_char(r.DATEreq, 'dd.mm.yyyy') as REQUESTDATE
FROM REQUESTpool r
WHERE (r.REQUESTDATE >= to_date('01.07.2002 14:42','dd.mm.yyyy hh24:mi'))
AND (r.REQUESTDATE <= to_date('07.07.2002 14:42','dd.mm.yyyy hh24:mi'))
AND lower(r.RECIPIENT) like lower('%00491633732648%')
GROUP BY to_char(r.REQUESTDATE, 'dd.mm.yyyy')
ORDER BY to_char(r.REQUESTDATE, 'dd.mm.yyyy')
here the output i get is
request requestdate
-----------------------
1 02.07.2002
4 03.07.2002
2 06.07.2002
What i want the output to be is. I want the dates to be displayed from 01.07.2002 to 07.07.2002
request requestdate
-----------------------
0 01.07.2002
1 02.07.2002
4 03.07.2002
0 04.07.2002
0 05.07.2002
2 06.07.2002
0 07.07.2002
|
|
|
|
|
|
|
|
|
|
|
|
| Re: very urgent..help in very simple sql statement [message #21024 is a reply to message #20995] |
Mon, 08 July 2002 00:11  |
ksr
Messages: 112
Registered: January 2002
|
Senior Member |
|
|
I have 5 records in my database.
You can see
1 record for date 02.07.2002
2 record for date 03.07.2002
2 record for date 04.07.2002
DSREQUEST DDREQUESTDATE
test1 02.07.2002
test2 03.07.2002
test3 03.07.2002
test4 04.07.2002
test5 04.07.2002
This is the original query which i was trying..
----------------------------------------------
SELECT
count(r.DSREQUEST) ,
to_char(r.DDREQUESTDATE, 'dd.mm.yyyy') as DDREQUESTDATE
FROM WNS.TDTAREQUEST r
WHERE (r.DDREQUESTDATE >= to_date('01.07.2002 14:42','dd.mm.yyyy hh24:mi'))
AND (r.DDREQUESTDATE <= to_date('07.07.2002 14:42','dd.mm.yyyy hh24:mi'))
AND lower(r.DSRECIPIENT) like lower('%00491633732648%')
GROUP BY to_char(r.DDREQUESTDATE, 'dd.mm.yyyy')
ORDER BY to_char(r.DDREQUESTDATE, 'dd.mm.yyyy')
The output i get is
----------------------
COUNT(R.DSREQUEST) DDREQUESTDATE
-----------------------------------
1 02.07.2002
2 03.07.2002
2 04.07.2002
The output i want is
COUNT(R.DSREQUEST) DDREQUESTDATE
-----------------------------------
0 01.07.2002
1 02.07.2002
2 03.07.2002
2 04.07.2002
0 05.07.2002
0 06.07.2002
0 07.07.2002
The basic thing is, if there are no records for that date,then it should show 0 in the count and print that date..
How to do it...
Can you give me code for this..
|
|
|
|
| Re: very urgent..help in very simple sql statement [message #21048 is a reply to message #20995] |
Tue, 09 July 2002 06:05 |
AYSEGUL
Messages: 4
Registered: July 2002
|
Junior Member |
|
|
select decode('adư','recep','NO','YES','notin');
adi= column name
if adi RECEP THEN WRITE NO ELSE WRITE YES
ELSE
WRITE NOTIN
----------------------------------
IF YOU WANT YOU DON'T USE NOTIN then
create table test (adi varchar2(30));
insert into adi values('aysegul');
insert into adi values('recep');
insert into adi values('fatma');
commit;
select decode('adư','recep','NO','YES') from test;
|
|
|
|
| Re: very urgent..help in very simple sql statement [message #21058 is a reply to message #20995] |
Tue, 09 July 2002 19:09 |
Alex
Messages: 190
Registered: August 1999
|
Senior Member |
|
|
No data of "01.07.2002, 04.07.2002, 05.07.2002. 07.07.2002" in your table, so your query can't output the result what you want.
the DECODE function can't work too.
You can resolve the problem by add a table. It has one column which datatype is date. The table contains values from '01.07.2002' to '31.07.2002'.
then make a outer join between the REQUESTpool table and the table.
SELECT nvl(count(r.REQUEST), 0) request,to_char(r.DATEreq, 'dd.mm.yyyy') as REQUESTDATE
FROM REQUESTpool r , testtable t
WHERE (r.REQUESTDATE >= to_date('01.07.2002 14:42','dd.mm.yyyy hh24:mi'))
AND (r.REQUESTDATE <= to_date('07.07.2002 14:42','dd.mm.yyyy hh24:mi'))
AND lower(r.RECIPIENT) like lower('%00491633732648%')
AND trunc(r.REQUESTDATE) = trunc(t.date)
GROUP BY to_char(r.REQUESTDATE, 'dd.mm.yyyy')
ORDER BY to_char(r.REQUESTDATE, 'dd.mm.yyyy')
sorry for my poor english, I hope you can understant it.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
