USP_GET_MEMBERSHIP_USAGE_TRACKING_DATA

    create procedure dbo.USP_GET_MEMBERSHIP_USAGE_TRACKING_DATA(
    @LastProcessingDate DateTime,
    @ProductName varchar(4)
    )
    as
    begin
            DECLARE @OrganizationDetails NVARCHAR(400)

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

          WITH MEMBERSHIP_TRANSACTION_INFORMATION(MembershipTransactionsID, PartTypeID, PageName, Currency)
             AS (SELECT ID,

                        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 xmlns="urn:blackbaud.RE7.XDATA">%', CONVERT(VARCHAR(max), XMLObjectData)) + 46 ),
                                         /* Calculate SUBSTRING length as end - start. */
                                         ( Patindex('%</CurrencyType>%', CONVERT(VARCHAR(max), XMLObjectData)) - ( Patindex('%<CurrencyType xmlns="urn:blackbaud.RE7.XDATA">%', CONVERT(VARCHAR(max), XMLObjectData)) + 46 ) ))
                          ELSE NULL
                        END AS Currency
                 FROM   dbo.MEMBERSHIPTRANSACTIONS
                 WHERE  MEMBERSHIPTRANSACTIONS.Status = 1
                        AND IsDeleted = 0)
        SELECT @ProductName + '-' + 'OnlineMemberships' + '-'
               + @OrganizationDetails                               AS 'Group',
               CONTENTTYPES.NAME                                    AS PartType,
               MEMBERSHIP_TRANSACTION_INFORMATION.PageName          AS PageName,
               Year(MEMBERSHIPTRANSACTIONS.DateAdded)               AS TransactionYear,
               Month(MEMBERSHIPTRANSACTIONS.DateAdded)              AS TransactionMonth,
               Day(MEMBERSHIPTRANSACTIONS.DateAdded)                AS TransactionDay,      
               Count(MEMBERSHIPTRANSACTIONS.ID)                     AS TotalTransactionsCountForTheDay,
               Sum(MEMBERSHIPTRANSACTIONS.GiftAmount)               AS TotalTransactionsAmountForTheDay,
               MEMBERSHIP_TRANSACTION_INFORMATION.Currency          AS Currency,
               MAX(MEMBERSHIPTRANSACTIONS.DateAdded)                AS MaxTransactionDate
        FROM   dbo.MEMBERSHIPTRANSACTIONS
               INNER JOIN MEMBERSHIP_TRANSACTION_INFORMATION
                       ON MEMBERSHIP_TRANSACTION_INFORMATION.MembershipTransactionsID = MEMBERSHIPTRANSACTIONS.ID
               INNER JOIN dbo.CONTENTTYPES
                       ON CONTENTTYPES.id = MEMBERSHIP_TRANSACTION_INFORMATION.PartTypeID

        WHERE  MEMBERSHIPTRANSACTIONS.DateAdded   >   @LastProcessingDate               
        GROUP  BY 
                  CONTENTTYPES.NAME,
                  MEMBERSHIP_TRANSACTION_INFORMATION.PageName,
                  Year(MEMBERSHIPTRANSACTIONS.DateAdded),
                  Month(MEMBERSHIPTRANSACTIONS.DateAdded),
                  Day(MEMBERSHIPTRANSACTIONS.DateAdded),
                  MEMBERSHIP_TRANSACTION_INFORMATION.Currency
        ORDER  BY
                  Year(MEMBERSHIPTRANSACTIONS.DateAdded),
                  Month(MEMBERSHIPTRANSACTIONS.DateAdded) 
    end