Investigating window frames

In addition to the PARTITION BY and ORDER BY clauses, window functions can include frames which tell the database engine what we include as relevant to the window.

Here we will look at four different frames to understand how the results would differ. For each example, we want to fill in the window frame for the following query:

SELECT
    ir.IncidentDate,
    ir.IncidentTypeID,
    SUM(ir.NumberOfIncidents) OVER (
        PARTITION BY ir.IncidentTypeID
        ORDER BY ir.IncidentDate
        << WINDOW FRAME >>
    ) AS NumberOfIncidents
FROM dbo.IncidentRollup ir;    

We will specify ROWS or RANGE based on whether we want to include individual rows or a range of values. We will specify the "preceding" clause, which tells how many rows (or what range of values) we want prior to the current row. We will also specify the "following" clause, which tells what we want from the current row forward.