April 21, 2018 at 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
April 21, 2018 at 5:09 pm
This should give you a start (although you might need a Calendar table to join to so you can PARTITION wherever you want.)
https://blog.bertwagner.com/heres-a-quick-way-to-generate-a-running-total-in-sql-server-f5654d310030
April 22, 2018 at 9:02 am
thank you. will try and see how it goes
April 22, 2018 at 12:41 pm
jallowmarie - Saturday, April 21, 2018 3:38 PMhi ,
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
GOCREATE 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
It would appear that you're storing TINs, which could also be SSNs, in an unencrypted column. Really bad idea.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply