Generate duplicate/missing records for different field combination

Question

Hi,
I want to generate missing records for combination ACCNT, CRD, TRANS_DATE.
Below is input data, there are two tables, CRD_BCKT stores what should be combinations records for that CRD, @TABLE actual data table

DECLARE @CRD_BCKT TABLE  
(  
 CRD INT,  
 BCKT VARCHAR(8)  
)  
INSERT INTO @CRD_BCKT  
SELECT 1,'PRI'  
UNION ALL  
SELECT 1,'INT'  
UNION ALL  
SELECT 1,'FEE'  
UNION ALL  
SELECT 2,'PRI'  
UNION ALL  
SELECT 2,'FEE'  
  
DECLARE @TABLE TABLE  
(  
 ACCNT VARCHAR(10),  
 CRD INT,  
 TRANS_DATE DATE,  
 BCKT VARCHAR(8),  
 AMOUNT DECIMAL(18,2)  
)  
INSERT INTO @TABLE  
SELECT 'A1',1,'2022-01-01','PRI',23.23  
UNION ALL  
SELECT 'A1',1,'2022-01-01','INT',23.23  
UNION ALL  
SELECT 'A1',1,'2022-01-02','INT',23.23  
UNION ALL  
SELECT 'A2',1,'2022-01-03','PRI',23.24  
UNION ALL  
SELECT 'A2',2,'2022-01-03','FEE',23.44  

For ACCNT = A1, CRD = 1, TRANS_DATE = '2022-01-01' based on the CRD_BCKT table there should be three records for PRI, INT and FEE but there are only two second record for FEE should be added
For ACCNT = A1, CRD = 1, TRANS_DATE = '2022-01-02' based on the CRD_BCKT table there should be three records for PRI, INT and FEE but there is only one, two records for PRI and FEE should be added
For ACCNT = A2, CRD = 1, TRANS_DATE = '2022-01-03' based on the CRD_BCKT table there should be three records for PRI, INT and FEE but there is only one, two records for INT and FEE should be added
For ACCNT = A2, CRD = 2, TRANS_DATE = '2022-01-03' based on the CRD_BCKT table there should be two records for PRI and FEE but there is only one, one record for PRI should be added

Below is expected output:

--EXPECTED OUTPUT  
SELECT *FROM @TABLE  
--ADDING MISSING RECORDS  
UNION ALL  
SELECT 'A1',1,'2022-01-01','FEE',0  
UNION ALL  
SELECT 'A1',1,'2022-01-02','PRI',0  
UNION ALL  
SELECT 'A1',1,'2022-01-02','FEE',0  
UNION ALL  
SELECT 'A2',1,'2022-01-03','PRI',0  
UNION ALL  
SELECT 'A2',1,'2022-01-03','FEE',0  
UNION ALL  
SELECT 'A2',2,'2022-01-03','PRI',0  

Appreciate your inputs.
Thanks,
Eshwar.

Answer 1

Check a query:

select ACCNT, CRD, BCKT, TRANS_DATE, AMOUNT
from @TABLE
union all
(select t.ACCNT, c.CRD, c.BCKT, t.TRANS_DATE, 0
from @CRD_BCKT c, @TABLE t where c.CRD = t.CRD
except
select ACCNT, CRD, BCKT, TRANS_DATE, 0
from @TABLE)
order by ACCNT, CRD, TRANS_DATE, BCKT