Help in filtering - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help in filtering

Author

Topic

shilpash
Posting Yak Master

103 Posts

Posted - 2012-04-29 : 13:50:53

SELECT [Code] = CASE WHEN Detail.Code IN (1,2,3) THEN Detail.Code
WHEN Detail.Code IN (6) AND DATEDIFF(DAY,Loan.NextDueDate,Loan.ASOfDate) >= 15
THEN Detail.Code
WHEN Loan.Optn = 'A' THEN '1'
WHEN NULLIF(Loan.CompletionDate,'01-01-1900') IS NOT NULL THEN '2'
WHEN Loan.rank = 5
AND Loan.Optn <> 'A'
ANDLoan.CCode <> 6 THEN '3'
WHEN Loan.BCC > 0 THEN '90'
ELSE '0'
END
FROM Loan

LEFT OUTER JOIN Detail
ON LOAN.LoanNumber = Detail.LoanNumber
AND LOAN.AsOfDate =Detail.AsofDate

In the above query I need a condition where detail code in 6 then datediff has to equal to or greater thatn 15.But this query gives me where datediff<15,then code will be 0.I need to exclude those loannumber where datediff is less than 15 wherever code is 6.

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-29 : 14:39:34

[code]SELECT CASE
WHEN Detail.Code IN (1, 2, 3) THEN Detail.Code
WHEN Detail.Code IN (6) AND DATEDIFF(DAY, Loan.NextDueDate, Loan.ASOfDate) >= 15 THEN Detail.Code
WHEN Detail.Code IN (6) AND DATEDIFF(DAY, Loan.NextDueDate, Loan.ASOfDate) < 15 THEN '???'
WHEN Loan.Optn = 'A' THEN '1'
WHEN Loan.CompletionDate > '19000101' THEN '2'
WHEN Loan.[Rank] = 5 AND Loan.Optn <> 'A' AND Loan.CCode <> 6 THEN '3'
WHEN Loan.BCC > 0 THEN '90'
ELSE '0'
END AS [Code]
FROM dbo.Loan
LEFT JOIN dbo.Detail ON LOAN.LoanNumber = Detail.LoanNumber
AND LOAN.AsOfDate =Detail.AsofDate [/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources