July 13, 2006 at 2:34 pm
You are missing the ItemType in your table definitions. Also you have renamed changeddate to balchangeddate in the query.
With the expected sample data above, what is your complete expected output?
N 56°04'39.16"
E 12°55'05.25"
July 13, 2006 at 2:42 pm
pcq0125:
Select
srab1.TAGID,
rab.TAGID
From
s_RAB srab1
Inner Join RAB rab
on rab.RABID = srab1.RABID
Where
srab1.ChangedDate =
(
Select
Max(srab2.ChangedDate)
From
s_RAB srab2
Inner Join RBL rbl
on rbl.RABID = srab2.RABID
Where
rbl.ItemType = 'R'
and
srab2.ChangedDate < rbl.ChangedDate
)
and
srab1.TAGID <> rab.TAGID
July 13, 2006 at 3:09 pm
Another alternative:
create table dbo.RBL (
RABID int,
ChangedDate datetime,
ItemType char(1)
)
create table dbo.RAB (
RABID int,
TAGID char(4),
ChangedDate datetime
)
create table dbo.s_RAB (
RABID int,
TAGID char(4),
ChangedDate datetime
)
insert into dbo.RBL values (1111, '2006-01-03', 'R')
insert into dbo.RBL values (1112, '2006-01-05', 'R')
insert into dbo.RAB values (1111, '0044', '2006-01-03')
insert into dbo.RAB values (1112, '0033', '2006-01-05')
insert into dbo.s_RAB values (1111, '0011', '2006-01-01')
insert into dbo.s_RAB values (1111, '0033', '2006-01-02')
insert into dbo.s_RAB values (1111, '0044', '2006-01-06')
select
rbl.ItemType,
srab.RABID,
srab.ChangedDate,
srab.TAGID,
rab.TAGID,
(select max(rab1.ChangedDate) from dbo.RAB rab1 where rab1.RABID = rab.RABID) as Test
from
dbo.RBL rbl
inner join dbo.RAB rab
on (rbl.RABID = rab.RABID)
inner join dbo.s_RAB srab
on (rbl.RABID = srab.RABID)
where
rbl.ItemType = 'R'
and srab.ChangedDate = (select
max(srab1.ChangedDate)
from
dbo.s_RAB srab1
where
srab1.RABID = rab.RABID
and srab1.ChangedDate <= rab.ChangedDate)
and srab.TAGID <> rab.TAGID
drop table dbo.RBL
drop table dbo.RAB
drop table dbo.s_RAB
Viewing 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply