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.

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)

Leave a Reply