Advertisement
Guest User

Untitled

a guest
Aug 22nd, 2019
153
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.73 KB | None | 0 0
  1. DECLARE @orders TABLE
  2. (
  3. cust_id INT,
  4. ord_id INT,
  5. ord_date DATE
  6. )
  7. DECLARE @cust TABLE
  8. (
  9. cust_id INT,
  10. cust_name VARCHAR(200)
  11. )
  12.  
  13. INSERT INTO @orders VALUES
  14. (1, 1, '1/1/2018'),
  15. (1, 2, '1/2/2018'),
  16. (2, 3, '1/1/2019'),
  17. (2, 5, '1/5/2019'),
  18. (3, 7, '6/12/2019'),
  19. (4, 8, '3/21/2019'),
  20. (5, 9, '1/14/2019'),
  21. (5, 12, '5/17/2019'),
  22. (6, 10, '6/8/2019')
  23.  
  24. INSERT INTO @cust VALUES
  25. (1, 'Joes Bakery'),
  26. (2, 'Bobs Bread'),
  27. (3, 'HHA'),
  28. (4, 'Ma''s donuts'),
  29. (5, 'Empiric'),
  30. (6, 'Sally''s sweets')
  31.  
  32. SELECT
  33. o.cust_id,
  34. c.cust_name,
  35. COUNT(*) AS order_count
  36. FROM
  37. @orders o INNER JOIN
  38. @cust c ON t.cust_id = o.cust_id
  39. WHERE
  40. DATEPART(YEAR, o.ord_date) = 2019
  41. GROUP BY
  42. o.cust_id,
  43. c.cust_name
  44. HAVING
  45. COUNT(*) > 1
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement