Viewing 15 posts - 16 through 30 (of 1,416 total)
WITH Ranges AS (
SELECT (RecordId - NumDays + 1) As Start, RecordId
FROM #test
WHERE NumDays > 0)
UPDATE #test SET AmountAll = B.AmountAll
FROM #test A
JOIN (SELECT r.RecordId, SUM(t.Amount)...
August 18, 2021 at 8:50 am
This should work for you
select x.r.value('Index[1]','int') as ChannelID,
x.r.value('(SensorScaling/OutUnits)[1]','varchar(20)') as [SI Units]
from @DataSheetXML.nodes('Datasheet/ChannelList/ChannelDefinition[@IsEnableable="True"]') x(r);
July 20, 2021 at 2:50 pm
See if this helps
select pd.a.value('let $a := . return 1 + count(../ProbeDefinition[. << $a])', 'int') AS MasterRecordNumber,
cl.b.value('.','int') as ChannelListValue
from @DataSheetXML.nodes('Datasheet/ProbeList/ProbeDefinition')...
July 9, 2021 at 2:47 pm
with cte as (
select ID, TS, FLAG,
case when lag(flag) over(partition by id order by ID,TS) = flag then 0 else...
June 10, 2021 at 8:48 am
SELECT m.displayid, STRING_AGG(m.promo_id,';') AS Concat_Promo_Id, STRING_AGG(h.promogroup,';') AS Concat_promogroup
FROM Product_Main m
LEFT OUTER JOIN Product_Helper h ON h.displayid = m.displayid AND h.promo_id = m.promo_id
GROUP BY m.displayid;
June 3, 2021 at 7:53 am
Replace
Data.Col.value('(./PartyID)[1]', 'int') As Party_ID
with
Data.Col.value('(../PartyID)[1]', 'int') As Party_ID
May 3, 2021 at 2:35 pm
select t1.PartFamilyId, t1.FamilyStatus + isnull('|' + ca.FamilyStatus,'') as FamilyStatus
from #partsFamily t1
outer apply (select 'NULL' from #partsFamily t2 where t2.PartFamilyId = t1.PartFamilyId and t2.FamilyStatus is null) ca(FamilyStatus)
where t1.FamilyStatus...
April 28, 2021 at 1:00 pm
Here's another way to do gaps and islands
WITH C1 AS (
SELECT id, category, StartDate,
CASE WHEN LAG(category) OVER(PARTITION BY id ORDER BY StartDate) = category THEN 0...
April 27, 2021 at 3:38 pm
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...
April 15, 2021 at 1:26 pm
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
April 15, 2021 at 8:36 am
No sample data so this is untested.
WITH Recur AS (
SELECT PL.hMy hSupervisor,
PL.sCode sSuperCode,
PL.sDept,
PL.hMy,
CAST(1...
April 12, 2021 at 5:06 pm
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);
April 1, 2021 at 7:30 am
I think it's just a matter of adding this to the end of your query
ORDER BY SubQuery.department,SubQuery.myrank
March 18, 2021 at 1:33 pm
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...
March 17, 2021 at 11:13 am
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...
March 15, 2021 at 4:44 pm
Viewing 15 posts - 16 through 30 (of 1,416 total)