mardi 8 septembre 2015

Selecting 1 random item per user. Many tables inlcuded in my query

I have seen several ways of selecting random records from a table using several methods. However, my need I am sure is here, I just cannot find it. I have a query using several tables. What my end goal is is to have one random record for each user returned.

In my result set, I get users and the work items they have. What I need is to only return one random work item per user. This is where I am getting stuck. ANy assistance would be greatly appreciated.

Here is my code. What I need is 1 random C.credentilaing_k per user. I am using SQL 2012.

select 
    U.FULLNAME as 'Chg_By',
    CONVERT(DATE,AL.AUDITDATETIME) as 'DE_Date',
    P.ID,
    P.LONGNAME,
    CONVERT(DATE,P.DATEOFBIRTH) as 'DOB',
    C.CREDENTIALING_K,
    C.entity_k,
    R.DESCRIPTION as 'CVI_TYPE',
    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 = C.TYPE_RTK
            JOIN VisualCACTUS.REFTABLE R1
            ON R1.REFTABLE_K = C.CREDENTIALINGSTATUS_RTK
        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 = 'D2LC0YSXXW'))
    and
    CONVERT(DATE, AUDITDATETIME) = DATEADD(day, -1, convert(date, GETDATE()))




Aucun commentaire:

Enregistrer un commentaire