Ken's code is nice, but theoretically I believe it could yield a false positive if the previous month happened to be 13 months prior to the current month.  For...

WITH MonthCheck
AS
(
    SELECT mainhost, enter_date
        ,IIF(MONTH(enter_date) - 1
         ...

• This reply was modified 2 years, 4 months ago by Ken McKelvey.

Looking at this again the following is probably better:

WITH cte_Employee (EmployeeId, EmploymentTypeId, DateId)
AS
(
    SELECT EmployeeId, EmploymentTypeId, DateId
    FROM
   ...

You would make your life easier if you stored dates as dates. This is an Islands and Gaps problem. One solution:

WITH cte_Employee (EmployeeId, EmploymentTypeId, DateId)
AS
(
  ...

https://www.sqlservercentral.com/forums/topic/a-brief-explanation-and-solution-for-the-double-hop-problem

Run Ed B's code.

In future, provide a test rig and test answers before answering. If you do not know how...

I prefer migrations but have used in-place upgrades to buy time until there are the resources to look at a more managed upgrade. The trick is to have lots of...

I'll try to answer the questions all in one post here.

1. I'm not posting the full DDL of objects because there are only 2 views that are joined.  However,...

@ken - The 2 views are in the same schema,  but the underlying tables are in 2 different schemas.  That said, if I run "SELECT * FROM ViewA" and...

Personally I will only use DENY permissions when I really have no option.

Are all the tables and views in the same schema or at least in schemas with the same...

Your link uses the Microsoft OLE DB Driver for SQL Server not the ODBC driver. Maybe you should try that.

Where is the result showing 23? SSMS or, as I suspect, Excel etc

My first replay seemed to time out so I posted again.

• This reply was modified 2 years, 5 months ago by Ken McKelvey.

If this is an Agent Job step then Powershell is a lot less hassle. Play with something like:

$SQLparams...

LEAD is a windowed function - what makes you think it is going to work in a...