You're right tho it could be simpler

with
range_cte(EMployeeID, TranDate, NewStatus, EndDate) as (
    select
      EMployeeID, TranDate, NewStatus,
   ...

What is the necessity of dateadd/datediff functions here?

Lead gets you the next date and from what ive seen in the daterange post, it accepts 2 dates aswell.

So why not...

drop table if exists #EMployeeStatus;
go
create table #EMployeeStatus(
  EMployeeID Int,
  TranDate datetime,
  OldStatus varchar(50),
  NewStatus varchar(50));
go

insert #EMployeeStatus(EMployeeID, TranDate, OldStatus, NewStatus) values
(12345657, '07/11/2018', 'SUSPENDED', 'ACTIVE'),
(12345657,...

select * from sys.database_scoped_configurations;

This tells the settings.  Since for a while identity_cache ON has been the default.  I checked Azure Sql is set to ON by default. ...

In the DDL provided:

[event_id] [float] NULL
[race number] [tinyint] NULL

Are these 2 of the 3 columns to be included in the ORDER BY?  Which column corresponds to expr1003?

Sorry I should've also asked which are the ORDER BY column(s)?  The window has 2 parts 🙂

basically what I need to do is to summarize the  count of column "count2" for a every partition, and then subtract it to the field in "lead2"  so, with...

This uses the daterange function from this article: https://www.sqlservercentral.com/scripts/a-daterange-table-valued-function

drop table if exists #tblTest;
go
create table #tblTest
(ename varchar(10),
 Amount float,
 eDate date);
 go

insert #tblTest values
('ABC',615.00,'03/23/2020'),
('ABC',540.00,'03/23/2020'),
('ABC',207.00,'03/23/2020'),
('PQR',449.00,'03/23/2020'),
('PQR',1065.00,'03/23/2020'),
('PQR',-2435.00,'03/23/2020');

declare
  @max_dt          ...

Afaik and imo there are no pros only cons.  There is no such thing as a "gap" in a primary key because 'unit consistency' is not a necessary...

Afaik and imo there are no pros only cons.  There is no such thing as a "gap" in a primary key because 'unit consistency' is not a necessary attribute.  Maybe...

Clarity could not also come from conciseness?  It seems to me adding extra information which is of questionable necessity adds...

Steve and Jeff, thank you.  Good to have other eyes on this. 🙂  What should I do with the earlier incorrect code?  Wipe it out?  Should I have kept updating...

If dupes are ok but the alerttypeid may only be in (4, 6), then maybe this works

with only_two_cte(alertid) as (
    select
   ...

with only_two_cte(alertid) as (
    select
      alertid
    from
      #aaa
   ...

• This reply was modified 5 years, 8 months ago by Steve Collins.
• This reply was modified 5 years, 8 months ago by Steve Collins.