Help with where logic - 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 2008 Forums

Transact-SQL (2008)

Help with where logic

Author

Topic

dssrun
Starting Member

18 Posts

Posted - 2012-09-18 : 13:52:06

I have a query below that is part of a larger query. This portion looks up Person_ID, Datest and Membership, while returning the permission level. Person_ID is a bigint, datest is date and membership is char(8). The focus is on membership because all Person's are in the tmp2 table, but not all memberships. We have a default membership value of '0' for all persons_ids that should be used when a person_ID is found, a date is found, but a membership_id is not found. The query below only returns records where there is a membership. The question is how to tell SQL when to default tmp1.membership to '0' so it can find the default match. I cannot perform an update on tmp1 or add all memberships to tmp2

select person_id, membership, datest, tmp2.Permission_Level
from tmp1 a
outer apply
(
select top(1)*
from tmp2
where tmp2.person_id = a.person_id
and tmp2.date = a.date
and tmp2.membership = case when a.membership like '%' + tmp2.membership + '%' the tmp2.membership
)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-18 : 14:18:13

Are all person_id's in tmp1? If that is true, then you should be able to do the following, assuming the membership in the outer query is coming from a column in the inner query.

select person_id, COALESCE(membership,0) AS membership, datest, tmp2.Permission_Level
from tmp1 a
outer apply
(
select top(1)*
from tmp2
where tmp2.person_id = a.person_id
and tmp2.date = a.date
and tmp2.membership = case when a.membership like '%' + tmp2.membership + '%' the tmp2.membership
)

But, if tmp1 does not have all the person_id's, then you would need to sort of reverse the logic - i.e., select from tmp2 in the outer query and outer apply a query on tmp1 in the inner query.

It would be good to qualify the columns in the select list so it is unambiguous and easy for someone to discern whether the columns are coming from the inner query or outer query.

dssrun
Starting Member

18 Posts

Posted - 2012-09-18 : 14:33:26

quote:
Originally posted by sunitabeck

Are all person_id's in tmp1?

tmp1 has all person_id's plus more. tmp2 only holds permission levels for certain person_id's with associated membership_id's

quote:

Originally posted by sunitabeck

select person_id, COALESCE(membership,0) AS membership, datest, tmp2.Permission_Level
from tmp1 a
outer apply
(
select top(1)*
from tmp2
where tmp2.person_id = a.person_id
and tmp2.date = a.date
and tmp2.membership = case when a.membership like '%' + tmp2.membership + '%' the tmp2.membership
)

This query would just make memberships default to 0 if they are null. Maybe this example can help clear a few things up: I have person_ID 123, datest of '01/01/2012' and membership of 01 in tmp1. Tmp 2 has 3 records for this person on date '01/01/2012' because person_ID 123 has membership 0, 04, 06. If i did this outer apply i would not get a permission level since there are no matches on membership 01 for person_ID 123 and datst of '01/01/2012'. However, instead I would want the 01 to join on 0 and return that permission_level. I would need the SQL to read the 01 (and any other memberships that follow this example) as 0. Hopefully that makes better sense.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-18 : 14:42:49

[code]
select person_id, membership, datest, tmp2.Permission_Level
from tmp1 a
outer apply
(
select top(1)*
from tmp2
where tmp2.person_id = a.person_id
and tmp2.date = a.date
and (tmp2.membership = a.membership or tmp2.membership=0)
order by case when tmp2.membership = a.membership then 0 else 1 end
)b
[/code]

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

dssrun
Starting Member

18 Posts

Posted - 2012-09-18 : 15:05:00

quote:

Originally posted by visakh16


select person_id, membership, datest, tmp2.Permission_Level
from tmp1 a
outer apply
(
select top(1)*
from tmp2
where tmp2.person_id = a.person_id
and tmp2.date = a.date
and (tmp2.membership = a.membership or tmp2.membership=0)
order by case when tmp2.membership = a.membership then 0 else 1 end
)b

thank you visakhm, however this is duplicating some records. For example Person_ID 123, date '01/01/2012', membership '04' is matching with Person_ID 123, date '01/01/2012', membership '04' and is matching with Person_ID 123, date '01/01/2012', membership '0'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-18 : 15:10:38

thats not true
so far as you've top 1 on select you should get only single matching record for a person for a date

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

dssrun
Starting Member

18 Posts

Posted - 2012-09-18 : 15:50:20

sorry i had a typo of top(10)...can I have order by datest desc before your order by clause? I am not sure what your order by clause is doing? Would you mind explaining? Thank you

dssrun
Starting Member

18 Posts

Posted - 2012-09-18 : 15:52:40

nevermind, i took a second look and it looks like you are putting the exact matches up top so those are chosen and the matches that default to 0 below so the problem i said earlier does not arise.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-18 : 15:53:06

whats the use of that? you're retrieving only matching data for a date isnt it? so no point in adding it again to order by as all date fields will have same value

or did you mean this?


