Combining 2 temp queries into 1

Question

I have tried combining 2 queries into 1. However, I ran into some problems:

exec(@sql + @sql2 + @sql3)
exec(@sql4 + @sql5 + @sql6)

Executing the above, I have the following results:

However, since I would like to combine the 2 queries into 1 using date, I wrote a few more lines as followed:

--Join the 2 queries together for comparison

select a.stime as Date,
       a.bid as BidA,
       a.ask as AskA,
       b.bid as BidB,
       b.ask as AskB,
       ABS(ISNULL(a.bid, 0) - ISNULL(b.bid, 0)) as BidDiff,
       ABS(ISNULL(a.ask, 0) - ISNULL(b.ask, 0)) as AskDiff
from (exec(@sql + @sql2 + @sql3)) a left outer join (exec(@sql4 + @sql5 + @sql6)) b where a.stime = b.stime
Order by date DESC;

However, I kept getting an incorrect syntax error nearby exec.

Answer 1

Create 2 table variables (not sure what your data types are):

DECLARE @tableA AS TABLE([stime] AS datetime, [Bid] AS blah, [Ask] AS blah)
DECLARE @tableB AS TABLE([stime] AS datetime, [Bid] AS blah, [Ask] AS blah)

INSERT INTO @tableA
EXEC (@sql + @sql2 + @sql3)

INSERT INTO @tableB
EXEC (@sql4 + @sql5 + @sql6)

SELECT
    [A].[stime] AS [Date]
,   [A].[Bid] AS [BidA]
,   [A].[Ask] AS [AskA]
,   [B].[Bid] AS [BidB]
,   [B].[Ask] AS [AskB]
,   ABS(ISNULL([A].[Bid], 0) - ISNULL([B].[Bid], 0)) AS [BidDiff]
,   ABS(ISNULL([A].[Ask], 0) - ISNULL([B].[Ask], 0)) AS [AskDiff]
FROM @TableA AS [A]
LEFT JOIN @TableB AS [B]
    ON [A].[stime] = [B].[stime]
ORDER BY [A].[stime] DESC