多表关联统计GuestCount遇笛卡尔积，求正确求和方案

我需要从设计欠佳的数据库生成报表，统计GuestCount列的总和，但多表关联产生笛卡尔积，导致求和结果不准确。无法使用Sum(Distinct)，因为我要统计的是唯一行的总和，而非GuestCount列的唯一值之和。

建表及测试数据SQL #

CREATE TABLE TesttblTransactions (
    ID int, 
    [sysdate] date, 
    TxnHour tinyint,
    Facility nvarchar(50),
    TableID int, 
    [Check] int,
    Item int, 
    Parent int
)

Create Table TesttblTablesGuests (
    ID int, 
    Facility nvarchar(50),
    TableID int,
    GuestCount tinyint, 
    TableDate Date
)

Create Table TesttblFacilities (
    ID int,
    ClientKey nvarchar(50),
    Brand nvarchar(50),
    OrgFacilityID nvarchar(50),
    UnitID smallint
)



INSERT INTO testtbltransactions (
    ID, 
    [Sysdate], 
    TxnHour, 
    Facility, 
    TableID, 
    [Check], 
    Item, 
    Parent
)
VALUES
    (
        1,
        '20221201',
        7,
        'JOES',
        1001,
        12345,
        8898989,
        0
    ),
    (
        2,
        '20221201',
        7,
        'JOES',
        1001, 
        12345, 
        8776767,
        1
    ),
    (
        3,
        '20221201',
        7,
        'JOES', 
        1001, 
        12345, 
        856643,
        0
    ),
    (
        4,
        '20221201',
        7,
        'THE DIVE',
        1001, 
        67890,
        662342,
        0
    ),
    (
        5,
        '20221201',
        7,
        'THE DIVE', 
        1001, 
        67890, 
        244234,
        0
    ),
    (
        6,
        '20221201',
        7,
        'JOES',
        1002, 
        12344,
        873323,
        0
    );


INSERT INTO testtblTablesGuests (
    ID, 
    Facility,
    TableID,
    GuestCount, 
    TableDate
)
VALUES
    (
        1,
        'JOES',
        1001,
        4,
        '20221201'
    ),
    (
        2,
        'THE DIVE',
        1001,
        1,
        '20221201'
    ),
    (
        3,
        'JOES',
        1002,
        1,
        '20221201'
    );
    
INSERT INTO testtblFacilities (
    ID,
    ClientKey,
    Brand,
    OrgFacilityID,
    UnitID
)
VALUES
    (
        1,
        'JOES',
        'Joes Hospitality Group LLC',
        'Joes Bar',
        987
    ),
    (
        2,
        'THE DIVE',
        'The Dive Restaurant Group',
        'The Dive',
        565
    );

原错误查询语句 #

Declare @StartDate as Date = '12-1-2022'
Declare @EndDate as Date = '12-1-2022'

--The query we want to work
SELECT 
TesttblFacilities.ClientKey, 
TesttblFacilities.Brand, 
format(testtbltransactions.sysdate,'yyyy-MM-dd') AS [Date], 
'H' AS Freqency, 
Testtbltransactions.[TxnHour] AS [Hour], 
TesttblFacilities.UnitID AS [UnitID], 
'Dine In Guest Count' as Metric,
Sum(TesttblTablesGuests.GuestCount) AS [Value]
FROM ((Testtbltransactions 
JOIN Testtbltablesguests ON (Testtbltablesguests.TableDate = Testtbltransactions.sysdate) AND (Testtbltransactions.FACILITY = Testtbltablesguests.facility) AND (Testtbltransactions.tableid = Testtbltablesguests.tableid))   
JOIN TesttblFacilities ON Testtbltransactions.FACILITY = TesttblFacilities.ClientKey)
Where (((Testtbltransactions.parent)=0))
and Testtbltransactions.sysdate >= @StartDate
and Testtbltransactions.sysdate <= @EndDate
GROUP BY TesttblFacilities.ClientKey, Testtblfacilities.UnitID,TesttblFacilities.Brand, Testtbltransactions.facility, Testtbltransactions.sysdate, Testtbltransactions.TxnHour

该查询得到的结果是9和2，而预期结果应为5和1。

可行的子查询方案 #

Declare @StartDate as Date = '12-1-2022'
Declare @EndDate as Date = '12-1-2022'

Select 
t1.txnhour, 
t1.facility, 
SUM(t1.guestcount) from 
(
Select Distinct 
    TesttblTransactions.TableID as TableID, 
    testtbltransactions.[txnHour] as txnhour, 
    testtbltransactions.Facility as Facility,
    testtbltablesguests.GuestCount as guestcount,
    testtbltransactions.Parent as parent
    From TesttblTransactions
    Join TesttblTablesGuests on TesttblTablesGuests.TableID = TesttblTransactions.TableID and testtbltablesguests.Facility = TesttblTransactions.Facility   
    Where (((Testtbltransactions.parent)=0))
   and Testtbltransactions.sysdate >= @StartDate
   and Testtbltransactions.sysdate <= @EndDate
) T1
Group by t1.Facility, t1.txnhour, t1.Facility

我将继续优化该方案。

内容的提问来源于stack exchange，提问作者hubb412