WITH CTE AS (

SELECT [Index],Value,Stamp,

ROW_NUMBER() OVER(ORDER BY [Index])AS rn1,

ROW_NUMBER() OVER(PARTITION BY Value ORDER BY [Index])AS rn2

FROM...

Another way using ROW_NUMBER

WITH OrderedCosts AS (

SELECT ProductCode,Cost,

ROW_NUMBER() OVER(PARTITION BY ProductCode ORDER BY Cost) As rn

FROM ProductCosts)

SELECT a.ProductCode, a.InvoiceQty * b.Cost as TotalCost

FROM...

Another guess...

Select count(CASE WHEN RoType=0 THEN RoType END) count1,

count(CASE WHEN RoType=1 THEN RoType END) count2 FROM (

...

Maybe this?

Select RoType, count(*) count2 FROM (

SELECT DISTINCT tro.regno , tro.Color ,tRO.RoType

FROM tle INNER JOIN

tROD ON tle.Field = tROD.InvoiceId INNER JOIN

tRO ON tROD.ROID = tRO.ROID

Where tROD.JobType =...

SET @q = ((SELECT 'WHERE ' + T.c.value('@id', 'nvarchar') + ' > b'

FROM...

Also google for "sql nullbuster"

You need a "WITH XMLNAMESPACES" clause

declare @v_external_key_value varchar(100);

WITH XMLNAMESPACES ('urn:oasis:names:tc:SAML:1.0:assertion' as p1)

SELECT @v_external_key_value = tab.col.value('p1:AttributeValue[1]','varchar(100)')

FROM @p_xml.nodes('p1:Attribute') as tab(col)

select @v_external_key_value;

Also this

SELECT

I.value('(./text())[1]','int') AS Item

FROM

@tableToHoldXML

...

Try this, assumes value is an exact multiple of split_value

DECLARE @table1 TABLE(id INT,prodname VARCHAR(10), value INT, split_value INT)

INSERT INTO @table1(id,prodname,value,split_value)

SELECT 1, 'prod1', 1000, 500 UNION ALL

SELECT 2, 'prod2', 3000,...

SELECT d.EmployeeID,d.Name,d.Department,d.Manager,

STUFF(((SELECT ', '+e.EmailAddress AS "text()"

FROM EmployeeEmailAddresses e

...

You can sum the logs. A SQLCLR aggregate would work as well.

DECLARE @t TABLE(Row INT, Qty INT)

INSERT INTO @t(Row,Qty)

SELECT 1,1 UNION ALL

SELECT 2,6 UNION ALL

SELECT 3,1020

SELECT EXP(SUM(LOG(Qty)))

FROM @t

You'll have to...

Without sample data it's hard to tell, but also

SessionSlotDate = DATEADD(dd,1,SessionSlotDate)

is always false - typo?

Sean Lange (8/10/2011)

---

I knew somebody with more active brain cells than I can put together today would hop in. Thanks!! Definitely use the tally approach. It is WAY better than...

Another way, this one using a tally table

SELECT t.ID,

DATEADD(wk,n.N-1,t.StartDate) AS NewStartDate

FROM #MyCrazyDateThing t

INNER JOIN dbo.Tally n ON n.N BETWEEN 1 AND LEN(t.WeekPattern)

...

Have you tried something like this?

SELECT MemberID,

MAX(CASE WHEN PhoneTypeID=1 THEN PhoneNumber END) AS HomePhone,

MAX(CASE WHEN PhoneTypeID=2...