What's the [EndDate] that matches with [Created Date] so we know how long a duration is?

If you want useful help, please read and follow the instructions in this article:

Can't you just use OR in your WHERE clause and then do the aggregation? I'm not convinced you need a CTE at all.

Maybe start here - Cathrine Wilhelmsen's tutorial

#GotData?

Without a table with some data, it's hard to help.

This is a whole lot easier with some [fake] data.

Does a case have a Close Date or similar? If you have that and a Calendar table, then this is absolutely...

• This reply was modified 3 years, 3 months ago by pietlinden.

Okay, I totally stole this from an article that Kenneth Fisher wrote (@sqlstudies144). Basically, you use CROSS APPLY to "stack" the repeating groups, and then you query that. Here's my...

=iif((CountRows("SrvyRespondents") < 1 ), True, False).

is an odd way to express that. CountRows will return a positive number or zero. So why not

=iif(CountRows("SrvyRespondents") = 0, True, False)

You could do something...

• This reply was modified 3 years, 3 months ago by pietlinden.

Oh, NULLIF!! I knew I was missing something with the NULLs. <g>

Transpose table, do a distinct count, transpose back.

DECLARE @EndTargetDate DATE = '01-Jan-2022'

SELECT

FROM

WHERE SomeDate > DATEADD(week,-4,@EndTargetDate) AND SomeDate <= @EndTargetDate

like this?

SELECT  
FieldCount = (CASE WHEN Prg1 ='' THEN 0 ELSE 1 END + 
CASE WHEN Prg2 ='' THEN 0 ELSE 1 END +
CASE WHEN Prg3...

I wouldn't choose to create a table with that structure, but this should work:

SELECT VID, OrdDate, COUNT(DISTINCT(v.val))
FROM #T1
CROSS APPLY (VALUES (Prg1),(Prg2),(Prg3),(Prg4),(Prg5),(Prg6)) v(val)
GROUP BY VID, OrdDate;

case when Locale = 'h' then (lag(Score) over(order by Score)) end as Oppscore

CASE WHEN Locale = 'h' THEN ( LAG(score,1) OVER ( ORDER BY Score ) ) END AS OppScore

?

If...

Any chance you could mock up some fake data that represents the situation you're trying to model and query? And an expected query result? It's hard to know how to...

I started to suspect the OS was screwy after a while because I'd never had near that much trouble installing. I should have done the smart thing and emailed myself...