/* The queries below use data in a temp table, so at a minimum execute the queries for the temp table, and then use one or more of the queries on the BINARY table */

/*** CREATE THE TEMP TABLE ***/
/* Clear the temp table to be sure */
If(OBJECT_ID('tempdb..#CURRENT_ITEM_BINARIES') Is Not Null)
Begin
    Drop Table #CURRENT_ITEM_BINARIES
End

/* figure out which binaries are used by current (not old) versions of items */
SELECT BINARY_ID as CURRENT_BINARY_ID
    ,CONCAT('tcm:', [PUBLICATION_ID], '-', [ITEM_REFERENCE_ID], '-', [ITEM_TYPE]) as ITEM_TCM_ID
	,CONCAT('tcm:', [PUBLICATION_ID], '-', [ITEM_REFERENCE_ID], '-', [ITEM_TYPE], '-v', [VERSION]) as MAX_ITEM_VERSION,
	TITLE
INTO #CURRENT_ITEM_BINARIES
FROM [Tridion_CM].[dbo].[ITEMS] I1
WHERE I1.VERSION = (SELECT MAX(VERSION) FROM [ITEMS] I2 WHERE I2.PUBLICATION_ID = I1.PUBLICATION_ID AND I2.ITEM_REFERENCE_ID = I1.ITEM_REFERENCE_ID)
    AND BINARY_ID IS NOT NULL;

/*** GET INFORMATION ABOUT CURRENT ITEMS ***/
/* show info about binaries which are used in current versions of items */
SELECT [ID] as BINARY_ID, ([BINARIES].[CONTENT_SIZE] / 1024) AS SIZE_KB
    ,(SELECT TOP 1 (CONCAT('tcm:', [PUBLICATION_ID], '-', [ITEM_REFERENCE_ID], '-', [ITEM_TYPE]))
	  FROM ITEMS I2
	  WHERE I2.[BINARY_ID] = [BINARIES].[ID]
	  ORDER BY I2.[VERSION]) AS ITEM_TCM_ID
    ,(SELECT TOP 1 I3.[TITLE]
	  FROM ITEMS I3
	  WHERE I3.[BINARY_ID] = [BINARIES].[ID]
	  ORDER BY I3.[VERSION]) AS TITLE
FROM BINARIES
WHERE ID IN (SELECT CURRENT_BINARY_ID FROM #CURRENT_ITEM_BINARIES);

/* show total size of binaries which are used in current versions of items */
SELECT SUM(([BINARIES].[CONTENT_SIZE] / 1024)) AS TOTAL_SIZE_KB
FROM BINARIES
WHERE ID IN (SELECT CURRENT_BINARY_ID FROM #CURRENT_ITEM_BINARIES);

/*** GET INFORMATION ABOUT OLD ITEMS ***/
/* show info about binaries which are only used in old versions of items */
SELECT [ID] as BINARY_ID, ([BINARIES].[CONTENT_SIZE] / 1024) AS SIZE_KB
    ,(SELECT TOP 1 (CONCAT('tcm:', [PUBLICATION_ID], '-', [ITEM_REFERENCE_ID], '-', [ITEM_TYPE]))
	  FROM ITEMS I2
	  WHERE I2.[BINARY_ID] = [BINARIES].[ID]
	  ORDER BY I2.[VERSION]) AS ITEM_TCM_ID
    ,(SELECT TOP 1 I3.[TITLE]
	  FROM ITEMS I3
	  WHERE I3.[BINARY_ID] = [BINARIES].[ID]
	  ORDER BY I3.[VERSION]) AS TITLE
FROM BINARIES
WHERE ID NOT IN (SELECT CURRENT_BINARY_ID FROM #CURRENT_ITEM_BINARIES);

/* show total size of binaries which are only used in old versions of items */
SELECT SUM(([BINARIES].[CONTENT_SIZE] / 1024)) AS TOTAL_SIZE_KB
FROM BINARIES
WHERE ID NOT IN (SELECT CURRENT_BINARY_ID FROM #CURRENT_ITEM_BINARIES);