Joins on RequestedTask, SecurityIdentity, and Task table

Discussions related to custom development with Select.
Post Reply
WNCWSupport
Posts: 4
Joined: Fri Apr 22, 2016 10:06 am

Joins on RequestedTask, SecurityIdentity, and Task table

Post by WNCWSupport »

Hello,

Hope I can get some assistance from someone 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?
Lisa Ennis
Posts: 84
Joined: Thu Sep 11, 2008 1:57 pm

Re: Joins on RequestedTask, SecurityIdentity, and Task table

Post by Lisa Ennis »

To display the information you want, you would need to change your query. Instead of using MAX on the date and grouping by the completed by full name, you would need to do sub queries or temp tables to get the ID of the max task record for each task description for the order. Then you would need to get the specific fields you need for the task (completed date, requested date, completed by id, and requested by user id). Your next step would then be to join to the security identity table on each id field (completed by id, requested by id).
Lisa Ennis
Senior Report Developer
SoftPro
WNCWSupport
Posts: 4
Joined: Fri Apr 22, 2016 10:06 am

Re: Joins on RequestedTask, SecurityIdentity, and Task table

Post by WNCWSupport »

Lisa Ennis wrote:To display the information you want, you would need to change your query. Instead of using MAX on the date and grouping by the completed by full name, you would need to do sub queries or temp tables to get the ID of the max task record for each task description for the order. Then you would need to get the specific fields you need for the task (completed date, requested date, completed by id, and requested by user id). Your next step would then be to join to the security identity table on each id field (completed by id, requested by id).

Lisa, thanks for the pointers... However, when I do subqueries, it gives me errors messages like

"SQL Server Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >="

Is there an example you can provide?
BobRichards
Posts: 1376
Joined: Wed Jan 15, 2014 3:50 pm
Location: Raleigh, NC
Contact:

Re: Joins on RequestedTask, SecurityIdentity, and Task table

Post by BobRichards »

Sorry, but we do not have an example at this time and it would require significant time to put this one together. Work to understand/resolve the SQL error with your code and see if that helps.
Bob Richards, Senior Software Developer, SoftPro
chuckkramer
Posts: 3
Joined: Fri May 20, 2011 1:55 pm

Re: Joins on RequestedTask, SecurityIdentity, and Task table

Post by chuckkramer »

We have a similar query we use. If you e-mail me directly, I'll have someone pull this piece out and e-mail it to you.
chuck.kramer@titlemail.com -- please don't reply via forum post. I am on here only once or twice a year.
Regards
Post Reply