Select Subquery not returning all desired rows.

[lradon]

Hi,

I really need help with this, so I have updated the post with more information.

I work the IS dept. for a small credit union. I am using SQL Server 7.

I am trying to create a query for a report that summarizes our loan underwriters' response times. The data needs to be limited to applications from only 2 out of several underwriters and must contain all status records for those applications that are captured in our Lending system from each of the two user's acting on the loans.

What we are looking to analyze is the following:
1.) Date/Time from which last communication from the MSR to the Loan Underwriter
2.) Date/Time from which the loan was underwritten to when the loan was booked.

These times are being analyzed for quick turnaround date & times for our members.

The data is primarily coming from the APL and STL tables. The APL table contains the main application info. and the loan status codes are in the STL table. The rest of the tables are required for linking purposes or to provide attributal information.

I am not very good with T-SQL. I copied this from the View wizard. I have even tried left outer joins.

This is the code:

SELECT     dbo.apl.APLNUM, dbo.stl.LONNUM, dbo.apl.ORIGDATE, dbo.apl.ORIGTIME, 
		dbo.stl.SEQNUM, dbo.stl.STATUSER, dbo.stl.STATDATE, dbo.stl.STATTIME, dbo.stl.STATUS, dbo.apl.ORGBRNCH
FROM         dbo.lon INNER JOIN
                      dbo.stl INNER JOIN
                      dbo.alo INNER JOIN
                      dbo.apl ON dbo.alo.APLNUM = dbo.apl.APLNUM ON dbo.stl.APLNUM = dbo.apl.APLNUM 
			ON dbo.lon.LONNUM = dbo.alo.LONNUM
WHERE     (dbo.stl.SEQNUM > 0) AND (dbo.apl.ORGBRNCH < 7) AND 
	(dbo.apl.ORIGDATE >= CONVERT(DATETIME, '2005-12-01 00:00:00', 102)) AND (dbo.stl.STATUSER = 425 OR
                      dbo.stl.STATUSER = 429)
ORDER BY dbo.apl.APLNUM, dbo.stl.SEQNUM

The STATUSER[underwriters] can act on the loan application any number of times and each action is assign a SEQNUM and these action/status records are stored in the STL table along with the STATUSER number.

In the code above, I've restricted the STATUSER to 425 or 429, in regards to the SEQNUM's for underwriters 425 and 429, I am getting records returned only for these two STATUSERs. I need all the SEQNUM'S for applications that STATUSER'S 425 and 429 acted on, starting with SEQNUM 1.
*****************************************************************
--Here are the table definitions


APL Table
[APLNUM] [float] NULL ,
[MEMNBR] [float] NULL ,
[ORGBRNCH] [float] NULL ,
[ORIGDATE] [smalldatetime] NULL ,
[ORIGTIME] [nvarchar] (8) NULL ,
[ORIGUSER] [float] NULL ,
[SENTUSER1] [float] NULL ,
[SENTUSER2] [float] NULL ,
[SENTUSER3] [float] NULL ,
[SENTUSER4] [float] NULL ,
[SENTUSER5] [float] NULL ,
[STATDATE] [smalldatetime] NULL ,
[STATTIME] [nvarchar] (8) NULL ,


STL Table
[APLNUM] [float] NULL ,
[COUNT] [float] NULL ,
[LONNUM] [float] NULL ,
[SEQNUM] [float] NULL ,
[STATDATE] [smalldatetime] NULL ,
[STATTIME] [nvarchar] (8) NULL ,
[STATUS] [nvarchar] (1) NULL ,
[STATUSER] [float] NULL


********************************************************
aplnum,Loan#,origdate,origtime,seqnum,statuser,sentTo,name,statdate,stattime,status
99999,This would be SEQNUM 1 - MISSING FROM RECORDSET
99999,143,11/21/2005,14:34:52,2,425,123,Super Mario,11/21/05,09:35:54,A
99999,This would be SEQNUM 3 - MISSING FROM RECORDSET
88888,This would be SEQNUM 1 - MISSING FROM RECORDSET
88888,This would be SEQNUM 2 - MISSING FROM RECORDSET
88888,157,11/27/05,09:25:06,3,429,122,Tony Hawk,11/22/2005,14:34:52,D

There are 2 sample loan applications 88888,99999 and one has a record for user #425 and the other has for #429. There are 3 records in the STL table for each application. Two records out of the three for each application are missing from the recordset. Only the ones with the STATUSER for both 425 and 429 are showing. I need the missing records to return as well.

Then in my reporting application, CR 8.5, I will group by aplnum and then seqnum and then only applications that have a status record by 425 or 429 will display on report. If an application in APL has a record with either 425 or 429 in the STATUSER FIELD, I want all records associated with the application from the STL returned or I don't want to see the loan application on the report. STATUSER's 425 and 429 are being monitored on their decision making.

Thanks Laura