Advertisement
Max_Leb

Untitled

Jan 29th, 2023
1,131
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 1.88 KB | None | 0 0
  1. WITH Finite_Answer(FullName, IncidentTitle, Counter) AS
  2.     (WITH Answer(FullName, IncidentTitle, "date", Resolved, k_value) AS (SELECT s.FullName, i.Title, i.CreatedAt AS date_made, s.Resolved, key_value FROM "Assignment" a      
  3.         JOIN Specialists s ON s.ID = a.SpecialistID
  4.         JOIN Incidents i ON a.IncidentID = i.ID
  5.         JOIN (SELECT FullName, i.Title, i.CreatedAt AS date_made, s.Resolved,
  6.         SUM(s.Resolved) OVER (PARTITION BY s.FullName ORDER BY i.Title) AS key_value    
  7.         FROM "Assignment" a
  8.         JOIN Specialists s ON s.ID = a.SpecialistID
  9.         JOIN Incidents i ON a.IncidentID = i.ID) AS tmp
  10.         ON s.FullName = tmp.FullName AND i.Title = tmp.Title AND key_value NOT IN (SELECT * FROM (SELECT key_value FROM (SELECT FullName, i.Title, i.CreatedAt AS date_made, s.Resolved,
  11.         SUM(s.Resolved) OVER (PARTITION BY s.FullName ORDER BY i.Title) AS key_value    
  12.         FROM "Assignment" a
  13.         JOIN Specialists s ON s.ID = a.SpecialistID
  14.         JOIN Incidents i ON a.IncidentID = i.ID) WHERE key_value != 0) GROUP BY key_value HAVING key_value <= (SELECT key_value FROM (SELECT FullName, i.Title, i.CreatedAt AS date_made, s.Resolved,
  15.         SUM(s.Resolved) OVER (PARTITION BY s.FullName ORDER BY i.Title) AS key_value    
  16.         FROM "Assignment" a
  17.         JOIN Specialists s ON s.ID = a.SpecialistID
  18.         JOIN Incidents i ON a.IncidentID = i.ID) WHERE key_value != 0 ORDER BY key_value ASC LIMIT 1) * (SELECT key_value FROM (SELECT FullName, i.Title, i.CreatedAt AS date_made, s.Resolved,
  19.         SUM(s.Resolved) OVER (PARTITION BY s.FullName ORDER BY i.Title) AS key_value    
  20.         FROM "Assignment" a
  21.         JOIN Specialists s ON s.ID = a.SpecialistID
  22.         JOIN Incidents i ON a.IncidentID = i.ID) WHERE key_value != 0 ORDER BY key_value ASC LIMIT 1)))
  23.     SELECT FullName, IncidentTitle, COUNT(IncidentTitle) OVER(PARTITION BY FullName ORDER BY IncidentTitle) AS counter FROM Answer)
  24. SELECT FullName, IncidentTitle FROM Finite_Answer WHERE Counter <= 5;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement