For your duplicate rows - what is the business rule to determine the earliest record? If the date and ref are identical, how do you determine that one is 'earlier'...

Your example table has no unique primary key, but you could get the same result using an aggregate query as a derived table.

i.e. select min([date]) group by [ref] will give...

From looking at the query results, it is apparent that the total row count in Report Builder is the number of rows returned by the query, not the number of...

I have the same question. However right-clicking on a field in the design view does not show a 'Properties' choice - just 'Format', 'Edit Formula' and 'Delete'. How is the...

Amit:

Your solution returns the max(date_out) value, not the null value where present. The ISNULL() clause evaluates properly, but it looks like as soon as you throw the MAX() in front...

thanks for both solutions. I'll play around to see which works/performs best.

Perfect! Thanks so much.

Thanks SQLBill. That's exactly what I did and discovered that the Veritas backup job at our data centre was hitting all of the SQL Server instances on our domain.

I have already looked at the transaction log using APEX SQL Log and filtered out everything but deletes. To my shock and surprise there were none. Could a tran stay...

I have just had a similar experience. Sometime between 2pm and 3pm last Friday a table in an SQL Database lost 300+ records, with nothing in the transaction log to...

Antares666:

This works OK except for the case where September is abbreviated as Sept. That leaves Sept as the month string which will not convert (as per your note). it looks...

That is it for sure. I could import the field into Excel and into Access but not into SQL Server. Turns out the first 8 rows were null so JET...

Thanks - I managed to figure that out just after posting this issue.

I should add that the select stmt alone works fine with values in place of the parameters

TH

Right you are. It makes perfect sense using a decision table as opposed to "talking" it through. And it works in the query. Thanks!