test percentage

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

DECLARE @subContractorMarkedCode TABLE
	(quantity INTEGER,
	clientCusSubCont VARCHAR(30),
	codesStatus2 VARCHAR(30),
	codesStatus16 VARCHAR(30),
	codesStatus32 VARCHAR(30),
	codesStatus64 VARCHAR(30));
INSERT @subContractorMarkedCode
VALUES(30,'Gucci','Item Associated','Cloned','',''),
(30,'Gucci','Item Associated','','Blocked',''),
(20,'Gucci','Item Associated','','Blocked',''),
(20,'Gucci','Item Associated','','','Stolen'),
(40,'Nike','Item Associated','Cloned','',''),
(40,'Nike','Item Associated','','','Stolen'),
(40,'Dior','Item Associated','','Blocked','');

--
-- Gucci | Nike   | Dior
-- 100   | 80     | 40
-- 45.45%| 36.36% | 18.18%
--

            
WITH TEMPORARY_CTE (SUM_GRAN_TOTAL) AS (
SELECT
		SUM(INN_Q.SUB_QUANTITY) AS SUM_GRAN_TOTAL
	FROM
		(
		SELECT
			SUM(quantity) AS SUB_QUANTITY,
			clientCusSubCont
		FROM
			@subContractorMarkedCode
		WHERE
			codesStatus2 = 'Item Associated'
			AND (codesStatus16 = 'Cloned'
			OR codesStatus32 = 'Blocked'
			OR codesStatus64 = 'Stolen')
		GROUP BY
			clientCusSubCont) AS INN_Q) 
            
SELECT SUM(quantity)AS AGGR_SUM,(SELECT SUM_GRAN_TOTAL FROM TEMPORARY_CTE) AS TOTAL_SUM
	FROM
		@subContractorMarkedCode
	WHERE
		codesStatus2 = 'Item Associated'
		AND (codesStatus16 = 'Cloned'
		OR codesStatus32 = 'Blocked'
		OR codesStatus64 = 'Stolen')
	GROUP BY
		clientCusSubCont

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results