- The #temp table creates an unique ID for each row in AcctDetails. (Ordered by TransTypeCode)

- The derived table, D, has the minimum ID for each TransTypeCode. (This is needed...

1. As David mentioned, I am not sure why you would want to do this.

2. Avoid SELECT INTO in production code as it puts schema locks on tempdb.

3. Avoid loops....

Umm...

If the selected mappings can be a subset of the mappings in #tmpmappings, then the following may work. If not, I do not see any alternative but to count.

SELECT DISTINCT...

Maybe:

SELECT D1.ReferenceID

FROM (

        SELECT M.ReferenceID, COUNT(M.ReferenceID) AS RefCount

        FROM #tmpmappings M

        GROUP BY M.ReferenceID

    ) D1

    JOIN (

            SELECT M1.ReferenceID, COUNT(M1.ReferenceID) AS RefCount

            FROM #tmpmappings M1

                JOIN #tmpSelectedMappings S1

                    ON M1.MappingTypeID = S1.SelectedTypeID

                        AND M1.MappingID = S1.SelectedID

            GROUP BY M1.ReferenceID

        ) D2

        ON...

Joe,

As John indicated, we do not mean any offence but in order to solve the problem we need a 'test bed'.

Your CREATE TABLE...

Maybe you should post some correct DDL, some sensible sample data, the results you get and the results you want.

(If you do this you may even be able to work...

Maybe:

SELECT

    T.ittnumber

    ,COALESCE(T.itttitle, T.ittdescription) AS itttitle

    ,T.daterequired

FROM dbo.tbl3215 T

WHERE T.completed = 0

    AND COALESCE(T.itttitle, T.ittdescription) IS NOT NULL

    AND EXISTS (

            SELECT *

            FROM dbo.tblassignment A

            WHERE A.ittnumber = T.ittnumber

                AND EXISTS (

                        SELECT *

                        FROM dbo.tblfltwrkcntr W

                        WHERE W.fltwkcntrid = A.assfltwrkid

                            AND...

As t.PAY_CODE_NUM seems to be an int, it may be worth replacing t.PAY_CODE_NUM IN ('600', '17') with t.PAY_CODE_NUM IN (600, 17) in order

to stop the implicit conversion. (Might help...

You may want to consider shortening your nvarchar(max) columns.

The following may work:

SELECT

    T.ittnumber

    ,COALESCE(T.itttitle, T.ittdescription) AS itttitle

    ,T.daterequired

FROM dbo.tbl3215 T

WHERE T.completed = 0

    AND COALESCE(T.itttitle, T.ittdescription) IS NOT NULL

    AND EXISTS (

            SELECT *

            FROM dbo.tblassignment A

            WHERE...

You need to alias the derived table.

On looking at this again, James has a good point about an unique constraint on AA_Anno and Val_NumSpedizione. If you want to add this constraint then try an instead of trigger...

>>>And if the trigger goes in error (for some reason...), the transaction is rollbacked??

Maybe.

You need to be careful with transactions. If SET XACT_ABORT is off, as it normally is, then the statement is...

This may be what is wanted:

SET QUOTED_IDENTIFIER, ANSI_NULLS ON

GO

CREATE TRIGGER TR_I_GSP_Spedizione

ON dbo.GSP_Spedizione

AFTER INSERT

AS

DECLARE @I TABLE

(

    ID_Spedizione int NOT NULL

    ,AA_Anno smallint NOT NULL

    ,RowID int IDENTITY NOT NULL

)

INSERT INTO @I (ID_Spedizione, AA_Anno)

SELECT...

I have just looked at this again and have managed to get nearly the same result with one CTE.

The difference is Job4 starts with Job1 and not Job7.

-- *** Test...

I tend to agree with you and Ninja has already pointed out to Hans that the data model is bad.

My understanding, based on the limited information provided, is that some...