As has already been mentioned, IN is generally not a good idea.

The most efficient queries usually use either JOINs or EXISTs depending on what you want.

-- eg of IN

SELECT *

FROM...

1. It is not a good idea to prefix the slip_no with the order_id in the db. Do this in the front end or use a view.

2. In SQL2000 you...

Sorry about the CROSS JOIN. I do try to suppress my urge to put all the join conditions in the WHERE clause!

Maybe:

SELECT SUM(S.ESDS) AS ESDSTotal

    ,COUNT(S.ESDS) AS ESDSCount

    ,COUNT(*) AS ESDSCountWithNulls

FROM DEMANDSPREAD S

    CROSS JOIN (

            SELECT MAX(S1.[TimeStamp]) AS MaxTimeStamp

            FROM DEMANDSPREAD S1

        ) D

WHERE S.[TimeStamp] BETWEEN DATEADD(minute, -1, D.MaxTimeStamp) AND D.MaxTimeStamp

And this will show all the paths:

;WITH LongestPath (PathID, TeamID, PrevTeamID, Stage, StageDuration, Total)

AS

(

    SELECT ROW_NUMBER() OVER (ORDER BY P.Total, P.Stage)

        ,P.TeamID, P.PrevTeamID, P.Stage, P.StageDuration, P.Total

    FROM #PathTotals P

    WHERE P.NextTeamID is NULL

    UNION ALL

    SELECT...

I have just had a chance to have a quick look at this again. The following bit of code may help in showing duplicate maximum duration paths:

-- Now show the...

What a nice question. I hope the following will help you.

I have taken a slightly different approach.

As I found your non-normalized dataset confusing, I normalized it as follows:

CREATE TABLE #Teams

(

    TeamID...

A rough outline of the logic is:

1. The temp table gives alls combinations of order and article with each row assigned a sequential number. (TID) (I do not know if...

A fast forward cursor may be quicker, but you could try using a temp table. Something like:

CREATE TABLE #temp

(

    TID int IDENTITY NOT NULL PRIMARY KEY

    ,IdOrder int NOT NULL

    ,IdArticle char(4) COLLATE...

Try using a derived table:

SELECT *

FROM TableA A

    JOIN TableB B

        ON A.APK = B.APK

    JOIN (

            SELECT B1.APK, MIN(B1.DateCol) AS DateCol

            FROM TableB B1

            GROUP BY B1.APK

        ) D

        ON B.APK = D.APK

            AND B.DateCol = D.DateCol

See which of the following is better for your data:

-- 1. Probably not very efficient due to join in sub-query

SELECT DISTINCT -- distinct may not be needed

    V.FISCAL_YEAR

    ,V.ACCOUNTING_PERIOD

    ,V.AMOUNT

    ,V.DESCR

    ,V.REPORT_ITEM

    ,V.REPORT_NAME

    ,'BUDGET' AS ACCOUNTING_TYPE

FROM View3...

I think you will need to have another user for reporting. This user will have:

1. read rights to the Crystal DB.

2. public rights to the Live DB with DENY SELECT...

Maybe:

UPDATE T

SET TreeOrder = D.TreeOrder

FROM YourTable T

    JOIN (

            SELECT T1.[ID]

                ,ROW_NUMBER( ) OVER (PARTITION BY T1.ParentID ORDER BY T1.[Name]) AS TreeOrder

            FROM YourTable T1

        ) D

        ON T.[ID] = D.[ID]

If you do not mind the slight overhead of returning the total on each row, something like the following may work:

EXEC sp_executesql N'

        WITH YourQuery

        (

                ACCOUNT_NAME

                ,DUNS_ULTIMATE_NUMBER

                ,DUNS_NAME_OVERRIDE

                ,CHANNEL_ID

                ,AREA_NAME

                ,COUNTRY

                ,GLOBAL_MARKET_SEGMENT_NAME

                ,GLOBAL_ACCOUNT_COORDINATOR

                ,GLOBAL_CLIENT_SERVICE_PARTNER

                ,PRIORITY_STATUS

                ,PRIORITY

                ,CREATE_DATE

                ,UPDATE_DATE

                ,ACCOUNT_ID

                ,CHANNEL_NAME

                ,AREA_CODE

                ,ACCOUNT_ROLE_GAC

                ,COUNTRY_3CHAR

                ,GLOBAL_MARKET_SEGMENT_CODE

        )

        AS

        (

            SELECT

                Ac.ACCOUNT_NAME

                ,Ac.DUNS_ULTIMATE_NUMBER

                ,Ac.DUNS_NAME_OVERRIDE

                ,Ac.CHANNEL_ID

                ,Ar.AREA_DESCR

                ,T.COUNTRY_DESCR

                ,G.GLOBAL_MARKET_SEGMENT_NAME

                ,Ac.ACCOUNT_ROLE_GAC

                ,Ac.ACCOUNT_ROLE_GCSP

                ,Ac.PRIORITY_STATUS

                ,R.NAME_ENGLISH

                ,Ac.CREATE_DATE

                ,Ac.UPDATE_DATE

                ,Ac.ACCOUNT_ID

                ,Ac.CHANNEL_NAME

                ,Ac.AREA_CODE

                ,Ac.ACCOUNT_ROLE_GAC

                ,Ac.COUNTRY_3CHAR

                ,Ac.GLOBAL_MARKET_SEGMENT_CODE

            FROM Account Ac

                LEFT...

Try joining to a derived table.

SELECT T.*

FROM YourTable T

    JOIN (

            SELECT T1.Truck, MAX(T1.StatusDateTime) AS StatusDateTime

            FROM YourTable T1

            GROUP T1.Truck

        ) D

        ON...