金额填充异常及SQL查询中CrAmount计算失效问题求助

Alright, let's dig into why your CrAmount calculation is off—even without seeing your full table structure or data, there are some super common pitfalls that could be causing this. Here's what to check first:

• Cartesian Product (Cross-Join) Mess
  You're using the old comma-separated table syntax without an explicit JOIN condition, which is a huge red flag. Without linking tbl_TransactionDetail and tbl_TransactionMaster properly, you're creating a cross product where every row in the detail table matches every row in the master table. That means your SUMs are getting multiplied by the number of matching rows in the other table, completely skewing your totals.
  Fix this by switching to explicit JOIN syntax with a valid relationship (like matching TransactionCode between the two tables):

  WITH CTE AS (
      SELECT 
          [Master].[TransactionCode], 
          [Master].[TransactionDate], 
          SUM(DrAmount) [DrAmount], 
          SUM(CrAmount) [CrAmount]
      FROM [FICO].[tbl_TransactionDetail] [Detail]
      INNER JOIN [FICO].[tbl_TransactionMaster] [Master]
          ON [Detail].[TransactionCode] = [Master].[TransactionCode] -- Replace with your actual join key
      -- WHERE [VoucherDate] BETWEEN ... (finish your date filter here)
      GROUP BY [Master].[TransactionCode], [Master].[TransactionDate]
  )
  

• Duplicate Rows in the Detail Table
  If tbl_TransactionDetail has duplicate entries (e.g., the same TransactionCode + CrAmount appearing multiple times when it should only be there once), your SUM will overcount those values.
  Try deduplicating the detail data first before aggregating:

  WITH CTE_DistinctDetails AS (
      SELECT DISTINCT TransactionCode, DrAmount, CrAmount
      FROM [FICO].[tbl_TransactionDetail]
  ),
  CTE AS (
      SELECT 
          [Master].[TransactionCode], 
          [Master].[TransactionDate], 
          SUM(DrAmount) [DrAmount], 
          SUM(CrAmount) [CrAmount]
      FROM CTE_DistinctDetails [Detail]
      INNER JOIN [FICO].[tbl_TransactionMaster] [Master]
          ON [Detail].[TransactionCode] = [Master].[TransactionCode]
      -- WHERE ...
      GROUP BY [Master].[TransactionCode], [Master].[TransactionDate]
  )
  

• NULL Values Skewing the SUM
  SQL's SUM() function ignores NULL values entirely. If some of your CrAmount entries are NULL (and they should be treated as 0 in your calculations), your total will be lower than expected. Fix this by wrapping the column in ISNULL():

  SUM(ISNULL(CrAmount, 0)) [CrAmount]
  

• Incorrect GROUP BY Logic
  Double-check that your GROUP BY fields make sense for your business logic. For example, if TransactionCode is the unique identifier for master records, grouping by both TransactionCode and TransactionDate might split totals if a code has multiple dates (which might be invalid data). Alternatively, if the detail table's TransactionCode doesn't perfectly align with the master table's, you might be missing or misgrouping records.

• Truncated WHERE Condition Issues
  Your WHERE clause was cut off, but if the date filter (or any other filters) are targeting the wrong table's fields, or using incorrect logic (like mixing up AND/OR), you might be excluding valid CrAmount records or including unwanted ones. Make sure your filter applies to the correct table (e.g., [Master].[VoucherDate] instead of a vague VoucherDate) and that the date range is correctly defined.

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