Because the start date, end date combinations aren't uniformly spanning 2 years the first part of the script creates the year bands using a tally table.  Then the StudentData is...

This is very tricky.  I'm this far and my @$%@$ pivot isn't work for some reason and I can't figure out why.  If (or when) it works my plan is/was...

• This reply was modified 6 years, 2 months ago by Steve Collins.

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...