Help with query

[JosephLaz]

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

 

[notadba]

You could try something as simple as:

SELECT * FROM TABLE1 T1
WHERE T1.PERMIT_EVENT_DT IS NOT NULL

Which would remove the rows without a date. Your example seems to imply that you want "all rows" for an ID to be removed where there are any rows without an event date (PERMIT_id 20).

In this case - (though probably more efficient ways to do this):
SELECT * FROM TABLE1 T1
WHERE NOT EXISTS( SELECT 1
FROM TABLE 2 T2
WHERE T1.PERMIT_ID = T2.PERMIT_ID
AND T2.PERMIT_EVENT_DT IS NULL)

Is this what you were after?

 

[PHV]

Something like this ?
SELECT * FROM #Report1
WHERE PERMIT_ID NOT IN (SELECT PERMIT_ID FROM #Report1 WHERE PERMIT_EVENT_DT IS NULL)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886