SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE a1111
    ([PMM] int, [ORDER_NO] varchar(6), [MKQTY] int);
    
INSERT INTO a1111
    ([PMM], [ORDER_NO], [MKQTY])
VALUES
    (10905, '4N0241', 250),
    (10905, '4N0301', 375),
    (10905, '4N0292', 3700),
    (10905, '4N0331', 1335),
    (10905, '4N0341', 365),
    (10906, '5N3234', 1425),
    (10906, '5N3245', 7400),
    (10906, '5N0271', 1075),
    (10906, '5N0284', 1200),
    (10906, '5N3251', 4000),
    (10907, '6N3191', 2800),
    (10907, '6N3103', 8800),
    (10907, '6N0371', 1200),
    (10907, '6N3194', 1540),
    (10907, '6N3104', 2250),
    (10908, '7N3229', 10160),
    (10908, '7N0202', 1025),
    (10908, '7N0251', 6600),
    (10908, '7N0221', 1400),
    (10908, '7N3283', 3400),
    (10908, '8N3215', 1890),
    (10908, '8N3222', 9525),
    (10908, '8N0151', 7575),
    (10909, '8N0121', 11550),
    (10909, '9N3011', 7900),
    (10909, '8N0121', 660),
    (10909, '8N0201', 1515),
    (10909, '9N3011', 2600),
    (10909, '8N0211', 295),
    (10909, '9N3012', 10500),
    (10909, '8N3231', 8230),
    (10909, '9N3013', 2250),
    (10909, '9N3014', 2650),
    (10910, '9N0131', 7000),
    (10910, '9N0341', 5170),
    (10910, '9N3244', 1535),
    (10910, '9N3245', 1235),
    (10911, 'AN3226', 715),
    (10911, 'AN3223', 695),
    (10911, 'AN3251', 6015),
    (10911, 'AN3197', 4600),
    (10911, 'AN3222', 685),
    (10911, 'AN3231', 1535),
    (10912, 'BN3223', 1825),
    (10912, 'BN3227', 1535),
    (10912, 'BN3226', 5075),
    (10912, 'BN0291', 200),
    (10912, 'BN3224', 325),
    (10912, 'BN3221', 1115),
    (10912, 'BN3225', 1550),
    (10912, 'BN3244', 525),
    (11001, 'CN0031', 4000),
    (11001, 'CN0301', 600),
    (11001, 'CN0291', 4200),
    (11001, 'CN3154', 1550),
    (11001, 'CN0291', 6000),
    (11001, 'CN3134', 2125),
    (11001, 'CN0031', 6000),
    (11002, '1N0271', 10000),
    (11002, '1N0264', 3700),
    (11002, '1N3192', 10280),
    (11002, '1N0267', 1925),
    (11002, '1N0264', 1500),
    (11002, '1N0265', 1910),
    (11002, '1N0282', 1625),
    (11002, '1N3244', 300),
    (11002, '1N3248', 1075),
    (11003, '2N3196', 2800),
    (11003, '2N3201', 950),
    (11003, '2N0271', 1690),
    (11003, '2N3197', 9975),
    (11003, '2N0243', 225),
    (11004, '3N3123', 16800),
    (11004, '3N3124', 3900),
    (11004, '3N3121', 19800),
    (11004, '3N3161', 7500),
    (11004, '3N3161', 5425),
    (11004, '3N3122', 4600),
    (11004, '3N3124', 6300),
    (11004, '3N3121', 1800),
    (11004, '3N3161', 10375),
    (11004, '3N3122', 12100),
    (11005, '4N3197', 2908),
    (11005, '4N3196', 250),
    (11005, '4N3194', 10000),
    (11005, '4N3195', 1950),
    (11005, '5N3021', 1025),
    (11005, '4N3192', 100),
    (11005, '4N3191', 5000),
    (11005, '4N3211', 1765),
    (11005, '5N0011', 2045),
    (11005, '4N3192', 4200),
    (11005, '4N3193', 5100),
    (11005, '4N3192', 700),
    (11006, '5N3291', 4850),
    (11006, '5N0181', 1325),
    (11006, '6N3011', 10327),
    (11006, '5N3243', 3900),
    (11006, '5N3302', 8800),
    (11006, '6N3021', 3850),
    (11006, '5N3243', 6075),
    (11006, '5N3302', 8895),
    (11006, '5N0171', 5225),
    (11006, '6N3031', 300),
    (11006, '6N3021', 1300);
    
