I thought that I need to do just simple moving average

AVG([ONQTYCOGS]) OVER ( ORDER BY [WED] ROWS BETWEEN 52 PRECEDING AND CURRENT ROW ) ROLL1

but it doesn't return what...

What happens if you use the following to avoid the function breaking sargability (assumption: tables are indexed to support the query)

AND (cp.from_service_date BETWEEN d.eff_from AND d.eff_thru...

ISNULL() is not SARGable, so it should be avoided in WHERE clauses.  You have three instances of ISNULL() in your WHERE clause.  There are a couple of ways to remove...

This is a packing intervals problem.  You can Google "packing intervals" to find out more.

Here is a solution

/* Set up the sample data */
CREATE TABLE #Claims
(
 ...

The likely issue is the order of processing.  You have your COUNT inside your CASE when these should likely be reversed.  Your CASE should be inside your COUNT.

If you want...

My favorite project was converting our alumni/fundraising database system from a third-party mainframe product to a third-part SQL Server product.  It was my first exposure to SQL Server.  I wasn't...

I believe that reverse is...

Thanks for the reply, but that is not what I need.

I need a way to write this that when the state is MN then these ProcCodes are Excluded, but...

Hi,

I am trying to put a case statement in a where clause and nothing that I am doing seems to work, that I got online.

Basically, I need to put...

You are partioning on the same column that you do MIN/MAX on. This will not work. As I understand your requirements, you shouldn't do a PARTITION BY, since you...

All expressions in a SELECT clause are evaluated at the same time.  This means that you cannot use an alias in the same SELECT statement in which it is defined--which...

A couple of things:

1. DeviceSerialNumber is a sibling--not child--of ChannelReadings.

   1. Remove ChannelReadings from the path for DeviceSerialNumber

2. ChannelReadings is an ARRAY of JSON.

   1. You need to provide an index to...

This is as clear as mud.  You've been around long enough to know that you should post sample data and expected results using (temp) table creates and inserts.

Also, what is...

Here is a different approach.  It's better on some metrics, but worse on others.  I'm not sure which will play out better in the long run.

WITH TableBRanges...

The key here is to realize the misnomer.  What you find in SSMS is NOT a full version of RegEx.  I'm not sure it even qualifies as a valid...