Duplicate rows showing while using group by clause?

Question

I've written a SQL query which produces a table as given below.

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.

;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) < 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 <= [s].CreatedOnUtc
    AND (
      (
        EndDateUtc >= [s].CreatedOnUtc
      )
      OR (EndDateUtc is null)
    )
GROUP BY
  Start_Date,
  End_Date,
  BusinessRefId,
  Amount

OUTPUT:

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

Answer 1

It depends on the output you want to produce.

If you want one row per business, but also want to return the additional columns (Start Period Date, End Period Date, brand, Committed Transaction Fee), you probably won't be able to use a GROUP BY since you'll need to include those columns in your GROUP BY clause.

You can use a QUALIFY function to do this and still return the columns, but you have to decide how to "pick" each row to be returned. For example:

QUALIFY ROW_NUMBER() OVER(
  PARTITION BY [bg].BusinessRefId 
  ORDER BY Generate_Dates.Start_Date DESC
) = 1

This does the following:

1. Split the result set rows into groups based on [bg].BusinessRefId
2. Order the rows within those groups based on Generate_Dates.Start_Date
3. Return the first row of each group

You can replace your GROUP BY clause with the QUALIFY.

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) < End_Date
)
SELECT DISTINCT
  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
      TOP 1 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 <= [s].CreatedOnUtc
AND (EndDateUtc >= [s].CreatedOnUtc OR EndDateUtc IS NULL)
QUALIFY ROW_NUMBER() OVER(
  PARTITION BY [bg].BusinessRefId 
  ORDER BY Generate_Dates.Start_Date DESC
) = 1
--GROUP BY Start_Date, End_Date, BusinessRefId, Amount, [s].Culture

Also, like the previous poster mentioned, you don't need the DISTINCT in there. If you're doing a GROUP BY without any aggregate functions, it's logically equivalent to doing a DISTINCT.

I haven't tested it, so not sure if it works...but something to try.