September 3, 2006 at 3:59 pm
Hi, I need some help with writing the SQL definition necessary to do the following:
(Cut and paste into Notepad to see correct layout)
Temp table name: #Report1
PERMIT_ID PERMIT_NR PERMIT_TYPE_CD PERMIT_EVENT_DT DATE_TYPE_DS DATE_TYPE_CD
------------------------------------------------------------------------------------------------
18 05-04a PSD 2005-06-14 Date Ini app received 1
18 05-04a PSD 2005-07-08 Date Ini completeness 2
18 05-04a PSD 2005-07-15 Date Ini com app recved 3
18 05-04a PSD 2005-08-30 Date draft issued 5
18 05-04a PSD 2005-09-03 Date Pub commment 6
18 05-04a PSD 2005-11-03 Date final decision issued 7
19 05-01a PSD 2005-07-14 Date Ini app received 1
19 05-01a PSD 2005-08-01 Date Ini completeness 2
19 05-01a PSD 2005-08-05 Date Ini com app recved 3
19 05-01a PSD 2005-08-08 Date draft issued 5
19 05-01a PSD 2005-09-21 Date Pub commment 6
19 05-01a PSD 2005-11-19 Date final decision issued 7
20 05-04b PSD 2005-08-13 Date Ini app received 1
20 05-04b PSD 2005-08-21 Date Ini completeness 2
20 05-04b PSD 2005-09-10 Date Ini com app recved 3
20 05-04b PSD 2005-09-15 Date draft issued 5
20 05-04b PSD 2005-10-22 Date Pub commment 6
20 05-04b PSD Date final decision issued 7
21 05-06a PSD 2005-06-14 Date Ini app received 1
21 05-06a PSD 2005-07-09 Date Ini completeness 2
21 05-06a PSD 2005-07-18 Date Ini com app recved 3
21 05-06a PSD 2005-08-30 Date draft issued 5
21 05-06a PSD 2005-09-12 Date Pub commment 6
21 05-06a PSD 2005-11-15 Date final decision issued 7
What I need is a query that will return the data above and leave out those without a PERMIT_EVENT_DT. In other words
the output should be:
PERMIT_ID PERMIT_NR PERMIT_TYPE_CD PERMIT_EVENT_DT DATE_TYPE_DS DATE_TYPE_CD
------------------------------------------------------------------------------------------------
18 05-04a PSD 2005-06-14 Date Ini app received 1
18 05-04a PSD 2005-07-08 Date Ini completeness 2
18 05-04a PSD 2005-07-15 Date Ini com app recved 3
18 05-04a PSD 2005-08-30 Date draft issued 5
18 05-04a PSD 2005-09-03 Date Pub commment 6
18 05-04a PSD 2005-11-03 Date final decision issued 7
19 05-01a PSD 2005-07-14 Date Ini app received 1
19 05-01a PSD 2005-08-01 Date Ini completeness 2
19 05-01a PSD 2005-08-05 Date Ini com app recved 3
19 05-01a PSD 2005-08-08 Date draft issued 5
19 05-01a PSD 2005-09-21 Date Pub commment 6
19 05-01a PSD 2005-11-19 Date final decision issued 7
21 05-06a PSD 2005-06-14 Date Ini app received 1
21 05-06a PSD 2005-07-09 Date Ini completeness 2
21 05-06a PSD 2005-07-18 Date Ini com app recved 3
21 05-06a PSD 2005-08-30 Date draft issued 5
21 05-06a PSD 2005-09-12 Date Pub commment 6
21 05-06a PSD 2005-11-15 Date final decision issued 7
Thanks very much for your help
Joseph
September 4, 2006 at 2:49 am
select ...
from table1
where not exists (select PERMIT_ID from table1 table_bad where table1.PERMIT_ID=table_bad.PERMIT_ID and table_bad.PERMIT_EVENT_DT is null)
If could be rewritten to replace not exists, by some form of counting those whose event_date is not null
September 5, 2006 at 11:23 am
You can use this also
select
*
from
table1
where
(select count(PERMIT_ID) from table1 table_bad where table1.PERMIT_ID=table_bad.PERMIT_ID and table_bad.PERMIT_EVENT_DT is null)<1
September 5, 2006 at 1:56 pm
You can use subselect too:
Select * from table1 where PERMIT_ID not in (SElect PERMIT_ID from table1 where table1.PERMIT_EVENT_DT is null)
Thanks
Sreejith
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply