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

DECLARE @t TABLE

(

    PersonID int NOT NULL

    ,FirstName varchar(20) NOT NULL

    ,Surname varchar(20) NOT NULL

    ,Added datetime NOT NULL

)

INSERT INTO @t

SELECT 1234, 'John', 'Doe', '20071201 14:00' UNION ALL

SELECT 1234, 'John', 'Doe', '20071201 13:00' UNION...

In the case of a LEFT JOIN an INNER JOIN is done first, then any rows in the left table not in the result set are added with NULLs in...

If ROLEID is in glb_Item_Rights then you should put it into the ON clause of the join otherwise ROLEIDs of NULL will be removed when ROLEID = 11 is evaluated...

Try:

IF EXISTS (

    SELECT *

    FROM Indicator I

    WHERE EXISTS (

        SELECT *

        FROM jobs J

        WHERE J.jobid = I.jobid

            AND EXISTS (

                SELECT *

                FROM OPENXML (@empDdoc, '/emps/Item', 1) WITH (ChangeToemp int, emp int) AS X

                WHERE X.emp =...

SELECT e.empID, empName, lc.CountyName

FROM emp e

    JOIN (

            SELECT oc.empid

                -- You have to decide which county you want.

                -- Just taking the lowest ID here

                ,MIN(oc.countyid) AS countyid

            FROM OfficeCounty oc

            GROUP BY oc.empid

        ) D

        ON...