DECLARE @dateCutOff datetime

DECLARE @dateMonthBegin datetime

DECLARE @datePrevMonthBegin datetime

DECLARE @datePrev1MonthBegin datetime

DECLARE @datePrev2MonthBegin datetime

 

DELETE FROM tbl_ARSnap

SELECT @dateCutOff = '3/31/08 11:59:59 PM'

IF (DatePart(month, @dateCutOff) = DatePart(month, DateAdd(day, 1, @datecutoff)))

    BEGIN

                        SELECT @dateMonthBegin = DateAdd(month, -1, @dateCutOff)

                        SELECT @datePrevMonthBegin = DateAdd(month, -1, @dateMonthBegin)

                        SELECT @datePrev1MonthBegin = DateAdd(month, -1, @datePrevMonthBegin)

                        SELECT @datePrev2MonthBegin = DateAdd(month, -1, @datePrev1MonthBegin)

    END

ELSE

    BEGIN

                        SELECT @dateMonthBegin = DateAdd(month, -1, DateAdd(day, 1, @dateCutOff))

                        SELECT @datePrevMonthBegin = DateAdd(month, -1, @dateMonthBegin)

                        SELECT @datePrev1MonthBegin = DateAdd(month, -1, @datePrevMonthBegin)

                        SELECT @datePrev2MonthBegin = DateAdd(month, -1, @datePrev1MonthBegin)

    END

 

 

INSERT INTO tbl_ARSnap (CustID, Updated, CurrentBalance, ZeroToThirty, ThirtyToSixty, SixtyToNinety, NinetyToOneTwenty, GreaterOneTwenty)

SELECT tbl_CustomerData.CustID, @datecutoff, Sum(TransAmount+TransTax) AS CurrentBalance,

  Sum(CASE WHEN (TransDateTime >= @dateMonthBegin AND (TransAmount+TransTax > 0)) THEN (TransAmount+TransTax) ELSE 0 END) + (CASE WHEN Sum(CASE WHEN TransDateTime < @dateMonthBegin OR (TransDateTime >= @dateMonthBegin AND (TransAmount+TransTax < 0))  THEN (TransAmount+TransTax) ELSE 0 END) < = 0 THEN Sum(CASE WHEN TransDateTime < @dateMonthBegin OR (TransDateTime >= @dateMonthBegin AND (TransAmount+TransTax < 0))        THEN (TransAmount+TransTax) ELSE 0 END) ELSE 0 END) AS ZeroToThirty,

  Sum(CASE WHEN (TransDateTime < @dateMonthBegin AND TransDateTime >= @datePrevMonthBegin AND (TransAmount+TransTax > 0)) THEN (TransAmount+TransTax) ELSE 0 END) + (CASE WHEN Sum(CASE WHEN TransDateTime < @datePrevMonthBegin OR (TransDateTime >= @datePrevMonthBegin AND (TransAmount+TransTax < 0))      THEN (TransAmount+TransTax) ELSE 0 END) < = 0 THEN Sum(CASE WHEN TransDateTime < @datePrevMonthBegin OR (TransDateTime >= @datePrevMonthBegin AND (TransAmount+TransTax < 0)) THEN (TransAmount+TransTax) ELSE 0 END) ELSE 0 END) AS ThirtyToSixty,

  Sum(CASE WHEN (TransDateTime < @datePrevMonthBegin AND TransDateTime >= @datePrev1MonthBegin AND (TransAmount+TransTax > 0)) THEN (TransAmount+TransTax) ELSE 0 END) + (CASE WHEN Sum(CASE WHEN TransDateTime < @datePrev1MonthBegin OR (TransDateTime >= @datePrev1MonthBegin AND (TransAmount+TransTax < 0))      THEN (TransAmount+TransTax) ELSE 0 END) < = 0 THEN Sum(CASE WHEN TransDateTime < @datePrev1MonthBegin OR (TransDateTime >= @datePrev1MonthBegin AND (TransAmount+TransTax < 0)) THEN (TransAmount+TransTax) ELSE 0 END) ELSE 0 END) AS SixtyToNinety,

  Sum(CASE WHEN (TransDateTime < @datePrev1MonthBegin AND TransDateTime >= @datePrev2MonthBegin AND (TransAmount+TransTax > 0)) THEN (TransAmount+TransTax) ELSE 0 END) + (CASE WHEN Sum(CASE WHEN TransDateTime < @datePrev2MonthBegin OR (TransDateTime >= @datePrev2MonthBegin AND (TransAmount+TransTax < 0))      THEN (TransAmount+TransTax) ELSE 0 END) < = 0 THEN Sum(CASE WHEN TransDateTime < @datePrev2MonthBegin OR (TransDateTime >= @datePrev2MonthBegin AND (TransAmount+TransTax < 0)) THEN (TransAmount+TransTax) ELSE 0 END) ELSE 0 END) AS NinetyToOneTwenty,

    Sum(CASE WHEN (TransDateTime < @datePrev2MonthBegin AND (TransAmount+TransTax > 0)) THEN (TransAmount+TransTax) ELSE 0 END) + Sum(CASE WHEN TransAmount+TransTax < 0 THEN (TransAmount+TransTax) ELSE 0 END) AS GreaterThanOneTwenty

    FROM tbl_CustomerData

    INNER JOIN tbl_Transactions ON tbl_CustomerData.CustID = tbl_Transactions.CustID

    WHERE tbl_Transactions.TransDateTime <= @dateCutOff

    GROUP BY tbl_CustomerData.CustID

 

UPDATE tbl_ARSnap SET ZeroToThirty = 0 WHERE ZeroToThirty < 0 AND DatePart(month, Updated) = DatePart(month, @dateCutOff) AND DatePart(year, Updated) = DatePart(year, @dateCutOff)

UPDATE tbl_ARSnap SET ThirtyToSixty = 0 WHERE ThirtyToSixty < 0 AND DatePart(month, Updated) = DatePart(month, @dateCutOff) AND DatePart(year, Updated) = DatePart(year, @dateCutOff)

UPDATE tbl_ARSnap SET SixtyToNinety = 0 WHERE SixtyToNinety < 0 AND DatePart(month, Updated) = DatePart(month, @dateCutOff) AND DatePart(year, Updated) = DatePart(year, @dateCutOff)

UPDATE tbl_ARSnap SET NinetyToOneTwenty = 0 WHERE NinetyToOneTwenty < 0 AND DatePart(month, Updated) = DatePart(month, @dateCutOff) AND DatePart(year, Updated) = DatePart(year, @dateCutOff)

UPDATE tbl_ARSnap SET GreaterOneTwenty = 0 WHERE GreaterOneTwenty < 0 AND DatePart(month, Updated) = DatePart(month, @dateCutOff) AND DatePart(year, Updated) = DatePart(year, @dateCutOff)