calculate the percentage of items for various item types

0

Please login or register to vote for this query.

(click on this box to dismiss)

Motor Vehicle Maintenance and Repair Meta

Q&A about the site for mechanics and DIY enthusiast owners of cars, trucks, and motorcycles

CREATE TABLE #ITEM_DETAIL
	([ITEM_CREATION_DATE] datetime, [ITEM_ID] varchar(6), [ITEM_TYPEID] varchar(4), [ITEM_FOLDER] varchar(4))
;
	
INSERT INTO #ITEM_DETAIL
	([ITEM_CREATION_DATE], [ITEM_ID], [ITEM_TYPEID], [ITEM_FOLDER])
VALUES
	('2008-04-04 00:00:00', 'ITEM01', '12', 'Y'),
	('2008-12-14 00:00:00', 'ITEM04', '13', 'Y'),
	('2008-09-24 00:00:00', 'ITEM01', '13', 'Y'),
	('2009-12-04 00:00:00', 'ITEM01', '12', NULL),
	('2009-04-09 00:00:00', 'ITEM02', '13', NULL),
	('2010-08-13 00:00:00', 'ITEM01', '13', 'Y'),
	('2010-01-28 00:00:00', 'ITEM02', '14', 'Y'),
	('2011-04-09 00:00:00', 'ITEM03', '13', NULL),
	('2011-10-19 00:00:00', 'ITEM01', '12', NULL),
	('2011-04-29 00:00:00', 'ITEM04', '13', NULL),
	('2011-02-19 00:00:00', 'ITEM01', '13', NULL),
	('2011-04-09 00:00:00', 'ITEM03', '13', NULL),
	('2012-02-29 00:00:00', 'ITEM01', '14', 'Y'),
	('2012-12-09 00:00:00', 'ITEM03', '14', 'Y'),
	('2012-07-19 00:00:00', 'ITEM01', '14', 'Y'),
	('2012-06-29 00:00:00', 'ITEM01', '12', 'Y'),
	('2012-04-29 00:00:00', 'ITEM03', NULL, 'Y'),
	('2012-04-19 00:00:00', 'ITEM03', NULL, 'Y'),
	('2013-04-09 00:00:00', 'ITEM01', '13', 'Y'),
	('2013-09-14 00:00:00', 'ITEM01', '13', 'Y'),
	('2013-05-24 00:00:00', 'ITEM01', '12', 'Y'),
	('2013-01-09 00:00:00', 'ITEM01', '12', 'Y'),
	('2013-11-09 00:00:00', 'ITEM01', '14', 'Y'),
	('2013-09-21 00:00:00', 'ITEM01', '12', 'Y'),
	('2013-07-09 00:00:00', 'ITEM02', '14', 'Y'),
	('2013-08-09 00:00:00', 'ITEM02', NULL, 'Y'),
	('2013-09-09 00:00:00', 'ITEM02', NULL, 'Y')
;

SELECT [Year]
	,Convert(DECIMAL(8,2), [12]*100/Convert(DECIMAL(8,2),Total)) AS [12]
	,Convert(DECIMAL(8,2), [13]*100/Convert(DECIMAL(8,2),Total)) AS [13]
	,Convert(DECIMAL(8,2), [14]*100/Convert(DECIMAL(8,2),Total)) AS [14]
	,Convert(DECIMAL(8,2), [NULL]*100/Convert(DECIMAL(8,2),Total)) AS [NULL] 
FROM
(
	SELECT [Year],[12],[13],[14],[0] AS [NULL],
	(SELECT COUNT(ITEM_ID) FROM #ITEM_DETAIL WHERE YEAR(ITEM_CREATION_DATE) = MyTbl.[Year] GROUP BY YEAR(ITEM_CREATION_DATE)) AS Total 
	FROM 
	(
		SELECT  YEAR(ITEM_CREATION_DATE) AS [Year]
		, CASE WHEN ITEM_FOLDER IS NOT NULL THEN ITEM_ID ELSE NULL END AS [ITEM_ID]
		, ISNULL(ITEM_TYPEID,0) AS ITEM_TYPEID FROM #ITEM_DETAIL
	) AS Result 
	PIVOT 
	(
		COUNT(ITEM_ID) for ITEM_TYPEID in ([12],[13],[14],[0])
	) as MyTbl
) T


DROP TABLE #ITEM_DETAIL

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results