Joins on RequestedTask, SecurityIdentity, and Task table

Discussions related to integration with SoftPro Standard or SoftPro Enterprise editions.
Post Reply
WNCWSupport
Posts: 4
Joined: Fri Apr 22, 2016 10:06 am

Joins on RequestedTask, SecurityIdentity, and Task table

Post by WNCWSupport » 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:

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
The result shows:
Image

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?

WNCWSupport
Posts: 4
Joined: Fri Apr 22, 2016 10:06 am

Re: Joins on RequestedTask, SecurityIdentity, and Task table

Post by WNCWSupport » Thu Jun 02, 2016 10:52 am

In addition, is there an easier way to include profiles based at the parent level?

John Morris
Posts: 411
Joined: Thu Sep 11, 2008 11:35 am
Location: Raleigh, NC, USA
Contact:

Re: Joins on RequestedTask, SecurityIdentity, and Task table

Post by John Morris » Thu Jun 02, 2016 12:37 pm

This is posted in the wrong forum. Please post in the Integration Development forum.

This forum is only for the Classic product line.
John Morris
Sr. Software Architect
SoftPro

Post Reply