Select Subquery not returning all desired rows.

[lradon]

Hi,

I work the IS dept. for a small credit union. I am using SQL Server 7 and I'm not sure what else you need to know.

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, where I've restricted the STATUSER to 425 or 429, in regards to the SEQNUM's for underwriters 425 and 429, I am getting regards 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.

I have a feeling a subquery would work, but, am unable to figure out the syntax.

Would someone be able to assist me?

Thanks, Laura

 

[druer]

We'll need to see your table definitions in order to provide much help, along with some sample data for those tables. With examples specifically of what you mean by restricting it to statuser 425 or 429, but wanting all the sequences. Until then all we can do is guess. Which I'll do in a second.

 

[druer]

I am guessing that your STL table contains multiple sequences and that some of them may have been worked on by the 425 or 429 user. And that is what you mean in your request. In that case you are definitely saying show me only the sequence numbers where those users touched it.

One simple way to is consider a sub-select where you say:
some key field IN (select key field where StatUser = 425 or StatUser = 429)

Where you are basically saying "give me the list of the records/cases/projects that these two worked on, and then use that predone list to compare to. Without the table criteria I don't have a clue what your key field is that you would need this for. You could either use the select as as a subselect "IN Clause" in your WHERE conditions or you could add the select and reference it in your JOINS

table inner join on .... (select KeyField where StatuUser = 425 or StatUser = 429) ProjectsTouchedByUsers ON ProjectsTouchedByUsers.KeyField = OtherTable.KeyField

 

[lradon]

Thansks Druer. I will post the additional info. I so appreciate help.

Laura

 

[lradon]

--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.

Is this better?

Thanks Laura