SELECT *
FROM a1111;


PMM
ORDER_NO
MKQTY


10905
4N0241
250

10905
4N0301
375

10905
4N0292
3700

10905
4N0331
1335

10905
4N0341
365

10906
5N3234
1425

10906
5N3245
7400

10906
5N0271
1075

10906
5N0284
1200

10906
5N3251
4000

10907
6N3191
2800

10907
6N3103
8800

10907
6N0371
1200

10907
6N3194
1540

10907
6N3104
2250

10908
7N3229
10160

10908
7N0202
1025

10908
7N0251
6600

10908
7N0221
1400

10908
7N3283
3400

10908
8N3215
1890

10908
8N3222
9525

10908
8N0151
7575

10909
8N0121
11550

10909
9N3011
7900

10909
8N0121
660

10909
8N0201
1515

10909
9N3011
2600

10909
8N0211
295

10909
9N3012
10500

10909
8N3231
8230

10909
9N3013
2250

10909
9N3014
2650

10910
9N0131
7000

10910
9N0341
5170

10910
9N3244
1535

10910
9N3245
1235

10911
AN3226
715

10911
AN3223
695

10911
AN3251
6015

10911
AN3197
4600

10911
AN3222
685

10911
AN3231
1535

10912
BN3223
1825

10912
BN3227
1535

10912
BN3226
5075

10912
BN0291
200

10912
BN3224
325

10912
BN3221
1115

10912
BN3225
1550

10912
BN3244
525

11001
CN0031
4000

11001
CN0301
600

11001
CN0291
4200

11001
CN3154
1550

11001
CN0291
6000

11001
CN3134
2125

11001
CN0031
6000

11002
1N0271
10000

11002
1N0264
3700

11002
1N3192
10280

11002
1N0267
1925

11002
1N0264
1500

11002
1N0265
1910

11002
1N0282
1625

11002
1N3244
300

11002
1N3248
1075

11003
2N3196
2800

11003
2N3201
950

11003
2N0271
1690

11003
2N3197
9975

11003
2N0243
225

11004
3N3123
16800

11004
3N3124
3900

11004
3N3121
19800

11004
3N3161
7500

11004
3N3161
5425

11004
3N3122
4600

11004
3N3124
6300

11004
3N3121
1800

11004
3N3161
10375

11004
3N3122
12100

11005
4N3197
2908

11005
4N3196
250

11005
4N3194
10000

11005
4N3195
1950

11005
5N3021
1025

11005
4N3192
100

11005
4N3191
5000

11005
4N3211
1765

11005
5N0011
2045

11005
4N3192
4200

11005
4N3193
5100

11005
4N3192
700

11006
5N3291
4850

11006
5N0181
1325

11006
6N3011
10327

11006
5N3243
3900

11006
5N3302
8800

11006
6N3021
3850

11006
5N3243
6075

11006
5N3302
8895

11006
5N0171
5225

11006
6N3031
300

11006
6N3021
1300


-- Case 1 : 查詢日當月為第一個月
SELECT CASE WHEN CAST(14-ROW_NUMBER() OVER (ORDER BY PMM) AS CHAR(2)) ='0' THEN '<1~12>' 
ELSE CAST(14-ROW_NUMBER() OVER (ORDER BY PMM) AS CHAR(2)) END AS 'Phase',
CASE WHEN PMM =99999 THEN NULL ELSE PMM END AS PMM,
CLASS1 AS 'MKQTY<=500',CLASS2 AS 'MKQTY>500 & MKQTY<=1000',CLASS3 AS 'MKQTY>1000',Total
FROM (
SELECT PMM,
SUM(CASE WHEN MKQTY<=500 THEN 1 ELSE 0 END) AS 'CLASS1',
SUM(CASE WHEN MKQTY>500 AND MKQTY<=1000 THEN 1 ELSE 0 END) AS 'CLASS2',
SUM(CASE WHEN MKQTY>1000 THEN 1 ELSE 0 END) AS 'CLASS3',
COUNT(MKQTY) AS 'Total'
FROM a1111
WHERE PMM+191100=CONVERT(CHAR(6),DATEADD(MONTH,-12,GETDATE()),112)
GROUP BY PMM
UNION ALL
SELECT CASE WHEN PMM IS NULL THEN 99999 ELSE PMM END AS PMM,
SUM(CASE WHEN MKQTY<=500 THEN 1 ELSE 0 END) AS 'CLASS1',
SUM(CASE WHEN MKQTY>500 AND MKQTY<=1000 THEN 1 ELSE 0 END) AS 'CLASS2',
SUM(CASE WHEN MKQTY>1000 THEN 1 ELSE 0 END) AS 'CLASS3',
COUNT(MKQTY) AS 'Total'
FROM a1111
WHERE PMM+191100>=CONVERT(CHAR(6),DATEADD(MONTH,-11,GETDATE()),112)
AND PMM+191100<=CONVERT(CHAR(6),DATEADD(MONTH,0,GETDATE()),112)
GROUP BY ROLLUP(PMM)
) AS M
ORDER BY CASE WHEN PMM IS NULL THEN 99999 ELSE PMM END


Phase
PMM
MKQTY<=500
MKQTY>500 & MKQTY<=1000
MKQTY>1000
Total


-- Case 2 : 查詢日的上一個月為第一個月
SELECT CASE WHEN CAST(14-ROW_NUMBER() OVER (ORDER BY PMM) AS CHAR(2)) ='0' THEN '<1~12>' 
ELSE CAST(14-ROW_NUMBER() OVER (ORDER BY PMM) AS CHAR(2)) END AS 'Phase',
CASE WHEN PMM =99999 THEN NULL ELSE PMM END AS PMM,
CLASS1 AS 'MKQTY<=500',CLASS2 AS 'MKQTY>500 & MKQTY<=1000',CLASS3 AS 'MKQTY>1000',Total
FROM (
SELECT PMM,
SUM(CASE WHEN MKQTY<=500 THEN 1 ELSE 0 END) AS 'CLASS1',
SUM(CASE WHEN MKQTY>500 AND MKQTY<=1000 THEN 1 ELSE 0 END) AS 'CLASS2',
SUM(CASE WHEN MKQTY>1000 THEN 1 ELSE 0 END) AS 'CLASS3',
COUNT(MKQTY) AS 'Total'
FROM a1111
WHERE PMM+191100=CONVERT(CHAR(6),DATEADD(MONTH,-13,GETDATE()),112)
GROUP BY PMM
UNION ALL
SELECT CASE WHEN PMM IS NULL THEN 99999 ELSE PMM END AS PMM,
SUM(CASE WHEN MKQTY<=500 THEN 1 ELSE 0 END) AS 'CLASS1',
SUM(CASE WHEN MKQTY>500 AND MKQTY<=1000 THEN 1 ELSE 0 END) AS 'CLASS2',
SUM(CASE WHEN MKQTY>1000 THEN 1 ELSE 0 END) AS 'CLASS3',
COUNT(MKQTY) AS 'Total'
FROM a1111
WHERE PMM+191100>=CONVERT(CHAR(6),DATEADD(MONTH,-12,GETDATE()),112)
AND PMM+191100<=CONVERT(CHAR(6),DATEADD(MONTH,-1,GETDATE()),112)
GROUP BY ROLLUP(PMM)
) AS M
ORDER BY CASE WHEN PMM IS NULL THEN 99999 ELSE PMM END


Phase
PMM
MKQTY<=500
MKQTY>500 & MKQTY<=1000
MKQTY>1000
Total


-- Case 3 : 查詢日當月為第一個月
SELECT PMM,
SUM(CASE WHEN MKQTY<=500 THEN 1 ELSE 0 END) AS 'MKQTY<=500',
SUM(CASE WHEN MKQTY>500 AND MKQTY<=1000 THEN 1 ELSE 0 END) AS 'MKQTY>500 & MKQTY<=1000',
SUM(CASE WHEN MKQTY>1000 THEN 1 ELSE 0 END) AS 'MKQTY>1000',
COUNT(MKQTY) AS 'Total',
SUM(CASE WHEN PMM+191100=CONVERT(CHAR(6),DATEADD(MONTH,-12,GETDATE()),112) THEN 0
ELSE COUNT(MKQTY) END) OVER (ORDER BY PMM) AS 'Accumulation'
FROM a1111
WHERE PMM+191100>=CONVERT(CHAR(6),DATEADD(MONTH,-12,GETDATE()),112)
AND PMM+191100<=CONVERT(CHAR(6),DATEADD(MONTH,0,GETDATE()),112)
GROUP BY PMM
ORDER BY PMM


PMM
MKQTY<=500
MKQTY>500 & MKQTY<=1000
MKQTY>1000
Total
Accumulation

PMM	ORDER_NO	MKQTY
10905	4N0241	250
10905	4N0301	375
10905	4N0292	3700
10905	4N0331	1335
10905	4N0341	365
10906	5N3234	1425
10906	5N3245	7400
10906	5N0271	1075
10906	5N0284	1200
10906	5N3251	4000
10907	6N3191	2800
10907	6N3103	8800
10907	6N0371	1200
10907	6N3194	1540
10907	6N3104	2250
10908	7N3229	10160
10908	7N0202	1025
10908	7N0251	6600
10908	7N0221	1400
10908	7N3283	3400
10908	8N3215	1890
10908	8N3222	9525
10908	8N0151	7575
10909	8N0121	11550
10909	9N3011	7900
10909	8N0121	660
10909	8N0201	1515
10909	9N3011	2600
10909	8N0211	295
10909	9N3012	10500
10909	8N3231	8230
10909	9N3013	2250
10909	9N3014	2650
10910	9N0131	7000
10910	9N0341	5170
10910	9N3244	1535
10910	9N3245	1235
10911	AN3226	715
10911	AN3223	695
10911	AN3251	6015
10911	AN3197	4600
10911	AN3222	685
10911	AN3231	1535
10912	BN3223	1825
10912	BN3227	1535
10912	BN3226	5075
10912	BN0291	200
10912	BN3224	325
10912	BN3221	1115
10912	BN3225	1550
10912	BN3244	525
11001	CN0031	4000
11001	CN0301	600
11001	CN0291	4200
11001	CN3154	1550
11001	CN0291	6000
11001	CN3134	2125
11001	CN0031	6000
11002	1N0271	10000
11002	1N0264	3700
11002	1N3192	10280
11002	1N0267	1925
11002	1N0264	1500
11002	1N0265	1910
11002	1N0282	1625
11002	1N3244	300
11002	1N3248	1075
11003	2N3196	2800
11003	2N3201	950
11003	2N0271	1690
11003	2N3197	9975
11003	2N0243	225
11004	3N3123	16800
11004	3N3124	3900
11004	3N3121	19800
11004	3N3161	7500
11004	3N3161	5425
11004	3N3122	4600
11004	3N3124	6300
11004	3N3121	1800
11004	3N3161	10375
11004	3N3122	12100
11005	4N3197	2908
11005	4N3196	250
11005	4N3194	10000
11005	4N3195	1950
11005	5N3021	1025
11005	4N3192	100
11005	4N3191	5000
11005	4N3211	1765
11005	5N0011	2045
11005	4N3192	4200
11005	4N3193	5100
11005	4N3192	700
11006	5N3291	4850
11006	5N0181	1325
11006	6N3011	10327
11006	5N3243	3900
11006	5N3302	8800
11006	6N3021	3850
11006	5N3243	6075
11006	5N3302	8895
11006	5N0171	5225
11006	6N3031	300
11006	6N3021	1300