Creating a view using case (of if) ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Creating a view using case (of if) ?

Author

Topic

Bazinga
Starting Member

19 Posts

Posted - 2012-10-31 : 16:49:21

I am trying to create a view from an address table where the table contains the following.

addrkey, addrid, addrname, addrjan, addrfeb, addrmar, addrapr, etc, etc

The fields for addrjan, feb, mar, etc contain 'Y' or 'N', indicating that the address record is good to use in those months. I am trying to create a view based on this table that will only select the records for when the current month contains a 'Y'. For example, the current month is October so the view should select all records where addroct = 'Y'.

I am trying for something like:
DECLARE @Month CHAR(20);
SET @Month = DATENAME(mm,GETDATE());

IF @Month = 'October' THEN
SELECT * FROM addresstable WHERE addroct = 'Y'
ELSE IF @Month = 'November' THEN
SELECT * FROM addresstable WHERE addrnov = 'Y'
etc
etc

I can generate the current month via a formula like DATENAME(mm,GETDATE())

BUT I am not sure how to select only the records that match that, because a different field is used for each month in the address table???

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-31 : 16:51:35

You need to do an UNPIVOT to get contents from fields onto a single field

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-10-31 : 17:02:37

[code]SELECT * FROM myAddressTable
WHERE CASE DATENAME(month,GETDATE())
WHEN 'January' THEN addrjan
WHEN 'February' THEN addrfeb
WHEN 'March' THEN addrmar
...
WHEN 'December' THEN addrdec END = 'Y'[/code]The better option is not to store addresses with that structure, but something like this instead:[code]CREATE TABLE ValidAddressMonths(addrkey int not null,
Month tinyint not null,
CONSTRAINT PK_ValidAddressMonths PRIMARY KEY(addrkey,Month))[/code]You'd then insert the addrkey and month number only for those months where the address is valid (Y). A simple EXISTS query will find the rows:[code]SELECT * FROM myAddressTable A
WHERE EXISTS(SELECT * FROM ValidAddressMonths WHERE addrkey=A.addrkey and Month=DATEPART(month,GETDATE()))[/code]

Bazinga
Starting Member

19 Posts

Posted - 2012-10-31 : 17:09:11

quote:
Originally posted by robvolk

SELECT * FROM myAddressTable
WHERE CASE DATENAME(month,GETDATE())
WHEN 'January' THEN addrjan
WHEN 'February' THEN addrfeb
WHEN 'March' THEN addrmar
...
WHEN 'December' THEN addrdec END = 'Y'
The better option is not to store addresses with that structure, but something like this instead:
CREATE TABLE ValidAddressMonths(addrkey int not null,
Month tinyint not null,
CONSTRAINT PK_ValidAddressMonths PRIMARY KEY(addrkey,Month))
You'd then insert the addrkey and month number only for those months where the address is valid (Y). A simple EXISTS query will find the rows:
SELECT * FROM myAddressTable A
WHERE EXISTS(SELECT * FROM ValidAddressMonths WHERE addrkey=A.addrkey and Month=DATEPART(month,GETDATE()))

Thanks Robvolk. That worked perfectly!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources