Dave62 (5/10/2013)

---

Nice question! I had to guess the correct answer. According to the script below the correct answer may not be so simple.

-- Code from the QotD

select *...

Two possibilities, I'm sure there are more

SELECT 1 AS col

UNION ALL

SELECT 2

UNION ALL

SELECT 3

SELECT col

FROM (VALUES (1), (2), (3)) x(col)

I get 3 rows not 4, maybe I'm missing something.

DECLARE @Start INT = 395;

WITH Recur AS (

SELECT [ColA], [ColB], [ColC], [ColD], [ColE], [ColF]

FROM [dbo].[tmpTable]

WHERE [ColA] = @Start

...

kapil_kk (5/9/2013)

---

Mark-101232 (5/9/2013)

---

WITH CTE AS (

SELECT ID,VoucherNo,Status,VoucherType,

ROW_NUMBER() OVER(ORDER BY ID) AS rn1,

ROW_NUMBER() OVER(PARTITION BY Status ORDER...

WITH CTE AS (

SELECT ID,VoucherNo,Status,VoucherType,

ROW_NUMBER() OVER(ORDER BY ID) AS rn1,

ROW_NUMBER() OVER(PARTITION BY Status ORDER BY ID)...

Quick 'n dirty solution

SELECT *,

RxReferenceNumber = MyAlias.ConvertedXML.value(N'(/*:Message/*:Body/*:RefillRequest/*:RxReferenceNumber/text()) [1]', 'NVARCHAR(256)')

FROM (SELECT ID,CONVERT(xml,message)As ConvertedXML FROM [surescripts_msg_import]

)MyAlias

Works but I suspect there's a better way (using CROSS APPLY?)

WITH CTE AS (

SELECT *,

ROW_NUMBER() OVER(PARTITION BY fk ORDER BY CASE WHEN col1...

Depending on your requirements, you can do this in one statement

ALTER TABLE Schema1.Table1 ADD Column1 int NOT NULL DEFAULT(-1)

Run the same query without the aggregate (i.e. without the SUM) as below, you'll see that each row has either a zero or a one

calculated for it. All we...

Select sum(case when Loadflag='update' then 1 else 0 end) as 'Records Updated',

sum(case when Loadflag is null then 1 else 0 end) as 'New...

Not sure of your table structure, but this give the correct results

DECLARE @tbl TABLE(Code1 VARCHAR(10), Code2 VARCHAR(10), Field1 INT, Field2 INT, Field3 INT)

INSERT INTO @tbl(Code1,Code2,Field1,Field2,Field3)

VALUES ('Rec1_Code1','Rec1_Code2',11,22,33),

('Rec2_Code1','Rec2_Code2',1111,2222,3333);

SELECT

Code1...

Evil Kraig F (4/30/2013)

---

Appreciate the time Mark. Would you be willing to rerun with the harness in the fourth post? After...

Try this

DECLARE @t TABLE(Parent VARCHAR(10), Child VARCHAR(10))

INSERT INTO @t(Parent,Child)

VALUES

('300001','110081'),

('300001','102157'),

('300001','102158'),

('300001','102159'),

('110081','101000'),

('110081','101504'),

('110081','102129');

WITH Recur AS (

SELECT Parent,Child

FROM @t t

WHERE NOT EXISTS(SELECT * FROM @t t2 WHERE t2.Child=t.Parent)

UNION ALL

SELECT r.Parent,t.Child

...

Use ROW_NUMBER() OVER(PARTITION BY OM.ord_route ORDER BY ...)

Results from 2012 SP1

Cascade Test131824601220

CASE Test123823501150

Values Test6011016503