sql - SQL - 此查询需要帮助 - 表 A 和表 B 连接两个表并且不会丢失数据计数

Question

---Table A:    
 CREATE TABLE TABLE_A (
           BadgeNum varchar(10),     --This is a persons unique number.
        Gender varchar(2),            ---Gender 'F' or 'M'
        Date_Sent date,              --- Date of Questionnaire
        Type_Status varchar(3),       --- A person can be Single or Married 
        Living_State  varchar(2),     --Person's living state
        S_Type_Type int ,   -- Value can be 1 or 0 
        Recipient_num int,             --Key to create grouping to put each person in category. Using Case Statement for this 'Billing_Transaction' or 'Online_Transaction' 
        MONTH,
        YEAR
        );

-- Table B:
    CREATE TABLE TABLE_B
(

    BadgeNumber varchar,       --- This is a persons unique number
    RespondedYear smalldate,    --- Year a person responded
    RespondedMonth smalldate,   --- Month a person responded
    Date_Process                -- This is in YYMMWW (Year,Month,Week)
    Value money,     --- Cost of the purchase
    State           --- Person's resides
    
    )


----Sample Data for TABLE_A---  
INSERT INTO TABLE_A
VALUES  ('11E2', 'F',  '07/20/2020',  'Single', 'NV' , '1' , '00001', '07', '2020');
VALUES  ('11E3', 'M',  '06/30/2020', 'Married', 'AZ' , '1' , '00001', '06', '2020');
VALUES  ('11E4', 'F',  '05/22/2019',  'Single', 'TN',  '1' , '00001', '05', '2019'); 
VALUES  ('11E5', 'M',  '05/30/2018', 'Married', 'NY' , '1' , '00001', '05', '2018' ); 
VALUES  ('11E6', 'F',  '03/25/2017',  'Single', 'CA' , '1' , '00001', '03', '2017');
VALUES  ('11E7', 'M',  '02/27/2017', 'Married', 'VT' , '1' , '00002', '02', '2017');
VALUES  ('11E8', 'F',  '03/01/2018',  'Single', 'AL',  '1' , '00002', '03', '2018');
VALUES  ('11E2', 'F',  '07/20/2020',  'Single', 'NV' , '1' , '00001', '07', '2020');
VALUES  ('11E3', 'M',  '06/30/2020', 'Married', 'AZ' , '1' , '00001', '06', '2020');
VALUES  ('11E4', 'F',  '05/22/2019',  'Single', 'TN',  '1' , '00001', '05', '2019'); 
VALUES  ('11E5', 'M',  '05/30/2018', 'Married', 'NY' , '1' , '00001', '05', '2018' ); 
VALUES  ('11E6', 'F',  '03/25/2017',  'Single', 'CA' , '1' , '00001', '03', '2017');
VALUES  ('11E7', 'M',  '02/27/2017', 'Married', 'VT' , '1' , '00002', '02', '2017');
VALUES  ('11E8', 'F',  '03/01/2018',  'Single', 'AL',  '1' , '00002', '03', '2018');



---SampleDate for TABLE_B       

INSERT INTO TABLE_B
VALUES  ('11E3', '2020',  '7',  '200208', '200', 'AZ' );
VALUES  ('11E2', '2018',  '5',  '180520', '300', 'NV' );
VALUES  ('11E4', '2018',  '3',  '180311', '200', 'TN' ) ;
VALUES  ('11E5', '2020',  '6',  '200416', '800', 'NY' );
VALUES  ('11E6', '2019', ' 5',  '191250', '500', 'CA' );
VALUES  ('11E7', '2018',  '3',  '180313', '100', 'VT' );
VALUES  ('11E8', '2019', ' 5',  '191251', '1000', 'AL' );


----Queries for Table A---
SELECT 
MOnth,
Year, 
BadgeNum,
Gender,
Date_Sent,
Type_Status,
Living_State,
S_Type_Type,
,CASE WHEN Recipient_num IN ('00001') THEN 'Billing_Transaction'
WHEN Recipient_num IN ('00002') THEN 'Online_Transaction'
END as [Category]
,COUNT(DISTINCT case when [S_Type_Type] = '1' THEN 'BadgeNum' ELSE NULL END) as [Sent_Questions]
,COUNT([BadgeNum]) as [NumberOfBadges]

FROM [TABLE_A]

GROUP bY 
MOnth,
Year, 
BadgeNum,
Gender,
Date_Sent,
Type_Status,
Living_State,
S_Type_Type,

--- Queries for Table B----
Table B- 
=========

 SELECT 
    COUNT([BadgeNumber]) as [Total_Number_Answered] ,     
    RespondedYear, 
    RespondedMonth,  
    Date_Process,
    Value,                      
    State   

    FROM TABLE_B
    
    
    GROUP BY 
    
    RespondedYear, 
    RespondedMonth,  
    Date_Process,
    Value,                      
    State   

---- 两个表的输出：

Result of the TABLE_A
===================

YEAR     MONTH           Sent_Questions
=====   =======     =====================  
2017     03                 2
2017     02                 0   
2018     03                 0
2018     05                 2
2019     05                 2       
2020     07                 2
2020     06                 2



Result of the TABLE_B

=========================

YEAR     MONTH      Total_Number_Answered     
=====   =======     =====================   
2017     03                 0                  
2017     02                 0                   
2018     03                 1                   
2018     05                 1                   
2019     05                 2               
2020     07                 1               
2020     06                 1       

---这是我需要的结果----- 预期结果： ==============================

YEAR     MONTH      Total_Number_Answered     Sent_Questions
=====   =======     =====================    ===============
2017     03                 0                         2
2017     02                 0                         0 
2018     03                 1                         0
2018     05                 1                         2
2019     05                 2                         2     
2020     07                 1                         2
2020     06                 1                         2

这是我坚持的查询：我想获得与预期结果表相同的数字。我想以某种方式将表 A 和表 B 与年份和月份连接起来，而不会丢失任何数据。我遇到了麻烦。

这是我开始使用的查询：

SELECT 
MOnth,
Year, 
BadgeNum,
Gender,
Date_Sent,
Type_Status,
Living_State,
S_Type_Type,
,CASE WHEN Recipient_num IN ('00001') THEN 'Billing_Transaction'
WHEN Recipient_num IN ('00002') THEN 'Online_Transaction'
END as [Category]
,COUNT(DISTINCT case when [S_Type_Type] = '1' THEN 'BadgeNum' ELSE NULL END) as [Sent_Questions]
,COUNT([BadgeNum]) as [NumberOfBadges]
t2.[Counts_Display]

FROM [TABLE_A] as t1

FULL OUTER JOIN 

(

SELECT 
COUNT ([BadgeNumber]) as [Counts_Display]
,LEFT([RespondedYear],4)+LEFT([RespondedMonth],2) as [CombinedDates]
,VALUE
,State
) as t2

ON (t2.[BadgeNumber] = t1.[BadgeNum]) 

and t2.[RespondedYear] = t1.[Year]
and t1.[RespondedMonth] = t2.[Month]

GROUP bY 
MOnth,
Year, 
BadgeNum,
Gender,
Date_Sent,
Type_Status,
Living_State,
S_Type_Type,
t2.[Counts_Display]

标签： sqltsql