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...

I think the SELECT MyTab.record_id in the subquery is going to the select the record_id from the main table, thus returning all rows. Changing the subquery to SELECT dbo.MyTab.record_id will...

The problem could be something to do with the collation sequence.

The comparison of varchars with Windows collation sequences, eg Latin1_General_CI_AS, is considerably slower than the comparison of varchars with SQL...

You need to put the extra bit in the where clause into a CASE. Something like:

SELECT P.provider_ID

    ,P.Provider_Name

    ,F.Form_Year

    ,F.Form_Name

    ,P.Consortia_ID

    ,P.LEA_ID

    ,COUNT(*) AS Total_Quarter

    ,COUNT(DISTINCT U.[User_id]) AS Total_Quarter_Dist

    ,COUNT(DISTINCT CASE

            WHEN (DATEPART(hh, L.LoggedIn) < 8 AND DATEPART(hh,...