API tools faq
paste
Advertisement
Guest User

monster_query

a guest
Oct 30th, 2013
144
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 10.29 KB | None | 0 0
raw download clone embed print report
  1. SELECT  'Solar' AS Industry, 'http://www.mysite.com/solar-energy/' AS HREF,
  2. ROUND(AVG(100*(PCurr-P1)/P1),1) AS 'Day',  
  3. ROUND(AVG(100*(PCurr-P2)/P2),1) AS 'Week',  
  4. ROUND(AVG(100*(PCurr-P4)/P4),1) AS 'Qtr',  
  5. ROUND(AVG(100*(PCurr-P5)/P5),1) AS 'Year'    
  6. FROM (
  7.     SELECT DISTINCT    a_ticker AS Ticker FROM table_a  WHERE a_rfrindustry LIKE '%Solar%' AND a_current=1) AS table_a    
  8.     LEFT OUTER JOIN (
  9.     SELECT d_ticker AS TickerCurr, d_priceprevclose AS PCurr  FROM table_d  WHERE d_updated = (  SELECT MAX( d_updated )  FROM table_d)  ) AS table_dc ON Ticker = TickerCurr
  10.     LEFT OUTER JOIN (  SELECT d_ticker AS Ticker1, d_priceprevclose AS P1  FROM table_d  WHERE d_updated = (  SELECT MAX( d_updated )  FROM table_d  WHERE d_updated <= (  DATE_SUB( (  SELECT MAX( d_updated )  FROM table_d ) , INTERVAL 1 DAY ))))  AS table_d1  ON Ticker = Ticker1
  11.     LEFT OUTER JOIN (  SELECT d_ticker AS Ticker2, d_priceprevclose AS P2  FROM table_d  WHERE d_updated = (  SELECT MIN( d_updated )  FROM table_d  WHERE d_updated >= (  DATE_SUB( (  SELECT MAX( d_updated )  FROM table_d ) , INTERVAL 1 WEEK ))))  AS table_d2  ON Ticker = Ticker2
  12.     LEFT OUTER JOIN (  SELECT d_ticker AS Ticker4, d_priceprevclose AS P4  FROM table_d  WHERE d_updated = (  SELECT MIN( d_updated )  FROM table_d  WHERE d_updated >= (  DATE_SUB( (  SELECT MAX( d_updated )  FROM table_d ) , INTERVAL 1 QUARTER ))))  AS table_d4  ON Ticker = Ticker4
  13.     LEFT OUTER JOIN (  SELECT d_ticker AS Ticker5, d_priceprevclose AS P5  FROM table_d  WHERE d_updated = (  SELECT MIN( d_updated )  FROM table_d  WHERE d_updated >= (  DATE_SUB( (  SELECT MAX( d_updated )  FROM table_d ) , INTERVAL 1 YEAR ))))   AS table_d5  ON Ticker = Ticker5    
  14.  
  15. UNION ALL
  16.  
  17. SELECT  'Smart Grid' AS Industry, 'http://www.mysite.com/smartgrid/' AS HREF,
  18. ROUND(AVG(100*(PCurr-P1)/P1),1) AS 'Day',  
  19. ROUND(AVG(100*(PCurr-P2)/P2),1) AS 'Week',  
  20. ROUND(AVG(100*(PCurr-P4)/P4),1) AS 'Qtr',  
  21. ROUND(AVG(100*(PCurr-P5)/P5),1) AS 'Year'    
  22. FROM (
  23.     SELECT DISTINCT    a_ticker AS Ticker FROM table_a  WHERE a_rfrindustry LIKE '%Smart grid%' AND a_current=1) AS table_a    
  24.     LEFT OUTER JOIN (
  25.     SELECT d_ticker AS TickerCurr, d_priceprevclose AS PCurr  FROM table_d  WHERE d_updated = (  SELECT MAX( d_updated )  FROM table_d)  ) AS table_dc ON Ticker = TickerCurr
  26.     LEFT OUTER JOIN (  SELECT d_ticker AS Ticker1, d_priceprevclose AS P1  FROM table_d  WHERE d_updated = (  SELECT MAX( d_updated )  FROM table_d  WHERE d_updated <= (  DATE_SUB( (  SELECT MAX( d_updated )  FROM table_d ) , INTERVAL 1 DAY ))))  AS table_d1  ON Ticker = Ticker1
  27.     LEFT OUTER JOIN (  SELECT d_ticker AS Ticker2, d_priceprevclose AS P2  FROM table_d  WHERE d_updated = (  SELECT MIN( d_updated )  FROM table_d  WHERE d_updated >= (  DATE_SUB( (  SELECT MAX( d_updated )  FROM table_d ) , INTERVAL 1 WEEK ))))  AS table_d2  ON Ticker = Ticker2
  28.     LEFT OUTER JOIN (  SELECT d_ticker AS Ticker4, d_priceprevclose AS P4  FROM table_d  WHERE d_updated = (  SELECT MIN( d_updated )  FROM table_d  WHERE d_updated >= (  DATE_SUB( (  SELECT MAX( d_updated )  FROM table_d ) , INTERVAL 1 QUARTER ))))  AS table_d4  ON Ticker = Ticker4
  29.     LEFT OUTER JOIN (  SELECT d_ticker AS Ticker5, d_priceprevclose AS P5  FROM table_d  WHERE d_updated = (  SELECT MIN( d_updated )  FROM table_d  WHERE d_updated >= (  DATE_SUB( (  SELECT MAX( d_updated )  FROM table_d ) , INTERVAL 1 YEAR))))   AS table_d5  ON Ticker = Ticker5    
  30.  
  31. UNION ALL
  32.  
  33. SELECT  'Wind' AS Industry, 'http://www.mysite.com/wind-energy/' AS HREF,
  34. ROUND(AVG(100*(PCurr-P1)/P1),1) AS 'Day',  
  35. ROUND(AVG(100*(PCurr-P2)/P2),1) AS 'Week',  
  36. ROUND(AVG(100*(PCurr-P4)/P4),1) AS 'Qtr',  
  37. ROUND(AVG(100*(PCurr-P5)/P5),1) AS 'Year'    
  38. FROM (
  39.     SELECT DISTINCT    a_ticker AS Ticker FROM table_a  WHERE a_rfrindustry LIKE '%Wind%' AND a_current=1) AS table_a    
  40.     LEFT OUTER JOIN (
  41.     SELECT d_ticker AS TickerCurr, d_priceprevclose AS PCurr  FROM table_d  WHERE d_updated = (  SELECT MAX( d_updated )  FROM table_d)  ) AS table_dc ON Ticker = TickerCurr
  42.     LEFT OUTER JOIN (  SELECT d_ticker AS Ticker1, d_priceprevclose AS P1  FROM table_d  WHERE d_updated = (  SELECT MAX( d_updated )  FROM table_d  WHERE d_updated <= (  DATE_SUB( (  SELECT MAX( d_updated )  FROM table_d ) , INTERVAL 1 DAY ))))  AS table_d1  ON Ticker = Ticker1
  43.     LEFT OUTER JOIN (  SELECT d_ticker AS Ticker2, d_priceprevclose AS P2  FROM table_d  WHERE d_updated = (  SELECT MIN( d_updated )  FROM table_d  WHERE d_updated >= (  DATE_SUB( (  SELECT MAX( d_updated )  FROM table_d ) , INTERVAL 1 WEEK ))))  AS table_d2  ON Ticker = Ticker2
  44.     LEFT OUTER JOIN (  SELECT d_ticker AS Ticker4, d_priceprevclose AS P4  FROM table_d  WHERE d_updated = (  SELECT MIN( d_updated )  FROM table_d  WHERE d_updated >= (  DATE_SUB( (  SELECT MAX( d_updated )  FROM table_d ) , INTERVAL 1 QUARTER ))))  AS table_d4  ON Ticker = Ticker4
  45.     LEFT OUTER JOIN (  SELECT d_ticker AS Ticker5, d_priceprevclose AS P5  FROM table_d  WHERE d_updated = (  SELECT MIN( d_updated )  FROM table_d  WHERE d_updated >= (  DATE_SUB( (  SELECT MAX( d_updated )  FROM table_d ) , INTERVAL 1 YEAR ))))   AS table_d5  ON Ticker = Ticker5    
  46.  
  47. UNION ALL
  48.  
  49. SELECT  'Energy Efficiency' AS Industry, 'http://www.mysite.com/energy-efficiency/' AS HREF,
  50. ROUND(AVG(100*(PCurr-P1)/P1),1) AS 'Day',  
  51. ROUND(AVG(100*(PCurr-P2)/P2),1) AS 'Week',  
  52. ROUND(AVG(100*(PCurr-P4)/P4),1) AS 'Qtr',  
  53. ROUND(AVG(100*(PCurr-P5)/P5),1) AS 'Year'    
  54. FROM (
  55.     SELECT DISTINCT    a_ticker AS Ticker FROM table_a  WHERE a_rfrindustry LIKE '%Energy efficiency%' AND a_current=1) AS table_a    
  56.     LEFT OUTER JOIN (
  57.     SELECT d_ticker AS TickerCurr, d_priceprevclose AS PCurr  FROM table_d  WHERE d_updated = (  SELECT MAX( d_updated )  FROM table_d)  ) AS table_dc ON Ticker = TickerCurr
  58.     LEFT OUTER JOIN (  SELECT d_ticker AS Ticker1, d_priceprevclose AS P1  FROM table_d  WHERE d_updated = (  SELECT MAX( d_updated )  FROM table_d  WHERE d_updated <= (  DATE_SUB( (  SELECT MAX( d_updated )  FROM table_d ) , INTERVAL 1 DAY ))))  AS table_d1  ON Ticker = Ticker1
  59.     LEFT OUTER JOIN (  SELECT d_ticker AS Ticker2, d_priceprevclose AS P2  FROM table_d  WHERE d_updated = (  SELECT MIN( d_updated )  FROM table_d  WHERE d_updated >= (  DATE_SUB( (  SELECT MAX( d_updated )  FROM table_d ) , INTERVAL 1 WEEK ))))  AS table_d2  ON Ticker = Ticker2
  60.     LEFT OUTER JOIN (  SELECT d_ticker AS Ticker4, d_priceprevclose AS P4  FROM table_d  WHERE d_updated = (  SELECT MIN( d_updated )  FROM table_d  WHERE d_updated >= (  DATE_SUB( (  SELECT MAX( d_updated )  FROM table_d ) , INTERVAL 1 QUARTER ))))  AS table_d4  ON Ticker = Ticker4
  61.     LEFT OUTER JOIN (  SELECT d_ticker AS Ticker5, d_priceprevclose AS P5  FROM table_d  WHERE d_updated = (  SELECT MIN( d_updated )  FROM table_d  WHERE d_updated >= (  DATE_SUB( (  SELECT MAX( d_updated )  FROM table_d ) , INTERVAL 1 YEAR ))))   AS table_d5  ON Ticker = Ticker5    
  62.  
  63.  
  64. UNION ALL
  65.  
  66. SELECT  'Environmental' AS Industry, 'http://www.mysite.com/environmental/' AS HREF,
  67. ROUND(AVG(100*(PCurr-P1)/P1),1) AS 'Day',  
  68. ROUND(AVG(100*(PCurr-P2)/P2),1) AS 'Week',  
  69. ROUND(AVG(100*(PCurr-P4)/P4),1) AS 'Qtr',  
  70. ROUND(AVG(100*(PCurr-P5)/P5),1) AS 'Year'    
  71. FROM (
  72.     SELECT DISTINCT    a_ticker AS Ticker FROM table_a  WHERE a_rfrindustry LIKE '%Environmental%' AND a_current=1) AS table_a    
  73.     LEFT OUTER JOIN (
  74.     SELECT d_ticker AS TickerCurr, d_priceprevclose AS PCurr  FROM table_d  WHERE d_updated = (  SELECT MAX( d_updated )  FROM table_d)  ) AS table_dc ON Ticker = TickerCurr
  75.     LEFT OUTER JOIN (  SELECT d_ticker AS Ticker1, d_priceprevclose AS P1  FROM table_d  WHERE d_updated = (  SELECT MAX( d_updated )  FROM table_d  WHERE d_updated <= (  DATE_SUB( (  SELECT MAX( d_updated )  FROM table_d ) , INTERVAL 1 DAY ))))  AS table_d1  ON Ticker = Ticker1
  76.     LEFT OUTER JOIN (  SELECT d_ticker AS Ticker2, d_priceprevclose AS P2  FROM table_d  WHERE d_updated = (  SELECT MIN( d_updated )  FROM table_d  WHERE d_updated >= (  DATE_SUB( (  SELECT MAX( d_updated )  FROM table_d ) , INTERVAL 1 WEEK ))))  AS table_d2  ON Ticker = Ticker2
  77.     LEFT OUTER JOIN (  SELECT d_ticker AS Ticker4, d_priceprevclose AS P4  FROM table_d  WHERE d_updated = (  SELECT MIN( d_updated )  FROM table_d  WHERE d_updated >= (  DATE_SUB( (  SELECT MAX( d_updated )  FROM table_d ) , INTERVAL 1 QUARTER ))))  AS table_d4  ON Ticker = Ticker4
  78.     LEFT OUTER JOIN (  SELECT d_ticker AS Ticker5, d_priceprevclose AS P5  FROM table_d  WHERE d_updated = (  SELECT MIN( d_updated )  FROM table_d  WHERE d_updated >= (  DATE_SUB( (  SELECT MAX( d_updated )  FROM table_d ) , INTERVAL 1 YEAR ))))   AS table_d5  ON Ticker = Ticker5    
  79.  
  80.  
  81. UNION ALL
  82.  
  83. SELECT  'Fuel Alternatives' AS Industry, 'http://www.mysite.com/alternative-fuels/' AS HREF,
  84. ROUND(AVG(100*(PCurr-P1)/P1),1) AS 'Day',  
  85. ROUND(AVG(100*(PCurr-P2)/P2),1) AS 'Week',  
  86. ROUND(AVG(100*(PCurr-P4)/P4),1) AS 'Qtr',  
  87. ROUND(AVG(100*(PCurr-P5)/P5),1) AS 'Year'    
  88. FROM (
  89.     SELECT DISTINCT    a_ticker AS Ticker FROM table_a  WHERE a_rfrindustry LIKE '%Fuel alternatives%' AND a_current=1) AS table_a    
  90.     LEFT OUTER JOIN (
  91.     SELECT d_ticker AS TickerCurr, d_priceprevclose AS PCurr  FROM table_d  WHERE d_updated = (  SELECT MAX( d_updated )  FROM table_d)  ) AS table_dc ON Ticker = TickerCurr
  92.     LEFT OUTER JOIN (  SELECT d_ticker AS Ticker1, d_priceprevclose AS P1  FROM table_d  WHERE d_updated = (  SELECT MAX( d_updated )  FROM table_d  WHERE d_updated <= ( DATE_SUB( (  SELECT MAX( d_updated )  FROM table_d ) , INTERVAL 1 DAY ))))  AS table_d1  ON Ticker = Ticker1
  93.     LEFT OUTER JOIN (  SELECT d_ticker AS Ticker2, d_priceprevclose AS P2  FROM table_d  WHERE d_updated = (  SELECT MIN( d_updated )  FROM table_d  WHERE d_updated >= ( DATE_SUB( (  SELECT MAX( d_updated )  FROM table_d ) , INTERVAL 1 WEEK ))))  AS table_d2  ON Ticker = Ticker2
  94.     LEFT OUTER JOIN (  SELECT d_ticker AS Ticker4, d_priceprevclose AS P4  FROM table_d  WHERE d_updated = (  SELECT MIN( d_updated )  FROM table_d  WHERE d_updated >= ( DATE_SUB( (  SELECT MAX( d_updated )  FROM table_d ) , INTERVAL 1 QUARTER ))))  AS table_d4  ON Ticker = Ticker4
  95.     LEFT OUTER JOIN (  SELECT d_ticker AS Ticker5, d_priceprevclose AS P5  FROM table_d  WHERE d_updated = (  SELECT MIN( d_updated )  FROM table_d  WHERE d_updated >= ( DATE_SUB( (  SELECT MAX( d_updated )  FROM table_d ) , INTERVAL 1 YEAR ))))   AS table_d5  ON Ticker = Ticker5    
  96.  
  97.  
  98. ORDER BY Industry
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!