title: Duplicate rows showing while using group by clause?
body: <p>I've written a SQL query which produces a table as given below.</p>
  
  <p>The results needs to be grouped into a single line for each business. I've tried a lot of ways but I'm not able to do that. I've tried to insert the final table data into a temporary table and then perform grouping on the temp table but that does not produce the correct output.</p>
  
  <pre><code>;WITH Generate_Dates(Start_Date, End_Date) AS (
    SELECT
      CAST(StartDateUtc AS Date),
      CAST(
        CASE WHEN [bg].EndDateUtc IS NULL THEN GETDATE() ELSE CAST([bg].EndDateUtc AS Date) end AS Date
      )
    FROM
      BusinessGoal AS [bg] WITH (NOLOCK)
    WHERE
      [bg].Period = 'Year'
    UNION ALL
    SELECT
      DATEADD(yy, 1, Start_Date),
      End_Date
    FROM
      Generate_Dates
    WHERE
      DATEADD(yy, 1, Start_Date) &lt; End_Date
  )
  SELECT
    (
      Generate_Dates.Start_Date
    ) AS "Start Period Date",
    (
      Generate_Dates.End_Date
    ) AS "End Period Date",
    [bg].BusinessRefId AS 'Brand',
    (
      CAST (
        [bg].Amount AS DECIMAL
      )
    ) AS "Committed Transaction Fee",
    (
      SELECT
        CAST (
          sum (
            (
              CASE WHEN [s].Credit = 0 THEN - [s].SettlementAmount ELSE [s].SettlementAmount END
            )
          ) AS DECIMAL
        )
    ) AS "Actual Transaction Fee",
    (
      SELECT
        TOP 1 (
          CASE [s].Culture WHEN 'en-US' THEN 'USD' WHEN 'en-CA' THEN 'CAD' WHEN 'en-IE' THEN 'EUR' WHEN 'en-AU' THEN 'AUD' WHEN 'en-NZ' THEN 'NZD' WHEN 'en-Gbg' THEN 'GbgP' WHEN 'es-MX' THEN 'MXN' WHEN 'jp-JP' THEN 'JPY' WHEN 'nbg-NO' THEN 'NOK' end
        )
      FROM
        Settlement AS [s] WITH (NOLOCK)
    ) AS "Currency"
  FROM
    BusinessGoal AS [bg] WITH (NOLOCK)
    INNER JOIN Settlement AS [s] WITH (NOLOCK) ON [bg].BusinessRefId = [s].CompanyRefId
    INNER JOIN OrderProductVariant AS [opv] WITH (NOLOCK) ON
    (
      [opv].MRefId = [s].CompanyRefId
      AND [opv].Id = [s].OrderProductVariantId
    )
    INNER JOIN [Order] AS [o] WITH (NOLOCK) ON [o].Id = [opv].OrderId
    INNER JOIN Generate_Dates ON GoalType = 'CommittedTransactionFee'
  WHERE
      StartDateUtc &lt;= [s].CreatedOnUtc
      AND (
        (
          EndDateUtc &gt;= [s].CreatedOnUtc
        )
        OR (EndDateUtc is null)
      )
  GROUP BY
    Start_Date,
    End_Date,
    BusinessRefId,
    Amount
  </code></pre>
  
  <p>OUTPUT:</p>
  
  <pre><code>Start Period Date End Period Date brand   Committed Transaction Fee 
  2019-06-07        2019-08-26  NIKE        45000
  2019-06-07        2019-08-26  ADIDAS      18000 
  2019-06-07        2019-08-26  ADIDAS      18000 
  2019-06-14        2019-08-26  NIKE        45000
  2019-06-14        2019-08-26  ADIDAS      18000
  2019-06-14        2019-08-26  ADIDAS      18000 
  2019-07-25        2019-08-26  NIKE        45000
  2019-07-25        2019-08-26  ADIDAS      18000
  2019-07-25        2019-08-26  ADIDAS      18000
  </code></pre>