This approach will work, but it may not be the most efficient. It uses XML concatenation to create a comma-separated list of the ingredients and then checks for two...

Phil Parkin (12/22/2016)

---

IF OBJECT_ID('tempdb..#tmp', 'U') IS NOT NULL

DROP TABLE #tmp;

CREATE TABLE #tmp

(

FormulaId CHAR(2)

, IngredientId CHAR(2)

);

INSERT #tmp

...

Not necessarily. The problem is when a field from an outer table is used in a JOIN condition (or WHERE clause) without taking into account that it might be...

Thom A (12/22/2016)

---

Fully Windowed, and VALID included.

WITH CTE (EVENT_ID, EVENT_RANK, MIN_AGE, MAX_AGE, C_MIN, C_MAX) AS

(

SELECT A.EVENT_ID,

A.EVENT_RANK,

A.MIN_AGE,

...

Archeopteryx

Something along the lines of the following.

SELECT c.value('@name', 'VARCHAR(50)')

FROM @doc.nodes('/xs:schema/xs:element/xs:complexType/xs:sequence/xs:element[2]/xs:complexType/xs:sequence/xs:element[1]/xs:complexType/xs:sequence/xs:element') T(c)

You may need to use a CROSS APPLY if the XML is stored in a column instead of a variable....

whenriksen (12/21/2016)

---

I'll save Celko the effort here. NULL is not a valid action type. It is messing up your results because you...

You're using a subquery to get the rank when you should be using ROW_NUMBER().

/*ranking*/

SELECT compmcd.hedismeasure,

compmcd.measureid,

compmcd.competitor,

compmcd.market,

compmcd.prodabbrev,

compmcd.hedisrate,

ROW_NUMBER() OVER(PARTITION BY measureid, market, prodabbrev ORDER BY hedisrate DESC, measureid DESC ) AS rn

FROM...

Yes, it looks like...

sgmunson (12/20/2016)

---

Just another way to tackle it, using CROSS APPLY:

-- code snipped to pinpoint this section

SELECT D.*,

CAST(D.[Date] AS date) AS DATE_ONLY,

CASE

WHEN CAST(LEAD(D.[Date], 1, '9999-12-31') OVER(ORDER BY D.[Date]) AS date) =...

Jason A. Long (12/20/2016)

---

This is based on an old(ish) Itzik Ben-Gan solution... The Last non NULL Puzzle

IF OBJECT_ID('tempdb..#Data', 'U') IS NOT NULL

DROP TABLE #Data;

CREATE TABLE #Data (

ID VARCHAR(20),

SEQ INT,

Date...

Most approaches that I've seen collapse the ranges. That is, they don't retain a record with a zero length. Is there a particular reason that you need to...

Your DateKey is defined as INT. Don't provide your sample data as CHAR, because it forces an implicit conversion.

You say that your data contains NULLs which are causing you...

The query I gave you will produce a row with NULL values for the grouped fields. Since your results DO NOT...

I suspect what you want is

GROUP BY GROUPING SETS((EDI_Batch_Number, Reference_2),())

which will give you groups by EDI_Batch_Number/Reference and then a grand total.

What you currently have gives your one group on...