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