Share what you found, and help others learn.

Sooner or later ,someone else out there will be struggling with a similar problem, and your experience may help them.

This is precisely what foreign keys are for.

Is ItemNumber in the transaction table equivalent to the ProductId in the master table?

It's not a csv. There is no single, consistent delimiter.  This is invalid json. json keys/names should be enclosed in quotes. Those that contain delimiters must be enclosed in quotes.

If this...

Be methodical. Understand the relationships and data.

Look for obvious contradictions in the where clauses -- where StartDate > '2021-08-19' AND StartDate < '2021-08-01' will filter out everything.

Then look for typos...

I'm guessing you meant a UDF (user-defined function) or more specifically, an ITVF (inline-table valued function) rather than a CTE, since a common table expression doesn't really seem to enhance...

You need to wrap

(SELECT COUNT(*) FROM .[dbo].[Stage_SMSLog] 
with (NOLOCK) WHERE datestamp = CONVERT(nvarchar(10), GetDate(),120))

in CAST or CONVERT when concatenating into nvarchar (or varchar) string using "+"

(CONCAT function...

Removed inaccurate response.

You could make the parameter that window/time range.

Obviously your underlying will have to change.

If it's always 30 days windows like your two examples suggest (is that sample really representative, or...

• This reply was modified 4 years, 11 months ago by ratbak. Reason: Corrected logic to add 30 days to @days for maximum OpenDate

So you're saying it's a good thing there isn't a BacklogUpdateTrackingTable?

🙂

Indexed views can't reference other databases - Tables must be referenced by two-part names, schema.tablename in the view definition.

Indexed views cannot contain common table expressions.

https://docs.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views?view=sql-server-ver15

https://www.brentozar.com/archive/2013/11/what-you-can-and-cant-do-with-indexed-views/

A couple of possible alternatives:

• Depending...

You have no explicit column(s) by which the data can be you reliably ordered and grouped. You have no ID, date, or other criteria for ordering or grouping the data.

Your sample...

Does csi_sample_specs have an index on csi_sample_id and spec_size (ideally including spec_amount if a nonclustered index)?

How slow is super slow? What is expected/needed execution time? Do other queries execute quickly?

What...

https://www.microsoft.com/en-us/download/details.aspx?id=57474 is just the Service Pack 3 installer.

https://www.microsoft.com/en-us/download/details.aspx?id=57473 has downloads for SQL Express and other components with SP3 integrated.

You probably just need the service pack (57474).

You're often better off using a UNION ALL to separate the cases into statements with their own sargeable where clauses -- e.g.,

SELECT ...
FROM ...
WHERE @Test = 'Yes'...

where len(r.portionkey)=len(m.portionkey)  

The functions are going to prevent SQL Server from using indexes effectively even if you had them. It also seems to indicate that portion...