MegoSoft MegoSoft - 1 year ago 59
SQL Question

SQL Server : connect two results with specific row

I have a table of users and a table of their contracts. I have two selects. The first one, which selects all from users table and second one, which selects only new users (from another user table) for "today". I need to connect those two results, but there's a problem.

Each user had "rownumber" column, it depends by count of contracts for each user (each user can have 1 or more contracts). I need to get new rownumber for new row. See example

The first select returns:

user_id | contract_id | rownumber
1 456 1
1 457 2
1 699 3

The second select selects new rows for those users:

user_id | contract_id
1 1024
1 | 1079

And I need to connect those results to get this end result:

user_id | contract_id | rownumber
1 456 1
1 457 2
1 699 3
1 1024 4
1 1079 5

How can I do that?

Answer Source
;with cte
user_id,contractid from table1
union all
user_id,contractid from table2
select *,row_number() over (partition by user_id order by contractid ) from cte
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download