test percentage

0

Please login or register to vote for this query.

(click on this box to dismiss)

Open Data

Q&A for developers and researchers interested in open data

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'),
(10,'Gucci','Item Associated','','',''),
(40,'Nike','Item Associated','Cloned','',''),
(20,'Nike','Item Associated','','',''),
(40,'Nike','Item Associated','','','Stolen'),
(40,'Dior','Item Associated','','Blocked','');

--
-- Brand                  Gucci | Nike   | Dior
-- Tot assegnati al brand 110   | 100    | 40
-- Tot codice falsi brand 100   | 80%    | 0
-- Percentage             90.90%| % | 0%
-- 100 / 110 * 100
--

WITH TEMPORARY_CTE_DEN (SUM_GRAN_TOTAL,clientCusSubCont) AS (
		SELECT
			SUM(quantity) AS SUB_QUANTITY,
			clientCusSubCont
		FROM
			@subContractorMarkedCode
		WHERE
			codesStatus2 = 'Item Associated'
		GROUP BY
			clientCusSubCont) 

SELECT
	CAST((CAST(TMP_VIEW.AGGR_SUM AS FLOAT) / CAST(TMP_VIEW.TOTAL_SUM_DEN AS FLOAT)* 100.0) AS NUMERIC(12,3)),clientCusSubCont
FROM
	(
		SELECT SUM(quantity)AS AGGR_SUM,
        (SELECT TOP 1 SUM_GRAN_TOTAL FROM TEMPORARY_CTE_DEN ) AS TOTAL_SUM_DEN,
        clientCusSubCont
	FROM
		@subContractorMarkedCode
	WHERE
		codesStatus2 = 'Item Associated'
		AND (codesStatus16 = 'Cloned'
		OR codesStatus32 = 'Blocked'
		OR codesStatus64 = 'Stolen')
	GROUP BY
		clientCusSubCont) AS TMP_VIEW

Enter Parameters

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