Cacher is the code snippet organizer for pro developers

SELECT SourceUserId, usr.Username, FORM, count(1) as HowMany FROM 
 (
            SELECT
            SourceUserID,
            SourceWSID,
            msg,
            [Timestamp],
            ISNULL(msg.value('(/DCTTransactionRecord//DCT_DATA_RECORD//ITEM_DECODER/node())[1]', 'nvarchar(250)'),'BARCODE') as Barcode,
            --ISNULL(msg.value('(/DCTTransactionRecord//DCT_DATA_RECORD//FORM_PAGE/node())[1]', 'nvarchar(250)'), 'FORM') as FORM,
            --COUNT(msg.value('(/DCTTransactionRecord//DCT_DATA_RECORD//FORM_PAGE/node())[1]', 'nvarchar(250)')) as Formcount,
            --ISNULL(msg.value('(/DCTTransactionRecord//DCT_DATA_RECORD//ACTION/node())[1]', 'nvarchar(250)'),'NULL') as Cancel

			CASE WHEN msg.value('(/DCTTransactionRecord//DCT_DATA_RECORD//ACTION/node())[1]', 'nvarchar(250)') IN ( 'CANCEL')
				THEN	'CANCEL_'+ISNULL(msg.value('(/DCTTransactionRecord//DCT_DATA_RECORD//FORM_PAGE/node())[1]', 'nvarchar(250)'), 'FORM')
			ELSE
				ISNULL(msg.value('(/DCTTransactionRecord//DCT_DATA_RECORD//FORM_PAGE/node())[1]', 'nvarchar(250)'), 'FORM')
			END as FORM

            FROM (
                                SELECT  
                                SourceUserID,
                                SourceWSID,
                                CAST(MEssage as XML) as msg,
                                [Timestamp] 
                                FROM dms_data_exchange
                               WHERE MessageTypeID = 101 AND   [Timestamp] > '20160801'
            ) dx
              WHERE
              msg.value('(/DCTTransactionRecord//DCT_DATA_RECORD//ITEM_DECODER/node())[1]', 'nvarchar(250)') IN ( 'MANUAL')
			  --AND
			  --msg.value('(/DCTTransactionRecord//DCT_DATA_RECORD//ACTION/node())[1]', 'nvarchar(250)') IN ( 'CANCEL')
)fx 
 
LEFT JOIN security_users usr on SourceUserID = usr.UserID
  GROUP BY SourceUserID, FORM, Username
  ORDER BY count(1) DESC