Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Window Functions in MS SQL Server

Introduction
Simple OVER()
PARTITION BY
Ranking functions
Window Frames
Analytic Functions
25. Analytic Functions – Exercise 3
PARTITION BY ORDER BY
Order of Evaluation
Finished!

Instruction

Perfect! We'll now try to write a more complicated version of the previous exercise.

Exercise

For each rental date, show that RentalDate, the sum of payment amounts (column name PaymentAmounts) from all SingleRentals on that day, the sum of PaymentAmounts on the previous day (column PreviousPaymentAmounts) and the Difference between these two values.

Stuck? Here's a hint!

In this exercise, it is best to create a temporary table using WITH TableName AS (...). In the temporary table, group PaymentAmounts by date. Once this table is ready, you can easily select the daily sum from that temporary table and then use the lag function to retrieve the value on the previous day.