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