Home suggestions with unpivot SQL query
Reply: 0

suggestions with unpivot SQL query

Arman S
1#
Arman S Published in 2018-02-13 15:47:58Z

I have the following query that uses unpivot. The query works fine in oracle but when I use the same query in SQL, I get syntax error. any suggestions would greatly be appreciated.

SELECT 
    'BCBS' AS Payor,
    upiv.Measure AS Measure,
    prov.prv_npi AS AttributedProviderNPI,
    upiv.pcp_number AS AttributedProviderPayorID,
    SUBSTRing(upiv.member_id, 0, 9) AS SubscriberID,
    right(upiv.member_id, 2) AS MemberNumber,
    'BCBS-MA|MEM' AS IdLookupType,
    SUBSTRing(upiv.member_id, 0, 9) + '|' + right(upiv.member_id, 2) AS IdLookupId,
    '2017-01-01' AS DenominatorDate,
    CAST(upiv.InNumeratorBit AS numeric) AS InNumeratorBit,
    cast(upiv.NumeratorRelevantDate as date) AS NumeratorRelevantDate,
    NULL AS ExcludedRelevantDate, 
    NULL AS Data
  FROM
    (
      SELECT
       pcp_number,
        member_id,
        ldl_numerator,
        ldl_date_of_service,
        eye_numerator,
        eye_date_of_service,
        hba1c_numerator,
        COALESCE(cdc.hba1c_2_date_of_service, cdc.hba1c_1_date_of_service) AS hba1c_date_of_service,
        cdc.neph_numerator,
        cdc.neph_date_of_service
      FROM
        bcbs_metriceval_cdc 
    ) 
    UNpivot (

    InNumeratorBit,
    NumeratorRelevantDate

  FOR Measure IN 
  (
     ldl_numerator, ldl_date_of_service AS 'CDCLDL',
    eye_numerator, eye_date_of_service AS 'CDCEYE',
    hba1c_numerator, hba1c_date_of_service AS 'CDCHBA',
    neph_numerator, neph_date_of_service AS 'CDCNEPH'
  )
) upiv
LEFT JOIN [dbo].[BCBS_PROVIDERS] prov ON prov.prv_num = upiv.pcp_number
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.299573 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO