-- KAO MILAN TRIP -- Report runs automatically -- ^Monthly Reports -- ignore SELECT salesmanname, CAST( SUM( CASE WHEN tranmnth ='01' and s.supplierno IN ('GK', 'KM', 'KS', 'VA') and tranyear = '2024' THEN ( discountedlinetotal * - 1 ) ELSE 0 END ) AS DECIMAL ( 10, 2 )) AS 'Kao Sales 01 2024', CAST( SUM( CASE WHEN tranmnth ='01' and s.supplierno IN ('GK', 'KM', 'KS', 'VA') and tranyear = '2025' THEN ( discountedlinetotal * - 1 ) ELSE 0 END ) AS DECIMAL ( 10, 2 )) AS 'Kao Sales 01 2025', CAST( SUM( CASE WHEN tranmnth ='02' and s.supplierno IN ('GK', 'KM', 'KS', 'VA') and tranyear = '2024' THEN ( discountedlinetotal * - 1 ) ELSE 0 END ) AS DECIMAL ( 10, 2 )) AS 'Kao Sales 02 2024', CAST( SUM( CASE WHEN tranmnth ='02' and s.supplierno IN ('GK', 'KM', 'KS', 'VA') and tranyear = '2025' THEN ( discountedlinetotal * - 1 ) ELSE 0 END ) AS DECIMAL ( 10, 2 )) AS 'Kao Sales 02 2025', CAST( SUM( CASE WHEN tranmnth ='03' and s.supplierno IN ('GK', 'KM', 'KS', 'VA') and tranyear = '2024' THEN ( discountedlinetotal * - 1 ) ELSE 0 END ) AS DECIMAL ( 10, 2 )) AS 'Kao Sales 03 2024', CAST( SUM( CASE WHEN tranmnth ='03' and s.supplierno IN ('GK', 'KM', 'KS', 'VA') and tranyear = '2025' THEN ( discountedlinetotal * - 1 ) ELSE 0 END ) AS DECIMAL ( 10, 2 )) AS 'Kao Sales 03 2025', CAST( SUM( CASE WHEN tranmnth ='04' and s.supplierno IN ('GK', 'KM', 'KS', 'VA') and tranyear = '2024' THEN ( discountedlinetotal * - 1 ) ELSE 0 END ) AS DECIMAL ( 10, 2 )) AS 'Kao Sales 04 2024', CAST( SUM( CASE WHEN tranmnth ='04' and s.supplierno IN ('GK', 'KM', 'KS', 'VA') and tranyear = '2025' THEN ( discountedlinetotal * - 1 ) ELSE 0 END ) AS DECIMAL ( 10, 2 )) AS 'Kao Sales 04 2025', CAST( SUM( CASE WHEN tranmnth ='05' and s.supplierno IN ('GK', 'KM', 'KS', 'VA') and tranyear = '2024' THEN ( discountedlinetotal * - 1 ) ELSE 0 END ) AS DECIMAL ( 10, 2 )) AS 'Kao Sales 05 2024', CAST( SUM( CASE WHEN tranmnth ='05' and s.supplierno IN ('GK', 'KM', 'KS', 'VA') and tranyear = '2025' THEN ( discountedlinetotal * - 1 ) ELSE 0 END ) AS DECIMAL ( 10, 2 )) AS 'Kao Sales 05 2025', CAST( SUM( CASE WHEN tranmnth ='06' and s.supplierno IN ('GK', 'KM', 'KS', 'VA') and tranyear = '2024' THEN ( discountedlinetotal * - 1 ) ELSE 0 END ) AS DECIMAL ( 10, 2 )) AS 'Kao Sales 06 2024', CAST( SUM( CASE WHEN tranmnth ='06' and s.supplierno IN ('GK', 'KM', 'KS', 'VA') and tranyear = '2025' THEN ( discountedlinetotal * - 1 ) ELSE 0 END ) AS DECIMAL ( 10, 2 )) AS 'Kao Sales 06 2025' -- CAST( -- SUM( CASE WHEN tranmnth ='07' and s.supplierno IN ('GK', 'KM', 'KS', 'VA') and tranyear = '2024' -- THEN ( discountedlinetotal * - 1 ) ELSE 0 END ) AS DECIMAL ( 10, 2 )) AS 'Kao Sales 07 2024', -- CAST( -- SUM( CASE WHEN tranmnth ='07' and s.supplierno IN ('GK', 'KM', 'KS', 'VA') and tranyear = '2025' -- THEN ( discountedlinetotal * - 1 ) ELSE 0 END ) AS DECIMAL ( 10, 2 )) AS 'Kao Sales 07 2025', -- CAST( -- SUM( CASE WHEN tranmnth ='08' and s.supplierno IN ('GK', 'KM', 'KS', 'VA') and tranyear = '2024' -- THEN ( discountedlinetotal * - 1 ) ELSE 0 END ) AS DECIMAL ( 10, 2 )) AS 'Kao Sales 08 2024', -- CAST( -- SUM( CASE WHEN tranmnth ='08' and s.supplierno IN ('GK', 'KM', 'KS', 'VA') and tranyear = '2025' -- THEN ( discountedlinetotal * - 1 ) ELSE 0 END ) AS DECIMAL ( 10, 2 )) AS 'Kao Sales 08 2025', -- CAST( -- SUM( CASE WHEN tranmnth ='09' and s.supplierno IN ('GK', 'KM', 'KS', 'VA') and tranyear = '2024' -- THEN ( discountedlinetotal * - 1 ) ELSE 0 END ) AS DECIMAL ( 10, 2 )) AS 'Kao Sales 09 2024', -- CAST( -- SUM( CASE WHEN tranmnth ='09' and s.supplierno IN ('GK', 'KM', 'KS', 'VA') and tranyear = '2025' -- THEN ( discountedlinetotal * - 1 ) ELSE 0 END ) AS DECIMAL ( 10, 2 )) AS 'Kao Sales 09 2025', -- CAST( -- SUM( CASE WHEN tranmnth ='10' and s.supplierno IN ('GK', 'KM', 'KS', 'VA') and tranyear = '2024' -- THEN ( discountedlinetotal * - 1 ) ELSE 0 END ) AS DECIMAL ( 10, 2 )) AS 'Kao Sales 10 2024', -- CAST( -- SUM( CASE WHEN tranmnth ='10' and s.supplierno IN ('GK', 'KM', 'KS', 'VA') and tranyear = '2025' -- THEN ( discountedlinetotal * - 1 ) ELSE 0 END ) AS DECIMAL ( 10, 2 )) AS 'Kao Sales 10 2025', -- CAST( -- SUM( CASE WHEN tranmnth ='11' and s.supplierno IN ('GK', 'KM', 'KS', 'VA') and tranyear = '2024' -- THEN ( discountedlinetotal * - 1 ) ELSE 0 END ) AS DECIMAL ( 10, 2 )) AS 'Kao Sales 11 2024', -- CAST( -- SUM( CASE WHEN tranmnth ='11' and s.supplierno IN ('GK', 'KM', 'KS', 'VA') and tranyear = '2025' -- THEN ( discountedlinetotal * - 1 ) ELSE 0 END ) AS DECIMAL ( 10, 2 )) AS 'Kao Sales 11 2025', -- CAST( -- SUM( CASE WHEN tranmnth ='12' and s.supplierno IN ('GK', 'KM', 'KS', 'VA') and tranyear = '2024' -- THEN ( discountedlinetotal * - 1 ) ELSE 0 END ) AS DECIMAL ( 10, 2 )) AS 'Kao Sales 12 2024', -- CAST( -- SUM( CASE WHEN tranmnth ='12' and s.supplierno IN ('GK', 'KM', 'KS', 'VA') and tranyear = '2025' -- THEN ( discountedlinetotal * - 1 ) ELSE 0 END ) AS DECIMAL ( 10, 2 )) AS 'Kao Sales 12 2025' FROM stocktrans s inner join debtorsmaster d on s.debtorno = d.debtorno WHERE trandate BETWEEN '2024-01-01' and '2025-12-31' and salesperson in (select salesmancode from salesman where commissionrate1 =1 ) and salesperson NOT IN ('23','27','29','00','22', '03','04', '99') and s.supplierno IN ('GK', 'KM', 'KS', 'VA') and d.typeid not IN ('6', '8', '10', '21') group by salesmanname order by salesmanname ASC

Preview Save Changes Cancel

Preview Changes hide