Repeorting issue
-
I have two tables like this
user1
USER_ID MAILBOX_TYPE
---------- ------------
1090 2
1092 2
1093 2
1094 2
1095 1
1097 2
1098 2
1099 2
1100 2
1101 2
1102 5
1090 1
1093 5
1111 3
1111 4
and another table subscriber that has the following for example
subscriber_id user_id
2 1090
3 1095
4 1093
5 1111
6 1099
The output of the report shoule be as follows:
subscriber_id user_id mailbox_type
2 1090 2
3 1095 1
4 1093 2
5 1111 3
The logic has to work as follws:
when there are multiple records for a user in user1 table, then if the mailbox_type has a value of 2 then that should be displayed eliminating the rest. if there are multiple records and the mailbox_type is not 2 then any one can be selected. When there are no multiple records then that singe record nedd to be displayed
Please help. This is an urgent issue. Any help here is greatly appreciated.
-
Create table #T1 ( [USER_ID] int, MAILBOX_TYPE smallint)
insert into #t1 ([User_ID], Mailbox_Type)
select 1090,2 union
select 1092,2 union
select 1093,2 union
select 1094,2 union
select 1095,1 union
select 1097,2 union
select 1098,2 union
select 1099,2 union
select 1100,2 union
select 1101,2 union
select 1102,5 union
select 1090,1 union
select 1093,5 union
select 1111,3 union
select 1111,4
create table #subscriber (subscriber_id smallint, [user_id] smallint)
insert into #subscriber
select 2,1090 union
select 3,1095 union
select 4,1093 union
select 5,1111 union
select 6,1099
Heres one solution.
select A.[user_id], subscriber_Id, Mailbox_Type
from (-- Select users with Type2
select [user_id], MailBox_Type
from #t1
where MailBox_Type = 2
union
-- Select the last mailbox type where user does not have a type 2
select [user_id], max(MailBox_Type)
from #t1
where [user_ID] not in (select [user_ID] from #t1 where MailBox_Type = 2)
group by [user_id]
) A
join #subscriber B on a.[user_id] = b.[user_id]
Results:
user_id,subscriber_Id,Mailbox_Type
1090, 2, 2
1093, 4, 2
1095, 3, 1
1099, 6, 2
1111, 5, 4
-
Another possible solution:
SELECT * ,
(SELECT TOP 1 MAILBOX_TYPE
FROM user1
WHER user1.USER_ID = subscriber.user_id
ORDER BY CASE MAILBOX_TYPE WHEN 2 THEN 0 ELSE 1 END)
FROM subscriber
-
There ya go, thats the way.
-
Thanks a lot Ray!! I really appreciate your help. That worked
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply