SQL QUERY: Request For Part Overflow

• dderesdd

  SSC Rookie

  Points: 42

  More actions

  March 5, 2019 at 7:37 am

  #377145

  I have events on my database.
  Every event has datetime to be started and datetime to be ended.
  In every event appears request for part/s. Inventory of this parts just 2 units (not appears in the example table).

  I have to retreve to users the datetime for overrequest of the parts.
  In my example: part 1000 overrequested at 01-03-2019 17:30 and 01-03-2019 08:30.
  

  Thanks!

• drew.allen

  SSC Guru

  Points: 77014

  More actions

  March 5, 2019 at 4:08 pm

  #2023617

  dderesdd - Tuesday, March 5, 2019 7:37 AM

  I have events on my database.
  Every event has datetime to be started and datetime to be ended.
  In every event appears request for part/s. Inventory of this parts just 2 units (not appears in the example table).

  I have to retreve to users the datetime for overrequest of the parts.
  In my example: part 1000 overrequested at 01-03-2019 17:30 and 01-03-2019 08:30.
  

  Thanks!

  You're more likely to get a response if you provide consumable data as outlined in the first link in my signature.  A picture is not consumable data.

  Here is how you would provide consumable data:

  CREATE TABLE #Events
(
    Event_ID        INT,
    From_Date       DATETIME,
    To_Date         DATETIME,
    Part            INT,
    Part_Request    TINYINT
)
INSERT #Events(Event_ID, From_Date, To_Date, Part, Part_Request)
VALUES
    (100, '20190301 07:30', '20190301 09:00', 1000, 1),
    (200, '20190301 08:30', '20190301 13:30', 1000, 1),
    (500, '20190301 08:30', '20190301 14:00', 1000, 1),
    (900, '20190301 15:30', '20190301 18:30', 1000, 2),
    (1000, '20190301 17:30', '20190301 19:30', 1000, 1);


  I also changed the names of some of your columns, because they are reserved SQL keywords.  It's best to avoid that.

  Here is a solution.  It uses the packing intervals approach outlined by Itzik Ben-Gan.

  WITH event_intervals AS
(
    SELECT
        e.Event_ID,
        u.event_dt AS From_Date,
        LEAD(u.event_dt, 1, NULL) OVER(PARTITION BY e.Part ORDER BY event_dt) AS To_Date,
        SUM(u.part_request) OVER(PARTITION BY e.Part ORDER BY u.event_dt, u.part_request, e.Event_ID ROWS UNBOUNDED PRECEDING) AS active_part_requests
    FROM #Events e
    CROSS APPLY ( VALUES(e.From_Date, e.Part_Request), (e.To_Date, -e.Part_Request) ) u(event_dt, part_request)
)
SELECT *
FROM event_intervals
WHERE active_part_requests > 2


  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• dderesdd

  SSC Rookie

  Points: 42

  More actions

  March 6, 2019 at 1:21 am

  #2023627

  WOW...
  Great!!! Thank you!!!

  I need to take into account events that ends at (for example) 10:00 and starts at 10:00 as 2 units requested at 10:00.

• drew.allen

  SSC Guru

  Points: 77014

  More actions

  March 6, 2019 at 3:42 pm

  #2023740

  dderesdd - Wednesday, March 6, 2019 1:21 AM

  WOW...
  Great!!! Thank you!!!

  I need to take into account events that ends at (for example) 10:00 and starts at 10:00 as 2 units requested at 10:00.

  In that case, just change the sort for u.part_request to DESC in the OVER clause.  When sorting ascending, you are reducing the units (negative amounts) before increasing units (positive amounts).  By changing it to descending, you are increasing the units (positive amounts) before decreasing the units (negative amounts).

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• dderesdd

  SSC Rookie

  Points: 42

  More actions

  March 7, 2019 at 12:33 am

  #2023761

  Thank You!!!