In temporary tables/variables: join multiple rows to table with only 1 row

Hi, I have a small problem of joining a temporary variable with a temporary table. Any input would be much appreciated. I present the problem in the order in which I have tried to solve it.

First of, I have a temporary variable which is created from a select statement. The variable @enhet has 2 rows/observations (in my test file, later it will be many more):

declare @enhet varchar(50)

SELECT @enhet = 
     A.[EnhetsId]
FROM [StatistikinlamningDataSKL].[dbo].[StatusHistorik] A
inner join (
            select [EnhetsId], max(SenastUppdaterad) as SenastDatum
            from [StatistikinlamningDataSKL].[dbo].[StatusHistorik]
            group by [EnhetsId]
            ) B
on A.[EnhetsId] = B.[EnhetsId] and A.[SenastUppdaterad] = B.SenastDatum
WHERE [NyStatus] = 4

Secondly, I want to create a temporary table that combine these two observations (1 variable) with extra variables that are the same for both unique observations of variable @enhet. The aim is to have a table with unique values only for the variable @enhet but the rest is the same.

declare @temp2 table (
    EnhetsId varchar(50), 
    TjanstId Int, 
    Tabell varchar(50),
    Kommentar ntext,
    Uppdaterad datetime
)
insert into @temp2 (
    EnhetsId, TjanstId, Tabell, Kommentar, Uppdaterad) 
    values (
        @enhet, 1, 'GR_PS09_1', 'KLAR', getdate())

select * from @temp2

The problem is that when I run the script the output is only 1 row with just the last observation from the @enhet-variable.

Is there anyone who knows what to do? I have tried many things but since I am new to SQL I have failed to write the correct scripts. Can anyone point me in the right direction?

Thanks in advance and best regards! :)

Answers


Use a common table expression to hold the results of your first joined query:

declare @temp2 table (
   EnhetsId varchar(50), 
   TjanstId Int, 
   Tabell varchar(50),
   Kommentar ntext,
   Uppdaterad datetime
);

WITH ENHET_CTE AS 
(
    SELECT A.[EnhetsId]
    FROM [StatistikinlamningDataSKL].[dbo].[StatusHistorik] A
    inner join (
             select [EnhetsId], max(SenastUppdaterad) as SenastDatum
             from [StatistikinlamningDataSKL].[dbo].[StatusHistorik]
             group by [EnhetsId]
             ) B
    on A.[EnhetsId] = B.[EnhetsId] and A.[SenastUppdaterad] = B.SenastDatum
    WHERE [NyStatus] = 4
)

insert into @temp2 
    (EnhetsId, TjanstId, Tabell, Kommentar, Uppdaterad) 
SELECT 
    EnhetsId, 1, 'GR_PS09_1', 'KLAR', getdate() 
from ENHET_CTE;

select * from @temp2;

Need Your Help

NSTimer - Invalid operand to binary % error

iphone objective-c ios nstimer

I'm trying to update a UILabel with the amount of time left on an audio track in minutes and seconds. I'm getting an Invalid operand to binary % error . Here's the code:

How to handle an optional value returned by a query using the postgres crate?

postgresql rust

I'm trying to get a value for a query, but this value can be NULL and I don't know how to handle it in Rust. Here is my code:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.