Post reply

Howe do I get a sequential ID in my SELECT STATEMENT (

  • February 9, 2017 at 10:21 am

    #401588

    This works fine .. But I need every row to have a unique ID starting at 1 and going like 1,2,3,4,5.......
    I am sure this should be a  easy fix
    How ?


    Select
    ROW_NUMBER() OVER
    (
    PARTITION BY ISNULL(NameTitle,'') + ISNULL(Address,'') + ISNULL(CityStateZipCode,'')
    ORDER BY NameTitle, Address,CityStateZipCode
    ) as ID, *

    FROM
    (

    Select NameTitle,Specialty,Specialties,Address,CityStateZipCode,Phone,County,HospitalNames,IsAcceptingNewPatient,
    [Network Participation]
    FROM
    (

     SELECTLastName + ', ' + FirstName + ' ' + PPDGR as NameTitle,
    Specialty as Specialty,
    CASE
    WHEN (P.PTYPE LIKE 'PCPS%' OR P.PTYPE LIKE 'SPEC%') THEN REPLACE(ps.Specialties,',', ', ')
    ELSE '' END
    as Specialties,
    PADD1 + ' ' + ISNULL(PADD2,'') as Address,
    PCITY + ', ' + PSTAT + ' ' + PAZIP as CityStateZipCode,
    PPHON as Phone,
    ISNULL(p.County,'NA') as County,
    CASE
    WHEN (P.PTYPE LIKE 'PCPS%' OR P.PTYPE LIKE 'SPEC%') THEN REPLACE(phs.HospitalNames,',', ', ')
    ELSE ''
    END as HospitalNames,
    Case when IsAcceptingNewPatient =1 Then 'Yes' ELSE 'No' END as IsAcceptingNewPatient ,
    'P' as [Network Participation]
      FROM MHPAPP2.ProviderSystem.dbo.Providers as p WITH ( NOLOCK )
      LEFT JOIN MHPAPP2.ProviderSystem.dbo.DirectoryProviderHospitals as phs WITH ( NOLOCK )
       ON p.PNPID = phs.PNPID
      LEFT JOIN MHPAPP2.ProviderSystem.dbo.DirectoryProviderSpecialties as ps WITH ( NOLOCK )
      ON p.PNPID = ps.PNPID
     --WHERE Specialty <> 'Hospice' --Moderage: TAKE THIS OUT WHEN YOU ARE DONE WITH BRIANS PROJECT
     WHERE
    (P.PTYPE NOT LIKE 'LTSS%')
    AND
    Specialty <> 'Hospice'
    AND
    P.PlanDescription LIKE '%Medicaid%'
    AND
    (
    P.PTYPE LIKE 'PCPS%'
    OR
    P.PTYPE LIKE 'SPEC%'
    )

    )X
    GROUP BY NameTitle,
    Specialty,
    Specialties,
    Address,CityStateZipCode,Phone,
    County,HospitalNames,
    IsAcceptingNewPatient,[Network Participation]

    )D

  • February 9, 2017 at 10:24 am

    #1927591

    Ahhh...
    That was a simple fix, why didn't I think about that.....
    OK, DONE!

    Select
    ROW_NUMBER() OVER
    (
    PARTITION BY NULL
    ORDER BY NameTitle, Address,CityStateZipCode
    ) as ID, *

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply