Combine subqueries without views

Question

I work with languages where I can assign intermediate outputs to a variable and then work the with variables to create a final output. I know SQL doesn't work this way as much. Currently I have queries that require me to make subsets of tables and then I want to join those subsets together. I can mimic the variable assignment I do in my native languages using a VIEW but I want to know how to do this using a single query (otherwise the database will get messy with views quickly).

Below is a MWE to make 2 initial tables DeleteMe1 and DeleteMe2 (at the end). Then I'd use these two views to get current snapshots of each table. Last I'd use LEFT JOIN with the views to merge the 2 data sets.

1. Is there a way to see the code SQL uses on the Join Snapshoted Views header code I supply below
2. How could I eliminate the views intermediate step and combine into a single SQL query?

Create views for current snapshot:

CREATE VIEW [dbo].[CurrentSnapshotDeleteMe1] 
AS
    SELECT DISTINCT * 
    FROM
        (SELECT 
             t.[Id]
             ,t.[OppId]
             ,t.[LastModifiedDate]
             ,t.[Stage]
         FROM 
             [dbo].DeleteMe1 as t
         INNER JOIN 
             (SELECT 
                  [OppId], MAX([LastModifiedDate]) AS MaxLastModifiedDate
              FROM 
                  [dbo].DeleteMe1
              WHERE 
                  LastModifiedDate <= GETDATE()
              GROUP BY 
                  [OppId]) AS referenceGroup ON t.[OppId] = referenceGroup.[OppId] 
                                             AND t.[LastModifiedDate] = referenceGroup.[MaxLastModifiedDate]) as BigGroup
GO

CREATE VIEW [dbo].[CurrentSnapshotDeleteMe2] 
AS
    SELECT DISTINCT * 
    FROM
        (SELECT 
             t.[Id]
             ,t.[OppId]
             ,t.[LastModifiedDate]
             ,t.[State]
         FROM 
             [dbo].DeleteMe2 AS t
    INNER JOIN (
      SELECT [OppId], MAX([LastModifiedDate]) AS MaxLastModifiedDate
        FROM [dbo].DeleteMe2
        WHERE LastModifiedDate <= GETDATE()
        GROUP BY [OppId]
    ) as referenceGroup

    ON t.[OppId] = referenceGroup.[OppId] AND t.[LastModifiedDate] = referenceGroup.[MaxLastModifiedDate]
     ) as BigGroup
GO

Join snapshoted views:

SELECT 
     dm1.[Id] as IdDM1
    ,dm1.[OppId] 
    ,dm1.[LastModifiedDate] as LastModifiedDateDM1
    ,dm1.[Stage]
    ,dm2.[Id] as IdDM2
    ,dm2.[LastModifiedDate] as LastModifiedDateDM2
    ,dm2.[State]
  FROM [dbo].[CurrentSnapshotDeleteMe1] as dm1
  LEFT JOIN [dbo].[CurrentSnapshotDeleteMe2] as dm2 ON dm1.OppId = dm2.OppId

Create original tables:

CREATE TABLE DeleteMe1 
(
    [Id] INT,
    [OppId] INT,
    [LastModifiedDate] DATE,
    [Stage] VARCHAR(250),
)

INSERT INTO DeleteMe1 
VALUES ('1', '1', '2019-04-01', 'A'),
       ('2', '1', '2019-05-01', 'E'),
       ('3', '1', '2019-06-01', 'B'),
       ('4', '2', '2019-07-01', 'A'),
       ('5', '2', '2019-08-01', 'B'),
       ('6', '3', '2019-09-01', 'C'),
       ('7', '4', '2019-10-01', 'B'),
       ('8', '4', '2019-11-01', 'C')

CREATE TABLE DeleteMe2 
(
    [Id] INT,
    [OppId] INT,
    [LastModifiedDate] DATE,
    [State] VARCHAR(250),
)

INSERT INTO DeleteMe2 
VALUES (' 1', '1', '2018-07-01', 'California'),
       (' 2', '1', '2017-11-01', 'Delaware'),
       (' 3', '4', '2017-12-01', 'California'),
       (' 4', '2', '2018-01-01', 'Alaska'),
       (' 5', '4', '2018-02-01', 'Delaware'),
       (' 6', '2', '2018-09-01', 'Delaware'),
       (' 7', '3', '2018-04-01', 'Alaska'),
       (' 8', '1', '2018-05-01', 'Hawaii'),
       (' 9', '4', '2018-06-01', 'California'),
       ('10', '1', '2018-07-01', 'Connecticut'),
       ('11', '2', '2018-08-01', 'Delaware'),
       ('12', '2', '2018-09-01', 'California')

Answer 1

I work with languages where I can assign intermediate outputs to a variable and then work the with variables to create a final output. I know SQL doesn't work this way as much.

Well, that's not true, sql does work this way, or at least sql-server does. You have temp tables and table variables.

Although you named your tables DeleteMe, from your statements it seems like it's the views you wish to treat as variables. So I'll focus on this.

Here's how to do it for your first view. It puts the results into a temporary table called #tempData1:

-- Optional: In case you re-run before you close your connection
if object_id('tempdb..#snapshot') is not null
    drop table #snapshot1;

select
distinct     t.Id, t.OppId, t.LastModifiedDate, t.Stage
into         #snapshot1
from         dbo.DeleteMe1 as t
inner join   (
                select   OppId, max(LastModifiedDate) AS MaxLastModifiedDate
                from     dbo.DeleteMe1
                where    LastModifiedDate <= getdate()
                group by OppId
             ) referenceGroup 
                 on t.OppId = referenceGroup.OppId 
                 and t.LastModifiedDate = referenceGroup.MaxLastModifiedDate;

The hashtag tells sql server that the table is to be stored temporarially. #tempTable1 will not survive when your connection closes.

Alternatively, you can create a table variable.

declare @snapshot1 table (
    id int, 
    oppId int,
    lastModifiedDate date,
    stage varchar(50)
);

insert @snapshot1 (id, oppId, lastModifiedDate, stage)
select distinct ...

This table is discarded as soon as the query has finished executing.

From there, you can join on your temp tables:

SELECT    dm1.[Id] as IdDM1, dm1.[OppId],
          dm1.[LastModifiedDate] as LastModifiedDateDM1, dm1.[Stage],
          dm2.[Id] as IdDM2, dm2.[LastModifiedDate] as LastModifiedDateDM2,
          dm2.[State]
FROM      #snapshot1 dm1
LEFT JOIN #snapshot2 dm2 ON dm1.OppId = dm2.OppId 

Or your table variables:

From there, you can join on your temp tables:

SELECT    dm1.[Id] as IdDM1, dm1.[OppId],
          dm1.[LastModifiedDate] as LastModifiedDateDM1, dm1.[Stage],
          dm2.[Id] as IdDM2, dm2.[LastModifiedDate] as LastModifiedDateDM2,
          dm2.[State]
FROM      @snapshot1 dm1
LEFT JOIN @snapshot2 dm2 ON dm1.OppId = dm2.OppId