Want to Create Case with Another Case name

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

SQL Server 2005 Forums

Transact-SQL (2005)

Want to Create Case with Another Case name

Author

Topic

prodempsey
Starting Member

3 Posts

Posted - 2009-12-31 : 01:02:05

Can someone show me how to use one of my column names created by a Case Function in another Case Function?

For example I want to create another Case Function that say’s:

Case When [BkDischargeDateStepB55] is null then BK.BkrDischargeDate
Else When BK.BkrDischargeDate is null then [BkDischargeDateStepB55]
End ActualDischargeDate

I have been researching it, and I think I may need tocreate a Computed Column, but not sure how to do that.

Here is my code:

  
SELECT BK.LoanNumber, BK.BkrStatusCode, BK.BkrChapterType, BK.BkrRemovalCode, BK.BkrRemovalDescription, BK.BkrRemovalDate,

CASE When BKS.BksSetupCode = 'B20' Then BKS.BksActualCompletionDate 
End BKCaseClosedStepB20,

CASE When BKS.BksSetupCode = 'B55' Then BKS.BksActualCompletionDate 
End BkDischargeDateStepB55, BK.BkrDischargeDate,

CASE When BKS.BksSetupCode = 'B53' Then BKS.BksActualCompletionDate 
End BkDismissalDateStepB53, BK.BkrDismissalDate,

CASE When BKS.BksSetupCode = 'B52' Then BKS.BksActualCompletionDate 
End BkReliefDateStepB52, BK.BkrReliefGrantedDate,

CASE When BKS.BksSetupCode ='B84' Then BKS.BksActualCompletionDate 
End BkTrusteeAbandonmentDateStepB84, BK.BkrTrusteeAbandonmentDate,

CASE When BKS.BksSetupCode ='B30' Then BKS.BksActualCompletionDate 
End BkRemovalDateStepB30,

CASE When BK.BkrRemovalCode IN('05','21','22','23') Then 'Case Closed'
When BK.BkrRemovalCode IN('02','20') Then 'Trustee Abandonment' 
When BK.BkrRemovalCode IN('01' ,'06' ,'17' ,'18' ,'19') Then 'Relief Granted'
When BK.BkrRemovalCode IN('03','11' ,'12' ,'13') Then 'Dismissal'
When BK.BkrRemovalCode IN('04','14','15','16') Then 'Discharge'
End ActualRemovalReason 


FROM dbo.tblssbankruptcy BK
LEFT OUTER JOIN dbo.tblSSBankruptcyStep BKS
ON BK.LoanNumber = BKS.LoanNumber 

WHERE BK.BkrRemovalDate is not null

ORDER BY BK.LoanNumber

Thanks,
Jeremy

Sachin.Nand

2937 Posts

Posted - 2009-12-31 : 01:59:47

[code]
Case When [BkDischargeDateStepB55] is null then BK.BkrDischargeDate
When BK.BkrDischargeDate is null then [BkDischargeDateStepB55]
End ActualDischargeDate
[/code]

PBUH

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-31 : 09:11:24

That case can not be created in the same query though correct? Like you could not just add that as another column unless that table was created previously?

prodempsey
Starting Member

3 Posts

Posted - 2009-12-31 : 11:08:10

Idera, I tried that but I'm getting an invalid column name on BkDischargeDateStepB55

Thanks,
Jeremy

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-31 : 11:36:21

thats what I was thinking, if you need to do a case on a case you have to embed them.

Case
When (CASE When BKS.BksSetupCode = 'B55' Then BKS.BksActualCompletionDate
End ) is NULL then BK.BkrDischargeDate
When BK.BkrDischargeDate is null then (CASE When BKS.BksSetupCode = 'B55' Then BKS.BksActualCompletionDate
End )
End as CaseinCase

jcampbell
Starting Member

9 Posts

Posted - 2009-12-31 : 12:09:34

The short answer is you can not reference a column in a select that you have not created yet, however, you have two options
1. use the logic from the first case statement in the second Case statement(You can also nest case statements)

Case When (BKS.BksActualCompletionDate is null or BksSetupCode != 55) then use BK.BkrDischargeDate
else When BK.BkrDischargeDate is null then (CASE When BKS.BksSetupCode = 'B55' Then BKS.BksActualCompletionDate
End) End ActualDischargeDate

2. use can use an inline view to reference columns you just created like this

Select caseid,ccount from
(Select caseid, count(*) as ccount from cases group by caseid) TCases

Thats the answer, however, I think you may have other logic issues with your query.

This case statement,
CASE When BKS.BksSetupCode = 'B55' Then BKS.BksActualCompletionDate
End BkDischargeDateStepB55

will create a null value for BkDischargeDateStepB55 under two conditions

1. BksSetupCode is 55 and BKS.BksActualCompletionDate is null
2. BksSetupCode is not 55(regardless of BKS.BksActualCompletionDate)

Is this what you expect?

hope this helps
jc

prodempsey
Starting Member

3 Posts

Posted - 2009-12-31 : 14:27:20

DP978 and jcambell, this gave me exactly what I needed. Thank you so much!!

Thanks,
Jeremy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources