Stefan Krzywicki (6/6/2014)

---

I don't know who to contact since Steve is on vacation, but I got the daily newsletter this morning and clicked on the link for "Is Powershell Worth...

WITH cte (locationid,flag,RID) AS (

SELECT locationid,flag,ROW_NUMBER() OVER (

PARTITION BY locationid

ORDER BY ISNULL(createddate,modifieddate) DESC)

FROM #templocationhistory)

UPDATE l

SET l.maintainancefalg = ISNULL(CAST(cte.flag as varchar(1)),'')

FROM #templocation l

LEFT JOIN cte ON cte.locationid = l.locationid AND...

Michael Meierruth (6/3/2014)

---

zafarthesultan (6/2/2014)

---

The problem with my data is it comes as a result of a lot of calculation in a stored procedure. In the end there is only one...

The only way I can think of to process data with split row delimiter is to BULK load the data as CLOB using OPENROWSET and then parsing the data.

Without knowing...

If you want your results based on insert order and you add IDENTITY column you would end up with something like this

CREATE TABLE #sampledata

(

RowID int IDENTITY(1,1),

userId int,

BaseYear int,

TotalSales float

)

INSERT...

And quite right too! 🙂

Luis Cazares (5/30/2014)

---

Note that your sample data didn't gave the exact results because you used UNION instead of UNION ALL, so you were losing duplicates. You should know as well...

Sean Lange (5/30/2014)

---

Tables have no order if it is a heap or not. A clustered index does not provide...

Your results indicate that you want first and last by insert order. the table is a heap and therefore has no order.

You will need to add an IDENTITY column...

Because you are creating dynamic sql (why in this case with a fixed name?) you need to add extra quotes to get quotes in the query, like this

SET @temp_xml1 =...

Tallboy (5/28/2014)

---

In a perfect world they will but in reality users a rushing and type a slightly different date, so I would prefer a kind if catch all between 2...

SELECTt1.StaffID,

SUM(t2.ALHours) AS [ALHours],

SUM(t3.SLHours) AS [SLHours]

FROMtable1 t1

LEFT JOIN table2 t2

ON t2.StaffID = t1.StaffID

AND t2.WeekendingDate = t1.WeekendingDate

LEFT JOIN table3 t2

ON t3.StaffID = t1.StaffID

AND t3.WeekendingDate = t1.WeekendingDate

WHEREt1.StaffID = @StaffID

ANDt1.WeekendingDate...

Tallboy (5/27/2014)

---

What I want out is three columns as follows;

customerID , Max(4WeekEndDate), Sum(WorkedHours)

123456 , 30/1/14 ...

;WITH cte (CustomerID,WeekEndingDate,WorkedHours,WeekNo) AS (

SELECT CustomerID,WeekEndingDate,WorkedHours,CEILING(ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY WeekEndingDate) / 4.0)

FROM )

SELECT CustomerID,MAX(WeekEndingDate),SUM(WorkedHours)

FROM cte

GROUP BY CustomerID,WeekNo

ORDER BY CustomerID ASC,2 ASC

Also if your data contains duplicate invoice dates as shown in the result set in your first post then you will need to use DENSE_RANK not ROW_NUMBER.

Plus your two requirements...