Sql

Question

could you find what wrong in this query?

Answer 1

select bi.bookingnumber, wf.GroupName ,wfsi.id,wei.JobNumber
into #Pickingjobs
from wh_workflowstepinstance WFSI
left join Wh_WorkflowStep WF on WF.id = WFSI.WorkflowStepId
left join Wh_ExecutionItem WEI with (nolock) on Wei.WorkflowStepInstanceId = wfsi.Id
left join Bkg_BookingProcess BP on bp.ProcessNumber = wei.JobNumber
left join BKG_bookinginfo BI on BP.BookingInfoId = bi.Id
where wfsi.Status in ( 'InProgress') and groupname Like '%ULI Picking%'
and BI.OfficeId='d64b47f5-699c-4ae9-94f6-8837af1d2d91' and BI.ClientId='4cbc942a-eb29-4845-aa32-68af677bfd22'
and Bp.status = 'Initiated' and bp.ExecutingOfficeId = 'd64b47f5-699c-4ae9-94f6-8837af1d2d91'
and WEI.ClientId='4cbc942a-eb29-4845-aa32-68af677bfd22'
group by wf.GroupName ,wfsi.id,wei.JobNumber,bi.bookingnumber

Answer 2

select Pj.GroupName , Pj.BookingNumber ,wei.JobNumber,
Sum(Case when Wei.direction = 'In' then wei.QuantityValue else wei.QuantityValue *-1 end) as RemainingOrdQty,
Sum(Case when Wei.direction = 'In' then wei.QuantityValue else 0 end) as orderqty,
Sum(Case when Wei.direction = 'In' then 0 else wei.QuantityValue end) as Suggested
from #Pickingjobs PJ
inner join Wh_ExecutionItem WEI with (nolock) on Pj.Id = wei.WorkflowStepInstanceId
left join Wh_WorkflowStepInstance WFSI with (nolock) on WFSI.id = WEI.WorkflowStepInstanceId
left join Wh_WorkflowStep WF with (nolock) on WF.id = WFSI.WorkflowStepId
left join Cust_Product cp with (nolock) on cp.id = wei.productid
where Pj.groupname like '%Picking%' and wei.IsDeleted = 0 --and wei.status in ('NotStarted','InProgress','Completed')
and WEI.ClientId ='4cbc942a-eb29-4845-aa32-68af677bfd22' and WFSI.IsActive=1
Group by Pj.GroupName ,Pj.BookingNumber, wei.JobNumber
having Sum(Case when Wei.direction = 'In' then wei.QuantityValue else wei.QuantityValue *-1 end)