select person_id, membership, datest, tmp2.Permission_Level
from tmp1 a
outer apply
(
select top(1)*
from tmp2
where tmp2.person_id = a.person_id
and tmp2.date = a.date
and (tmp2.membership = a.membership or tmp2.membership=0)
order by case when tmp2.membership = a.membership then 0 else 1 end
)b
order by datest desc

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

dssrun
Starting Member

18 Posts

Posted - 2012-09-18 : 16:00:03

how do i solve the issue when the membership is a csv string. E.g. the membership can have a value in tmp1 of '04,08' so i can't use the equal sign. That is why i used the like operator to begin with. I realized the value is not relational but that is how these values are given to me in the view.

If i change the equal sign to a like operator i do not get the results i want.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-18 : 16:02:57

you've add another apply logic to split them out

see scenario 4

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

and here's the string splitting udf which enables you to do this

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

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

dssrun
Starting Member

18 Posts

Posted - 2012-09-18 : 16:07:32

i was trying to stay away from UDFs. I would think the like operator would work and be more efficient, is that not true?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-18 : 16:13:01

it depends!
first tell if tmp1 membership has '04,08' what should be permission level it should return?
04's,08's or both?

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

dssrun
Starting Member

18 Posts

Posted - 2012-09-18 : 16:17:11

here is the view logic

if person_id 123 has membership 0, 04, 06 in tmp2, then only one of those can appear in the membership in tmp1. So you can never have Person_ID 123 and membership 04,06...it can only be 04 and then some other values OR 06 and some other values OR just 0. Does that make sense?

When i switch you equal signs to like operators on the left side of the OR operator and in the order by clause, i do not get the value or the csv string.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-18 : 16:23:25

sorry i didnt get you . so is 04 and 06 only set which cant be together? are all other combinations valid? for valid combinations what should be returned permission_level? putting a dataset and output to illustrate this would be much better rather than in words!

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

dssrun
Starting Member

18 Posts

Posted - 2012-09-18 : 16:27:08

i changed your SQL below, but it is says that person_ID 123 membership '04,10' in tmp1 is not like peson_id 123 and membership 04 in tmp2

and (tmp2.membership like '%' + a.membership + '%' or tmp2.membership=0)
order by case when tmp2.membership like '%' + a.membership + '%' then 0 else 1 end

dssrun
Starting Member

18 Posts

Posted - 2012-09-18 : 16:29:58

sorry..

Each person_id always has a membership of 0, 04, 06 and some have 08. All of these cannot be in the same membership csv string from tmp1. So if there is a 04,10, match it with 04. If there is a 10,14 match it with 0. There cannot be a membership of '04, 06'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-18 : 16:31:43

sorry i'm not going to suggest further anything unless you provided elaborate explanation on your scenario(s) by mean of proper sample dat and output. that would really save us lot of time and uncessary confusion!

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

dssrun
Starting Member

18 Posts

Posted - 2012-09-18 : 17:21:22

okay, sorry for my rushed responses. If i run a query on tmp2, which holds the person_ID, membership, datest and permission_level as

select membership from tmp2 group by membership

my results are '0','04','06','08' because these are the only ones that have permission levels. However, there are 30 or so other memberships in tmp1, but the permission levels are all based on '0'.

0, 04 and 06 can NEVER be together in tmp1 and '08' can never be together with '04' or' 06' in tmp2. Example:

Example 1:

tmp1
person_id membership
567 '04,08'

tmp2
person_id membership permission
567 0 0.56
567 08 1.3

result = permission_level of 1.3

Example 2:
tmp1
person_id membership
789 '04,08'

tmp2
person_id membership permission
789 0 1.23
789 04 1.34
789 06 1.40

result = 1.34

Example 3:
tmp1
person_id membership
999 '10,04'

tmp2
person_id membership permission
999 0 1.23
999 04 1.34
999 06 1.40

result = 1.34

Example 4:
tmp1
person_id membership
998 '10,12'

tmp2
person_id membership permission
998 0 1.23
998 04 1.34
998 06 1.40

result = 1.23

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-18 : 17:36:16

ok this really helps

i hope this will fit your requirement


select person_id, membership, datest, tmp2.Permission_Level
from tmp1 a
outer apply
(
select top(1)*
from tmp2
where tmp2.person_id = a.person_id
and tmp2.date = a.date
and (',' + a.membership + ',' like '%,' + tmp2.membership + ',%'  or tmp2.membership=0)
order by case when ',' + a.membership + ',' like '%,' + tmp2.membership + ',%' then 0 else 1 end
)b
order by datest desc

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

dssrun
Starting Member

18 Posts

Posted - 2012-09-18 : 17:51:12

i think that did it! i did a quick test for one to four memberships in tmp1 and it worked. You do not know how much time you have saved me, i never thought to put a case statement in the order by clause, which is a big driver in your query.

I can use this method for many other queries! Thank you so much, especially for staying with me throughout this post!

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources