I would rather use

WHERE CAST(date AS  TIME) >= '00:00:00'
  AND CAST(date AS  TIME) < '06:00:00'

Hi @DesNorton, the other option is to have PK (TenantId,Id) in each table, but in many table i have always filter by year, so i believe it could be...

Why not simply add a new field (TenantID) to each table.  and leave your ID as is.  Now, ensure that all indexes include the TenantID, and add TenentID to all...

Your fingers might get considerably shorter, but you could always add a lot of these ...

IF UPDATE (colName )
BEGIN
  -- Add your column to DSQL ...
END;

Something like this

DECLARE @Year int = 2022;

WITH cteMonthStarts AS (
  SELECT FebStart =  DATEADD(mm, (@year-1900)*12 +1, 0)
       , AugStart...

You are creating a "Catch-All" query.  The pattern used in your WHERE clause is non-sargeable, forcing SQL to do a full scan.

With the following pattern,

AND (ISNULL(@email,'') =...

Assuming that all of the other 20 subqueries are based on the same pattern in tblPolSpec, you will probably get better performance if you use a CROSS-TAB query like this

My code works with the data that you provided.  I cant test against the unknown

Simply include the...

Setting Consumable data

IF OBJECT_ID(N'tempdb..#DocData', N'U') IS NOT NULL DROP TABLE #DocData;

CREATE TABLE #DocData (
    RefDocNum   char(10)      NOT...

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

Spam incoming .....

I'm having an issue on 2016 and 2017... If you have 2019 or any version prior to 2016, would you run the following code, please, and let me know...

I dont have access to SQL22, but looking at GREATEST/LEAST, I assume that using them in a WHERE clause will make the query NON-SARGEABLE.

SELECT P.Name, P.SellStartDate, P.DiscontinuedDate,...

Is it possible use case more than once in an expression? I have a query tha calculates the cost of insurence. but i need to see if i can...

What is the error?