Cross Apply Function

I am unsure how to select data within one table, and then select additional data from the result set.

There is an audit log table with columns:

Year, Month, Amount, AuditType, UniqueID, DateTime

AuditType: O = Original

AuditType: U = Update

Sample Data:

2007, January, 500, O, 333555, 3/10/2007 3:30:00 PM

2007, January, 1000, U, 333555, 4/10/2007 3:30:00 PM

2007, January, 1200, U, 333555, 5/10/2007 3:30:00 PM

2007, February, 500, O, 333556, 3/10/2007 3:30:00 PM

2007, March, 500, O, 333557, 3/10/2007 3:30:00 PM

I am trying to find all Updates in the table, ie where AuditType = U.

Then, for every Update, I want to see what was updated, ie the last record with that same UniqueID. Im guessing I should use an INNER JOIN or UNION with a SELECT TOP 1 ORDER BY DateTime but not sure how do to this.

Data returned should be:

2007, January, 1000, U, 333555, 4/10/2007 3:30:00 PM, 500

2007, January, 1200, U, 333555, 5/10/2007 3:30:00 PM, 1000

Any ideas greatly appreciated!

Which version of SQL Server do you use?|||SQL 2005 SP1|||

You could use CROSS APPLY statement:

Code Snippet

create table Log(

[Year] int,

[Month] varchar(10),

Amount decimal,

AuditType char(1),

UniqueID int,

date DateTime

)

insert into Log values(2007, 'January', 500, 'O', 333555, '3/10/2007 3:30:00 PM')

insert into Log values(2007, 'January', 1000, 'U', 333555, '4/10/2007 3:30:00 PM')

insert into Log values(2007, 'January', 1200, 'U', 333555, '5/10/2007 3:30:00 PM')

insert into Log values(2007, 'February', 500, 'O', 333556, '3/10/2007 3:30:00 PM')

insert into Log values(2007, 'March', 500, 'O', 333557, '3/10/2007 3:30:00 PM')

--Returns previous update

CREATE function udf_earlyUpdate(@.ID int,@.dt datetime) RETURNS TABLE

AS

RETURN SELECT TOP 1 Amount FROM Log WHERE UniqueID = @.ID and date<@.dt ORDER BY date DESC

select * from LOG CROSS APPLY udf_earlyUpdate(UniqueID,Date)

WHERE log.AuditType='U'

|||

I'm a bit confused about your goals.

You indicate that

the last record with that same UniqueID

, but your expected results show two records for the same UniqueID.

From you description, I would have expected that you wanted only one record for UniqueID 333555.

Please clarify.

|||

Every update should have one record. But a record with the same Unique ID can be updated multiple times.

The results should show every update, even if its the same Unique ID and what was updated.

|||

Here's the basic idea, I'll leave it to you to fill in the specific blanks.

select <alias>.columns

from (<any select statement that you want to specify, if calcing aggregates, alias each column) AS <alias>

Why does this work? A select statement simply returns a result set, which has exactly the same definition as a table. If you make sure each column in the select statement has a specific "column name", then it works just like a table. So, you simply wrap parens around the select statement within the FROM clause. The key to the whole thing is that you alias the select statement so that it really can be referenced in memory looking just like a table.

|||

The CROSS APPLY function works great with a simple SELECT. However, when I attempt to create a VIEW with this function combining two tables, I get an error because they contain the same column.

How can I get the result of the Function without using SELECT *, ie without selection all columns?

"Column names in each view or function must be unique. Column name 'BUDGETAMT' in view or function 'V_BUDGET_UPDATE_ALL' is specified more than once."