Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- WITH Finite_Answer(FullName, IncidentTitle, Counter) AS
- (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
- JOIN Specialists s ON s.ID = a.SpecialistID
- JOIN Incidents i ON a.IncidentID = i.ID
- JOIN (SELECT FullName, i.Title, i.CreatedAt AS date_made, s.Resolved,
- SUM(s.Resolved) OVER (PARTITION BY s.FullName ORDER BY i.Title) AS key_value
- FROM "Assignment" a
- JOIN Specialists s ON s.ID = a.SpecialistID
- JOIN Incidents i ON a.IncidentID = i.ID) AS tmp
- 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,
- SUM(s.Resolved) OVER (PARTITION BY s.FullName ORDER BY i.Title) AS key_value
- FROM "Assignment" a
- JOIN Specialists s ON s.ID = a.SpecialistID
- 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,
- SUM(s.Resolved) OVER (PARTITION BY s.FullName ORDER BY i.Title) AS key_value
- FROM "Assignment" a
- JOIN Specialists s ON s.ID = a.SpecialistID
- 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,
- SUM(s.Resolved) OVER (PARTITION BY s.FullName ORDER BY i.Title) AS key_value
- FROM "Assignment" a
- JOIN Specialists s ON s.ID = a.SpecialistID
- JOIN Incidents i ON a.IncidentID = i.ID) WHERE key_value != 0 ORDER BY key_value ASC LIMIT 1)))
- SELECT FullName, IncidentTitle, COUNT(IncidentTitle) OVER(PARTITION BY FullName ORDER BY IncidentTitle) AS counter FROM Answer)
- SELECT FullName, IncidentTitle FROM Finite_Answer WHERE Counter <= 5;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement