SQL joins showing duplicate transactions

Hi All,
Advance thanks. I need a advise on following query.
Issue: Getting multiple rows (TaskName)
Expectation: Get recent record for each TaskName

Query:
declare @userId int  
set @userId= (select UserID from [HCP_Live_db_Staging].dbo.HCP_Authentication where UserName= 'venkateshm.pwlm@gmail.com')  
select  taskStep .TaskStepId as StatusId,
                  
                  '(' + opa. fhaNumber +')' + PA.ProjectTypeName as TaskName, fhaRequest. ProjectName ,task.TaskInstanceId as ParentChildInstanceId ,  
                                            lender . Lender_Name as lenderName ,fhaRequest . FHANumberRequestId as FhaRequestInstanceId, task.PageTypeId as FhaRequestType,  
                                            users . FirstName+ ' ' +users . LastName as AssignedTo ,task.AssignedBy as AssignedBy,  
                                                      null as groupid ,  
                                                      'Form 290' as productiontype ,  
                                                      task .StartTime as LastUpdated ,
                                            opa . aeComments as Comments ,DATEDIFF ( day,  
                                                      task . StartTime, GETDATE ()) AS Duration,    
                          taskStep . TaskStepNm as Status,
                          --max(task . TaskStepId) as Status ,
                          taskStep .TaskStepId as StatusId,  
                          pageType. PageTypeDescription as ProductionTaskType ,  pageType.orderby,  
                                                   users.UserID as   CreatedBy  
 
                  from [HCP_Task_Staging].dbo.task task
                  join [HCP_Task_Staging].dbo.Prod_Form290Task form290  
                         on form290.TaskInstanceID = task.TaskInstanceId  
                  join [HCP_Live_db_Staging].dbo.opaform opa on opa.TaskInstanceId = form290.ClosingTaskInstanceID  
                  join [HCP_Live_db_Staging].dbo.Prod_FHANumberRequest fhaRequest  
                         on opa. fhaNumber =fhaRequest . fhanumber  
                left join [HCP_Live_db_Staging].dbo.[Prod_ProjectType]  PA  
                        on PA. ProjecttypeId =opa . Projectactiontypeid  
                  left join [HCP_Live_db_Staging].dbo.LenderInfo lender on lender .LenderID =fhaRequest. LenderId  
                  left join [HCP_Live_db_Staging].dbo.HCP_Authentication users on users. UserName= task .AssignedTo  
                  left join [HCP_Live_db_Staging].dbo. HCP_PageType as pageType on pageType. PageTypeId= task .PageTypeId  
                  join [HCP_Task_Staging].dbo.TaskStep taskStep on task. TaskStepId = taskStep. TaskStepId                     
                  where task.PageTypeId = 16 and task.AssignedTo =  'venkateshm.pwlm@gmail.com'
                   and task . TaskStepId= 22 or  task . TaskStepId= 23  
                   --  group by task.AssignedTo,task .StartTime,taskStep .TaskStepId,PA.ProjectTypeName,opa. fhaNumber,
                   --  fhaRequest. ProjectName ,task.TaskInstanceId  ,  lender . Lender_Name,
                   --  fhaRequest . FHANumberRequestId , task.PageTypeId ,
                   --   users . FirstName,users . LastName ,
                        --task.AssignedBy , opa . aeComments  , taskStep . TaskStepNm , taskStep .TaskStepId ,  
                        --  pageType. PageTypeDescription  ,  pageType.orderby,   users.UserID
                        
                        -- having task.PageTypeId = 16 and task.AssignedTo =  'venkateshm.pwlm@gmail.com'
                   --  and Max(task . TaskStepId)= 22 or  Max(task . TaskStepId)= 23  
                        
please see the attachment

Current Result
===========
22      (222-20000)Purchase/Refinance 223(f)
23      (222-20000)Purchase/Refinance 223(f)
22      (111-22111)Purchase/Refinance 223(f)
23      (400-44444)Purchase/Refinance 223(f)
23      (121-21111)Purchase/Refinance 223(f)
23      (121-21212)Purchase/Refinance 223(f)
22      (111-44444)Purchase/Refinance 223(f)
23      (111-44444)Purchase/Refinance 223(f)



Expected Result:
=============
normally status id are 22 or 23
i need to get latest statusId

23      (222-20000)Purchase/Refinance 223(f)
22      (111-22111)Purchase/Refinance 223(f)
23      (400-44444)Purchase/Refinance 223(f)
23      (121-21111)Purchase/Refinance 223(f)
23      (121-21212)Purchase/Refinance 223(f)
23      (111-44444)Purchase/Refinance 223(f)

Kindly let me know if i need to give more clarifications....

Kind regards,
Suresh
Capture.PNG
poojasureshkumarAsked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Be very careful when working with the OR operator. You'll need to use parenthesis to define what's the OR for. By the logic I think the following is what you pretend:
where task.PageTypeId = 16 and task.AssignedTo =  'venkateshm.pwlm@gmail.com' 
    and (task.TaskStepId= 22 or  task.TaskStepId= 23)

Open in new window

Alternative is to use the IN clause, so you won't need the OR operator:
where task.PageTypeId = 16 and task.AssignedTo =  'venkateshm.pwlm@gmail.com'
  and task.TaskStepId IN (22,23)

Open in new window

0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
  and task . TaskStepId= 22 or  task . TaskStepId= 23  

if you took out the taskstepid 22 do you get your results?
0
 
poojasureshkumarAuthor Commented:
Hi Kyle,
I need both status 22 and 23. But some TaskName has both statuses . In that scenario, i need to pick status 23.

Kind regards,
Suresh
0
 
awking00Commented:
Add this to the beginning of your query -

select TaskName, max(StatusId) as StatusId from

<your query> ==> Be sure to enclose in parentheses and provide an alias

Then this at the end -
group by TaskName;
0
 
poojasureshkumarAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.