Advertisement
Guest User

Untitled

a guest
Jun 26th, 2017
65
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 1.15 KB | None | 0 0
  1. andy:
  2. WITH cteContractFipsMatches (contract_h_name, plan_name, segment, fipsRowToMatch) AS (  
  3.  SELECT
  4.   t1.contract_h_name
  5.   ,t1.plan_name
  6.   ,t1.segment
  7.   ,MIN(f1.rownum) AS fipsRowToMatch
  8.  FROM  
  9.   mds_schema.mds_wc_tmp_moc_plan_county t1
  10.   INNER JOIN
  11.   mds_schema.mds_vFips_wc f1 ON t1.fips = f1.fips
  12.  GROUP BY contract_h_name, plan_name, segment
  13. )  
  14.  
  15. SELECT DISTINCT  
  16.  z.[zip code],f1.fips, c1.county_name -- , cte1.contract_h_name, cte1.plan_name, cte1.segment, t1.planDetailsUrl
  17. FROM  
  18.  cteContractFipsMatches cte1
  19. INNER JOIN  
  20.  mds_schema.mds_vFips_wc f1 ON cte1.fipsRowToMatch = f1.rownum
  21. INNER JOIN
  22.  mds_schema.mds_county c1 ON f1.fips = c1.fips
  23. INNER JOIN
  24.  mds_schema.mds_wc_tmp_moc_plan_county t1 ON cte1.contract_h_name = t1.contract_h_name
  25.  AND
  26.  cte1.plan_name = t1.plan_name
  27.  AND
  28.  cte1.segment = t1.segment
  29.  AND
  30.  f1.fips = t1.fips
  31. LEFT JOIN
  32.  --mds_schema.mds_zip_preferred z on (z.fips=f1.fips)
  33. (
  34.     SELECT p.*
  35.     FROM
  36.     mds_schema.mds_zip_preferred p
  37.     INNER JOIN
  38.     (
  39.         SELECT
  40.             CASE
  41.                 WHEN len(fips)=4 THEN '0'+fips
  42.                 ELSE fips
  43.             END AS FIPS
  44.         FROM
  45.             dbo.alex_fips
  46.     ) a ON a.fips=p.fips
  47. ) z ON (z.fips=f1.fips)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement