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)
 Conditional selection in a view

Author  Topic 

jigsh
Starting Member

6 Posts
Posted - 2008-11-24 : 10:38:22
Friends,

I have one employee table , which has some set of data as follows.
ID............|Address...|City....|State|PrimaryFlag|TempFlag|
1234.........|1b xyz st.|Troy....|PA...|True.......|False...|
1234.........|127 ABC st|Yale....|PA...|False......|True....|
4567.........|451 PQR st|Columbia|MD...|False......|True....|
8901.........|12 Yale st|Yale....|PA...|True.......|False...|
8901.........|32 Yale st|Yale....|MD...|False......|True....|


I need to write a view which returns addresses of an employee for which PrimaryFlag is true. If Primary Flag is not true then check to see for that Id if TempFlag is true if so then return that employess.

As a conclusion I need following data from above sample data
ID............|Address...|City....|State|PrimaryFlag|TempFlag|
1234..........|1b xyz st.|Troy....|PA...|True.......|False...|
4567..........|451 PQR st|Columbia|MD...|False......|True....|
8901..........|12 Yale st|Yale....|PA...|True.......|Fals....|

How can I achieve this thing in a View

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-11-24 : 10:41:10
SELECT *
FROM YourTableNameHere
WHERE 1 IN (primaryflag, tempflag)


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-11-24 : 10:41:51
[code]
CREATE VIEW YourView
AS
SELECT ID,Address,City,State,PrimaryFlag,TempFlag
FROM employees
WHERE PrimaryFlag='True'
OR TempFlag='True'
[/code]
Go to Top of Page

jigsh
Starting Member

6 Posts
Posted - 2008-11-24 : 10:58:21
quote:
Originally posted by visakh16


CREATE VIEW YourView
AS
SELECT ID,Address,City,State,PrimaryFlag,TempFlag
FROM employees
WHERE PrimaryFlag='True'
OR TempFlag='True'


Hi Visakh16,

Thanks for your response, appreciate it.

As per you response, it will return two rows for id-1234 and id=8901. I need only one record for those IDs from view

Thanks
Jigsh
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-11-24 : 11:02:36
[code]
CREATE VIEW YourView
AS
SELECT ID,Address,City,State,PrimaryFlag,TempFlag
FROM employees
WHERE PrimaryFlag='True'
UNION ALL
SELECT ID,Address,City,State,PrimaryFlag,TempFlag
FROM employees t
WHERE TempFlag='True'
AND NOT EXISTS (SELECT *
FROM employees
WHERE PrimaryFlag='True'
AND ID=t.ID)
[/code]
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-11-24 : 11:03:55
[code]SELECT ID,
Address,
City,
State
FROM (
SELECT ID,
Address,
City,
State,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY PrimaryFlag DESC, TempFlag DESC) AS recID
FROM Employees
) AS d
WHERE recID = 1[/code]

E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-11-24 : 11:09:42
[code]
CREATE VIEW YourView
AS
SELECT ID,Address,City,State,PrimaryFlag,TempFlag
FROM employees e
OUTER APPLY (SELECT COUNT(*) AS cnt
FROM employees
WHERE ID=e.ID
AND PrimaryFlag='True') t
WHERE e.PrimaryFlag='True'
OR (ISNULL(t.cnt,0)=0 AND e.TempFlag='True')
[/code]
Go to Top of Page

jigsh
Starting Member

6 Posts
Posted - 2008-11-24 : 11:24:53
quote:
Originally posted by Peso

SELECT	ID,
	Address,
	City,
	State
FROM	(
		SELECT	ID,
			Address,
			City,
			State,
			ROW_NUMBER() OVER (PARTITION BY ID ORDER BY PrimaryFlag DESC, TempFlag DESC) AS recID
		FROM	Employees
	) AS d
WHERE	recID = 1


E 12°55'05.63"
N 56°04'39.26"

Thanks Peso,

This is awesome...it works great...

Thanks
Shah
Go to Top of Page

jigsh
Starting Member

6 Posts
Posted - 2008-11-24 : 11:25:42
quote:
Originally posted by visakh16


CREATE VIEW YourView
AS
SELECT ID,Address,City,State,PrimaryFlag,TempFlag
FROM employees e
OUTER APPLY (SELECT COUNT(*) AS cnt
             FROM employees
             WHERE ID=e.ID
             AND PrimaryFlag='True') t
WHERE e.PrimaryFlag='True'
OR (ISNULL(t.cnt,0)=0 AND e.TempFlag='True')



Thanks Vishakh16..it really works ..great..

Shah
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-11-24 : 11:30:18
Cheers
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site