The Best SQL Patterns for Yardi Custom Reports

Ready to unlock the full potential of your Yardi data? With a little technical expertise, you will uncover a wealth of valuable insights hidden within your data. Read on for the best SQL patterns for Yardi custom reports today!

The below SQL patterns will take your Yardi custom reports to the next level. Trust us – these techniques will give you a competitive edge. Don’t miss out on the opportunity to become a Yardi data pro.

1. Get Row of Data with the Latest Date

Why this matters: Often, Yardi stores the same row of data multiple times in the same table. Each time the data changes, Yardi records a new row with the updates along with a new “effective date” to outline the sequence of events. While this information can be useful, most reports only need the latest row of data.

Yardi use cases: Yardi stores unit and tenant history in this fashion. In the affordable module, certificate data is also tracked historically.

Are you tired of sifting through outdated records? With this powerful query pattern, you will easily retrieve the most recent record for a specific key field in just a few clicks.

SELECT t.username, t.date, t.value
FROM MyTable t
INNER JOIN (
    SELECT username, max(date) as MaxDate
    FROM MyTable
    GROUP by username
) tm ON t.username = tm.username AND t.date = tm.MaxDate

2. Get the Current and Previous Records

Why this matters: Need to see the changes made to a particular record? This query pattern is your key to unlocking valuable insights. By identifying the previous row of data, you can easily see recent updates and make informed decisions.

Yardi use cases: This pattern helps you compare new versus old values, such as current and prior rents, unit statuses, and lease charges. Yardi stores this data in their history tables. If you want to know the rent change amount, release spreads, or number of days between events, you must deploy this pattern.

As you may have guessed, this is one of the more complicated SQL patterns. You will need to adjust the code for special use cases. However, the code below should get you started.

WITH cte AS (
SELECT groupid, date, value,
ROW_NUMBER() OVER (PARTITION BY groupid ORDER BY date) rn
FROM table1
)
SELECT a.groupid, a.date, a.value, b.value Prev_Value,
a.value-b.value Result
FROM cte a
LEFT JOIN cte b
ON a.groupid = b.groupid
AND a.rn = b.rn + 1
ORDER BY a.groupid, a.date

3. The Timeseries Snapshot Loop

Why this matters: To create a picture of the data on a specific day, you will need a specific date, multiple tables, and complex joins. Yardi does a great job of storing data correctly so that you can create historic reports. If you haven’t heard of snapshotting, here’s a quick definition: a snapshot acts like a detailed table of contents, allowing you to choose any point in time to reference.

After accomplishing snapshotting for one date, you may need multiple dates snapshotted to trend your data. This is a powerful analysis mechanism needed in all good BI systems.

Yardi use cases: Rent rolls, financials, and aging receivables are all point-in-time reports inside Yardi. To trend these, you usually need to run multiple exports and then combine them in Excel or a database.

Use this classic pattern to loop through dates and create snapshots of your data. This is a process-intensive and slow SQL pattern. Many database administrators do not like cursors or SQL loops. Therefore, use this pattern sparingly and only where needed. Limit looping to key data to avoid creating massive snapshots.

Check out the sample query below!

DECLARE @StartDate DATE = ‘2016-10-01’;
DECLARE @EndDate DATE = ‘2016-10-31’;

WHILE (@StartDate <= @EndDate)

BEGIN

print @StartDate;

— Do Something like call a proc with the variable @StartDate

set @StartDate = DATEADD(day, 1, @StartDate);

END;

Don’t have the time or expertise to tackle complex SQL? Let us help! We love diving into custom Yardi SQL reports. Simply send us a note and we’ll be happy to lend a hand.

The Best SQL Patterns for Yardi Custom Reports