CODEWITHSUNDEEP
sqlsql-servert-sql
A DUBEY
A DUBEY

Reputation: 846

Package all the SQL create table statements into a single statement

I finished writing multiple SQL statements in order to generate my final table CCtbase2. What I want to do next is to package the entire SQL code into a single SQL statement avoiding creating multiple temporary tables. I just want to write my query in a way in which I will be creating my final table without creating multiple temporary tables but I am not sure how to go about it and I apologise for not providing sample of the tables.

DROP TABLE IF EXISTS temp_t1;

Select PID, CID ,CCID, CExpiry, PStatusID, PDate into temp_t1 
FROM  TABLE1 where PStatusID = 1001;

-- Applying filters

DROP TABLE IF EXISTS temp_t2;
SELECT * into temp_t2 
FROM TABLE2 where ADate < GETDATE() and PTID > 0 and  PTStatusID <> 812 ;

-- Fetching the latest record as per ADate based on PTID
DROP TABLE IF EXISTS temp_t3;
Select * into temp_t3 from (
    select *,  row_number() over(partition by PTID order by ADate desc) as rn
    from temp_t2
) t
where t.rn = 1 

DROP TABLE IF EXISTS temp_t4;
SELECT *,
       CASE WHEN TTID = 2301 and PTStatusID in (800,801) THEN PAmount
            ELSE 0 END AS OAmount
            into temp_t4 from temp_t3

DROP TABLE IF EXISTS temp_t5
Select PID, sum(OAmount) as Final_amt into temp_t5
from  temp_t4  group by PID; 

DROP TABLE IF EXISTS CCtbase1
Select * into CCtbase1  from temp_t1 where PID not in 
(Select distinct(PID) from temp_t5 where Final_amt = 0);

DROP TABLE IF EXISTS CCtbase2
Select a.*, b.EDate, c.EID into CCtbase2 from CCtbase1 a 
left join  TABLE3 b on a.CCID = b.CCID  
left join  TABLE4 c on a.CID = c.CID;

Upvotes: 0

Views: 108

Answers (2)

Zunayed Shahriar
Zunayed Shahriar

Reputation: 2743

As no sample data was provided, I've come up with this:

WITH T1_CTE as (
    SELECT PID, CID ,CCID, CExpiry, PStatusID, PDate
    FROM  TABLE1 where PStatusID = 1001
),
T2_CTE as (
    SELECT *, CASE WHEN TTID = 2301 AND PTStatusID IN (800,801) THEN PAmount ELSE 0 END AS OAmount
    FROM
    (
        SELECT *, row_number() OVER(PARTITION BY PTID ORDER BY ADate DESC) rowNum
        FROM TABLE2
        WHERE ADate < GETDATE() AND PTID > 0 AND  PTStatusID <> 812
    ) t
    WHERE t.rowNum = 1
),
T2_F_CTE as (
    SELECT PID, SUM(OAmount) as Final_amt
    FROM T2_CTE
    GROUP BY PID
)
SELECT a.*, b.EDate, c.EID
FROM T1_CTE a
LEFT JOIN TABLE3 b ON a.CCID = b.CCID
LEFT JOIN TABLE4 c ON a.CID = c.CID
WHERE PID NOT IN (SELECT PID from T2_F_CTE where Final_amt = 0)

This will prevent you from creating too much temporary tables.

Upvotes: 2

Ashutosh Ranjan
Ashutosh Ranjan

Reputation: 29

Can you try this solution

CREATE TABLE TABLE1(
    PID int, 
    CID int,
    CCID int, 
    CExpiry datetime, 
    PStatusID int, 
    PDate datetime
)

CREATE TABLE TABLE2(
    PID int, 
    ADate DATETIME,
    PTID int,
    PTStatusID int, 
    TTID int, 
    PAmount int
)

DROP TABLE IF EXISTS CCtbase1

;with TABLE2CTE(PID,OAmount) as  
(  
SELECT TOP 1 
    PID, CASE 
            WHEN TTID = 2301 AND PTStatusID in (800,801) THEN PAmount
            ELSE 0 
        END AS OAmount 
    FROM TABLE2 
    WHERE 
        ADate < GETDATE() 
        AND PTID > 0 
        AND TTID = 2301 
        AND PTStatusID in (800,801) 
    ORDER BY ADate DESC
)  

SELECT 
    PID, 
    CID, 
    CCID, 
    CExpiry, 
    PStatusID, 
    PDate 
    INTO CCtbase1 
    FROM TABLE1 
    WHERE 
        PID NOT IN 
        (Select distinct(PID) from TABLE2CTE where OAmount = 0)

Upvotes: 1

Related Questions