Past due borrowed items report.

stevensexton

I need a report that lists all borrowed objects that are past due.  I also need to know whom it was borrowed by and their userid, OR if it was borrowed externally by a client, I would need the client name and their email address.

I have tried to build this several ways, but it still looks to not be bringing everything back, and I would really appreciate a sanity check.

Ideally, the report output would look something like this:

Borrowed Item | Number of days past due | Internal Borrower Name or External Client Name | Internal or External Borrower Email Address

 

It will require a join with data in the PHYSOBJCLIENT table if the "PHYSITEMCO.OBTAINEDBYID" result is not a user in KUAF.

 

Anyone done anything similar to this before?

 

Thanks!

John W. Simon, Jr.

Steven,

I don't have any data in PhysObjClient to try and create your query.  Here is what I came up with ignoring that table (using Oracle)...

select nodeid "Borrowed Item",
sysdate - returnby "Days Past Due",
chargedto "Borrower Name",
k.mailaddress
from dtree dt
join physitemco pco on dt.dataid = pco.nodeid
join kuaf k on pco.userid = k.id
where borrowperformerid is not null
and returnby < sysdate

I would think you could UNION this query with a copy of it (replacing KUAF with PhysObjClient in the copy), so something like this...

select nodeid "Borrowed Item",
sysdate - returnby "Days Past Due",
chargedto "Borrower Name",
k.mailaddress
from dtree dt
join physitemco pco on dt.dataid = pco.nodeid
join kuaf k on pco.userid = k.id
where borrowperformerid is not null
and returnby < sysdate
union
select nodeid "Borrowed Item",
sysdate - returnby "Days Past Due",
chargedto "Borrower Name",
poc.email
from dtree dt
join physitemco pco on dt.dataid = pco.nodeid
join physobjclient poc on pco.userid = poc.physobjclient
where borrowperformerid is not null
and returnby < sysdate

Again we do not have any data in PhysObjClient so I am not really sure how the join to that table should work (I took a best guess).

Cheers...