Can anyone join in?

Here's an efficient dynamic SQL version and a rCTE version. The rCTE version updates a million rows in about 30 seconds, the dynamic SQL version appears to...

-- To obtain accurate rowcounts from the two tables, you need to aggregate them separately

;WITH Matrix (listcode,[year]) AS (

(SELECT DISTINCT listcode FROM NamesTest) as A

CROSS JOIN

(SELECT DISTINCT [year] FROM NamesTest...

UPDATE od

SET value_exists = 1

FROM LII od

INNER JOIN VPL sl

ON sl.it LIKE '%' + od.value + '%'

WHERE od.tn = 'VPL'

AND od.cn = 'it'

AND od.opr =...

Welsh Corgi (1/19/2013)

---

I'm haveing trouble with a simple CAST to VARCHAR Statement.

SELECT

CASE WHEN CAST(LEFT(Customer.STARTDATE, 2 AS VARCHAR(2)))

...

Have you considered using a more efficient inline table-valued function instead? Here are a couple of ideas:

DECLARE @amount MONEY

SET @amount = -3211987654321.10 -- [-3,210,987,654,321.10]

---------------------------------------------------------------------------------------

SELECT

FormattedAmount = CASE SIGN(@amount) WHEN -1...

Like this?

--===== Create the table

CREATE TABLE #T_BMAL(

[DocLinkID] [nvarchar](50)...

Strip out the noise, and introduce another little statement.

SELECT [An Aggregate] = SUM(1)

FROM (SELECT n = 1) d

WHERE 1=0

SELECT [An Aggregate] = COUNT(*)

FROM (SELECT n = 1) d

WHERE 1=0

SELECT [Something]...

-- you only need to read the SalesGLLink table once:

INSERT INTO #TempSalesGLLink(

StoreId, LocId, SalesGLLinkUniqueNum, SalesTypeCd, LineCd,

VehSaleAcct, VehSaleDept, TradeACVAcct, TradeACVDept, OverAllowAcct, OverAllowDept, LoanDiscountAcct, LoanDiscountDept,

DueFromFinAcct, DueFromFinDept, CashDownAcct, CashDownDept, CostGoodsAcct, CostGoodsDept, InvSoldDept,...

HildaJ (1/17/2013)

---

Hard to maintain it's really not an option when it comes to working with databases, you got to do what you got to do. Data entered by users...

I'd very much like to see your simpler version.

-- Convert to an inline table-valued function: more efficient.

-- minimise the work done to identify a date within a string.

DECLARE @InputString NVARCHAR(500) = 'ABC01/01/2013XYZ'

SELECT

DateString = CASE WHEN ISDATE(PatternString) =...

It looks ok to me, apart from UNION picking up the 40th bin. When this is done, the query is quite efficient:

;with cteHistogram (N, StartBin, EndBin, TotalCount)

as

(

select

N,

@min-2+(@interval*(N-1)) StartBin,...

No but one of my programming buddies went AWOL for six months last year - and resurfaced in Michigan married to...

sabeer.mvit (1/16/2013)

---

The ccount is not obtain directly from table as done here.Am rather using the View written as below

With compTable(cid,ccid,ccount)

as

AS

(

select...

Sorry, my mistake:

SELECT

y.MonthNo,

y.[Year],

y.[Month],

COUNT(doclinkid) as Registered, -- is this correct?

COUNT(DISTINCT doclinkid) as Registered, -- OR is this correct?

SUM(x.Finalised) as Finalised,

COUNT(*) - SUM(x.Finalised) AS [Remaining]

FROM REPWPK.T_BMAL

CROSS APPLY...