Viewing 15 posts - 76 through 90 (of 1,438 total)
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...
March 5, 2020 at 1:23 pm
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...
March 2, 2020 at 12:57 pm
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,
...
February 28, 2020 at 10:31 am
WITH CTE AS (
SELECT OrderDetailsID, OrderID, Active, CreatedDate,
ROW_NUMBER() OVER(PARTITION BY OrderID ORDER BY OrderDetailsID DESC) AS rn
FROM OrderDetails
)
UPDATE CTE
...
February 19, 2020 at 3:02 pm
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...
February 17, 2020 at 2:09 pm
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.
February 13, 2020 at 1:15 pm
As simple as
cross apply x.XmlDoc.nodes('/assessment/template//question[@key=sql:column("QuestionID")]') AS Form(Question)Note the double //
January 29, 2020 at 1:35 pm
Sounds like you want this
cross apply x.XmlDoc.nodes('/assessment/template/question[@key=sql:column("QuestionID")]') AS Form(Question)January 29, 2020 at 11:52 am
Here's a recursive CTE solution
WITH Recur AS (
SELECT Person_FROM AS Person_FROM_Start, Person_FROM, Person_To, Kind, Pctg_New, Eff_Date
FROM #transfers
UNION ALL
SELECT ts.Person_FROM_Start, te.Person_FROM, te.Person_To, te.Kind, te.Pctg_New, te.Eff_Date
FROM Recur ts
INNER JOIN...
January 24, 2020 at 4:17 pm
If I understand this correctly, you want only leaf nodes
select s.Id,s.Category,
x.n.value('local-name(.)','varchar(30)') as LimitDescription,
x.n.value('./text()[1]','int') as LimitValue
from @sampledata s
outer apply s.Restriction.nodes('//*[not(child::*)]') x(n);
December 16, 2019 at 9:33 am
You'll need a dynamic crosstab
-- Variables used.
DECLARE @sqlQuery NVARCHAR(MAX) ;
DECLARE @sqlQuery1 NVARCHAR(MAX) ;
DECLARE @sqlQuery2 NVARCHAR(MAX) ;
SELECT @sqlQuery1 = (
SELECT ',MAX(CASE WHEN RuleName = '''+RuleName+''' THEN RuleValue...
December 12, 2019 at 10:53 am
I think the issue here is that not all values can by represented by floating point numbers. 0.29 is one of them.
SELECT cast(0.29 as float) -- shows...
December 11, 2019 at 3:32 pm
Just a simple crosstab should do it
SELECT Id, GroupName,
MAX(CASE WHEN RuleName = 'Exclude1' THEN RuleValue END) AS Exclude1,
MAX(CASE WHEN RuleName = 'Exclude2' THEN RuleValue END)...
December 11, 2019 at 9:55 am
Here's a way using recursion, probably not efficient but appears to work with your data.
WITH recur AS (
SELECT x1.id AS idstart,
x1.value AS valuestart,
x1.id AS idend,
...
November 15, 2019 at 4:23 pm
You'll need a numbers/tally table for this. Code below uses a built in one.
declare @chunk_row_size int = 50;
select row_number() over(order by t.id,ca.number) as id,
case when (ca.number+1)*@chunk_row_size...
October 23, 2019 at 3:31 pm
Viewing 15 posts - 76 through 90 (of 1,438 total)