For a single row, you can use SCOPE_IDENTITY().

For multiple rows (or a single row if you wish), you can use the OUTPUT clause.

Do you need more help?

I guess there's a misunderstanding.

You want to delete rows from PL1 when the data is in PL2.

You can't delete rows from a query, you delete rows from a table.

Should I...

Something like this?

WITH CTE AS(

SELECT *, ROW_NUMBER() OVER(PARTITION BY FirstVisit ORDER BY ScheduledEnd) rn

FROM #Visits

)

SELECT OpportunityID,

ActivityID,

...

Actually, the code is trying to delete PL2 instead of PL1.

PL2 is not updateable, so you can delete from it.

This might not be better, but it's different. 😉

SELECT NHS_NUMBER_ANON,

NHS_NUMBER,

MIN(MonthDate) Doctor_Start_Period,

MAX(MonthDate) Doctor_End_Period,

Value Doctor_Code

FROM PS_Table

CROSS...

As Gail said, SQL Server won't show an error like that. SSIS might do that and you should specify that because the syntax is completely different.

If the number goes over...

One option is to use group by inside your ctePreAgg, the other is to change ROW_NUMBER to DENSE_RANK.

Hardy21 (8/26/2014)

Luis Cazares (8/25/2014)

jshahan (8/25/2014)

I like PIVOT...

jshahan (8/25/2014)

Luis Cazares (8/25/2014)

jshahan (8/25/2014)

I like PIVOT...

jshahan (8/25/2014)

I like PIVOT because you can...

Here's an extended set of sample data and the solutions posted. I guess Micky's got the correct formula.

CREATE TABLE PRE_LOAD(

KitID varchar(20) NULL,

BatteryID varchar(20) NULL,

TestID varchar(20) NULL,

LBSTAT varchar(8) NULL

)

INSERT INTO PRE_LOAD

...

If you want to look in a single package, you can right click on the package (inside BIDS) and select "View Code". That will show the xml and you can...

According to what I understood, this should work:

select *

from PRE_LOAD ex

where EXISTS

(

select 1

from PRE_LOAD i

where LBSTAT <> 'NOT DONE'

AND ex.kitID = i.KitID

)

The sad reality is that "very controlled, monitored and process oriented system" is just a nice wish as there are many things that just get into production and haven't been...

Lots of people come to the forums...