SQL Server: how to do 3 months' partition?

Question

I want to count the number of rows in the partition 0-3months. Months are specified by MYMONTH in the format such that 201601 for 2016 January. I am using SQL Server 2014. How can I do the partition over 3 months?

SELECT  COUNT(*), 
        COUNT(*)
           /  
        (COUNT(*) OVER (PARTITION 
            BY MYMONTH RANGE BETWEEN  3 MONTH PRECEDING AND CURRENT MONTH))
FROM myData

Sample

|  Month  | Value  |  ID |
-------------------------|
| 201601  |   1    |  X  |
| 201601  |   1    |  Y  |
| 201601  |   1    |  Y  |
| 201602  |   1    |  Z  |
| 201603  |   1    |  A  |
| 201604  |   1    |  B  |
| 201605  |   1    |  C  |
| 201607  |   1    |  E  |
| 201607  |   10   |  EE |
| 201607  |   100  |  EEE|

Counts

| Month | Count | Count3M | Count/Count3M |
-------------------------------------------
| 201601|   3   |    3    |     3/3       |
| 201602|   1   |    4    |     1/4       |
| 201603|   1   |    5    |     1/5       |
| 201604|   1   |    6    |     1/6       |
| 201605|   1   |    4    |     1/4       |
| 201607|   3   |    5    |     3/5       |

Edit your question and provide sample data and desired results. — Gordon Linoff, Apr 11 '17 at 12:08
@etsa 201604 Count3M contain 201601-201604: `3+1+1+1=6` for 201604 i.e. 3 previous months including itself. — hhh, Apr 11 '17 at 12:47
@hhh I edited my answer (in former I used 2 prec month, instead of 3) — etsa, Apr 11 '17 at 12:49

etsa · Accepted Answer · 2017-04-11T13:03:02.107

You can try this (MSSQL 2012):

Sample data

CREATE TABLE mytable(
   MONT INTEGER  NOT NULL  
  ,Value INTEGER  NOT NULL
  ,ID    VARCHAR(5) NOT NULL
);
INSERT INTO mytable(MONT,Value,ID) VALUES (201601,1,'X');
INSERT INTO mytable(MONT,Value,ID) VALUES (201601,1,'Y');
INSERT INTO mytable(MONT,Value,ID) VALUES (201601,1,'Y');
INSERT INTO mytable(MONT,Value,ID) VALUES (201602,1,'Z');
INSERT INTO mytable(MONT,Value,ID) VALUES (201603,1,'A');
INSERT INTO mytable(MONT,Value,ID) VALUES (201604,1,'B');
INSERT INTO mytable(MONT,Value,ID) VALUES (201605,1,'C');
INSERT INTO mytable(MONT,Value,ID) VALUES (201607,1,'E');
INSERT INTO mytable(MONT,Value,ID) VALUES (201607,10,'EE');
INSERT INTO mytable(MONT,Value,ID) VALUES (201607,100,'EEE');

Query 1

SELECT MONT, RC,  RC+ LAG(RC,3,0)  OVER (  ORDER BY MONT)+ LAG(RC,2,0)  OVER (  ORDER BY MONT)  + LAG(RC,1,0)  OVER (  ORDER BY MONT)   AS RC_3M_PREC -- + COALESCE( LEAD(RC)  OVER (  ORDER BY MONT),0) AS RC_3M
FROM (SELECT MONT
    , COUNT(*) RC
     FROM mytable
     GROUP BY MONT
 ) A

Output:

MONT        RC          RC_3M_PREC
----------- ----------- -----------
201601      3           3
201602      1           4
201603      1           5
201604      1           6
201605      1           4
201607      3           6

Or using what you proposed (option ROWS ... PRECEDING):

Query 2:

SELECT MONT, RC
    ,   COALESCE(SUM(RC)   OVER (ORDER BY MONT ROWS BETWEEN 3 PRECEDING AND CURRENT ROW),0) AS RC_3M
FROM (SELECT MONT
    , COUNT(*) RC    
FROM mytable
GROUP BY MONT
 ) A

Output:

MONT        RC          RC_3M
----------- ----------- -----------
201601      3           3
201602      1           4
201603      1           5
201604      1           6
201605      1           4
201607      3           6

@hhh I added another query, similiar at what you were looking for — etsa, Apr 11 '17 at 12:58
I don't see how this answers the question. It counts three preceding rows, not three preceding months. If there are 10 preceding rows all of which belong to the same month, it will not count them. — GSerg, Jun 08 '20 at 06:55

score 0 · Answer 2 · answered Apr 11 '17 at 12:11

If you want to count rows in the previous three months, just use conditional aggregation. You do need a way to enumerate the months:

SELECT COUNT(*), 
       SUM(CASE WHEN yyyymm_counter <= 3 THEN 1 ELSE 0 END) 
FROM (SELECT md.*,
             DENSE_RANK() OVER (ORDER BY MYMONTH DESC) as yyyymm_counter
      FROM myData md
     ) md;

Another way without the subquery converts the month value to an actual date. Let me assume that it is a string:

SELECT COUNT(*),
       SUM( CASE WHEN DATEDIFF(month, CAST(MYMONTH + '01' as DATE), GETDATE()) <= 3
                 THEN 1 ELSE 0
            END)
FROM MyData;

I've left the / out of the answer. You need to be aware that SQL Server does integer division, so you may not get the results you want -- unless you convert values to non-integer number (I would suggest multiplying by 1.0 or using 1.0 instead of 1 in the queries).

Why does this use `GETDATE()`? Suppose `MyMONTH` is 201601, then the previous 3 months are 201512, 201511 and 201510. I cannot understand how this count them with GETDATE() that equals to `2017-04-11 15 15:53:57.173` now? — hhh, Apr 11 '17 at 13:04

SQL Server: how to do 3 months' partition?

2 Answers2

Linked