sku370870 (3/1/2012)

---

Wow, thanks very much. Yes, that's exactly what I need.

Seeing your code makes me realise what a novice I am at this lark.

Can I ask ...

Using that 'With' statement,...

It's the syntax used to delete using a subquery.

Again, nope. You could use FOR XML to generate your SQL dynamically then execute it.

e.g.

DECLARE @sql AS NVARCHAR(MAX);

SELECT @sql...

Lowell (2/29/2012)

---

I am so stupid;

I had no idea I could go to Object Explorer>>Specific Database>> Programmability>>Assemblies and script them out.

I'd always take a model and copy/pasted all the pieces into...

No, because the @YEARMONTH is an INT. So in my example, 20000101, represents the 1st Jan 2000, but when we minus 93 from the @YEARMONTH we get 20000008 which...

Not sure I'd go so far as to say I hate it, but I do think it's a bit too graphic for a professional forum.

Lowell (2/29/2012)

---

Cadavre my friend how do you script your clr to be the binary string like that?

what's the trick?

i've only done it manually so far, nd it's a pain....load...

You've not shown where @Yearmonth is declared or assigned. Nor have you shown DDL for the tables involved.

WHERE (RTRIM(YEAR(A.DOCDATE)) + RIGHT('0' + CONVERT(VARCHAR, MONTH(A.DOCDATE)), 2) <= @YEARMONTH) AND

(RTRIM(YEAR(A.DOCDATE)) +...

It depends on the context.

Fairly certain that you won't have the same issue with my CLR.

As I see...

Sample data:-

SELECT date, cnt, Empcode

INTO QMaster

FROM (VALUES(N'2/2/2011', 1, 101),(N'2/2/2011', 1, 102),

(N'2/4/2011', 1, 103),(N'2/4/2011', 1, 104),(N'2/6/2011', 1, 105),

(N'2/6/2011', 1, 106),(N'2/8/2011', 1, 107),(N'2/9/2011', 1, 108),

(N'2/10/2011', 1, 109),(N'2/11/2011', 1, 110),(N'2/11/2011', 1, 111),

(N'2/24/2011', 1,...

You could create your scripts dynamically into a variable then use sp_executesql...

You can block all avatars in control panel -> forum settings.

fahey.jonathan (2/24/2012)

---

I agree that your version is faster, but not by the margin indicated. Most of the difference is created by the ORDER BY clause in "my" code, which...

OK, I'll play. . .

Your code:

SET STATISTICS IO ON

SET STATISTICS TIME ON

;WITH t1(N) AS (SELECT 1 UNION ALL SELECT 1),

t2(N) AS...