Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Location
    Washington, DC - USA
    Posts
    53

    Smile Unanswered: Excluding data for certain criteria only

    Hi!

    I'm having a problem writing a SQL query that excludes certain data. This is for a pay stub application to display current and previous paycheck stubs. To calculate certain data such as YTD figures and time off, we SUM on other tables. However, to display correctly, I can't SUM bonus checks for the current payperiod ONLY - but for previous pay periods, I must SUM bonus checks.

    Here's an example of my data:

    No code has to be inserted here.
    No code has to be inserted here.


    No code has to be inserted here.
    No code has to be inserted here.

    Right now my SQL is this:
    Code:
    SELECT PR04PTF.PayCheckNo, SUM(PR11ERF_History.PayCheckAmt) AS [TotalSum]
    FROM PR04PTF
    
    INNER JOIN PR11ERF_History ON
    PR11ERF_History.EmployeeID = PR04PTF.EmployeeID
    AND PR11ERF_History.PayPeriodEnd <= PR04PTF.PayPeriodEnd
    
    WHERE PR04PTF.EmployeeID=441
    Any help anyone could give me would be greatly appreciated.

    Thanks!
    -T

  2. #2
    Join Date
    Jan 2012
    Posts
    84
    Could you provide an expected output of the query for this example data ?

  3. #3
    Join Date
    Feb 2012
    Posts
    7
    You could try something like this


    Code:
    SELECT PR04PTF.PayCheckNo, SUM(PR11ERF_History.PayCheckAmt) AS [TotalSum]
    FROM PR04PTF
    INNER JOIN PR11ERF_History ON PR11ERF_History.EmployeeID = PR04PTF.EmployeeID AND 
                                  (PR11ERF_History.PayPeriodEnd < PR04PTF.PayPeriodEnd OR
                                   (PR11ERF_History.PayPeriodEnd = PR04PTF.PayPeriodEnd AND
                                    PR11ERF_History.PaycheckType = 'Regular'))
    WHERE PR04PTF.EmployeeID=441
    group by PR04PTF.PayCheckNo
    This excludes bonuses from the current period.
    Not sure if you want the query to return the aggregate of all bonuses and regular pay up to each pay period.

    If you want to just include the bonus and regular pay for each period, I suggest you add in a payPeriodStart to make your job way easier.

  4. #4
    Join Date
    Sep 2003
    Location
    Washington, DC - USA
    Posts
    53

    Talking

    Thanks, Mark! This did the trick!


    Quote Originally Posted by Mark_Otmarich View Post
    You could try something like this


    Code:
    SELECT PR04PTF.PayCheckNo, SUM(PR11ERF_History.PayCheckAmt) AS [TotalSum]
    FROM PR04PTF
    INNER JOIN PR11ERF_History ON PR11ERF_History.EmployeeID = PR04PTF.EmployeeID AND 
                                  (PR11ERF_History.PayPeriodEnd < PR04PTF.PayPeriodEnd OR
                                   (PR11ERF_History.PayPeriodEnd = PR04PTF.PayPeriodEnd AND
                                    PR11ERF_History.PaycheckType = 'Regular'))
    WHERE PR04PTF.EmployeeID=441
    group by PR04PTF.PayCheckNo
    This excludes bonuses from the current period.
    Not sure if you want the query to return the aggregate of all bonuses and regular pay up to each pay period.

    If you want to just include the bonus and regular pay for each period, I suggest you add in a payPeriodStart to make your job way easier.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •