Hi ,

In my database I have an Audit table, that keeps track of teams worked upon the same record in a workflow.

I need to find out how many records have been returned to the first team for correction ?

The column 'Status' is numbered from 1 to 6 and Column 'EditTime' saves the time when record has been edited.

Could you please suggest how to find , how many records have been returned for correction and identify those records.

Thanks in advance

Can you provide sample data + desired result?

Hi,

Also please update the version.  Latest SQL server versions have built-in support for audit.

Thanks.

In the example below the record id 45 was in status 3 on 7/7 then status increased to 4 on 8/7

it was returned back to status 3 on 9/7

I need to find all such records that were returned back to lower status from higher status

id	recID	status	edittime
1	45	3	7/07/2015
2	45	4	8/07/2015
3	45	3	9/07/2015

See if this helps

create table #t (id int, col1 int, st int, dt date)

insert into #t values (1,45,3,'20150707')
insert into #t values (2,45,4,'20150708')
insert into #t values (3,45,3,'20150709')



with cte
as
(
select * from #t 
where st=(select max(st) from #t where col1 =45)
) select #t.* from #t  join cte on #t.col1 =cte.col1
  where #t.st< cte.st and #t.dt>cte.dt

if it's SQL Server 2012 and onward, try this:

;with cte as (
select*,lag(st,1,0) over (partition by col1 order by dt) as lastst
from #t
)
select id,col1,st,dt
from cte
where st < lastst 

Hope it helps!

If there is another row here (4, 45, status 6, 10/07/2015), do you still want to return row 3 or not?

And also, if the status went up few revisions but then get back to an earlier stage, say, from 5 back to 2, what would you want

select * from myTable T where exists (select 1 from myTable T1

where RecId = T.RecId and T1.EndTime < T.EndTime and T1.[Status] > T.Status)

-------------

This query will return all rows that have corresponding earlier date rows with greater status.

Thanks for all the replies