Hi All,

I have a tricky problem that I'm trying to find the most effective method to solve.

Here's a simplified version of my View structure.

Table: Audits

AuditID | PublicationID | AuditEndDate | AuditStartDate
1       | 3             | 13/05/2010   | 01/01/2010
2       | 1             | 31/12/2009   | 01/10/2009
3       | 3             | 31/03/2010   | 01/01/2010
4       | 3             | 31/12/2009   | 01/10/2009
5       | 2             | 31/03/2010   | 01/01/2010
6       | 2             | 31/12/2009   | 01/10/2009
7       | 1             | 30/09/2009   | 01/01/2009 

There's 3 query's that I need from this. I need to one to get all the data. The next to get only the history data (that is, everything but exclude the latest data item by AuditEndDate) and then the last query is to obtain the latest data item (by AuditEndDate).

There's an added layer of complexity that I have a date restriction (This is on a per user/group basis) where certain user groups can only see between certain dates. You'll notice this in the where clause as AuditEndDate<=blah and AuditStartDate>=blah

1. Foreach publication, select all the data available.

select * from Audits Where auditEndDate<='31/03/10' and AuditStartDate>='06/06/2009';

1. foreach publication, select all the data but Exclude the latest data available (by AuditEndDate)

select * from Audits left join (select AuditId as aid, publicationID as pid and max(auditEndDate) as pend from Audit where auditenddate <= '31/03/2009' /* user restrict / group by pid) Ax on Ax.pid=Audit.pubid where pend!=Audits.auditenddate AND auditEndDate<='31/03/10' and AuditStartDate>='06/06/2009' / user restrict */

1. Foreach publication, select only the latest data available (by AuditEndDate)

select * from Audits left join (select AuditId as aid, publicationID as pid and max(auditEndDate) as pend from Audit where auditenddate <= '31/03/2009'/* user restrict / group by pid) Ax on Ax.pid=Audit.pubid where pend=Audits.auditenddate AND auditEndDate<='31/03/10' and AuditStartDate>='06/06/2009' / user restrict */

So at the moment, query 1 and 3 work fine, but query 2 just returns all the data instead of the restriction.

Can anyone help me?

Thanks

jason