USP_GET_DONATIONS_USAGE_TRACKING_DATA

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

          DECLARE @OrganizationDetails NVARCHAR(400)

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

      WITH DONATION_TRANSACTION_INFORMATION(DonationTransactionsID, PartTypeID, PageName, Currency)
        AS (SELECT  DonationTransactionsID,
                    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.DONATIONTRANSACTIONS
            WHERE   DONATIONTRANSACTIONS.Status = 1
                    AND IsDeleted = 0)

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

    end