CODEWITHSUNDEEP
sqlsql-servertimepartition
hhh
hhh

Reputation: 52810

SQL Server: how to do 3 months' partition?

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       |

Upvotes: 0

Views: 1666

Answers (2)

etsa
etsa

Reputation: 5060

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269445

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).

Upvotes: 0

Related Questions