Convoluted record selection 1

[LMGroup]

I'm using SQL Server 2005 and trying to create a view from a single table that will select a myriad of different records based on other records it finds. I have to manipulate the dates to calculate time intervals.

I have to find the time between the filedate and enddate and also the time between the startdate and enddate. If a holddate exists, a second startdate that falls between the holddate and enddate will need to be found to calculate the hold interval and subtract it from the total time. The reason for the mins and maxes is that statuscodes can be entered multiple times; work can move in and out and back into departments depending on different situations.

Essentially, in garbled pseudocode,

enddate=min(date) where statuscode in (10335, 10340)
startdate=max(date) where statuscode = 10000 and date < enddate
filedate=max(date) where statuscode = 10001 and date < enddate
holddate=date where statuscode in (10002, 10390) and date between startdate and enddate

This is the basic select statement for the table. The TransactionN field could be used with the min and max functions as well but I still have to use the dates to calculate the intervals. I'm not quite sure how to get the rest of it.

SELECT     JobN, TransactionN, StatusCode, Date, Time
FROM       JobStatus1

I appreciate any help.

 

[johnherman]

Instead of trying to do this in one big step, how about creating a temporary (or permanent) table containing the date selection criteria, and possibly some control columns like the date and time created/ran and maybe user. Then CREATE OR REPLACE the view, perhaps having that same control criteria as in the date selection table appended to the view name?

==================================
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[LMGroup]

Temporary tables could work. I'll give that a try. Thanks!

 

[wbodger]

Without seeing the data, could you do something like

;with cte as
(
SELECT case
	when statuscode in (10335,10340) then min(date)
end as enddate,
case
	when statuscode=10000 and date<enddate then max(date)
end as startdate
case
	when statuscode=10001 and date<enddate then max(date)
end as filedate
case
	when statuscode in (10002,10390) and startdate<date<enddate then date
end as holddate
FROM       JobStatus1
)

and then do your calculations or create another (or two or three etc.) CTEs to build your view.

 

[LMGroup]

Thanks. That's very helpful