Combine a variable table with single variable in T- SQL

Question

From my Claim table, I get the following,

select min([Time]) minimumtime, terminal_id     
from Claims
where claimflag = 2
group by terminal_id    

Output,

    minimumtime              terminal_id
------------------------------------------
2017-04-21 20:02:00.000          9
2017-04-21 20:01:00.000         10  

Now, I am trying to get those values into variables like this,

declare @claimmintime datetime,
        @term_id_val varchar(max)

select @claimmintime = min([Time]), @term_id_val= terminal_id       
from Claims
where claimflag = 2
group by terminal_id    

select  @claimmintime [claims_came_in], @term_id_val [terminal_id_came_in]

I get the following,

    claims_came_in             terminal_id_came_in
-----------------------------------------------------
2017-04-21 20:01:00.000          *

I need for both ID's like this,

    claims_came_in             terminal_id_came_in
-----------------------------------------------------
2017-04-21 20:02:00.000             9
2017-04-21 20:01:00.000             10

I saw a post, which is done using table, I tried it like this,

declare @term_id_val table(id int)

insert into  
      @term_id_val
select 
      distinct(terminal_id)

from  
     claims
where 
     claimflag = 2

select * from @term_id_val

Output,

id
-----
9
10

I can get the ids into the table. However, I need everything in the same query above where it gives * for ids. Now that I have a table of IDs how can I combine both table and the single variable returns min time in one query?

Answer 1

I don't see anything wrong with your variable assignment method. But for the table method, try adding one more column in table variable.

declare @term_id_val table(mintime datetime, id int)

insert into  
      @term_id_val
select min([Time]) minimumtime, terminal_id     
from Claims
where claimflag = 2
group by terminal_id  

select * from @term_id_val