SQL Server - Disappearing Records

Hi experts,

I'm getting some behavior in my SQL code where, when I uncomment part of the code I lose 2000 records in my result set, and when I comment it out, I get the records back. Does anyone have any idea why the commented out code is causing me to lose records??

Thanks!!
USE LMOutput

declare @today smalldatetime
declare @startdate smalldatetime
set @today = getdate()
set @startdate = cast(cast(month(@today) as varchar) + '/1/' + cast(year(@today) as varchar) as smalldatetime)
set @startdate = dateadd(m, -1, @startdate)
                
select
    v.[acct#] as 'Acct_count',
    v.[acct#] as 'Acct_percent'
      ,v.[Loan_Status]
      ,v.[Process_Step]
      ,v.[Process_step_date]
      ,v.[Loan_status_date]
      ,v.[UWFinalDecision]
      ,v.[UWFinalDecisionDate]
      ,v.[InlineQA_Status]
      ,v.[QA_Decision_date]
      ,v.[QAIneligibleLoanReason]
      ,v.[LMOApproved_date]
      ,v.[AA_Exclusion_date]
      ,v.[AA_Exclusion_name]
      ,v.[AA_Shipped_Date]
      ,v.[Perm_approved_date]
      ,v.[Perm_shipped_date]
      ,v.[Post_trial_Sent_AA]
      ,v.[HAMP_Code]
      ,v.[Date_Referred_to_AA]
      ,AA_Start_Date,
      dbo.udf_BusinessDay_Calc(v.AA_Start_date, v.AA_Shipped_Date) as 'AA_Age',
	  CASE
		when dbo.udf_BusinessDay_Calc(v.AA_Start_date, v.AA_Shipped_Date) <= 1 then '1'
		when dbo.udf_BusinessDay_Calc(v.AA_Start_date, v.AA_Shipped_Date) = 2 then '2'
		when dbo.udf_BusinessDay_Calc(v.AA_Start_date, v.AA_Shipped_Date) = 3 then '3'
		when dbo.udf_BusinessDay_Calc(v.AA_Start_date, v.AA_Shipped_Date) = 4 then '4'
		when dbo.udf_BusinessDay_Calc(v.AA_Start_date, v.AA_Shipped_Date) = 5 then '5'
		when dbo.udf_BusinessDay_Calc(v.AA_Start_date, v.AA_Shipped_Date) = 6 then '6'
		when dbo.udf_BusinessDay_Calc(v.AA_Start_date, v.AA_Shipped_Date) = 7 then '7'
		when dbo.udf_BusinessDay_Calc(v.AA_Start_date, v.AA_Shipped_Date) = 8 then '8'
		when dbo.udf_BusinessDay_Calc(v.AA_Start_date, v.AA_Shipped_Date) = 9 then '9'
		when dbo.udf_BusinessDay_Calc(v.AA_Start_date, v.AA_Shipped_Date) = 10 then '10'
		when dbo.udf_BusinessDay_Calc(v.AA_Start_date ,v.AA_Shipped_Date) < 15 then '< 15'
		when dbo.udf_BusinessDay_Calc(v.AA_Start_date ,v.AA_Shipped_Date) < 20 then '< 20'
		when AA_Start_date is null then ''
		else '> 20'
		end as 'AA_Interval',
	  Aging_Type, 
	  
from (

select
    [acct#]
      ,[Loan_Status]
      ,[Process_Step]
      ,[Process_step_date]
      ,[Loan_status_date]
      ,[UWFinalDecision]
      ,[UWFinalDecisionDate]
      ,[InlineQA_Status]
      ,[QA_Decision_date]
      ,[QAIneligibleLoanReason]
      ,[LMOApproved_date]
      ,[AA_Exclusion_date]
      ,[AA_Exclusion_name]
      ,[Decline_shipped_date] as AA_Shipped_Date
      ,[Perm_approved_date]
      ,[Perm_shipped_date]
      ,[Post_trial_Sent_AA]
      ,[HAMP_Code]
      ,[Date_Referred_to_AA],
      CASE 
		WHEN 
				(((Date_Referred_to_AA IS NOT NULL and Date_Referred_to_AA <= dateadd(d,0,datediff(d,0,LMOApproved_date)))
					OR
				(Post_Trial_Sent_AA IS NOT NULL and Post_Trial_Sent_AA <= dateadd(d,0,datediff(d,0,LMOApproved_date)))) 
			and
				ISNULL(UWFinalDecision, '') <> 'Declined') 	
			THEN
				ISNULL(Date_Referred_to_AA, Post_Trial_Sent_AA)

--THIS CODE BELOW CAUSES RECORDS TO GO AWAY
		
--WHEN
		--		(Date_Referred_to_AA IS NULL
		--	and
		--		Post_Trial_Sent_AA IS NULL
		--	and
		--		dateadd(d,0,datediff(d,0,UWFinalDecisionDate)) <= QA_Decision_Date
		--	and
		--		QA_Decision_Date <= dateadd(d,0,datediff(d,0,LMOApproved_date)) 
		--	and
		--		InlineQA_Status = 'pass')
		--	THEN QA_Decision_Date
		--WHEN
		--		(Date_Referred_to_AA IS NULL
		--	and
		--		Post_Trial_Sent_AA IS NULL
		--	and
		--		UWFinalDecision = 'Declined'
		--	and
		--		UWFinalDecisionDate <= dateadd(d,0,datediff(d,0,LMOApproved_date)) 
		--	and
		--		ISNULL(InlineQA_Status, '') <> 'pass')
		--	THEN UWFinalDecisionDate
		
--THE CODE ABOVE CAUSES RECORDS TO GO AWAY

WHEN
				((Date_Referred_to_AA IS NOT NULL and Date_Referred_to_AA <= dateadd(m,-2,datediff(d,0,LMOApproved_date)))
				OR
				(Post_Trial_Sent_AA IS NOT NULL and Post_Trial_Sent_AA <= dateadd(m,-2,datediff(d,0,LMOApproved_date)))
			and
				UWFinalDecision = 'Declined')
			THEN
				dbo.HigherDate(ISNULL(Date_Referred_to_AA, Post_Trial_Sent_AA),UWFinalDecisionDate)
		Else LMOApproved_date
		End As AA_Start_Date,
	  Case
            When AA_Exclusion_name Is not null THEN 'E'
                  --AND t.AA_Exclusion_date > t.uwfinaldecisiondate THEN 'E'
            else 'P'
    End As Aging_Type
	  

from SD10_View (nolock)

where
    ISNULL(Decline_pool_name, '') <> 'BACLMWDEN_07112011D'
and 
	ISNULL(SecondRun_Ind, '') <> '%CIC%' 
and
	[Process_Step] like '%110%'
and
	[Process_step_date] > @startdate
and 
	LMOApproved_date is not null

) v

where
	v.AA_Start_Date > @startdate

Open in new window

JC_LivesAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
Hi. Sorry if this is a silly thought, but on lines 141-142, you have a filter:
where
      v.AA_Start_Date > @startdate

If the pieces of the CASE statement you filter out case the 2000 rows to have a date <= @startdate or NULL, then you will not see them.
0
 
JC_LivesAuthor Commented:
Oh of course!!! Thank you!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.