신한 InsideBankNew

WITH DateList AS (
    SELECT CONVERT(CHAR(8), DATEADD(DAY, number, '20250109'), 112) AS tr_date
    FROM master.dbo.spt_values
    WHERE type = 'P'
      AND DATEADD(DAY, number, '20250109') <= '20250115'
),
TrSum AS (
    SELECT tr_date, 
           SUM(tr_amt) AS tr_amt_p, 
           COUNT(tr_date) AS tr_p_cnt
    FROM acct_tr
    WHERE acct_seq = 39 
      AND tr_date BETWEEN '20250109' AND '20250115'
      AND tr_pl_gbn = 'P'
    GROUP BY tr_date
),
TrAdd AS (
    SELECT tr_date, 
           SUM(tr_amt) AS tr_amt_n, 
           COUNT(tr_date) AS tr_n_cnt
    FROM acct_tr_add
    WHERE acct_seq = 39 
      AND tr_date BETWEEN '20250109' AND '20250115'
      AND tr_pl_gbn = 'N'
    GROUP BY tr_date
),
LastAmt AS (
    SELECT tr_date, MAX(tr_amt) AS tr_last_amt
    FROM acct_tr
    WHERE acct_seq = 39 
      AND tr_date BETWEEN '20250109' AND '20250115'
    GROUP BY tr_date
)
SELECT d.tr_date,
       ISNULL(tr_amt_p, 0) AS tr_amt_p,
       ISNULL(tr_p_cnt, 0) AS tr_p_cnt,
       ISNULL(tr_amt_n, 0) AS tr_amt_n,
       ISNULL(tr_n_cnt, 0) AS tr_n_cnt,
       ISNULL(tr_last_amt, 0) AS tr_last_amt
FROM DateList d
LEFT JOIN TrSum p ON d.tr_date = p.tr_date
LEFT JOIN TrAdd n ON d.tr_date = n.tr_date
LEFT JOIN LastAmt l ON d.tr_date = l.tr_date
ORDER BY d.tr_date;