SQL Query question

Question

Post reply

SQL Query question

jlaz

SSC Enthusiast

Points: 165
More actions
January 13, 2007 at 10:07 am

#122722

Hi everyone,

I am in the process of creating a report that will report the number of permits that have met date event milestones. Each permit has a number of milestones to meet and the data would look something like this for each permit:

PermitNum   EventDate   EventDesc                   EventDateID
_________________________________________________
PSD-1206     5/11/06      App Received               1
PSD-1206     5/25/06       Determination              2
PSD-1206     7/25/06       Complete App Rec       3
PSD-1206     8/25/06       Process                         5
PSD-1206     9/25/06       Disclosure                    6
PSD-1206     11/22/06     Final Decision              7

NOC-1055     3/11/06      App Received               1
NOC-1055      3/25/06       Determination              2
NOC-1055      7/25/06       Complete App Rec       3
NOC-1055      9/25/06       Process                         5
NOC-1055      10/25/06       Disclosure                  6
NOC-1055      12/25/06       Final Decision            7

The question I have is this:
How can I query all the data above using a date range (quarter) and where the “Final Decision” EventDate is the date that will fall within the date range parameter. (SQL Server 2000)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

--===== Build some test data so we can demo the solution

CREATE TABLE #YourTable (PermitNum VARCHAR(10),EventDate DATETIME ,EventDesc VARCHAR(20),EventDateID INT)

INSERT INTO #YourTable

(PermitNum,EventDate,EventDesc,EventDateID)

SELECT 'PSD-1206','5/11/06','App Received',1 UNION ALL

SELECT 'PSD-1206','5/25/06','Determination',2 UNION ALL

SELECT 'PSD-1206','7/25/06','Complete App Rec',3 UNION ALL

SELECT 'PSD-1206','8/25/06','Process',5 UNION ALL

SELECT 'PSD-1206','9/25/06','Disclosure',6 UNION ALL

SELECT 'PSD-1206','11/22/06','Final Decision',7 UNION ALL

SELECT 'NOC-1055','3/11/06','App Received',1 UNION ALL

SELECT 'NOC-1055','3/25/06','Determination',2 UNION ALL

SELECT 'NOC-1055','7/25/06','Complete App Rec',3 UNION ALL

SELECT 'NOC-1055','9/25/06','Process',5 UNION ALL

SELECT 'NOC-1055','10/25/06','Disclosure',6 UNION ALL

SELECT 'NOC-1055','12/25/06','Final Decision',7

--===== These variables represent parameters for a sproc

DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME

SET @StartDate = '12/01/2006'

SET @EndDate = '12/31/2007'

--===== This would be the body of the sproc

SELECT y.*

FROM #YourTable y,

(--Derived table "d" finds PermitNums with Final Decision in the correct date range

SELECT PermitNum

FROM #YourTable

WHERE EventDateID = 7

AND EventDate >= @StartDate

AND EventDate < @EndDate+1

) d

WHERE y.PermitNum = d.PermitNum

ORDER BY y.PermitNum,EventDateID

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jlaz SSC Enthusiast Points: 165 More actions · Answer 2

Thanks Jeff,

This looks like what I need. The real data comes from multiple related tables which I will apply this query to.