Excluding data for certain criteria only

Tarkon · #1 (**permalink**) 02-06-12, 12:09

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:

Table PR04PTF

PayPeriodEnd	EmployeeID	PaycheckNo
2012-02-03	441	113223
2012-01-20	441	113208
2012-01-06	441	112950

Table PR11ERF_History

PaycheckType	EmployeeID	PayPeriodEnd	PaycheckAmt
Bonus	441	2012-02-03
Regular	441	2012-02-03
Regular	441	2012-01-20
Bonus	441	2012-01-20
Regular	441	2012-01-20
Bonus	441	2012-01-06
Regular	441	2012-01-06

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

kordirko · #2 (**permalink**) 02-06-12, 18:05

Could you provide an expected output of the query for this example data ?

Mark_Otmarich · #3 (**permalink**) 02-06-12, 20:27

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.

Tarkon · #4 (**permalink**) 02-07-12, 11:49

Thanks, Mark! This did the trick!

Quote:

Originally Posted by Mark_Otmarich

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech