Phalanx Phalanx - 7 months ago 9
SQL Question

SQL Show which entries are missing

I'm trying to create an exception report in MSSQL.

In our Payroll software we have two processes that we do every week. We calculate how much leave an employee has accrued and taken, and then we need to reset the leave accrued so they may accrue more next week without hitting the limit. This is all done in a single table called PRLH. This is a smaller version of the table without the clutter. This reset process needs to be done weekly without fail, otherwise it's a huge task to fix it. (I spent 4.75 hours today fixing a mistake our payroll officer did when she forgot to reset it 5 weeks ago).

PRLH



| PRCo | PREndDate | Employee | LeaveCode | ActDate | Type |
| 3 | 2016-03-05 00:00:00 | 3 | ALH | 2016-01-24 00:00:00 | A |
| 3 | NULL | 3 | ALH | 2016-01-26 00:00:00 | R |


PRLV



| PRCo | LeaveCode | AccType |
| 3 | ALH | R |
| 4 | FLY | F |


PRCo is a company number. PREndDate only applies when Type is either 'U' or 'A'and it is the Week Ending date (for us it's always Tuesday) otherwise if it's type 'R' it's null, ActDate is the date that it actually occurred, the ActDate with Type R should always be on a Tuesday. Type is either 'R', 'U', 'A' (Reset, Usage, Accrual).

What I'm looking for is a statement which will show me any combination of PRCo, PREndDate and LeaveCode where ActDate doesn't exist with the type R for the last Tuesday, though I want to exclude it if the LeaveCodes AccType is 'F'. This will be run every Wednesday/Thursday. I've been using this formula in it for the to get me the Tuesday I need.

DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 1)


Here's a sample table:

| PRCo | PREndDate | Employee | LeaveCode | ActDate | Type |
| 3 | 2016-03-05 00:00:00 | 3 | ALH | 2016-01-24 00:00:00 | A |
| 1 | 2016-03-05 00:00:00 | 3 | PLH | 2016-01-23 00:00:00 | A |
| 1 | NULL | 3 | PLH | 2016-01-26 00:00:00 | R |
| 4 | 2016-03-05 00:00:00 | 3 | FLY | 2016-01-23 00:00:00 | A |


I would like it to return

| PRCo | PREndDate | LeaveCode |
| 3 | 2016-03-05 00:00:00 | ALH |


I'm fairly new to SQL, I only know basic SELECT statments with JOINS and not a huge amount more and this is out of my scope and I've tried to experiment to get this done, but no success so far.

EDIT



With only limited sample data, I can't really test it, but the below code modified slightly from Sunny's appeared to give me what I wanted.

Select DISTINCT(a.PREndDate) as EndDate, a.PRCo, a.LeaveCode
From PRLH a Inner Join PRLV b
on a.PRCo = b.PRCo
Left Outer Join
(Select PRCo, PREndDate
From PRLH
Where [Type] = 'R'
And ActDate = DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 1)) as c
on a.PRCo = c.PRCo
Where c.PRCo IS NULL
AND b.AccType <> 'F' AND a.PRCo < 100

Answer

If I got your example and requirement correct, this should help.....

Select  a.PRCo, a.PREndDate, a.LeaveCode
From    PRLH a  Inner Join PRLV b
                on a.PRCo = b.PRCo
                Left Outer Join
                (Select PRCo, PREndDate
                    From    PRLH
                    Where   [Type]  = 'R'
                    And     ActDate = DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 1)) as c
        on  a.PRCo = c.PRCo
Where   c.PRCo IS NULL
AND     b.AccType <> 'F'