December 14, 2006 at 4:21 pm
I have a table called MachineDtl in SQL Server 2000 with the following fields:
Date Machine# Value
1/1/06 100001 50
1/8/06 100001 -90
1/9/06 100001 -60
1/10/06 100001 -900
1/1/06 100008 10
1/2/06 100008 12
1/25/06 100008 -1
1/26/06 100008 -1
Is there a way to select only the records where a machine has a negative value on 2 or more consecutive dates?
(For example I would only want the following returned from the sample above:
Date Machine# Value
1/8/06 100001 -90
1/9/06 100001 -60
1/10/06 100001 -900
1/25/06 100008 -1
1/26/06 100008 -1
Thanks!
Allan
December 14, 2006 at 4:57 pm
How about this way?
(Of course, I am assuming there's only the one entry per day)
declare @MachineDtl table([Date] datetime,
[Machine#] int,
[Value] int)
insert into @MachineDtl
select '20060101', 100001, 50 union all
select '20060108', 100001, -90 union all
select '20060109', 100001, -60 union all
select '20060110', 100001, -900 union all
select '20060101', 100008, 10 union all
select '20060102', 100008, 12 union all
select '20060125', 100008, -1 union all
select '20060126', 100008, -1
select distinct m.*
from @MachineDtl m
inner join ( select [Machine#], [Date]-1, [Date]+1
from @MachineDtl
where Value < 0
) t (MachineNo, Yesterday, Tomorrow) on m.[Machine#]=MachineNo and ([Date]=Yesterday or [Date]=Tomorrow)
where Value < 0 December 14, 2006 at 5:09 pm
This should work (I assumed no times are involved)
select distinct
t1.*
from #temp t1
inner join #temp t2 on t1.machine = t2.machine and t1.value < 0 and t2.value < 0
and t1.dte = dateadd(d,-1,t2.dte)
union
select distinct
t2.*
from #temp t1
inner join #temp t2 on t1.machine = t2.machine and t1.value < 0 and t2.value < 0
and t2.dte = dateadd(d,1,t1.dte)
order by t1.dte
December 14, 2006 at 5:27 pm
Thanks! This did exactly what I needed!
Cheers
Allan
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply