mercredi 5 août 2015

SQL Random Item for each USER

I have been searching and cannot find the right way to do this. In my query below, What I need is one random C.CREDENTIALING_K for each U.USER_K. I know the newid() will be needed somewhere, I just cannot find the proper place to put it.

select 
    U.USER_K,
    U.FULLNAME as 'Chg_By',
    CONVERT(DATE,AL.AUDITDATETIME) as 'Verif_Date',
    P.ID,
    P.LONGNAME,
    CONVERT(DATE,P.DATEOFBIRTH) as 'DOB',
    C.entity_k,
    R.DESCRIPTION as 'CVI_TYPE',
    C.CREDENTIALING_K,
    CG.GROUPDESCRIPTION,
    C.APPLICATION_RECEIVED,
    R1.DESCRIPTION as 'Cur_STATUS',
    CONVERT(DATE,C.USERDEF_D3) as 'MSO_DUE_DT'
from
    VisualCACTUS.AUDITLOG AL
        JOIN VisualCACTUS.USERS U
        on U.user_k = AL.USER_K
        join VisualCACTUS.CREDENTIALING C
            JOIN VisualCACTUS.PROVIDERS P
            on P.provider_k = C.PROVIDER_K
            JOIN visualcactus.CREDENTIALINGGROUP CG
            on CG.CREDENTIALINGGROUP_K = C.CREDENTIALINGGROUP_K
            JOIN VisualCACTUS.REFTABLE R
            on R.reftable_k = CG.TYPE_RTK
            JOIN VisualCACTUS.REFTABLE R1
            ON R1.REFTABLE_K = C.CREDENTIALINGSTATUS_RTK
            --JOIN VisualCACTUS.CREDENTIALINGASSIGNMENTS CA
            --on CA.credentialing_k = CA.credentialing_k
        on C.CREDENTIALING_K = AL.FILE_PRIMARYKEY
where
    AUDITLOG_K in (select AUDITLOG_K from VisualCACTUS.AUDITLOG_RECORDLEVEL where TABLE_NAME = 'CREDENTIALING                 ' 
                    and
                    AUDITLOG_RECORDLEVEL_K in (SELECT AUDITLOG_RECORDLEVEL_K from VisualCACTUS.AUDITLOG_FIELDLEVEL where NEWVALUE_SHORT = 'D2680X38F3'))
    and
    C.USERDEF_L1 = 0
    and 
    CG.TYPE_RTK NOT IN ('D2870MPPSO','D2LC0YR0AR','D2DD1EIY5X')
    and
    CG.TYPE_RTK NOT LIKE ('SSP%')
    and
    C.credentialing_k not in (select credentialing_k from VisualCACTUS.CREDENTIALINGASSIGNMENTS where EA_K in (select ea_k from visualcactus.entityassignments where entity_k in ('HCA0000039',
'HCA0000040',
'HCA0000041',
'HCA0000043',
'HCA0000096',
'HCA0000095',
'HCA0000337',
'HCA0000903',
'HCA0000904',
'HCA0000905',
'HCA0000906',
'HCA0000080')))
    and
    CONVERT(DATE, AUDITDATETIME) = DATEADD(day, -1, convert(date, GETDATE()))
order by 'Chg_By'




Aucun commentaire:

Enregistrer un commentaire