SELECT op, SUM(CASE WHEN dupes > 1 THEN 1 ELSE 0 END)

FROM (

SELECT op, imei, dupes = COUNT(*)

FROM #mastertab

GROUP BY op, imei

) d

GROUP BY op

Use an inline tally table as a row generator. Generate the week-based rows and the month-based rows separately:

DROP TABLE #dates

CREATE TABLE #dates (date_debut DATE,date_end DATE)

DECLARE @date_start DATE, @date_end DATE,...

John Mitchell-245523 (7/22/2013)

---

You have a date stored as varchar or similar and you want to check it's a valid date?

Two options - the first being vastly more preferable:

(1) Don't store...

Can you provide more information about the context please Ananth? For instance, are you designing a validation process for an import of a text-typed date?

It does return results:

ChrisM@Work...

There's scope for improvement but without ddl/execution plan it's a long shot. Try this:

SELECT

t.TradeId,

CASE WHEN x.OtpTradeId IS NULL THEN 'Y' Else 'N' End As 'TCM'

FROM Trade t

OUTER APPLY...

It creates a CTE consisting of a single datetime column - a very simple sample data set of your docket table.

Can I have some too? 🙂

What was the problem again? 😀

You're renamed the column to [Earn Code] in the inner select.

Cool, now post this code into the same ssms window underneath the rest of the code and run the lot as one batch:

SELECT *

FROM dbo.DocketTB

CROSS APPLY (

SELECT MondayLastWeek =...

Steve-3_5_7_9 (7/19/2013)

---

Of course you can....

Curious...ok, paste this whole batch into a new ssms window and execute:

DECLARE @RangeStart DATETIME, @RangeEnd DATETIME

SELECT

@RangeStart = x1.MondayLastWeek,

@RangeEnd = DATEADD(DD,7,x1.MondayLastWeek)

FROM (

SELECT MondayLastWeek = DATEADD(hh,7,CAST(CAST(DATEADD(DD,-((DATEDIFF(DD,0,GETDATE())%7)+7),GETDATE()) AS DATE) AS DATETIME))

)...

Like this?

With subCA As (

Select distinct

OTPTradeId,

ConfoAuditSenderRef

from ConfirmationAudit

where ConfoAuditSenderRef like 'HBEUM%'

),

TCM As (

Select distinct

OTPTradeID

from subCA

union ALL

select TradeId

from subCA

inner join ConfirmationSent

on...

Check:

SELECT TOP 100 Docket_EngFinish

FROM DocketTB

ORDER BY Docket_EngFinish DESC