Rate Calc Query.sql

SELECT      INVOICE_HEADER.CONTRACT_NO,  SUM(INVOICE_DETAIL.AIR_TIME_AMOUNT) AS Amount, CHANNEL_ID, AGENT_CODE, CUSTOMER_CODE,
CASE WHEN CHANNEL_ID = 1 THEN
	CASE  WHEN TELECAST_TIME >= '19:00:00' AND TELECAST_TIME <= '21:30:00' THEN
		CASE WHEN AGENT_CODE = 'DIRT' THEN 'CH1 A-DIRT' ELSE 'CH1 A' END
	ELSE CASE WHEN AGENT_CODE = 'DIRT' THEN 'CH1 B-DIRT' ELSE 'CH1 B' END
	END
WHEN CHANNEL_ID = 2 THEN
	CASE WHEN AGENT_CODE = 'DIRT' THEN 'CH2 DIRT' ELSE 'CH2' END
WHEN CHANNEL_ID = 3 THEN
	CASE WHEN AGENT_CODE = 'DIRT' THEN 'CH3 DIRT' ELSE 'CH3' END
END AS Category,
CASE WHEN CHANNEL_ID = 1 THEN
	CASE  WHEN TELECAST_TIME >= '19:00:00' AND TELECAST_TIME <= '21:30:00' THEN
		CASE WHEN AGENT_CODE = 'DIRT' THEN 1.5 ELSE 1.5 END
	ELSE CASE WHEN AGENT_CODE = 'DIRT' THEN 3 ELSE 1.5 END
	END
WHEN CHANNEL_ID = 2 THEN
	CASE WHEN AGENT_CODE = 'DIRT' THEN 1.5 ELSE 1.5 END
WHEN CHANNEL_ID = 3 THEN
	CASE WHEN AGENT_CODE = 'DIRT' THEN 3 ELSE 3 END
END AS RATE , 'MONTH' AS [MONTH], 'YEAR' AS[YEAR]
FROM            INVOICE_HEADER INNER JOIN
                         INVOICE_DETAIL ON INVOICE_DETAIL.INVOICE_NO = INVOICE_HEADER.INVOICE_NO
WHERE        (INVOICE_HEADER.CONTRACT_NO = 'C218/00040') and MONTH(DATE_TRANS) = 9 AND INVOICE_DETAIL.AIR_TIME_AMOUNT > 0
GROUP BY  INVOICE_HEADER.CONTRACT_NO, CHANNEL_ID, CASE WHEN CHANNEL_ID = 1 THEN
	CASE  WHEN TELECAST_TIME >= '19:00:00' AND TELECAST_TIME <= '21:30:00' THEN
		CASE WHEN AGENT_CODE = 'DIRT' THEN 'CH1 A-DIRT' ELSE 'CH1 A' END
	ELSE CASE WHEN AGENT_CODE = 'DIRT' THEN 'CH1 B-DIRT' ELSE 'CH1 B' END
	END
WHEN CHANNEL_ID = 2 THEN
	CASE WHEN AGENT_CODE = 'DIRT' THEN 'CH2 DIRT' ELSE 'CH2' END
WHEN CHANNEL_ID = 3 THEN
	CASE WHEN AGENT_CODE = 'DIRT' THEN 'CH3 DIRT' ELSE 'CH3' END
END ,
CASE WHEN CHANNEL_ID = 1 THEN
	CASE  WHEN TELECAST_TIME >= '19:00:00' AND TELECAST_TIME <= '21:30:00' THEN
		CASE WHEN AGENT_CODE = 'DIRT' THEN 1.5 ELSE 1.5 END
	ELSE CASE WHEN AGENT_CODE = 'DIRT' THEN 3 ELSE 1.5 END
	END
WHEN CHANNEL_ID = 2 THEN
	CASE WHEN AGENT_CODE = 'DIRT' THEN 1.5 ELSE 1.5 END
WHEN CHANNEL_ID = 3 THEN
	CASE WHEN AGENT_CODE = 'DIRT' THEN 3 ELSE 3 END
END , AGENT_CODE, CUSTOMER_CODE

--SUBSTRING(INVOICE_DETAIL.SHEDULE_NO,0,11),