| Author: Lakhan Pal 01 Oct 2008 | Member Level: Diamond | Rating:  Points: 1 |
Hi-
Use This query:
SELECT id,COUNT(id) FROM <TableName> WHERE
State='Rejected ' GROUP BY id
Thanks
Lakhan Pal Garg
Please rate this answer if it helped you.
Thanks & Regards
Lakhan Pal Garg
Free Code Snippets
|
| Author: Sajikumar 01 Oct 2008 | Member Level: Silver | Rating: Points: 6 |
try this
declare @Rejected table(IdVal int identity(1,1),Id int,times datetime,RowNum int)
declare @Count int,@Loop int,@NextVal int
declare @Current datetime,@Next datetime
insert @Rejected(Id,times)
select Id,times from Table1 where State = 'Rejected'
select @Count = count(*) from @Rejected
select @Loop = 1
while (@Loop < @Count)
begin
select top 1 @NextVal = isnull(IdVal,0) from @Rejected where IdVal > @Loop order by IdVal
if (isnull(@NextVal,0) > 0)
begin
select @Current = times from @Rejected where IdVal = @Loop
select @Next = times from @Rejected where IdVal = @NextVal
if not exists(select 1 from Table1 where State = 'Submitted' and times between @Current and @Next )
begin
delete @Rejected where IdVal = @NextVal
if (@@rowcount = 0) break
end
else
select @Loop = @NextVal
end
else
break
end
select count(*) from @Rejected
Thanks
Saji
|