-1

Need SQL Query to get a max of the sum of Days where enddate is equal to startdate .Below is a table

ID StartDate EndDate Days
121 01-01-2022 01-03-2022 2
121 01-03-2022 01-04-2022 1
121 01-04-2022 01-06-2022 2
121 01-07-2022 01-08-2022 1
121 01-08-2022 01-09-2022 1

In the above table, the 01-01-2022 to 01-06-2022 sum is 5 which is greater than the sum of 2 from 01-07-2022 to 01-09-2022. Output required

ID Days
121 5
Priyanka Vadhwani
  • 991
  • 7
  • 10
Lucidity
  • 7
  • 3
  • 3
    *Need SQL Query* -- This website is not a free code writing service. What have you tried so far and what are the issues you are facing with your scripts? – iamdave Oct 06 '22 at 10:12
  • 1
    Does this answer your question? [Merge overlapping date intervals](https://stackoverflow.com/questions/2561130/merge-overlapping-date-intervals). Or [Overlapping effective dates aggregation](https://stackoverflow.com/questions/67415749/overlapping-effective-dates-aggregation) – astentx Oct 06 '22 at 10:15
  • I have written recursive CTE for same but its taking long time and not best practice, therefore looking for partition by – Lucidity Oct 06 '22 at 10:16
  • 2
    *"I have written recursive CTE for same but its taking long time and not best practice"* -- Please include this script within your question and provide details on why you think it is not best practice – iamdave Oct 06 '22 at 10:39
  • 1
    Please provide a [Minimal Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) of what you tried into your question. It will increase the chances of you receiving help from others and reduce the changes of having your question flagged and removed. – acarlstein Oct 06 '22 at 13:24

1 Answers1

0

I have written as per my understanding. correct me if I did worse

with table as (
  select 121 id, "12-28-2021" startdate, "12-30-2021" enddate, 2 days
  union all
  select 121 id, "12-30-2021" startdate, "12-31-2021" enddate, 1 days
  union all 
  select 121 id, "01-01-2022" startdate, "01-03-2022" enddate, 2 days
  union all
  select 121 id, "01-03-2022" startdate, "01-04-2022" enddate, 1 days
  union all 
  select 121 id, "01-04-2022" startdate, "01-06-2022" enddate, 2 days
  union all 
  select 121 id, "01-07-2022" startdate, "01-08-2022" enddate, 1 days
  union all 
  select 121 id, "01-08-2022" startdate, "01-09-2022" enddate, 1 days
)
select
  table3.id,
  max(days) days
from
  (
    select 
      table2.id,
      sum(days) days
    from
      (
        select 
          table1.*,
          case 
            when sum(x) over(rows between unbounded preceding and 1 preceding) is null then 0
            else sum(x) over(rows between unbounded preceding and 1 preceding)
          end as y
        from
          (
            select 
            table.*,
            case 
              when enddate = lead(startdate) over(order by startdate) then 0
              else 1 
            end as x
          from table
          ) table1
      ) table2
    group by id,y
  ) table3
group by id
POORNA VANGARA
  • 1
  • 1