Case in Where condition - 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)

Case in Where condition

Author

Topic

SCHEMA
Posting Yak Master

192 Posts

Posted - 2009-05-22 : 10:20:52

I have a table A

ID FirstName LastName
1 S A
2 D P

Table B

ID Roletype HoursWorked
1 1 20
1 2 30
2 1 40

Table C (Lookuprole)
Lookupcode Description
1 D
1 E
2 D

I want to get Firstname and lastname for matching ID.

I need:

I want to first get Lookupcode = 2 and if it is null then I want to get Lookupcode =1

SCHEMA
Posting Yak Master

192 Posts

Posted - 2009-05-22 : 11:27:14

Anyone?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-22 : 11:31:30

don't quite understand what you need here .. .. can explain more in details ?

what is Table B for ?

quote:
I need:
I want to first get Lookupcode = 2 and if it is null then I want to get Lookupcode =1

You want to get lookupcode = 2 from what ? what is your condition ? rules ?

What is your expected result ?

KH
[spoiler]Time is always against us[/spoiler]

SCHEMA
Posting Yak Master

192 Posts

Posted - 2009-05-22 : 11:34:27

ID Code Name Roletype
59474 H- 00090 Bachman 2
59474 H- 00090 Arshadi 1
59490 H- 00083 Ken G 1
59540 H- 00103 John 1
59642 H- 00060 Michelle 1
59664 H- 00071 Milton 1
59664 H- 00071 Nancy C 2

I want to everything for RoleType = 2 if there is entry for roletype = 1,2

Else I want to get data where roletype = 1

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-22 : 11:37:07

what you have just posted is the result that you want ? Can also post the corresponding sample data that will generate these result ?

KH
[spoiler]Time is always against us[/spoiler]

SCHEMA
Posting Yak Master

192 Posts

Posted - 2009-05-22 : 11:39:30

59474 H- 00090 Bachman 2
59490 H- 00083 Ken G 1
59540 H- 00103 John 1
59642 H- 00060 Michelle 1
59664 H- 00071 Nancy C 2

This is result I want from above sample table

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-22 : 11:44:42

[code]
DECLARE @sample TABLE
(
ID int,
Code varchar(10),
Name varchar(10),
Roletype int
)
INSERT INTO @sample
SELECT 59474, 'H- 00090', 'Bachman', 2 UNION ALL
SELECT 59474, 'H- 00090', 'Arshadi', 1 UNION ALL
SELECT 59490, 'H- 00083', 'Ken G', 1 UNION ALL
SELECT 59540, 'H- 00103', 'John', 1 UNION ALL
SELECT 59642, 'H- 00060', 'Michelle', 1 UNION ALL
SELECT 59664, 'H- 00071', 'Milton', 1 UNION ALL
SELECT 59664, 'H- 00071', 'Nancy C', 2

SELECT [ID], Code, [Name], Roletype
FROM
(
SELECT *,
row_no = row_number() OVER (PARTITION BY ID ORDER BY Roletype DESC)
FROM @sample
) s
WHERE row_no = 1

/*
ID Code Name Roletype
----------- ---------- ---------- -----------
59474 H- 00090 Bachman 2
59490 H- 00083 Ken G 1
59540 H- 00103 John 1
59642 H- 00060 Michelle 1
59664 H- 00071 Nancy C 2

(5 row(s) affected)
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources