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...

Would have sworn there was a handy article by Perry Whittle or Lynn Pettis that covers useful date functions, but I can't find it right now... but this one looks...

Okay, that was fun. Finally gave up and reset my install of Win10, and reinstalled all the software. apparently some settings somewhere went haywire, causing the SQL/Azure Data Tools install...

Use a matrix in SSRS?

Normally, you do this in the UI... either PowerBI or Excel... unless you don't want to ever see that data.

you can create a stored procedure and then use that as...