How to create a 2nd temp table in SQL

Question

I have such a situation. I basically need to put my whole code into a second temp table(?) to be able to select from it. The code inside works just fine I just cannot understand how to basically create a bigger temp table that will absorb the first one and the previous select and later allow select one more time.

This is what I have right now

WITH t AS 
(       Select
        p.[PDITM#], l.SGDWHS, p.PDCHN, p.PDWEDT, Sum(p.PDQSLD) AS 'Sum', 
        case when p.PDCHN = 'DH' then p.PDQSLD else 0 end as DHSLD,
        case when p.PDCHN = 'WM' then p.PDQSLD else 0 end as WMSLD,
        case when p.PDCHN = 'MJ' then p.PDQSLD else 0 end as MJSLD,
        case when p.PDCHN = 'BJ' then p.PDQSLD else 0 end as BJSLD
        FROM  [Repit].[LEVYDTA].[POSDTLM] p
  LEFT OUTER JOIN [Repit].[LEVYDTA].[LDSSGEN] l 

  ON p.[PDAGY]=l.[SGAGY] AND p.[PDCHN] = l.[SGCHN] AND p.[PDSTR] = l.[SGSTR]


  WHERE p.[PDQSLD] > 0 AND p.[PDAGY] != 15
  AND l.[SGDWHS] IN (01, 21, 30, 40, 02) 

  AND CONVERT(DATE, CONVERT(CHAR(8), p.PDWEDT ))  = cast(DATEADD(dd, DATEPART(DW,GETDATE())*-1, GETDATE()) as date)

  GROUP BY p.[PDITM#], l.[SGDWHS], p.[PDCHN], p.[PDWEDT], p.[PDQSLD]

)
select  t.[PDITM#] as [PDITM#],
        t.SGDWHS as [SGDWHS],
        SUM(t.Sum) as [PDQSLD],
        SUM(t.DHSLD) AS [DHSLD],
        SUM(t.WMSLD) AS [WMSLD],
        SUM(t.MJSLD) AS [MJSLD],
        SUM(t.BJSLD) AS [BJSLD],
        SUM(t.Sum) - (SUM(t.DHSLD) + SUM(t.WMSLD) +  SUM(t.MJSLD) + SUM(t.BJSLD)) as OTHRSLD
            from t
GROUP BY t.PDITM#, t.SGDWHS 
ORDER BY t.PDITM#, t.SGDWHS

I need to select such fields from the upper table

SELECT t.PDITM#, t.SGDWHS, 
CAST((t.DHSLD/t.Sum) * 100 as DECIMAL (18,2)) AS 'DHPCT',
CAST((t.WMSLD/t.Sum) * 100 as DECIMAL (18,2)) AS 'WMPCT',
CAST((t.MJSLD/t.Sum) * 100 as DECIMAL (18,2)) AS 'MJPCT',
CAST((t.BJSLD/t.Sum) * 100 as DECIMAL (18,2)) AS 'BJPCT',
CAST(((t.Sum - ( t.DHSLD + t.WMSLD +  t.MJSLD + t.BJSLD))/t.Sum) * 100 as DECIMAL (18,2)) AS 'OTHRPCT'
FROM second temp table

I will appreciate if someone can let me know how it may be possible. I have spent time trying to figure it out by myself, but there is not much info when you need to make multiple temp tables and selecting them.

Answer 1

If you're using MSSQL, this might be what you're trying to do?

-- Drop temp table if already present
DROP TABLE IF EXISTS #temp_main

SELECT
    p.[PDITM#]
,   l.SGDWHS
,   p.PDCHN
,   p.PDWEDT
,   SUM(p.PDQSLD) AS [Sum_PDQSLD]
,   case when p.PDCHN = 'DH' then p.PDQSLD else 0 end as [DHSLD]
,   case when p.PDCHN = 'WM' then p.PDQSLD else 0 end as [WMSLD]
,   case when p.PDCHN = 'MJ' then p.PDQSLD else 0 end as [MJSLD]
,   case when p.PDCHN = 'BJ' then p.PDQSLD else 0 end as [BJSLD]

-- Select data into temp table.
INTO #temp_main

FROM [Repit].[LEVYDTA].[POSDTLM] AS p
LEFT JOIN [Repit].[LEVYDTA].[LDSSGEN] AS l 
ON p.[PDAGY]=l.[SGAGY] AND p.[PDCHN] = l.[SGCHN] AND p.[PDSTR] = l.[SGSTR]
WHERE p.[PDQSLD] > 0 AND p.[PDAGY] != 15 AND l.[SGDWHS] IN (01, 21, 30, 40, 02) 
AND CONVERT(DATE, CONVERT(CHAR(8), p.PDWEDT)) = cast(DATEADD(dd, DATEPART(DW,GETDATE())*-1, GETDATE()) as date)
GROUP BY 
    p.[PDITM#]
,   l.[SGDWHS]
,   p.[PDCHN]
,   p.[PDWEDT]
,   p.[PDQSLD]

-- Table 1
SELECT 
        t.[PDITM#]
    ,   t.SGDWHS
    ,   SUM(t.Sum_PDQSLD) as [PDQSLD]
    ,   SUM(t.DHSLD) AS [DHSLD]
    ,   SUM(t.WMSLD) AS [WMSLD]
    ,   SUM(t.MJSLD) AS [MJSLD]
    ,   SUM(t.BJSLD) AS [BJSLD]
    ,   SUM(t.Sum_PDQSLD) - (SUM(t.DHSLD) + SUM(t.WMSLD) +  SUM(t.MJSLD) + SUM(t.BJSLD)) as [OTHRSLD]
from #temp_main AS t -- Temp table
GROUP BY t.[PDITM#], t.SGDWHS
ORDER BY t.[PDITM#], t.SGDWHS

-- Table 2
SELECT 
        t.[PDITM#]
    ,   t.SGDWHS 
    ,   CAST((t.DHSLD/t2.Sum_PDQSLD) * 100 as DECIMAL (18,2)) AS [DHPCT]
    ,   CAST((t.WMSLD/t.Sum_PDQSLD) * 100 as DECIMAL (18,2)) AS [WMPCT]
    ,   CAST((t.MJSLD/t.Sum_PDQSLD) * 100 as DECIMAL (18,2)) AS [MJPCT]
    ,   CAST((t.BJSLD/t.Sum_PDQSLD) * 100 as DECIMAL (18,2)) AS [BJPCT]
    ,   CAST(((t.Sum_PDQSLD - (t.DHSLD + t.WMSLD +  t.MJSLD + t.BJSLD))/t.Sum_PDQSLD) * 100 as DECIMAL (18,2)) AS [OTHRPCT]
FROM #temp_main AS t -- Temp table
ORDER BY t.[PDITM#], t.SGDWHS

-- Drop table once complete.
DROP TABLE #table_main