CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
WhoaItsAFactorial
WhoaItsAFactorial

Reputation: 3558

SQL Server Query Explanation

I am working on code from a person who held my position 2+ years ago, and am having trouble deciphering a block of their SQL code.

WHERE  ACCOUNT NOT IN (SELECT ACCOUNT
                   FROM   MWAPPTS A1
                   WHERE  A1.ACCOUNT = A.ACCOUNT
                          AND ADATE > dbo.Endofmonth(ACTIONDATE)
                          AND REASON IN ('CPE','NPPE')
                          AND USERFLAG IN ( 'U', 'B' ))

dbo.Endofmonth is a stored procedure which gets the last day of the month for the inputed date.

If I am reading that right, we are not including rows where the account would be returned by the select statement shown. I am having problems with the select statement though. Am I correct that it is getting ACCOUNT where ADATE is older than the end of the month. The reason IS "CPE" or "NPPE", and the USERFLAG is either "U", or "B"?

Upvotes: 0

Views: 89

Answers (2)

bfavaretto
bfavaretto

Reputation: 71908

Am I correct that it is getting ACCOUNT where ADATE is older than the end of the month.

The inner SELECT itself is looking for entries where ADATE is after the end of the month from ACTIONDATE. The NOT IN in the outer SELECTshould then retrieve rows where the account's ADATE is <= the end of the month from ACTIONDATE.

Your other assumptions seem correct.

Aside: It seems dbo.Endofmonth is a User-Defined Function, not a stored procedure (or it wouldn't be possible to call it like that).

Upvotes: 1

Taryn
Taryn

Reputation: 247680

Yes, you are returning the records where the account does not have the following:

1) an ADATE greater than the date returned by the function dbo.Endofmonth(ACTIONDATE).

2) a Reason that is CPE or NPPE

3) and the Userflag is either U or B.

You would have to look at the code in the function dbo.Endofmonth(ACTIONDATE) to determine exactly what it is returning.

Upvotes: 1

Related Questions