clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591543 fiddles created (45736 in the last week).

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
 hidden batch(es)


-- 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
13 10906 0 0 5 5
12 10907 0 0 5 5
11 10908 0 0 8 8
10 10909 1 1 8 10
9 10910 0 0 4 4
8 10911 0 3 3 6
7 10912 2 1 5 8
6 11001 0 1 6 7
5 11002 1 0 8 9
4 11003 1 1 3 5
3 11004 0 0 10 10
2 11005 2 1 9 12
1 11006 1 0 10 11
<1~12> 8 8 79 95
 hidden batch(es)


-- 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
13 10905 3 0 2 5
12 10906 0 0 5 5
11 10907 0 0 5 5
10 10908 0 0 8 8
9 10909 1 1 8 10
8 10910 0 0 4 4
7 10911 0 3 3 6
6 10912 2 1 5 8
5 11001 0 1 6 7
4 11002 1 0 8 9
3 11003 1 1 3 5
2 11004 0 0 10 10
1 11005 2 1 9 12
<1~12> 7 8 74 89
 hidden batch(es)


-- 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
10906 0 0 5 5 0
10907 0 0 5 5 5
10908 0 0 8 8 13
10909 1 1 8 10 23
10910 0 0 4 4 27
10911 0 3 3 6 33
10912 2 1 5 8 41
11001 0 1 6 7 48
11002 1 0 8 9 57
11003 1 1 3 5 62
11004 0 0 10 10 72
11005 2 1 9 12 84
11006 1 0 10 11 95
 hidden batch(es)