Advertisement
Guest User

Untitled

a guest
Nov 15th, 2017
81
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 1.00 KB | None | 0 0
  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.     )
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement