Tech Blog

Alma Analytics SQL Filter Examples

Often when creating an integration with an Alma Analytics report it is useful to filter by an SQL statement.

These integrations might schedule an Alma Analytics report to an FTP server, or perhaps use an API to retrieve an Alma Analytics report.

SQL statements will retrieve by dynamic dates, rather than hard coded dates.  This is particularly useful when the running of the report is done via an automated schedule, as is particularly the case in integrations.

Below are several examples from various Alma Analytics subject areas and fields.  They can be amended to meet your particular needs.
You may wish to see also external source OBIEE Date Expressions Reference.

Date Fields

Bibliographic Fields

Date Fields

SQL Queries for retrieving by date fields:

Retrieve by loan date for the last four complete calendar years. If today is Jul. 17, 2019 this will retrieve from Jan. 01, 2015 to Dec. 31, 2018

“Loan Date”.”Loan Date” between TIMESTAMPADD( SQL_TSI_YEAR ,-4,TIMESTAMPADD( SQL_TSI_DAY,EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1,CURRENT_DATE)) and TIMESTAMPADD( SQL_TSI_DAY ,-1,TIMESTAMPADD( SQL_TSI_DAY,EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1,CURRENT_DATE))

Retrieve usage request date for the last 24 complete months. If today is Jul. 17, 2019 this will retrieve from Jul. 01, 2017 to Jun. 30, 2019

“Request Date”.”Request Date” between TIMESTAMPADD(SQL_TSI_MONTH, -24, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) AND TIMESTAMPADD( SQL_TSI_DAY , -(1),TIMESTAMPADD( SQL_TSI_DAY,DAYOFMONTH( CURRENT_DATE) * -(1) +1, CURRENT_DATE))

Retrieve physical item creation date for all of one previous calendar year. If today is Jul. 17, 2019 this will retrieve all records with “Physical Item Details”.”Creation Date” between Jan. 1 2018 and Dec. 31 2018

YEAR(“Physical Item Details”.”Creation Date”) = YEAR(TIMESTAMPADD(SQL_TSI_YEAR, -1, CURRENT_DATE))

Retrieve all transactions in the last 14 days. If today is Jul. 17, 2019 this will retrieve from Jul. 3, 2019 to Jul. 17, 2019.

“Transaction Date”.”Transaction Date” >= TIMESTAMPADD(SQL_TSI_DAY, -14, CURRENT_DATE)

Retrieve all transactions in the last 2 years. If today is Jul. 17, 2019 this will retrieve from Jul. 17, 2017 to Jul. 17, 2019.

“Transaction Date”.”Transaction Date” >= TIMESTAMPADD(SQL_TSI_YEAR, -2, CURRENT_DATE)

Retrieve all transactions in the last 3 months. If today is Jul. 17, 2019 this will retrieve from Apr. 17, 2017 to Jul. 17, 2019.

“Transaction Date”.”Transaction Date” >= TIMESTAMPADD(SQL_TSI_MONTH, -3, CURRENT_DATE)

Retrieve all transactions in the last 1 week. If today is Jul. 17, 2019 this will retrieve from Jul. 10, 2017 to Jul. 17, 2019.

“Transaction Date”.”Transaction Date” >= TIMESTAMPADD(SQL_TSI_WEEK, -1, CURRENT_DATE)

Retrieve all borrowers with expiration date between today and 1 month from now.

“Borrower Details”.”Expiry Date” BETWEEN (CURRENT_DATE) AND TIMESTAMPADD(SQL_TSI_MONTH, +1, CURRENT_DATE)

Retrieve all loans with due date between now and 1 month from now. If today is Jul. 17, 2019 this will retrieve from Jul. 17, 2019 to Aug. 17, 2019.

“Due Date”.”Due Date” BETWEEN (CURRENT_DATE) AND TIMESTAMPADD(SQL_TSI_MONTH, +1, CURRENT_DATE)

Retrieve anything with a loan date greater than or equal to the first day of the month 1 month ago. If today is July 17, 2019 then it will retrieve from Jun. 1 2019.

“Loan Date”.”Loan Date” >= TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

Retrieve anything with a loan date greater than or equal to the first day of the month 2 months ago. If today is July 17, 2019 then it will retrieve from May. 1 2019.

“Loan Date”.”Loan Date” >= TIMESTAMPADD(SQL_TSI_MONTH, -2, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

Retrieve anything loaned during the current year. If today is July 17, 2019 then it will retrieve any date in 2019.

YEAR(“Loan Date”.”Loan Date”) = YEAR(CURRENT_DATE)

Retrieve anything loaned during the current month. If today is July 17, 2019 then it will retrieve any date in July of any year.

MONTH(“Loan Date”.”Loan Date”) = MONTH(CURRENT_DATE)

Retrieve anything loaned during the current month of the current year. If today is July 17, 2019 then it will retrieve any date in July of 2019.

YEAR(“Loan Date”.”Loan Date”) = YEAR(CURRENT_DATE) AND MONTH(“Loan Date”.”Loan Date”) = MONTH(CURRENT_DATE)

Retrieve all borrowers with an expiration date within the coming week. If today is Jul. 17, 2019 then it will retrieve from Jul. 17 to Jul. 24.

“Borrower Details”.”Expiry Date” BETWEEN (CURRENT_DATE) AND TIMESTAMPADD(SQL_TSI_WEEK, +1, CURRENT_DATE)

Bibliographic Fields

SQL Queries for retrieving bibliographic fields:

Find all titles that begin with “Contemporary Ital”

“Bibliographic Details”.”Title” LIKE ‘Contemporary Ital%’

This will find for example:

  • Contemporary Italian poetry; an anthology.
  • Contemporary Italy : a selective bibliography.

Find all titles that include “cinema” and “contemporary” in any order and any case

option 1:
LOWER(“Bibliographic Details”.”Title”) LIKE ‘%contemporary%cinema%’ OR LOWER(“Bibliographic Details”.”Title”) LIKE ‘%cinema%contemporary%’

option 2:
LOWER(“Bibliographic Details”.”Title”) LIKE ‘%contemporary%’ AND LOWER(“Bibliographic Details”.”Title”) LIKE ‘%cinema%’

This will find for example:

  • (Re)viewing Creative, Critical and Commercial Practices in Contemporary Spanish Cinema
  • A companion to contemporary French cinema /
  • Abstinence cinema : virginity and the rhetoric of sexual purity in contemporary film /
  • CONTEMPORARY WOMEN’S CINEMA, GLOBAL SCENARIOS AND TRANSNATIONAL CONTEXTS

Find all titles that end in “media” in any case

LOWER(“Bibliographic Details”.”Title”) LIKE ‘%media’

This will find for example:

  • REPUBLIC – DIVIDED DEMOCRACY IN THE AGE OF SOCIAL MEDIA
  • Advertising in New Formats and Media
  • All about e-portfolios & multimedia
  • Analytical and numerical methods for wave propagation in fluid media

Find all tiles that end in “media” in any case as a separate word (and not for example “multimedia”)

LOWER(“Bibliographic Details”.”Title”) LIKE ‘% media’

This will find for example:

  • REPUBLIC – DIVIDED DEMOCRACY IN THE AGE OF SOCIAL MEDIA
  • Advertising in New Formats and Media
  • Analytical and numerical methods for wave propagation in fluid media

This will not find for example:

  • All about e-portfolios & multimedia

Find all places of publication with “Saint Paul” in the first 10 positions

LEFT(“Bibliographic Details”.”Publication Place”, 10) = ‘Saint Paul’

This will find for example:

  • Saint Paul (50 W. Kellogg Blvd., St. Paul 55165) :
  • Saint Paul :
  • Saint Paul,
  • Saint Paul, MN :
  • Saint Paul, Minn.
  • Saint Paul, Minn. :
  • Saint Paul, Minnesota :

Leave a Reply