Join temp table with existing query

Question

I have 2 queries in SSMS which are executing fine. I need to join them together, and I'm not sure how to (I'm a beginner at T-SQL).

The first script is pulling data from a database and transforming some of the columns to create my fact table:

SELECT 'Policy' As PolicyType,  
PolicyKey = [POLICY_NUMBER] + ' ' + CONVERT(VARCHAR(11), [TRNS_MSG_TS], 120),  
"BATCH_ID",   
"HISTORY_ID",   
"POLICY_NUMBER",  
"TRANSACTION_TYPE_CODE",  
"POLICY_STATUS",  
"COVER_WANTED",  
"PACKAGE_TYPE",  
"MIGRATION_INDICATOR",  
"REFERRER_PREFIX",  
"PROCESSOR_PREFIX",  
"INTRODUCER_CHANNEL",  
"INTRODUCER_SUBCHANNEL",  
CAST("POLICY_VERSION_EFFECTIVE_START_DATE" AS DATE) As StartDate,  
CAST("POLICY_EXPIRY_DATE" AS DATE) As PQExpiryDate,  
CAST("CANCELLATION_DATE" AS DATE) As CancellationDate,  
"FINALISED_PREMIUM_AMOUNT",  
"ANNUAL_PREMIUM",  
"TRANSACTIONAL_PREMIUM",  
CAST("TRNS_MSG_TS" AS DATE) As TRNSDate,  
CAST(LOADDATE AS DATE) As LoadDate,  
OFFSET_IND,  
FIX_IND  

FROM Database.dbo."View1"  
WHERE "POLICY_VERSION_EFFECTIVE_START_DATE" >= DATEADD(DAY,-1*DAY(GETDATE())+0,         DATEADD(MONTH,-12,GETDATE()))  
AND OFFSET_IND = 0  
AND FIX_IND = 0  
AND LOADDATE <= CAST(GETDATE() AS DATE)  

The second script is creating a temp table which I want to use as a mapping table:

SELECT DISTINCT "POLICY_NUMBER",  
'Policy' as PolicyType,  
CAST(MIN("TRNS_MSG_TS") As Date) As MinDate  

into #FirstTransFlagMap  
FROM Database.dbo."View1"  
Group by "POLICY_NUMBER";  

SELECT PolicyKey = ["POLICY_NUMBER"] + ' ' + CONVERT(VARCHAR(11),[MinDate], 120),  
'1' as FirstTransFlag  
FROM #FirstTransFlagMap;  

What I'm trying to do is join these two scripts together so that the fact table outputs all of the aforementioned columns, along with the FirstTransFlag column where the values are 1 (if the PolicyKeys match) and 0 (if they don't)

Can anyone help me join these two scripts together to get the desired result?

I just can't work out how to format my syntax to join these scripts. First time trying to work with temp tables/fields, and first time trying to join tables together in SSMS

Answer 1

Well, since your temp table is already created you can use it as is. For the first query, you can use CTE (common table expression).

So you would have something like

WITH FirstQueryTabularDataCTE (all properties that you need)
AS
(
   FirstQuery
)
SELECT f.Property1 from #FirstTransFlagMap AS f
JOIN FirstQueryTabularDataCTE AS CTE
ON f.Id = CTE.Id

Obviously, pseudo code, but should steer you in the right direction.