[Wactclc-alma] Combining Date and Time for calculation

[Sally Sheedy]

Thanks Kirsti and Wade,

I really appreciate your efforts and helping me to up my SQL game.

You are both awesome!

From: Wactclc-alma <wactclc-alma-bounces at lists.ctc.edu> On Behalf Of Guidry, Wade
Sent: Friday, December 14, 2018 10:20 AM
To: WACTCLC Alma Discussion <wactclc-alma at lists.ctc.edu>
Subject: Re: [Wactclc-alma] Combining Date and Time for calculation

OK, so it can be done completely in analytics.

The formulas aren't straightforward, but they work.

I saved an example in analytics / community / reports / consortia / wactclc / Loan Date Time Math Example

You can make a copy add the filtering necessary to report on just the transactions you're interested in.


Wade Guidry
Library Consortium Services Manager, WACTCLC
wadeg at bigbend.edu<mailto:wadeg at bigbend.edu>
(509) 760-4474
http://www.wactclc.org


From: Wactclc-alma <wactclc-alma-bounces at lists.ctc.edu<mailto:wactclc-alma-bounces at lists.ctc.edu>> On Behalf Of Thomas, Kirsti
Sent: Thursday, December 13, 2018 4:19 PM
To: WACTCLC Alma Discussion <wactclc-alma at lists.ctc.edu<mailto:wactclc-alma at lists.ctc.edu>>
Subject: Re: [Wactclc-alma] Combining Date and Time for calculation

Hey Sally,

I'm procrastinating starting work on another project and fiddled around with whether it might be possible to figure this out in Alma Analytics and now I really, really want you to file a case so you can report back on whether Ex Libris has a good solution to this. :)

I found out that the Loan Time and Return Time are stored as text, instead of "TIME" or "TIMESTAMP" data types.  (TIMESTAMP being a data type that contains both date and time together in one field).  I figured out that you can use the CAST function when you "Edit formula" for a particular column to convert Loan Time and Return Time data from text into "TIME" but you can't convert those fields into "TIMESTAMP" because no date information exists anywhere in the field. "TIMESTAMP" requires that there be a date somewhere in the field (even if it's hidden).

I could combine the Date and Time into a single field, but I could only do that by turning both fields into text:
CAST("Loan Date"."Loan Date" AS CHAR)||CAST("Loan Date"."Loan Time" AS CHAR

Once you do this, it might (?) be possible to use CAST a second time to convert that data into a TIMESTAMP data type but I couldn't figure out how.


There is function called TIMESTAMPDIFF that's supposed to let you calculate the difference between dates in terms of years, quarters, months, weeks, days, hours, minutes, seconds, and fractional seconds.

I could get the number of hours between the Loan Date and the Return Date in one of two ways:

1)      By calculating the number of days between the dates and multiplying by 24:
TIMESTAMPDIFF(SQL_TSI_DAY,"Loan Date"."Loan Date","Return Date"."Return Date")*24

2)      By converting the dates into TIMESTAMP and then calculating the number of hours:
TIMESTAMPDIFF(SQL_TSI_HOUR,CAST("Loan Date"."Loan Date" AS TIMESTAMP),CAST("Return Date"."Return Date" AS TIMESTAMP))


You can't use the TIMESTAMPDIFF function on the Loan Time and the Return Time because you can't convert those into TIMESTAMP data types.

There's a SQL command called TIMEDIFF that's supposed to calculate the difference between two times, but I can't figure out if it even works in Alma Analytics (i.e. Oracle Business Intelligence Enterprise Edition, aka OBIEE).  If it did work, you'd also have to have some kind of formula to convert any negative values to positive values in situations where the Return Time was an earlier hour than the Loan Time.

So, yeah, to calculate the loan length in hours, the easiest thing is probably going to be exporting everything to Excel.  And then you should submit a case and report back on whether you've managed to stump the Ex Libris support staff. :)


Kirsti S. Thomas
Library Technical Services Manager
Seattle Colleges
kirsti.thomas at seattlecolleges.edu<mailto:kirsti.thomas at seattlecolleges.edu>
206-934-4106




From: Wactclc-alma [mailto:wactclc-alma-bounces at lists.ctc.edu] On Behalf Of Sally Sheedy
Sent: Friday, December 7, 2018 11:38 AM
To: 'WACTCLC Alma Discussion' <wactclc-alma at lists.ctc.edu<mailto:wactclc-alma at lists.ctc.edu>>
Subject: [Wactclc-alma] Combining Date and Time for calculation

Hi All,

We are looking at data to see if our policies are set to maximally serve patrons' needs.

I've been asked to do an analysis of how long our two-hour items are out for and that won't be too difficult because you can assume the same day. In fact, we will ignore the outliers for items that are seriously late.  But I want to compare date AND time for Loaned and Returned for overnight and three-day loans. I can do this in Excel but I don't know how to add the date and time fields together in Analytics/SQL. I'd like to be able to use bins instead of grouping Pivot tables in Excel.

Any ideas?

Thanks!

Sally Sheedy
Systems Librarian
Whatcom Community College Library
237 West Kellogg Road
Bellingham, WA 98226
360-383-3287  (library 3300, college 3000)
ssheedy at whatcom.edu<mailto:ssheedy at whatcom.edu>
library.whatcom.edu

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.ctc.edu/pipermail/wactclc-alma_lists.ctc.edu/attachments/20181214/2ad314c3/attachment-0002.html>