Distinct row based upon date/time criterion 1

[Sideline]

I have little sql experience beyond the access gui and would appreciate any tips on how to get this started.

Based upon my event dataset I need to create a query which returns for each day the first event of each day in the dataset based upon a data/time criteria.

That criteria is a) the first event after time x on a day if there is no ongoing event at that time and b) the first event after time x if an event is ongoing at time x.

thx

 

[Remou]

I imagine that the reason you have not got an answer is that you have not provided an attempt at the sql nor have you provided any sample data.

 

[Sideline]

Thanks for the feedback Remou.

As indicated in the initial post I was unsure on how to get this started using sql as opposed to the access query gui.

A colleague created the following, using sql server, and I will now have a go at translating this into a code which I can use in access.

If anyone can offer any assistence in this translation process it would be most appreciated.

thanks

declare @time_of_day datetime
set @time_of_day = '1/1/1900 8:30:00'

select Operation.[op date],
Operation.theatre,
[Operation id],
--calc delay = start time - (later of "" end time OR @Time_of_day)
[first operation].[arrive theatre] - isnull([Operation in progress].[depart theatre], Operation.[op date] + @time_of_day) as [start delay]
from Operation --the operations
inner join (
select [op date], --the first operation begining after @time_of_day for each day and theatre
Theatre,
min([arrive theatre]) as [arrive theatre]
from Operation
where [arrive theatre] > [op date] + @time_of_day
group by [op date],
Theatre
) as [first operation] on Operation.[op date] = [first operation].[op date]
and Operation.[arrive theatre] = [first operation].[arrive theatre]
and Operation.Theatre = [first operation].Theatre
left join (
select [op date], --end time of any operation "in progress" at @time_of_day
Theatre,
min([depart theatre]) as [depart theatre]
from Operation
where [op date] + @time_of_day between [arrive theatre] and [depart theatre]
group by [op date],
Theatre
) as [Operation in progress] on Operation.[op date] = [Operation in progress].[op date]
and Operation.Theatre = [Operation in progress].Theatre
order by Operation.[op date],
Operation.theatre

 

[Sideline]

But then again there is nothing like self help!

SELECT Transformation.Theatre, Transformation.[Op Date], Transformation.[Operation Id], [qry NT 1]![Anaes Start]-IIf(IsNull([qry NT 2]![Surg Fin]),0.3541661,[qry NT 2]![Surg Fin]) AS [Start Delay], Transformation.Year, Transformation.Month, Transformation.Specialty, Transformation.SurgDay
FROM (Transformation INNER JOIN [qry NT 1] ON (Transformation.Theatre = [qry NT 1].Theatre) AND (Transformation.[Anaes Start] = [qry NT 1].[Anaes Start]) AND (Transformation.[Op Date] = [qry NT 1].[Op Date])) LEFT JOIN [qry NT 2] ON (Transformation.Theatre = [qry NT 2].Theatre) AND (Transformation.[Op Date] = [qry NT 2].[Op Date])
ORDER BY Transformation.Theatre, Transformation.[Op Date];