SELECT a.ITEM, b.FACTORY_NO, b.ORDERQTY, c.LINE_INFO, c.CARD_NUMBER
FROM OPENJSON (@json, '$.data')
WITH (ITEM VARCHAR(10) '$.ITEM',
      FACTORY_NO NVARCHAR(MAX) '$.FACTORY_NO' as json,
  DETAILS NVARCHAR(MAX) '$.DETAILS' as...

Try this

SELECT   FeatureName,displayorder 
  FROM   [ExtractReports].[dbo].[FeaturesOrder]  with(nolock)
  group by FeatureName,displayorder
  ORDER BY  displayorder ASC,SUBSTRING(FeatureName,CHARINDEX(' ',FeatureName),511) asc,FeatureName

No sample data so this is untested.

WITH Recur AS (
SELECT PL.hMy hSupervisor, 
   PL.sCode sSuperCode, 
   PL.sDept,
   PL.hMy,
   CAST(1...

Try this

SELECT n.x.value('(../../page_id)[1]','varchar(1000)') page_id,
       n.x.value('(../../content/html)[1]','varchar(max)') content,
       n.x.value('.','varchar(1000)') title
FROM @xmlDocument.nodes('/pages/page/properties/property[@key="title"]') n(x);

I think it's just a matter of adding this to the end of your query

ORDER BY SubQuery.department,SubQuery.myrank

I would replace the recursive CTE 'DateRangeEndDays' with a look up against a calendar table

DateRangeEndDays (StudentId, EndDay) AS
SELECT a.StudentId,c.CalendarDate
FROM FirstLastAbsentDates a
INNER JOIN MyCalendar c ON c.CalendarDate BETWEEN...

I think this will work for you

with cteOrders as (
select OrderId, ItemNumber, OrderAmount,RowNum,
   sum(OrderAmount) over(partition by ItemNumber order by RowNum) - OrderAmount + 1 as...

• This reply was modified 3 years, 8 months ago by  Mark Cowne.

You're welcome!

Maybe this?

SELECT o.value AS origin_address,
   d.value AS destination_address,
       JSON_VALUE(e.value,N'$.distance.text') AS [dist_text],
       JSON_VALUE(e.value,N'$.distance.value') AS...

Here's a couple of queries that should help

SELECT JSON_VALUE(@json,N'$.destination_addresses[0]') as destination_addresses,
       JSON_VALUE(@json,N'$.origin_addresses[0]') as origin_addresses;


SELECT e.*
FROM OPENJSON(@json, '$.rows') r
CROSS APPLY OPENJSON(r.value, '$.elements')...

SELECT rn,dur,21-sum(dur) over(order by rn desc) as calculated_col
FROM sample_data
order by rn;

• This reply was modified 3 years, 9 months ago by  Mark Cowne.

Not totally clear what you want, but this gives you counts from your XML

declare @x xml = '
<Employees>
<Person>
<ID>1000</ID>
<minor>yes</minor>
</Person>
<Person>
<ID>1001</ID>
<minor>yes</minor>
</Person>
<Person>
<ID>1002</ID>
<minor>yes</minor>
</Person>
<Person>
<ID>1003</ID>
<minor>no</minor>
</Person>
</Employees>
';

select @x.query('
 for $value in distinct-values(/Employees/Person/minor)
  let $count :=...

• This reply was modified 3 years, 9 months ago by  Mark Cowne. Reason: Typo

You should be able to parse this using OPENJSON

declare @data table(instance varchar(20),array nvarchar(max));
insert into @data(instance,array)
values('server1.com',N'[[1613347200,"7"],[1613347205,"6"],[1613347210,"7"]]'),
      ('server2.com',N'[[1613347200,"5"],[1613347205,"8"],[1613347210,"7"]]');

select d.instance,
   json_value(j.value, '$[0]') as...

• This reply was modified 3 years, 9 months ago by  Mark Cowne. Reason: Simplified code

This should work for you

select j.JobNumber,j.JobType,j.ProductType,j.Date,ca.DateOfCheckBeforeInstallation,ca.CheckDoneBy
from JobsTable j
cross apply(select top 1 p.DateOfCheck,p.CheckDoneBy
            from ProductChecks p
where j.JobType...

See if this works

SELECT N.usernode.value('@id','varchar(10)') AS id
,N.usernode.value('(add[@key="email"])[1]/@value', 'varchar(30)') AS email
FROM (values (@xml)) X(xmlcol)
CROSS APPLY X.xmlcol.nodes(N'/Users/User') AS N (usernode);