How to create a 2nd temp table in SQL

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.


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