SQL Query question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Query question

Author

Topic

jlaz461
Starting Member

2 Posts

Posted - 2007-01-13 : 12:20:37

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.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-13 : 14:00:05

declare @f datetime, @t datetime

select @f = '20070101', @t = '20070331'

SELECT x.PermitNum, x.EventDate, x.EventDesc, x.EventDateID
FROM <YourTableNameHere> AS x
LEFT JOIN (
SELECT PermitNum
FROM <YourTableNameHere>
WHERE EventDate >= @f AND EventDate < DATEADD(day, 1, @t)
AND EventDesc = 'Final Decision' ) AS y ON y.PermitNum = x.PermitNum

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources