1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
|
declare @t TABLE( Nom Char(1), Qtte Int, DateD DateTime )
insert @t values( 'A', 10, Convert(datetime, '31/12/2006',103) )
insert @t values( 'A', 15, Convert(datetime, '12/12/2006' ,103) )
insert @t values( 'A', 1, Convert(datetime, '17/12/2006' ,103) )
insert @t values( 'B', 2, Convert(datetime, '10/12/2006',103) )
insert @t values( 'B', 14, Convert(datetime, '25/12/2006' ,103) )
insert @t values( 'C', 15, Convert(datetime, '28/12/2006',103) )
insert @t values( 'C', 1, Convert(datetime, '31/12/2006',103) )
insert @t values( 'D', 1, Convert(datetime, '31/12/2006',103) )
select x.*
from @t x
inner join (
select a.Nom, a.DateD, Count(*) n
from @t A
left join @t b
on ( b.Nom = a.Nom ) and
( b.DateD >= a.DateD )
group by a.Nom, a.DateD
having ( Count(*) <= 1 )
) y
on ( y.Nom = x.Nom ) and
( y.DateD = x.DateD ) |
Partager