how to determine if week one payroll exist base upon the data below. In the data below there are two pay periods week 1 and week 2. I am trying to wrap a stored procedure to say if week 1 pay exist then do something, then if pay period 2 exists do something.
I need to determine if payroll weeks exist by date time and not ID. As you can see in the data below there are two 1 payroll period with 2 paypoll weeks. I can only calculate payoll week 2 only if week 1 payroll exist. So basically i need to know if 2015/7/21 - 2015/7/30 Payroll Week Exist before I can execute payroll week 2 2015/8/1 - 2015/8/4
ID EmpNum Hours PayType StartDate EndDate
1 2223-ZZ 8.00 SICK 2015-7-21 2015-7-30
2 2223-ZZ 55.25 REG 2015-7-21 2015-7-30
3 2223-ZZ 5.00 VAC 2015-8-1 2015-8-4
4 2223-ZZ 44.75 REG 2015-8-1 2015-8-4
Stored Procedure Below:
if pay period 1 exist execute procedure below
SELECT
EmpNum
,SUM(CASE WHEN PayType = 'REG' THEN Hours ELSE 0 END) Hours
,SUM(CASE WHEN PayType <> 'REG' THEN Hours ELSE 0 END) PTO
,MIN(StartDate) StartDate
,MAX(EndDate) EndDate
INTO #TempTable
FROM @Tmp GROUP BY EmpNum
Then if pay period 2 exist execute another
Select (80 - (Hours + PTO) ) AdjustHours From #TempTable
Else
do nothing