Combining queries

[spatik]

I have a scenario like this in my application.
We can have values documented for each time column.
I have discrete timepoints in hand and I need to find the
documented values just before and just after the time columns.

Consider a scenario where I have 8 time points like
10:00,10:05,10: 10,10:15,10:20, 10:25,10:30 and 10:35.

I run a query for that like to find the documented value just before the 8 time points.
I also run the same big query to find the documented value just after the 8 time points by changing the
condition " FLOWSHEET_COLUM N_TIME < '2007-04-11 10:00:00' " to " FLOWSHEET_COLUM N_TIME > '2007-04-11 10:00:00' "
in all the 8 places. Is there any better way to do this. Can I combine these queries to one?
Thanks for any efforts put into thinking this.

Select top 2 PFS_ELEMENT_PAR AM_ID, FLOWSHEET_COLUM N_TIME
from io_object_fluid s_blood_product s
where visit_id = 156 and
PFS_ELEMENT_PAR AM_ID in (18845,18847) and
FLOWSHEET_COLUM N_TIME < '2007-04-11 10:00:00'
group by FLOWSHEET_COLUM N_TIME, PFS_ELEMENT_PAR AM_ID

union

Select top 2 FLOWSHEET_COLUM N_TIME, PFS_ELEMENT_PAR AM_ID
from io_object_fluid s_blood_product s
where visit_id = 156 and
PFS_ELEMENT_PAR AM_ID in (18845,18847) and
FLOWSHEET_COLUM N_TIME < '2007-04-12 10:05:00'
group by FLOWSHEET_COLUM N_TIME, PFS_ELEMENT_PAR AM_ID

union

Select top 2 FLOWSHEET_COLUM N_TIME, PFS_ELEMENT_PAR AM_ID
from io_object_fluid s_blood_product s
where visit_id = 156 and
PFS_ELEMENT_PAR AM_ID in (18845,18847) and
FLOWSHEET_COLUM N_TIME < '2007-04-12 10:10:00'
group by FLOWSHEET_COLUM N_TIME, PFS_ELEMENT_PAR AM_ID

union

Select top 2 FLOWSHEET_COLUM N_TIME, PFS_ELEMENT_PAR AM_ID
from io_object_fluid s_blood_product s
where visit_id = 156 and
PFS_ELEMENT_PAR AM_ID in (18845,18847) and
FLOWSHEET_COLUM N_TIME < '2007-04-12 10:15:00'
group by FLOWSHEET_COLUM N_TIME, PFS_ELEMENT_PAR AM_ID

union

Select top 2 FLOWSHEET_COLUM N_TIME, PFS_ELEMENT_PAR AM_ID
from io_object_fluid s_blood_product s
where visit_id = 156 and
PFS_ELEMENT_PAR AM_ID in (18845,18847) and
FLOWSHEET_COLUM N_TIME < '2007-04-12 10:20:00'
group by FLOWSHEET_COLUM N_TIME, PFS_ELEMENT_PAR AM_ID

union

Select top 2 FLOWSHEET_COLUM N_TIME, PFS_ELEMENT_PAR AM_ID
from io_object_fluid s_blood_product s
where visit_id = 156 and
PFS_ELEMENT_PAR AM_ID in (18845,18847) and
FLOWSHEET_COLUM N_TIME < '2007-04-12 10:25:00'
group by FLOWSHEET_COLUM N_TIME, PFS_ELEMENT_PAR AM_ID

union

Select top 2 FLOWSHEET_COLUM N_TIME, PFS_ELEMENT_PAR AM_ID
from io_object_fluid s_blood_product s
where visit_id = 156 and
PFS_ELEMENT_PAR AM_ID in (18845,18847) and
FLOWSHEET_COLUM N_TIME < '2007-04-12 10:30:00'
group by FLOWSHEET_COLUM N_TIME, PFS_ELEMENT_PAR AM_ID

union

Select top 2 FLOWSHEET_COLUM N_TIME, PFS_ELEMENT_PAR AM_ID
from io_object_fluid s_blood_product s
where visit_id = 156 and
PFS_ELEMENT_PAR AM_ID in (18845,18847) and
FLOWSHEET_COLUM N_TIME < '2007-04-12 10:35:00'
group by FLOWSHEET_COLUM N_TIME, PFS_ELEMENT_PAR AM_ID;

[iburyak]

If it is SQL 2005 you can probably use Ranking functions.

Good Luck.

[iburyak]

See what I wrote for you.
Hope you would be able to modify it to suet your case:

[PHP]
declare @aa table
(Time datetime)
Declare @date1 datetime
Declare @date2 datetime
Declare @date3 datetime

Select @date1 = '2007-04-11 10:00:00',
@date2 = dateadd(n,5, '2007-04-11 10:00:00'),
@date3 = dateadd(n,10, '2007-04-11 10:00:00')


insert into @aa values ('2007-04-11 10:00')
insert into @aa values ('2007-04-11 10:01')
insert into @aa values ('2007-04-11 10:02')
insert into @aa values ('2007-04-11 10:03')

insert into @aa values ('2007-04-11 10:05')
insert into @aa values ('2007-04-11 10:06')
insert into @aa values ('2007-04-11 10:07')
insert into @aa values ('2007-04-11 10:09')

insert into @aa values ('2007-04-11 10:10')
insert into @aa values ('2007-04-11 10:12')
insert into @aa values ('2007-04-11 10:13')
insert into @aa values ('2007-04-11 10:14')

Select *
From (
select time,
coalesce(case when Time between @date1 and dateadd(n,4,@da te1) then 1 end,
case when Time between @date2 and dateadd(n,4,@da te2) then 2 end,
case when Time between @date3 and dateadd(n,4,@da te3) then 3 end
) Rank from @aa) tmp1
Where time in (select top 2 time
from @aa where coalesce(case when Time between @date1 and dateadd(n,4,@da te1) then 1 end,
case when Time between @date2 and dateadd(n,4,@da te2) then 2 end,
case when Time between @date3 and dateadd(n,4,@da te3) then 3 end) = tmp1.Rank
order by time) [/PHP]

Good Luck.