Advertisement
simonradev

T-SQL CTE

Sep 14th, 2017
153
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 1.47 KB | None | 0 0
  1. WITH ContinentsAndCurrencyUsage_CTE (ContinentCode,
  2.                                      CurrencyCode,
  3.                                      CurrencyUsage)
  4. AS
  5. (
  6.     SELECT c.ContinentCode,
  7.            cntr.CurrencyCode,
  8.            COUNT(*) AS [Currency Usage]
  9.      FROM Continents AS c
  10.      INNER JOIN Countries AS cntr
  11.      ON c.ContinentCode = cntr.ContinentCode
  12.      GROUP BY c.ContinentCode, cntr.CurrencyCode
  13. ),
  14. CountriesWithMaxCurrencyUsage_CTE (ContinendCode, CurrencyCode, MaxCurrencyUsage)
  15. AS
  16. (
  17.    SELECT ContinentsAndCurrencyUsage_CTE.ContinentCode,
  18.           ContinentsAndCurrencyUsage_CTE.CurrencyCode,
  19.           MAX(ContinentsAndCurrencyUsage_CTE.CurrencyUsage) AS [Max currency usage]
  20.     FROM ContinentsAndCurrencyUsage_CTE
  21.     GROUP BY ContinentsAndCurrencyUsage_CTE.ContinentCode, ContinentsAndCurrencyUsage_CTE.CurrencyCode
  22.     HAVING MAX(ContinentsAndCurrencyUsage_CTE.CurrencyUsage) > 1
  23. ),
  24. ContinentsWithMaxCurrencyUsage_CTE (ContinentCode, MaxCurrencyUsage)
  25. AS
  26. (
  27.     SELECT CountriesWithMaxCurrencyUsage_CTE.ContinendCode,
  28.            MAX(CountriesWithMaxCurrencyUsage_CTE.MaxCurrencyUsage) AS [Max]
  29.      FROM CountriesWithMaxCurrencyUsage_CTE
  30.      GROUP BY CountriesWithMaxCurrencyUsage_CTE.ContinendCode
  31. )
  32.  
  33. SELECT cont.ContinentCode,
  34.        coun.CurrencyCode,
  35.        cont.MaxCurrencyUsage
  36.  FROM ContinentsWithMaxCurrencyUsage_CTE AS cont
  37.  INNER JOIN CountriesWithMaxCurrencyUsage_CTE AS coun
  38.  ON cont.ContinentCode = coun.ContinendCode AND
  39.     cont.MaxCurrencyUsage = coun.MaxCurrencyUsage
  40.  ORDER BY cont.ContinentCode
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement