USP_GET_EVENTS_USAGE_TRACKING_DATA

    create procedure dbo.USP_GET_EVENTS_USAGE_TRACKING_DATA(
    @LastProcessingDate DateTime,
    @ProductName varchar(4)
    )
    as
    begin

        DECLARE @OrganizationDetails NVARCHAR(400)

        SELECT @OrganizationDetails = CLARIFYSITEID,
               @OrganizationDetails = @OrganizationDetails + ' - ' + INSTALLATIONNAME
        FROM   dbo.INSTALLATIONINFO;

        WITH EVENT_TRANSACTION_INFORMATION(EventTransactionsID, PartTypeID, PageName, Currency)
             AS (SELECT EventTransactionsID,

                        CASE
                          WHEN Charindex('PartTypeID', CONVERT(VARCHAR(max), XMLObjectData)) > 0 
                          THEN Substring(CONVERT(VARCHAR(max), XMLObjectData), ( Patindex('%<PartTypeID>%', CONVERT(VARCHAR(max), XMLObjectData)) + 12 ),
                                         /* Calculate SUBSTRING length as end - start. */
                                         ( Patindex('%</PartTypeID>%', CONVERT(VARCHAR(max), XMLObjectData)) - ( Patindex('%<PartTypeID>%', CONVERT(VARCHAR(max), XMLObjectData)) + 12 ) ))
                          ELSE NULL
                        END AS PartId,
                        CASE
                          WHEN Charindex('PageName', CONVERT(VARCHAR(max), XMLObjectData)) > 0 
                          THEN Substring(CONVERT(VARCHAR(max), XMLObjectData), ( Patindex('%<PageName>%', CONVERT(VARCHAR(max), XMLObjectData)) + 10 ),
                                         /* Calculate SUBSTRING length as end - start. */
                                         ( Patindex('%</PageName>%', CONVERT(VARCHAR(max), XMLObjectData)) - ( Patindex('%<PageName>%', CONVERT(VARCHAR(max), XMLObjectData)) + 10 ) ))
                          ELSE NULL
                        END AS PageName,
                        CASE
                          WHEN Charindex('CurrencyType', CONVERT(VARCHAR(max), XMLObjectData)) > 0 
                          THEN Substring(CONVERT(VARCHAR(max), XMLObjectData), ( Patindex('%<CurrencyType>%', CONVERT(VARCHAR(max), XMLObjectData)) + 14 ),
                                         /* Calculate SUBSTRING length as end - start. */
                                         ( Patindex('%</CurrencyType>%', CONVERT(VARCHAR(max), XMLObjectData)) - ( Patindex('%<CurrencyType>%', CONVERT(VARCHAR(max), XMLObjectData)) + 14 ) ))
                          ELSE NULL
                        END AS Currency
                 FROM   dbo.EVENTTRANSACTIONS
                 WHERE  EVENTTRANSACTIONS.Status = 1
                        AND IsDeleted = 0)

        SELECT @ProductName + '-' + 'EventRegistrations' + '-'
               + @OrganizationDetails                       AS 'Group',
               CONTENTTYPES.NAME                            AS PartType,
               EVENT_TRANSACTION_INFORMATION.PageName       AS PageName,
               Year(EVENTTRANSACTIONS.DateAdded)            AS TransactionYear,
               Month(EVENTTRANSACTIONS.DateAdded)           AS TransactionMonth,
               Day(EVENTTRANSACTIONS.DateAdded)             AS TransactionDay,
               Count(EVENTTRANSACTIONS.EventTransactionsID) AS TotalTransactionsCountForTheDay,
               Sum(EVENTTRANSACTIONS.GiftAmount)            AS TotalTransactionsAmountForTheDay,
               EVENT_TRANSACTION_INFORMATION.Currency       AS Currency,
               MAX(EVENTTRANSACTIONS.DateAdded)             AS MaxTransactionDate
        FROM   dbo.EVENTTRANSACTIONS
               INNER JOIN EVENT_TRANSACTION_INFORMATION
                       ON EVENT_TRANSACTION_INFORMATION.EventTransactionsID = EVENTTRANSACTIONS.EventTransactionsID
               INNER JOIN dbo.CONTENTTYPES
                       ON CONTENTTYPES.id = EVENT_TRANSACTION_INFORMATION.PartTypeID
        WHERE  EVENTTRANSACTIONS.DateAdded   >   @LastProcessingDate        
        GROUP  BY 
 CONTENTTYPES.NAME,
                  EVENT_TRANSACTION_INFORMATION.PageName,
                  Year(EVENTTRANSACTIONS.DateAdded),
                  Month(EVENTTRANSACTIONS.DateAdded),
                  Day(EVENTTRANSACTIONS.DateAdded), 
                  EVENT_TRANSACTION_INFORMATION.Currency
        ORDER  BY 
                  Year(EVENTTRANSACTIONS.DateAdded),
                  Month(EVENTTRANSACTIONS.DateAdded) 

    end