Data not pulling through

Question

select *
into [comp].[dbo].[Staff_log]
from (

select
year(date_created) as year,
comp_no as staff_number,
substring(comp_id,3,6) as comp_code,
0 as comp_rep_no

from
[tm].[dbo].[mort_Data]

where id = '98'
and typ = '1'
and year(date_created) > 2017

union

select
year(date_in) as year,
comp_number as staff_number,
comp_code as comp_code,
comp_rep_no as comp_rep_no
from
[tm].[dbo].[comp_data]

where year(date_in) > 2017

) a

This is the output I get running the logic above

However, this is the output that I was hoping to get but not sure why I don't, any idea why my logic isn't returning the value for Comp_rep_no when it should be as it meets all the criteria?

Answer 1

Hi @Bone_12 ,

Welcome to the microsoft TSQL Q&A forum!

Your question is related to TSQL. My colleague helped you add the TSQL tag.

I see that you have 0 as comp_rep_no in the first SELECT statement, so comp_rep_no will definitely return a value of 0.

When you insert the value into the [comp].[dbo].[Staff_log] table, the result you return is only one row. If you don't have a qualification, it will randomly return one row.

Maybe this is what you want:

    select top(1)*   
    from [comp].[dbo].[Staff_log]  
    order by comp_rep_no DESC  

If this does not solve your problem,please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)along with your expected result. So that we’ll get a right direction and make some test.

Regards
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

What do you get when you run this query?

select
year(date_in) as year,
comp_number as staff_number,
comp_code as comp_code,
comp_rep_no as comp_rep_no
from
[tm].[dbo].[comp_data]
where year(date_in) > 2017

How about this query?

select
year(date_created) as year,
comp_no as staff_number,
substring(comp_id,3,6) as comp_code,
0 as comp_rep_no

from
[tm].[dbo].[mort_Data]

where id = '98'
and typ = '1'
and year(date_created) > 2017

Your final result will be the combination of the results of these 2 queries.