Joins on RequestedTask, SecurityIdentity, and Task table
Posted: Thu Jun 02, 2016 10:47 am
Hello SoftPro,
Hope I can get some assistance from you as I'm trying to create a small dashboard report for our post closing employees based on 6 checklist task-Completed date and 1 requested task-Requested Date..
The issue I'm having is joining the security identity table with checklist task and requested task as I'm looking for two different dates as mentioned above.
The SQL I wrote is here:
The result shows:
I obviously joined the securityidentity table with the Task table by the completedID field to determine who completed the 6 tasks.
However, I need to show who REQUESTED the requested task. On the parsed result picture above, Recorded Docs Requested, was actually Requested by Kathy and not Stacey.
So, with all that said, how would I alter my securityidentity table join to reflect the correct information?
Hope I can get some assistance from you as I'm trying to create a small dashboard report for our post closing employees based on 6 checklist task-Completed date and 1 requested task-Requested Date..
The issue I'm having is joining the security identity table with checklist task and requested task as I'm looking for two different dates as mentioned above.
The SQL I wrote is here:
Code: Select all
select
o.Number as 'OrderNo',
s.FullName as 'CPC Emp',
MAX(case when t.description='Loan Package to Lender' then DATEADD(hour, -4,t.CompletedDate) else NULL end) as 'Loan Pck To Lender',
MAX(case when t.description='Recording Audit' then DATEADD(hour, -4,t.CompletedDate) else NULL end) as 'Recording Audit',
MAX(case when t.description='Recorded Docs' then DATEADD(hour, -4,rt.RequestedDate) else NULL end) as 'Recorded Docs Requested',
MAX(case when t.description='Recorded Docs to Lender' then DATEADD(hour, -4,t.CompletedDate) else NULL end) as 'Recorded Docs to Lender',
MAX(case when t.description='Recorded Docs to Purchaser' then DATEADD(hour, -4,t.CompletedDate) else null end) as 'Recorded Docs to Purchaser',
MAX(case when t.description='Title Policy to Lender' then DATEADD(hour, -4,t.CompletedDate) else NULL end) as 'TP to Lender',
MAX(case when t.description='Title Policy to Purchaser' then DATEADD(hour, -4,t.CompletedDate) else NULL end) as 'TP Purchaser'
FROM pf.OrderInfo oi
INNER JOIN pfm.[Order] o
on (o.RootId# = oi.rootid)
INNER JOIN core.Profile op
ON (oi.OwningProfileID = op.ID)
INNER JOIN zref.OrderStatus os
ON (oi.OrderStatus = os.ID)
INNER JOIN zref.ProductType pt
on (o.ProductTypeID = pt.ID and pt.ID <>'15')
INNER JOIN pfm.Task t
ON (t.RootId# = oi.RootID
and (t.Description in ('Loan Package to Lender','Recording Audit','Recorded Docs to Lender',
'Recorded Docs to Purchaser', 'Title Policy to Lender','Title Policy to Purchaser','Recorded Docs')
) )
left outer JOIN pfm.RequestedTask rt
ON (rt.RootId# = t.RootId# and rt.Id#=t.Id# and rt.LastId# = t.LastId#)
LEFT OUTER JOIN core.SecurityIdentity s
ON (s.ID = t.CompletedByID)
WHERE
(op.Name like 'BH104%' -- WNCW Res Profiles Begin.
or op.name like 'CL170%'
or op.name like 'CM104%'
or op.name like 'IN103%'
or op.name like 'SF107%'
or op.name like 'AW170%'
or op.name like 'JW170%'
or op.name like 'KH170%'
or op.name like 'PM170%'
or op.name like 'SC170%'
or op.name like 'EC102%'
or op.name like 'MT175%'
or op.name like 'CU108%'
or op.name like 'NF106%'
or op.name like 'GA105%'
or op.name like 'SL105%'
or op.name like 'PC135%'
or op.name like 'PE103%'
or op.name like 'OC130%'
or op.name like 'WF130%'
or op.name like 'SS100%'
or op.name like 'SO191%'
or op.name like 'CA115%'
or op.name like 'DO115%'
or op.name like 'WC115%'
or op.name like 'WO115%' --WNCW RES Profiles End.
and op.Enabled=1
and oi.IsTemplate=0
and oi.Number not like '%test%'
and oi.Number not like 'tr%')
--and t.CompletedDate between '2016-1-1' and '2016-4-30'
and oi.Number='WC115-13-0048'
and s.fullname is not null
group by s.fullname, o.Number
order by 2 desc
I obviously joined the securityidentity table with the Task table by the completedID field to determine who completed the 6 tasks.
However, I need to show who REQUESTED the requested task. On the parsed result picture above, Recorded Docs Requested, was actually Requested by Kathy and not Stacey.
So, with all that said, how would I alter my securityidentity table join to reflect the correct information?