CData Drivers, Connectors, and other solutions now include the LAG() window function in the library of supported SQL functions. This capability unlocks the ability to compare and calculate the change in values over a sequence of data. This support is across all data sources. Where possible, CData solutions will push the LAG request down to the data source, otherwise the function will be resolved in memory after the initial data is retrived.
Example Query Using the LAG() Function
The example below uses the Data Explorer in CData Connect Cloud with a Salesforce connection, but the principals apply to any CData solution across all data sources.
The query below calculates the sum of all expected revenue for all opportunities, grouped by month of the opportunity and the associated customer account in Salesforce. Using the LAG() function, we compare each month's expected revenue to the previous month.
Query
SELECT Account.Name,
SUM(ExpectedRevenue), AS CurExpRevenue
MONTH(CloseDate) AS CloseMonth,
LAG(SUM(ExpectedRevenue), 1, 0)
OVER (
PARTITION BY AccountId
ORDER BY CloseDate) AS PrevExpRevenue
FROM Salesforce1.Salesforce.Opportunity
JOIN Salesforce1.Salesforce.Account
on Opportunity.AccountId = Account.Id
WHERE MONTH(CloseDate) IN ( '3', '4', '5', '6', '7', '8' )
GROUP BY AccountId,
MONTH(CloseDate)
Results
The results below show the first 10 rows of the response from Salesforce. Not each Month is consecutive, representing accounts where there may not have been opportunities for the months in between.
Name | CurExpRevenue | CloseMonth | PrevExpRevenue |
---|---|---|---|
Edge Communications, Inc. | 29999 | 5 | 0 |
Burlington Textiles Corp of America | 872303.85 | 4 | 29999 |
Burlington Textiles Corp of America | 36624.2 | 5 | 872303.85 |
Burlington Textiles Corp of America | 72666.1 | 7 | 36624.2 |
Pyramid Construction Inc. | 8843.4 | 3 | 72666.1 |
Pyramid Construction Inc. | 95835.3 | 5 | 8843.4 |
Dickenson plc | 91724.6 | 8 | 95835.3 |
Grand Hotels & Resorts Ltd | 612608.8 | 4 | 91724.6 |
Grand Hotels & Resorts Ltd | 22857.2 | 5 | 612608.8 |
Grand Hotels & Resorts Ltd | 460070.1 | 6 | 22857.2 |
... | ... | ... | ... |