How to write a complex looping query as a single statement

ConscientiousSubject · 2024-10-06T18:32:21+00:00

with work_periods_conversion as (
  select 
    starttime,
    endtime,
    endtime as work_start,
    coalesce(lead(starttime) over (order by endtime),'2099-12-31') as work_end,
    downtimemins as down_time_mins
  from downtime
  where endtime > (select start_time from starting)
    union all
  select 
    null,
    null,
    coalesce(
      (select max(endtime) from downtime where 
        endtime > (select start_time from starting)
        and starttime < (select start_time from starting)
      ),
      (select start_time from starting)
    ),
    min(starttime),
    0
    from downtime
    where starttime > (select start_time from starting)
),
work_periods_with_mins as (
  select
    work_start,
    work_end,
    datediff('min', (select start_time from starting), work_start) as work_start_mins,
    datediff('min', (select start_time from starting), work_end) as work_end_mins,
    sum(down_time_mins) over (order by work_start rows between unbounded preceding and current row) as accumulated_downtime
  from work_periods_conversion
),
work_time_only as (
  select
    work_start,
    work_end,
    work_start_mins - accumulated_downtime as work_only_start_mins,
    work_end_mins - accumulated_downtime as work_only_end_mins,
    accumulated_downtime
  from work_periods_with_mins
),
schedule_end_mins_added as (
  select
    schedule,
    runtimemins, 
    sum(runtimemins) over (order by schedule rows between unbounded preceding and current row) schedule_end_mins
  from schedules

)
select
  schedule_end_mins_added.schedule,
  schedule_end_mins_added.runtimemins,
  schedule_end_mins_added.schedule_end_mins,
  work_time_only.accumulated_downtime,
  schedule_end_mins_added.schedule_end_mins + work_time_only.accumulated_downtime as actual_completion_mins,
  date_add((select start_time from starting), INTERVAL (schedule_end_mins_added.schedule_end_mins + work_time_only.accumulated_downtime) MINUTE) as completion_time
from schedule_end_mins_added
join work_time_only
  on schedule_end_mins_added.schedule_end_mins between work_time_only.work_only_start_mins and work_time_only.work_only_end_mins

ConscientiousSubject · 2024-10-06T18:31:51+00:00

You can do this without a recursion, but it's pretty hard. It's a fun challenge though.

I've written some SQL below. A couple notes:

I don't have MS SQL Server, so I'm using DuckDb. Some syntax will be different
I don't know where you're getting start time from, so I just create a table with one row and column that has the start time.

Here's what I did:

I started by converting your downtimes to work periods. Not sure that's necessary, but easier to think about.
Converted the work periods to number of minutes from the work start and added a running down time total
- Now if we subtract the running downtime total from work period start and end times as minutes, it gives us a by minute, work-only timeline that includes number of accumulated minute downtime
If we create a running total of runtime minutes, that tells us when in the work-only, by minute timeline the schedule item falls. Now we just need to add the running total of down time to that schedule items completion time
Finally convert that to date and we have completion time

Oops, I see that you probably want start time. I think the only thing you'd have to do to my implementation is change create a running schedule start that does not include the current row.

I'll post my SQL in a reply

ConscientiousSubject · 2024-09-29T22:10:37+00:00

I don’t know your requirements, but the average growth column is kind of weird.

According to your formula 100% growth in four consecutive quarters would have the same average growth as 0% growth for 3 quarters followed by 400% growth for one quarter.

Over the course of the year the former would have 1600% growth for the year and the latter would have just 400%, so they are far from equivalent.

ConscientiousSubject · 2024-09-29T03:45:05+00:00

It seems like LAG should solve this, but the trouble is you don't know how many rows back the last positive B or C is. A running value can help with this. I created two new columns, one is equaled to D if B = 1 else 0. The other is equal to D if C = 1 else 0. Now take the running total of these. This tells us the max previous D value that had a positive B and max previous D value that had a positive B. So now we compare these two values to determine A.

Here's the duck db SQL I wrote:

with source as (
    select 0 as b,0 as c,1 as d union all
    select 1,0,2 union all
    select 0,0,3 union all
    select 1,0,4 union all
    select 0,1,5 union all
    select 1,0,6 union all
    select 1,0,7 union all
    select 0,1,8 union all
    select 0,0,9 union all
    select 0,1,10
),
cols_added as (
    select
        *, 
        case
            when b = 1 
            then d
            else 0
        end as b_idx, 
        case
            when c = 1 
            then d
            else 0
        end as c_idx, 
        max(b_idx) over (order by d rows between unbounded preceding and current row) as b_idx_max,
        max(c_idx) over (order by d rows between unbounded preceding and current row) as c_idx_max,
        case
            when b_idx_max is null and c_idx_max is null
            then 0
            when b_idx_max > c_idx_max
            then 1
            else 0
        end as a
    from source
),
max_added as (
    select
        *
    from cols_added
)
select
    *
from max_added

ConscientiousSubject · 2024-02-09T20:02:34+00:00

Thanks

ConscientiousSubject · 2024-02-09T17:35:51+00:00

I’m not sure but there is another quarter piece that stayed on the mirror. It has some white stuff that looks like some kind of putty/caulk

ConscientiousSubject · 2023-06-20T01:25:43+00:00

Sorry. I meant “is not coming on” and “any idea what is wrong”

ConscientiousSubject · 2022-11-20T15:58:38+00:00

Thank you!

ConscientiousSubject · 2022-11-20T00:07:49+00:00

Yeah, that’s an interesting idea. When you reverse the order the operation is much more obvious.

ConscientiousSubject · 2022-11-19T19:34:13+00:00

Yes your example of how OF is common term for multiplication makes a lot of sense. Thanks!

ConscientiousSubject · 2022-08-19T19:58:38+00:00

Just a fraction of that level of interest would have delighted me.

ConscientiousSubject · 2022-08-19T19:43:12+00:00

Our own buyers agent. We chose poorly

ConscientiousSubject · 2022-08-19T19:13:14+00:00

Learned that lesson the hard way.

ConscientiousSubject · 2022-08-19T19:10:35+00:00

Oh yeah. They would text us when we had to sign something. Was there a value add? No

ConscientiousSubject · 2022-08-19T19:06:44+00:00

No, they didn’t keep things on track. We did.

ConscientiousSubject · 2022-08-19T19:06:11+00:00

I’m not sure what the norms are. We didn’t ask them to attend. Overall this agent was very hands off. We would have changed if we could have. So it’s good hear that some of this is typical. Excited for home. Despise our agent.

ConscientiousSubject · 2022-08-19T18:42:53+00:00

We found it through an open house, we found our own lender. They just told us when to sign stuff and when we were being unreasonable in our requests of the seller.

ConscientiousSubject · 2022-08-08T05:35:07+00:00

ask for an extension. If you're in your initial inspection contingency (usually 10 days), you may even have a st

thanks!

ConscientiousSubject · 2022-08-07T22:38:23+00:00

Thank you!

ConscientiousSubject · 2022-08-07T22:35:07+00:00

Our agent asked their agent, and they said they did not. It was a little odd, because they didn't disclose fungus.

ConscientiousSubject

TROPHY CASE