daily pastebin goal
9%
SHARE
TWEET

Untitled

a guest Nov 15th, 2017 60 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2.  
  3. CREATE TABLE Efremova
  4. (
  5.     did INT,
  6.     summa INT,
  7.     dt DATE
  8. );
  9.  
  10. INSERT Efremova
  11. VALUES
  12.     (1, 100, '20170101'),
  13.     (1, 110, '20170102'),
  14.     (2, 100, '20170104'),
  15.     (2, 140, '20170107'),
  16.     (2, 90, '20170108'),
  17.     (3, 100, '20170105'),
  18.     (3, 200, '20170201')
  19. ;
  20.  
  21. WITH
  22.     Abstract AS
  23.     (
  24.         SELECT *
  25.         FROM Efremova
  26.     )
  27.  
  28. SELECT DISTINCT did
  29. FROM Abstract AS e
  30. WHERE
  31.     (
  32.         SELECT summa
  33.         FROM Abstract AS e_max
  34.         WHERE
  35.             e.did = did
  36.             AND dt =
  37.             (
  38.                 SELECT MAX(dt)
  39.                 FROM Abstract
  40.                 WHERE e_max.did = did
  41.                 GROUP BY did
  42.             )
  43.     )
  44.     >
  45.     (
  46.         SELECT summa
  47.         FROM Abstract AS e_min
  48.         WHERE
  49.             e.did = did
  50.             AND dt =
  51.             (
  52.                 SELECT MIN(dt)
  53.                 FROM Abstract
  54.                 WHERE e_min.did = did
  55.                 GROUP BY did
  56.             )
  57.     )
RAW Paste Data
Top