jallowmarie - Saturday, April 21, 2018 3:38 PM

hi ,
i  need help with calculating YTD for Tax and TaxablePay. I already have a script that calculates the monthly values but i just cant get the YTD totals right. Any help will be highly appreciated.

IF OBJECT_ID ( 'spTaxReturn', 'P' ) IS NOT NULL
  DROP PROC spTaxReturn
GO

CREATE PROC spTaxReturn
    @PayYearId AS VARCHAR(10) = '2006',
    @PayPeriodId AS TINYINT = 1
WITH ENCRYPTION
AS
    DECLARE @msg VARCHAR(8000);
    SET @msg = 'Printed Tax Return for ' + @PayYearId + ' ' +
        COALESCE(CAST(@PayPeriodId AS VARCHAR(3)), '');
    exec spAddLogEntry 'INFO', @msg;

    SELECT TIN, S.StaffId, Name = (FirstName + ' ' + MiddleName + ' ' + Surname),
        ((s.TaxablePay/12) + ISNULL(d.Tax, 0)) AS TaxablePay, Tax =Amount
    FROM (Staff S INNER JOIN PayRunIncomeTax ON S.StaffId = PayRunIncomeTax.StaffId) LEFT JOIN
        dbo.fnGetExtraPayAndDeductionsForReporting(@PayYearId, @PayPeriodId) d ON s.StaffId = d.StaffId
    WHERE PayYearId =@PayYearId AND PayPeriodId =@PayPeriodId
    ORDER BY TIN
GO