JC_Lives
asked on
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!!
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER