0

I have a view that is pulling sales from multiple companies and I need to basically 'combine' two rows so they display as one. Here is a picture of what the data looks like:

enter image description here

I need to take all rows for Wine Group and add them into Other/Us Direct and then delete Wine Group or Filter it out so it doesn't show after adding it's values. I tried writing a case statement and also just tried to aggregate the sum of values and union it to the existing view but it still displays them as separate entities. Any ideas or tips would be much appreciated!

Here is current view query that pulls from the table that has this information:

SELECT  [LoadDate]
      ,[Company]
      ,CASE WHEN [ReportGroup] = 'Germany' THEN 'Direct Germany' ELSE ReportGroup END AS ReportGroup
      ,[ShipToCountryCode]
      ,[LLCCustName]
      ,[TodayGrossSalesUSD]
      ,[TodayGrossSalesEURO]
      ,[CurrMonthInvoiceUSD]
      ,[CurrMonthInvoiceEURO]
      ,[CurrMonthShipUSD]
      ,[CurrMonthShipEURO]
      ,[CurrMonthBudgetUSD]
      ,[CurrMonthBudgetEURO]
      ,[CurrMonthLinearUSD]
      ,[CurrMonthLinearEURO]
      ,[LastYearCurrMonthUSD]
      ,[LastYearCurrMonthEURO]
      ,[LastYearCurrMonthLinearUSD]
      ,[LastYearCurrMonthLinearEURO]
      ,[TodayOrderIntakeUSD]
      ,[TodayOrderIntakeEUR]
      ,[TodayVolume]
      ,[CurrMonthInvoiceVolume]
      ,[CurrMonthShipVolume]
      ,[CurrMonthBudgetVolume]
      ,[CurrMonthLinearVolume]
      ,[LastYearCurrMonthVolume]
      ,[LastYearCurrMonthLinearVolume]
      ,[TodayOrderIntakeVolume]
      ,[NextMonthVolume]
      ,[NextMonthGrossSalesUSD]
      ,[NextMonthGrossSalesEURO]
      ,[LastYearCurrMonthAsOfVolume]
      ,[LastYearCurrMonthAsOfUSD]
      ,[LastYearCurrMonthAsOfEURO]
      ,[CurrMonthForecastVolume]
      ,[CurrMonthForecastUSD]
      ,[CurrMonthForecastEURO]
      ,DayType
  FROM [BI_DB].[dbo].[DailySummary]

UNION

SELECT
DISTINCT 
LoadDate,
Company,
CASE WHEN [ReportGroup] = 'Germany' THEN 'Direct Germany' ELSE ReportGroup END AS ReportGroup,
[ShipToCountryCode],
[LLCCustName],
NULL AS [TodayGrossSalesUSD],
NULL AS [TodayGrossSalesEURO],
NULL AS [CurrMonthInvoiceUSD],
NULL AS [CurrMonthInvoiceEURO],
NULL AS [CurrMonthShipUSD],
NULL AS [CurrMonthShipEURO],
[CurrMonthBudgetUSD],
[CurrMonthBudgetEURO],
[CurrMonthLinearUSD],
[CurrMonthLinearEURO],
NULL AS [LastYearCurrMonthUSD],
NULL AS [LastYearCurrMonthEURO],
NULL AS [LastYearCurrMonthLinearUSD],
NULL AS [LastYearCurrMonthLinearEURO],
NULL AS [TodayOrderIntakeUSD],
NULL AS [TodayOrderIntakeEUR],
NULL AS [TodayVolume],
NULL AS [CurrMonthInvoiceVolume],
NULL AS [CurrMonthShipVolume],
[CurrMonthBudgetVolume],
[CurrMonthLinearVolume],
NULL AS [LastYearCurrMonthVolume],
[LastYearCurrMonthLinearVolume],
NULL AS [TodayOrderIntakeVolume],
NULL AS [NextMonthVolume],
NULL AS [NextMonthGrossSalesUSD],
NULL AS [NextMonthGrossSalesEURO],
NULL AS [LastYearCurrMonthAsOfVolume],
NULL AS [LastYearCurrMonthAsOfUSD],
NULL AS [LastYearCurrMonthAsOfEURO],
[CurrMonthForecastVolume],
[CurrMonthForecastUSD],
[CurrMonthForecastEURO],
DayType
  FROM [BI_DB].[dbo].[DailySummaryFullMonth]
WHERE LoadDate > (SELECT MAX(LoadDate) FROM [BI_DB].[dbo].[DailySummary])
6
  • Sounds like you're talking about conditional aggregation (aka a Pivot)
    – Thom A
    Commented Feb 25, 2020 at 15:18
  • CASE WHEN [LLCCustName] = 'Wine Group' THEN 'Other/Us Direct' ELSE [LLCCustName] END AS [LLCCustName] ? Commented Feb 25, 2020 at 15:44
  • yeah, That is what I scripted but it just returned two entries named 'Other/US Direct' but didn't aggregate their values. I need them to be consolidated.
    – Muab Nhoj
    Commented Feb 25, 2020 at 20:26
  • 1
    Probably not the best way, but you could query the data into a temp table, update the columns as needed, then run the query with aggregation against the temp table. Commented Feb 27, 2020 at 16:03
  • Please add an expected output
    – Jhunter1
    Commented Mar 1, 2020 at 21:59

1 Answer 1

0

This should address the jist of your question. You will need to check the aggregation to make sure it is appropriate for the data, though at a quick glance everything seemed to be good with SUM or Group. You may want to replace the CTE with a real table as well.

;WITH
LLCCustNameMapping AS (
    SELECT * FROM (VALUES
        ('Wine Group', 'Other/Us Direct')
    )V(From, To)
)

  SELECT  [LoadDate]
       ,[Company]
       ,CASE WHEN [ReportGroup] = 'Germany' THEN 'Direct Germany' ELSE ReportGroup END AS ReportGroup
       ,[ShipToCountryCode]
       ,IsNull(map.To,  [LLCCustName]) [LLCCustName]
       ,SUM([TodayGrossSalesUSD]) [TodayGrossSalesUSD]
       ,SUM([TodayGrossSalesEURO]) [TodayGrossSalesEURO]
       ,SUM([CurrMonthInvoiceUSD]) [CurrMonthInvoiceUSD]
       ,SUM([CurrMonthInvoiceEURO]) [CurrMonthInvoiceEURO]
       ,SUM([CurrMonthShipUSD]) [CurrMonthShipUSD]
       ,SUM([CurrMonthShipEURO]) [CurrMonthShipEURO]
       ,SUM([CurrMonthBudgetUSD]) [CurrMonthBudgetUSD]
       ,SUM([CurrMonthBudgetEURO]) [CurrMonthBudgetEURO]
       ,SUM([CurrMonthLinearUSD]) [CurrMonthLinearUSD]
       ,SUM([CurrMonthLinearEURO]) [CurrMonthLinearEURO]
       ,SUM([LastYearCurrMonthUSD]) [LastYearCurrMonthUSD]
       ,SUM([LastYearCurrMonthEURO]) [LastYearCurrMonthEURO]
       ,SUM([LastYearCurrMonthLinearUSD]) [LastYearCurrMonthLinearUSD]
       ,SUM([LastYearCurrMonthLinearEURO]) [LastYearCurrMonthLinearEURO]
       ,SUM([TodayOrderIntakeUSD]) [TodayOrderIntakeUSD]
       ,SUM([TodayOrderIntakeEUR]) [TodayOrderIntakeEUR]
       ,SUM([TodayVolume]) [TodayVolume]
       ,SUM([CurrMonthInvoiceVolume]) [CurrMonthInvoiceVolume]
       ,SUM([CurrMonthShipVolume]) [CurrMonthShipVolume]
       ,SUM([CurrMonthBudgetVolume]) [CurrMonthBudgetVolume]
       ,SUM([CurrMonthLinearVolume]) [CurrMonthLinearVolume]
       ,SUM([LastYearCurrMonthVolume]) [LastYearCurrMonthVolume]
       ,SUM([LastYearCurrMonthLinearVolume]) [LastYearCurrMonthLinearVolume]
       ,SUM([TodayOrderIntakeVolume]) [TodayOrderIntakeVolume]
       ,SUM([NextMonthVolume]) [NextMonthVolume]
       ,SUM([NextMonthGrossSalesUSD]) [NextMonthGrossSalesUSD]
       ,SUM([NextMonthGrossSalesEURO]) [NextMonthGrossSalesEURO]
       ,SUM([LastYearCurrMonthAsOfVolume]) [LastYearCurrMonthAsOfVolume]
       ,SUM([LastYearCurrMonthAsOfUSD]) [LastYearCurrMonthAsOfUSD]
       ,SUM([LastYearCurrMonthAsOfEURO]) [LastYearCurrMonthAsOfEURO]
       ,SUM([CurrMonthForecastVolume]) [CurrMonthForecastVolume]
       ,SUM([CurrMonthForecastUSD]) [CurrMonthForecastUSD]
       ,SUM([CurrMonthForecastEURO]) [CurrMonthForecastEURO]
       ,DayType
  FROM [BI_DB].[dbo].[DailySummary] s
    LEFT JOIN LLCCustNameMapping map ON s.LLCCustName = map.From
  WHERE [LLCCustName] IN ('Wine Group', 'Other/Us Direct')
  GROUP BY [LoadDate]
       ,[Company]
       ,CASE WHEN [ReportGroup] = 'Germany' THEN 'Direct Germany' ELSE ReportGroup END AS ReportGroup
       ,[ShipToCountryCode]
       ,IsNull(map.To,  [LLCCustName]) [LLCCustName]
       ,DayType

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.