asked on

Customize the query to get max column record from sub query

Hi Experts,
Advance Thanks!
I need the great help to do subquery . Have attached the original script and attached screen shot.
Currently am getting 9 rows.

Expected output is count 6
There are multiple fileids(10671,10672,10700) in the original output (count 9). i need to pick max(isApprove) for those mutiple unique fileids...

My expected out is attached..
Please help me to tune this query...

Kind regards,
Pooja
Current-Output.PNG
Expected-Output.PNG
query.txt

Raja Jegan R

Try this one..

select actionTaken, name, userRole, comment, submitDate, isFileHistory, isFileComment, taskFileId, fileId, downloadFileId, fileRename, fileExt, fileSize, isReqAddInfo, max(isapprove) isApprove, raiComments, fileReviewStatusId
from ( 
select  CASE WHEN rfs.[StatusId] =1then 'File Upload '  
   WHEN  rfs.[StatusId] = 2 then 'File Upload '  
   WHEn  rfs.[StatusId] = 3then 'File Upload'  
   WHEn  rfs.[StatusId] = 4 then  rfs.[StatusName]   
   WHEN rfs.[StatusId] =5 then 'File Upload '   
   WHEN rfs.[StatusId] =6 then rfs.[StatusName] end as actionTaken,  
  
   CASE WHEN rfs.[StatusId] =4 then  (ra.FirstName + ' ' + ra.LastName)   
   else (A.FirstName + ' ' + A.LastName)end  as name,  
  
   CASE WHEN rfs.[StatusId] =4 then  rr.RoleName +'('+rv.[ViewName] +')'   
   else r.RoleName end  as userRole,  
  
   null as comment,  
  
   CASE WHEN rfs.[StatusId] =4 then  fileStatus.ModifiedOn   
   else  COALESCE(tfchild.UploadTime, tfparent.UploadTime) end   as submitDate,  
  
   CAST(  
    CASE   
     WHEN tfchild.TaskFileId is null   
      THEN 0   
     ELSE 1  
    END as bit) as isFileHistory,  
  
       CAST(  
    CASE   
     WHEN rfcHistory.Comment is null   
      THEN 0   
     ELSE 1  
    END as bit) as isFileComment,  
  
    tfparent.TaskFileId as taskFileId,  
    tfparent.FileId as fileId ,  
     COALESCE(tfchild.[FileId], tfparent.[FileId]) as [downloadFileId] ,  
  
   COALESCE(tfchild.[FileName], tfparent.[FileName])  as [fileName],  
                
    
  reverse(Substring(reverse(tfparent.[FileName]), Charindex('.', reverse(tfparent.[FileName]))+1,Len(tfparent.[FileName])))as fileRename,  
  
   reverse(left(reverse(tfparent.[FileName]), charindex('.', reverse(tfparent.[FileName])) -1)) as fileExt,  
  
 COALESCE(tfchild.fileSize, tfparent.fileSize)  as fileSize,  
  
    CAST(  
    CASE   
     WHEN rf.[Status] = 4  
      THEN 1  
     WHEN rf.[Status] = 6  
      THEN 1    
     ELSE 0  
    END as bit) as isReqAddInfo,  
    CAST(  
    CASE   
     WHEN rf.[Status] = 5  
      THEN 1   
     ELSE 0  
    END as bit) as isApprove,  
      
    raiComments=CASE   
     WHEN rf.[Status] = 4  
      THEN rfcFinal.Comment    
     WHEN rf.[Status] = 6  
      THEN rfcFinal.Comment  
     WHEN rf.[Status] = 5  
      THEN COALESCE(rfcFinal.Comment  , '')   
     ELSE ''  
     end,  
  
  fileStatus.[Status] as fileReviewStatusId  
    
from [HCP_Task_Staging].dbo.TaskFile tfparent  
  
 
inner join [HCP_Task_Staging].dbo.Task t on t.TaskInstanceId = tfparent.TaskInstanceId and t.SequenceId = 0  
inner join [HCP_Task_Staging].dbo.Prod_TaskXref xref on xref.TaskInstanceId = t.TaskInstanceId  
 
inner join [HCP_Task_Staging].dbo.TaskFile_FolderMapping tf on tf.[TaskFileId] = tfparent.TaskFileId and tf.FolderKey = @FolderKey  
--tables join to get lender info  
left join HCP_Authentication a on a.UserName = t.AssignedBy  
left join webpages_UsersInRoles l on l.UserId = a.userid  
left join webpages_Roles r on r.roleid = l.roleid  

left join [HCP_Task_Staging].dbo.ReviewFileStatus rf on tfparent.TaskFileId = rf.TaskFileId and xref.AssignedTo = rf.ReviewerUserId and xref.ViewId = rf.ReviewerProdViewId  
left join [HCP_Task_Staging].dbo.ReviewStatusList rsl on rsl.StatusId = rf.[Status]  
 
left join (   
    
   select rfs1.[TaskFileId],rfs1.[ModifiedOn],[ReviewerUserId],[ReviewerProdViewId],[Status] from [HCP_Task_Staging].[dbo].[ReviewFileStatus] rfs1   
   inner join   
   (select rf.TaskFileId, max(rf.ModifiedOn) as ModifiedOn   
   from [HCP_Task_Staging].[dbo].[ReviewFileStatus] rf  
   inner join [HCP_Task_Staging].[dbo].[TaskFile] tf on tf.[TaskFileId] = rf.TaskFileId  
   inner join [HCP_Task_Staging].[dbo].[Prod_TaskXref] tx on tx.TaskInstanceId = tf.[TaskInstanceId]  
   inner join [dbo].[webpages_UsersInRoles] ur on ur.[UserId] = rf.ReviewerUserId  
   inner join  [dbo].[webpages_Roles] r on r.[RoleId] = ur.[RoleId]  
   where tx.AssignedTo = rf.ReviewerUserId and tx.ViewId = rf.ReviewerProdViewId
   group by rf.TaskFileId) rfs2 on rfs2.TaskFileId = rfs1.TaskFileId and rfs2.ModifiedOn = rfs1.ModifiedOn  
)fileStatus  on fileStatus.TaskFileId = tfparent.TaskFileId  
left join [HCP_Task_Staging].dbo.ReviewStatusList rfs on rfs.StatusId = fileStatus.[Status] 
 
left join webpages_UsersInRoles ru on ru.UserId = fileStatus.ReviewerUserId  
left join HCP_Authentication ra on ra.UserID = fileStatus.ReviewerUserId  
left join webpages_Roles rr on rr.roleid = ru.roleid  
left join [HCP_Task_Staging].[dbo].[Prod_View] rv on rv.[ViewId] = fileStatus.[ReviewerProdViewId]  
--join to get latest user's comment   
left join  (select rfc.FileTaskId,rfc.comment,rfc.CreatedBy,rfc.ReviewerProdViewId from [HCP_Task_Staging].dbo.ReviewFileComment rfc  
   inner join   
   (select FileTaskId,CreatedBy, max(CreatedOn) as MaxDate   
    from [HCP_Task_Staging].dbo.[ReviewFileComment]  group by FileTaskId , [CreatedBy] )rfcl on rfc.FileTaskId = rfcl.FileTaskId and rfc.CreatedOn = rfcl.MaxDate and  rfc.CreatedBy = rfcl.CreatedBy)rfcFinal on rfcFinal.FileTaskId = tfparent.TaskFileId and
  xref.AssignedTo = rfcFinal.CreatedBy and xref.ViewId = rfcFinal.ReviewerProdViewId  
  
left join  (select rfc.FileTaskId,rfc.comment from [HCP_Task_Staging].dbo.ReviewFileComment rfc  
   inner join   
   (select FileTaskId, max(CreatedOn) as MaxDate   
    from [HCP_Task_Staging].dbo.[ReviewFileComment]  group by FileTaskId  )rfcl on rfc.FileTaskId = rfcl.FileTaskId and rfc.CreatedOn = rfcl.MaxDate )rfcHistory on rfcHistory.FileTaskId = tfparent.TaskFileId   
 
left join (select tf1.FileId, tf1.TaskFileId,tf1.[FileName],tf1.FileData,tf1.fileSize,tf1.UploadTime,tf2.ParentTaskFileId   
  from [HCP_Task_Staging].dbo.TaskFile tf1  
  inner join ( select   tfh.ChildTaskFileId ,tfh.ParentTaskFileId  
        from [HCP_Task_Staging].dbo.TaskFile tfc   
        inner join [HCP_Task_Staging].dbo.TaskFileHistory tfh on tfc.TaskFileId = tfh.ParentTaskFileId  
        inner join  (select tfpf.ParentTaskFileId, max(tfpf.CreatedOn) as MaxDate  
            from [HCP_Task_Staging].dbo.TaskFileHistory tfpf  
            group by tfpf.ParentTaskFileId) basetable on basetable.ParentTaskFileId = tfh.ParentTaskFileId   
                       and basetable.MaxDate = tfh.CreatedOn  ) tf2 on tf1.TaskFileId = tf2.ChildTaskFileId  
            ) tfchild on tfparent.TaskFileId = tfchild.ParentTaskFileId  
  
  where tfparent.TaskInstanceId =  @TaskInstanceId  
 
  and  (rr.RoleName in ('tester1', 'tester2','tester3')  or r.RoleName in ('tester4'))  
  
  )t 
  --left join ( select Max(isApprove) from t join t t1 on t.fileId=t1.fileId)
  --where t.isApprove=1
GROUP BY actionTaken, name, userRole, comment, submitDate, isFileHistory, isFileComment, taskFileId, fileId, downloadFileId, fileRename, fileExt, fileSize, isReqAddInfo, raiComments, fileReviewStatusId

Open in new window

suresh pondicherry

ASKER

Thanks Raja Jegan R.
Am not able to see output because of this error
Operand data type bit is invalid for max operator.

Kind regards,
Pooja

suresh pondicherry

ASKER

Hi Raja Jegan R,
After modifying the script from
--CAST(
--CASE
-- WHEN rf.[Status] = 5
-- THEN 1
-- ELSE 0
--END as bit) as isApprove,
CAST(
CASE
WHEN rf.[Status] = 5
THEN 1
ELSE 0
END as int) as isApprove,

getting the attached output. But am getting duplicate record (10670). Please find the screen shot.

Kind regards,
Pooja
Current-Output-new-script-.PNG

Raja Jegan R

You got 2 records since there the raicomments column value was unique. added aggregate option to this column as well and try this query..

select actionTaken, name, userRole, comment, submitDate, isFileHistory, isFileComment, taskFileId, fileId, downloadFileId, fileRename, fileExt, fileSize, isReqAddInfo, max(isapprove) isApprove, max(raiComments) raiComments, fileReviewStatusId
from ( 
select  CASE WHEN rfs.[StatusId] =1 then 'File Upload '  
   WHEN  rfs.[StatusId] = 2 then 'File Upload '  
   WHEn  rfs.[StatusId] = 3then 'File Upload'  
   WHEn  rfs.[StatusId] = 4 then  rfs.[StatusName]   
   WHEN rfs.[StatusId] =5 then 'File Upload '   
   WHEN rfs.[StatusId] =6 then rfs.[StatusName] end as actionTaken,  
  
   CASE WHEN rfs.[StatusId] =4 then  (ra.FirstName + ' ' + ra.LastName)   
   else (A.FirstName + ' ' + A.LastName)end  as name,  
  
   CASE WHEN rfs.[StatusId] =4 then  rr.RoleName +'('+rv.[ViewName] +')'   
   else r.RoleName end  as userRole,  
  
   null as comment,  
  
   CASE WHEN rfs.[StatusId] =4 then  fileStatus.ModifiedOn   
   else  COALESCE(tfchild.UploadTime, tfparent.UploadTime) end   as submitDate,  
  
   CAST(  
    CASE   
     WHEN tfchild.TaskFileId is null   
      THEN 0   
     ELSE 1  
    END as bit) as isFileHistory,  
  
       CAST(  
    CASE   
     WHEN rfcHistory.Comment is null   
      THEN 0   
     ELSE 1  
    END as bit) as isFileComment,  
  
    tfparent.TaskFileId as taskFileId,  
    tfparent.FileId as fileId ,  
     COALESCE(tfchild.[FileId], tfparent.[FileId]) as [downloadFileId] ,  
  
   COALESCE(tfchild.[FileName], tfparent.[FileName])  as [fileName],  
                
    
  reverse(Substring(reverse(tfparent.[FileName]), Charindex('.', reverse(tfparent.[FileName]))+1,Len(tfparent.[FileName])))as fileRename,  
  
   reverse(left(reverse(tfparent.[FileName]), charindex('.', reverse(tfparent.[FileName])) -1)) as fileExt,  
  
 COALESCE(tfchild.fileSize, tfparent.fileSize)  as fileSize,  
  
    CAST(  
    CASE   
     WHEN rf.[Status] = 4  
      THEN 1  
     WHEN rf.[Status] = 6  
      THEN 1    
     ELSE 0  
    END as bit) as isReqAddInfo,  
    CAST(  
    CASE   
     WHEN rf.[Status] = 5  
      THEN 1   
     ELSE 0  
    END as int) as isApprove,  
      
    raiComments=CASE   
     WHEN rf.[Status] = 4  
      THEN rfcFinal.Comment    
     WHEN rf.[Status] = 6  
      THEN rfcFinal.Comment  
     WHEN rf.[Status] = 5  
      THEN COALESCE(rfcFinal.Comment  , '')   
     ELSE ''  
     end,  
  
  fileStatus.[Status] as fileReviewStatusId  
    
from [HCP_Task_Staging].dbo.TaskFile tfparent  
  
 
inner join [HCP_Task_Staging].dbo.Task t on t.TaskInstanceId = tfparent.TaskInstanceId and t.SequenceId = 0  
inner join [HCP_Task_Staging].dbo.Prod_TaskXref xref on xref.TaskInstanceId = t.TaskInstanceId  
 
inner join [HCP_Task_Staging].dbo.TaskFile_FolderMapping tf on tf.[TaskFileId] = tfparent.TaskFileId and tf.FolderKey = @FolderKey  
--tables join to get lender info  
left join HCP_Authentication a on a.UserName = t.AssignedBy  
left join webpages_UsersInRoles l on l.UserId = a.userid  
left join webpages_Roles r on r.roleid = l.roleid  

left join [HCP_Task_Staging].dbo.ReviewFileStatus rf on tfparent.TaskFileId = rf.TaskFileId and xref.AssignedTo = rf.ReviewerUserId and xref.ViewId = rf.ReviewerProdViewId  
left join [HCP_Task_Staging].dbo.ReviewStatusList rsl on rsl.StatusId = rf.[Status]  
 
left join (   
    
   select rfs1.[TaskFileId],rfs1.[ModifiedOn],[ReviewerUserId],[ReviewerProdViewId],[Status] from [HCP_Task_Staging].[dbo].[ReviewFileStatus] rfs1   
   inner join   
   (select rf.TaskFileId, max(rf.ModifiedOn) as ModifiedOn   
   from [HCP_Task_Staging].[dbo].[ReviewFileStatus] rf  
   inner join [HCP_Task_Staging].[dbo].[TaskFile] tf on tf.[TaskFileId] = rf.TaskFileId  
   inner join [HCP_Task_Staging].[dbo].[Prod_TaskXref] tx on tx.TaskInstanceId = tf.[TaskInstanceId]  
   inner join [dbo].[webpages_UsersInRoles] ur on ur.[UserId] = rf.ReviewerUserId  
   inner join  [dbo].[webpages_Roles] r on r.[RoleId] = ur.[RoleId]  
   where tx.AssignedTo = rf.ReviewerUserId and tx.ViewId = rf.ReviewerProdViewId
   group by rf.TaskFileId) rfs2 on rfs2.TaskFileId = rfs1.TaskFileId and rfs2.ModifiedOn = rfs1.ModifiedOn  
)fileStatus  on fileStatus.TaskFileId = tfparent.TaskFileId  
left join [HCP_Task_Staging].dbo.ReviewStatusList rfs on rfs.StatusId = fileStatus.[Status] 
 
left join webpages_UsersInRoles ru on ru.UserId = fileStatus.ReviewerUserId  
left join HCP_Authentication ra on ra.UserID = fileStatus.ReviewerUserId  
left join webpages_Roles rr on rr.roleid = ru.roleid  
left join [HCP_Task_Staging].[dbo].[Prod_View] rv on rv.[ViewId] = fileStatus.[ReviewerProdViewId]  
--join to get latest user's comment   
left join  (select rfc.FileTaskId,rfc.comment,rfc.CreatedBy,rfc.ReviewerProdViewId from [HCP_Task_Staging].dbo.ReviewFileComment rfc  
   inner join   
   (select FileTaskId,CreatedBy, max(CreatedOn) as MaxDate   
    from [HCP_Task_Staging].dbo.[ReviewFileComment]  group by FileTaskId , [CreatedBy] )rfcl on rfc.FileTaskId = rfcl.FileTaskId and rfc.CreatedOn = rfcl.MaxDate and  rfc.CreatedBy = rfcl.CreatedBy)rfcFinal on rfcFinal.FileTaskId = tfparent.TaskFileId and
  xref.AssignedTo = rfcFinal.CreatedBy and xref.ViewId = rfcFinal.ReviewerProdViewId  
  
left join  (select rfc.FileTaskId,rfc.comment from [HCP_Task_Staging].dbo.ReviewFileComment rfc  
   inner join   
   (select FileTaskId, max(CreatedOn) as MaxDate   
    from [HCP_Task_Staging].dbo.[ReviewFileComment]  group by FileTaskId  )rfcl on rfc.FileTaskId = rfcl.FileTaskId and rfc.CreatedOn = rfcl.MaxDate )rfcHistory on rfcHistory.FileTaskId = tfparent.TaskFileId   
 
left join (select tf1.FileId, tf1.TaskFileId,tf1.[FileName],tf1.FileData,tf1.fileSize,tf1.UploadTime,tf2.ParentTaskFileId   
  from [HCP_Task_Staging].dbo.TaskFile tf1  
  inner join ( select   tfh.ChildTaskFileId ,tfh.ParentTaskFileId  
        from [HCP_Task_Staging].dbo.TaskFile tfc   
        inner join [HCP_Task_Staging].dbo.TaskFileHistory tfh on tfc.TaskFileId = tfh.ParentTaskFileId  
        inner join  (select tfpf.ParentTaskFileId, max(tfpf.CreatedOn) as MaxDate  
            from [HCP_Task_Staging].dbo.TaskFileHistory tfpf  
            group by tfpf.ParentTaskFileId) basetable on basetable.ParentTaskFileId = tfh.ParentTaskFileId   
                       and basetable.MaxDate = tfh.CreatedOn  ) tf2 on tf1.TaskFileId = tf2.ChildTaskFileId  
            ) tfchild on tfparent.TaskFileId = tfchild.ParentTaskFileId  
  
  where tfparent.TaskInstanceId =  @TaskInstanceId  
 
  and  (rr.RoleName in ('tester1', 'tester2','tester3')  or r.RoleName in ('tester4'))  
  
  )t 
  --left join ( select Max(isApprove) from t join t t1 on t.fileId=t1.fileId)
  --where t.isApprove=1
GROUP BY actionTaken, name, userRole, comment, submitDate, isFileHistory, isFileComment, taskFileId, fileId, downloadFileId, fileRename, fileExt, fileSize, isReqAddInfo, fileReviewStatusId

Open in new window

suresh pondicherry

ASKER

i applied that already, till gives 2 rows. raiComments is the text field

ASKER CERTIFIED SOLUTION

Raja Jegan R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

suresh pondicherry

ASKER

Thanks Raja Jegan R. It worked well.

Kind regards,
Pooja

Raja Jegan R

Welcome..

suresh pondicherry

ASKER

Hi Raja Jegan R,
When i added new column in the select, it is not working...can i send you the query now..

Kind regards,
Pooja

Raja Jegan R

Sure, can you send me the query you have modified, error or records returned and your expected result set so that I can understand your scenario better and modify it accordingly.