YTD Calculation

• jallowmarie

  SSC Journeyman

  Points: 77

  More actions

  April 21, 2018 at 3:38 pm

  #344842

  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

• pietlinden

  SSC Guru

  Points: 63551

  More actions

  April 21, 2018 at 5:09 pm

  #1987786

  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

• jallowmarie

  SSC Journeyman

  Points: 77

  More actions

  April 22, 2018 at 9:02 am

  #1987795

  thank you. will try and see how it goes

• Jeff Moden

  SSC Guru

  Points: 1004558

  More actions

  Follow

  April 22, 2018 at 12:41 pm

  #1987798

  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

  It would appear that you're storing TINs, which could also be SSNs, in an unencrypted column.  Really bad idea.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)