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, 6 months ago by Steve Collins.
• This reply was modified 5 years, 6 months ago by Steve Collins.

Ok yeah I'm starting to see the alerttypeid's should be counted separately.

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

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

select distinct
  first_value(alertid) over (partition by alerttypeid order by alertid desc) alertid,
  alerttypeid
from
  #aaa
where
  alerttypeid in(4,6);

This...

;with
ordered_amounts as (
    select distinct first_value(amount) over (partition by userid order by keyid desc) amount from #t)
select 
  sum(amount) total
from
  ordered_amounts;

I need one column for BSP between 1 and 3, one column for BSP between 3 and 5, one column for bsp between 5 and 8 (and up to...