Need help with a Query - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Need help with a Query

Author

Topic

V-Nest
Starting Member

10 Posts

Posted - 2007-11-12 : 16:13:36

Hi all,

I'm trying to combine description and maturitydate filed together with some conditionals. I have the sample code below.


SELECT     dbo.Cases.CaseID, CASE dbo.Cases.CaseTypeID WHEN 5 THEN (dbo.Cases.Description + ' ' + CONVERT(varchar(10), 
                      dbo.Cases.MaturityDate, 120)) ELSE dbo.Cases.Description END AS Description, dbo.CaseCodes.CodeTypeID, dbo.Codes.CodeDescription, 
                      dbo.Cases.CaseTypeID
FROM         dbo.Cases INNER JOIN
                      dbo.CaseCodes ON dbo.Cases.CaseID = dbo.CaseCodes.CaseID INNER JOIN
                      dbo.Codes ON dbo.CaseCodes.CodeID = dbo.Codes.CodeID
WHERE     (dbo.Cases.DataSetID = 3) AND (dbo.CaseCodes.CodeTypeID = 20)

All I need to have this finish is that if maturitydate > 01/01/2000 and casetypeid = 5, combine the description and the maturitydate together.

Any help would be much appreciated.

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-12 : 16:31:33

like this?

SELECT dbo.Cases.CaseID, CASE WHEN dbo.Cases.CaseTypeID = 5 AND dbo.Cases.MaturityDate >= '2000-01-01' THEN (dbo.Cases.Description + ' ' + CONVERT(varchar(10),
dbo.Cases.MaturityDate, 120)) ELSE dbo.Cases.Description END AS Description, dbo.CaseCodes.CodeTypeID, dbo.Codes.CodeDescription,
dbo.Cases.CaseTypeID
FROM dbo.Cases INNER JOIN
dbo.CaseCodes ON dbo.Cases.CaseID = dbo.CaseCodes.CaseID INNER JOIN
dbo.Codes ON dbo.CaseCodes.CodeID = dbo.Codes.CodeID
WHERE (dbo.Cases.DataSetID = 3) AND (dbo.CaseCodes.CodeTypeID = 20)

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-11-12 : 16:32:42

Does this work?


SELECT 	
	dbo.Cases.CaseID, 
	CASE WHEN dbo.Cases.CaseTypeID = 5 AND maturitydate > '20000101' THEN (dbo.Cases.Description + ' ' + CONVERT(varchar(10), dbo.Cases.MaturityDate, 120)) 
			ELSE dbo.Cases.Description 
	END AS Description, 
	dbo.CaseCodes.CodeTypeID, 
	dbo.Codes.CodeDescription, 
	dbo.Cases.CaseTypeID
FROM dbo.Cases 
INNER JOIN dbo.CaseCodes ON dbo.Cases.CaseID = dbo.CaseCodes.CaseID 
INNER JOIN dbo.Codes ON dbo.CaseCodes.CodeID = dbo.Codes.CodeID
WHERE (dbo.Cases.DataSetID = 3) AND (dbo.CaseCodes.CodeTypeID = 20)

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

V-Nest
Starting Member

10 Posts

Posted - 2007-11-12 : 16:37:32

Thank you both for replying so quickly and you both were right. It worked like charm.

Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources