The dbo.daterange function, which is awesome, is described here:

https://www.sqlservercentral.com/scripts/a-daterange-table-valued-function

declare
  @start_dt              datetime='2019-12-03',
  @end_dt        ...

This sql gives me 'deja view' because the OP has posted many questions on SSC.  ahmed_elbarbary.2010 please recognize the answers in some way.  I posted an answer a few months...

The other constraint on the size of unique keys is the number of columns, which I believe is 16.  Bigint is only 8 bytes.  So to the extent all of...

select stuff((select concat(' ', TeamName, ',', concat(TeamName, 'Date')) 
from #Teams for xml path ('')), 1, 1, '');

O the question is for 2012.  Sorry, it needs the xml trick.  The above is 2017+

select string_agg(concat(TeamName, ',', concat(TeamName, 'Date')), ',') from #Teams;

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 5 years, 6 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 🙂