Select N rows in aggregate functions SQL Server

Viewed 354

I have a table that looks like this:

+--------+----------+--------+------------+-------+
|   ID   | CHANNEL  | VENDOR | num_PERIOD | SALES |
+--------+----------+--------+------------+-------+
| 000001 | Business | Shop   |          1 | 40    |
| 000001 | Business | Shop   |          2 | 60    |
| 000001 | Business | Shop   |          3 | NULL  |
+--------+----------+--------+------------+-------+

With many combinations of ID, CHANNEL and VENDOR, and sales records for each of them over time (num_PERIOD).

The idea is to obtain a new column which returns the number of NULLS in SALES column, but in the first 111 registers according to num_PERIOD column.

I have been trying something like this:

SELECT ID,
       CHANNEL,
       VENDOR,
       sum(CASE
               WHEN SALES IS NULL THEN 1
               ELSE 0
           END) OVER (PARTITION BY ID,
                                   CHANNEL,
                                   VENDOR
                      ORDER BY num_PERIOD ROWS BETWEEN UNBOUNDED PRECEDING AND 111 FOLLOWING) AS NULL_SALES_SET
FROM TABLE
GROUP BY ID,
         CHANNEL,
         VENDOR

But I'm not obtaining what I'm looking for.

So to obtain a table simillar to:

+--------+--------------+--------+----------------+
|   ID   |   CHANNEL    | VENDOR | NULL_SALES_SET |
+--------+--------------+--------+----------------+
| 000001 | Business     | Shop   |              1 |
| 000002 | Business     | Market |              0 |
| 000002 | Non Business | Shop   |              3 |
+--------+--------------+--------+----------------+

The difficulty comes when selecting these first 111 rows per ID, CHANNEL AND VENDOR ordered by num_PERIOD.

2 Answers
Related