We are proud to share our inclusion in the 2024 Gartner Magic Quadrant for Data Integration Tools. We believe this recognition reflects the differentiated business outcomes CData delivers to our customers.
Get the Report →
CData Connectors Support the LAG() Window Function
CData Drivers & Connectors include support for the LAG() window function for more comprehensive and powerful querying capabilities.
Date Entered: 01/31/2025 Last Updated: 01/31/2025
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,
MONTH(CloseDate) AS CloseMonth,
LAG(SUM(Revenue), 1, 0)
OVER (
PARTITION BY AccountId
ORDER BY CloseDate) AS PreviousRevenue,
SUM(Revenue) AS CurrentRevenue
FROM Salesforce1.Salesforce.Opportunity
JOIN Salesforce1.Salesforce.Account
on Opportunity.AccountId = Account.Id
WHERE MONTH(CloseDate) IN ( '3', '4', '5', '6', '7', '8' ) AND
AccountId = '0016A000003FZacQAG'
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 | CloseMonth | PreviousRevenue | CurrentRevenue |
|---|---|---|---|
| Burlington Textiles Corp of America | 3 | 0 | 29999 |
| Burlington Textiles Corp of America | 4 | 29999 | 872303.85 |
| Burlington Textiles Corp of America | 5 | 872303.85 | 36624.2 |
| Burlington Textiles Corp of America | 6 | 36624.2 | 72666.1 |
| Burlington Textiles Corp of America | 7 | 72666.1 | 8843.4 |
| Burlington Textiles Corp of America | 8 | 8843.4 | 95835.3 |
We appreciate your feedback. If you have any questions, comments, or suggestions about this entry, please contact our support team at [email protected].
