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

Try converting to bigint first.

SELECT CAST(CAST(9.37123e+009 AS bigint) AS nvarchar(50))

IF OBJECT_ID('tempdb..#TableExist') IS NOT NULL

    TRUNCATE TABLE #TableExist

I am not sure what "where exp_dt like '%9999%'" means but I suspect it is causing the problem.

If you are looking for dummy dates in the year 9999, try WHERE...

Two common ways of achieving this are:

1. Indexed View

Create a View which includes WHERE UserName IS NOT NULL and put an unique index on the view.

2. Calculated Column

Create a calculated...

Assuming e.empid should be o.empid, try something like the following:

IF EXISTS (

        SELECT *

        FROM tblemp o1

        WHERE EXISTS (

                SELECT *

                FROM indicator I1

                WHERE o1.empid=I1.empid

                    AND I1.userroleid=4

            )

            AND EXISTS (

                SELECT *

                FROM OPENXML (@empIDdoc, '/emps/Item', 1) WITH(ChangeToemp...