Display Multiple Fields in Case

Question

Display Multiple Fields in Case

sharonsql2013

SSCertifiable

Points: 5908
More actions
September 27, 2013 at 11:23 am

#283781

How can I display multiple fields in Case statement.
If I use a "," , it gives an error...
Declare @DateInput as Date = '2012-06-10'
Select
Case when @DateInput between '2012-06-06' and '2013-06-10'
then
AnniversaryStartDate
,AnniversaryEndDate
Else
Null
End
from dbo.AnniversaryData
where DateInput = @DateInput
Error Incorrect syntax near ','.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

You can't. You need to repeat the CASE statement for every column needed or change the logic.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

sharonsql2013 SSCertifiable Points: 5908 More actions · Answer 2

Declare @DateInput as Date

Select

Case when @DateInput between '2012-06-06'and '2013-06-10'

Then

AnniversaryStartDate

when @DateInput between '2012-06-06'and '2013-06-10'

Then

AnniversaryEndDate

Else

Null

End as AnniversaryStartDate,AnniversaryEndDate

from dbo.AnniversaryData

where DateInput = @DateInput

This resolves the issue of multiple cases, but now get an error saying Invalid Column. I do not have a columnname dateInput in my Anniversary Data table. But I want to accept a date parameter and if it falls between the start and end anniversary dates , display the details. Any advise?

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

I don't understand completely your problem. Please read the article linked on my signature to guide you on your posts and get better help. If you give me DDL, some sample data and expected results based on the sample data, I can give you more help.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

sharonsql2013 SSCertifiable Points: 5908 More actions · Answer 4

Here is the script:

CREATE TABLE [dbo].[AnniversaryData](

[AnniversaryNumber] [varchar](10) NOT NULL,

[ARAmt] [decimal](24, 2) NULL,

[AnniversaryStartDate] [date] NULL,

[AnniversaryEndDate] [date] NULL,

[InvoiceDueDate] [date] NULL)

CONSTRAINT [AnniversaryData] PRIMARY KEY CLUSTERED

(

[AnniversaryNumber] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

Output must be : If I pass 10-10-2012 as input then it should check between Anniversarystartdate and

AnniversaryEndDate. If it falls in that then it should display that else Null

LinksUp SSCertifiable Points: 6526 More actions · Answer 5

sharonsql2013 (9/27/2013)
Output must be : If I pass 10-10-2012 as input then it should check between Anniversarystartdate and
AnniversaryEndDate. If it falls in that then it should display that else Null

I'm taking a shot in the dark here. But is this what you are looking for?

declare @DateInput Date = '2012-10-10'

select anniversaryNumber,

case when @DateInput between AnniversaryStartDate and AnniversaryEndDate then AnniversaryStartDate else null end StartDate,

case when @DateInput between AnniversaryStartDate and AnniversaryEndDate then AnniversaryEndDate else null end EndDate

from AnniversaryData

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/