This should work for you

WITH CTE1 AS (
	SELECT [ROW_NUMBER],VALUE,
		   CASE WHEN LAG(VALUE) OVER(ORDER BY [ROW_NUMBER]) IS NULL THEN 1 ELSE 0 END AS ISSTART
	FROM T_REST
),
CTE2...

To make this dynamic have a look here

https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs

If I understand correctly, this should work for you

SELECT Tenor,
       COUNT(CASE WHEN ABS(ColA) > 0 THEN ColA END) + COUNT(CASE WHEN...

This is the standard SQL 'IS DISTINCT FROM' operator, not current implemented in T-SQL (yet!)

Interesting article below mentions it is implemented in the query processor

https://www.sql.kiwi/2011/06/undocumented-query-plans-equality-comparisons.html

The json isn't valid around "Website": "www.test.com": {

declare @json nvarchar(max) = '
{
    "id": "9999",
    "position": 1,
    "visible": true,
...

I think this is what you're after

declare @json nvarchar(max) = '
{"data": [{"id": "123456", "first_name": "John", "last_name": "Doe", "email": "jdoe@gmail.com", "phone_number": "333-333-3333", "href": "http://jdoe.com", "custom_fields": {"1": "John Doe...

• This reply was modified 2 months, 2 weeks ago by  Mark Cowne.
• This reply was modified 2 months, 2 weeks ago by  Mark Cowne.

This should work for you

Select b.id, b.title, b.nickname, b.href
from openjson(@json)
with ([data] nvarchar(max) '$.data' as json) a
cross apply openjson(a.[data])
with (id   int   '$.id',
  ...

Try this

SELECT v.n2.value('(@*:ObjectName)[1]','varchar(30)') AS Name,
	   p.n1.value('(@*:ObjectName)[1]','varchar(30)') AS Scope
FROM #SSISTest st
CROSS APPLY st.PackageXML.nodes('//*:Executable') AS p(n1)
CROSS APPLY p.n1.nodes('*:Variables/*:Variable') as v(n2);

This is as near as I can get. You'll have to add '<?xml version="1.0" encoding="UTF-8"?>' manually. Also you've defined the date as a varchar, it should be a date or...

Looks like a gaps and islands problem

with cte1 as (
select *,case when TimeSlotAvailable = LAG(TimeSlotAvailable) over(partition by CwsCalID order by StartDateTime) then 0 else 1 end as...

Assuming you're using SQL Server version 2005 or later, this should work for you

SELECT Store,Article,Family,[Article Level Qty],
       SUM([Article Level Qty]) OVER(PARTITION...

Here's a simpler alternative to your query

select e.Clinic,ca.Area,
       sum(case when ca.AreaVal = 'A' then 1 else 0 end) as A,
 ...

WITH CTE AS (
SELECT OrderDetailsID, OrderID, Active, CreatedDate,
       ROW_NUMBER() OVER(PARTITION BY OrderID ORDER BY OrderDetailsID DESC) AS rn
FROM OrderDetails
)
UPDATE CTE
 ...

This is close to what you want, but I can't see where 'Management Unit' comes from - is there data missing? Also the 'Management Unit Default' column doesn't make sense...

Not really clear what you're after but see if this helps. Obviously this is for a fixed number of levels only, a variable number of levels will need dynamic SQL.