SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Creating a view using case (of if) ?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Bazinga
Starting Member

19 Posts

Posted - 10/31/2012 :  16:49:21  Show Profile  Reply with Quote
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???

Edited by - Bazinga on 10/31/2012 16:53:25

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/31/2012 :  16:51:35  Show Profile  Reply with Quote
You need to do an UNPIVOT to get contents from fields onto a single field

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

Go to Top of Page

robvolk
Most Valuable Yak

USA
15663 Posts

Posted - 10/31/2012 :  17:02:37  Show Profile  Visit robvolk's Homepage  Reply with Quote
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()))
Go to Top of Page

Bazinga
Starting Member

19 Posts

Posted - 10/31/2012 :  17:09:11  Show Profile  Reply with Quote
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!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000