You can do this get all of the element and attribute names

SELECT DISTINCT c.s.value('local-name(.)','VARCHAR(64)') AS ElementName
FROM #XML x
CROSS APPLY x.XMLColumn.nodes('//*') AS c(s);

SELECT...

Use LAG instead

SELECT ID, S_CODE, SnapshotDateTime,
   CASE WHEN S_CODE = LAG(S_CODE) OVER(PARTITION BY ID ORDER BY SnapshotDateTime) THEN 'Same' ELSE 'S_CODE changed' END
FROM DataChanges

That's because you've now got a SN_Status of 'PARTIALLY' that the query didn't cater for

WITH Src AS (
SELECT ServiceNumber, Identifier, CreatedDate, CompletedDate, SN_Type, SN_Status,
...

Here's another to try, this works with your sample data

WITH Src AS (
SELECT ServiceNumber, Identifier, CreatedDate, CompletedDate, SN_Type, SN_Status,
   ROW_NUMBER() OVER(PARTITION BY Identifier ORDER...

Not totally clear what you want but see if this helps

WITH OrderedSrc AS (
SELECT Site,C_Date,ID,Series,LongRun,Flag,
   ROW_NUMBER() OVER(PARTITION BY Site ORDER...

Here's a 2008 version

WITH Starts AS (
SELECT IDClient,IDHAAndD,LocationName,StartDate,IDStay,
   ROW_NUMBER() OVER(PARTITION BY IDClient,IDHAAndD,LocationName ORDER BY StartDate) AS rn
FROM #testtable t
...

See if this helps

WITH C1 AS (
SELECT IDHAAndD,IDClient,IDStay,LocationName,StartDate,EndDate,
 CASE WHEN StartDate = LAG(EndDate) OVER(PARTITION BY IDClient,IDHAAndD,LocationName ORDER BY StartDate, EndDate) THEN 0 ELSE 1 END...

Try this

SELECT @ColumnsForPivot = CAST((SELECT STUFF((SELECT ',[' + CAST(fldProductCode AS VARCHAR(255)) + ']'
           FROM tlkp_Product
                                ORDER BY fldProductCode FOR XML PATH(''),TYPE).value('./text()[1]','VARCHAR(MAX)'),1,1,'')) AS VARCHAR(MAX))


There's a nasty gotcha in MySQL - UPDATE uses the *current* value of columns so that

UPDATE x SET c1 = c1 + 1,
     c2 =...

See if this helps

WITH Data AS (
SELECT 'Your user id' AS UserName,
   'Your password' AS Password,
   '25/01/2017' AS TransDate,
   '09:00:00' AS...

Just use an explicit cast, so change

SUM(b.ALLOCATION_PCT * s.RATIO) AS ALLOCATION_PCT

to

CAST(SUM(b.ALLOCATION_PCT * s.RATIO) AS DECIMAL(5,2)) AS ALLOCATION_PCT

Hi,

Lots of sample data (great!) but no expected results so I've had to guess.

WITH Src AS (
SELECT BSKEY ,PROVIDER_CODE ,TOTAL_VALUE,TOTAL_VALUE / SUM(TOTAL_VALUE) OVER(PARTITION...

i