Sql-server – T SQL/ Reporting Question

sql servert-sql

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:

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])

Best Answer

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

Related Solutions

Sql-server – Setting user permissions for different SQL Server schemas

The basic concept is to use GRANT/DENY Schema Permissions. You can efficiently manage permissions by creating a role and then adding members to it.

Below is an example that will explain you in detail

use master
go
--Create Logins
CREATE LOGIN UserA WITH Password='UserA123';
go
CREATE LOGIN UserB WITH Password='UserB123';

use AdventureWorks2008R2
go
--Create Database Users
CREATE USER UserA;
go
CREATE USER UserB;
go
--Create the Test Schemas
CREATE SCHEMA SchemaA AUTHORIZATION UserA
go
CREATE SCHEMA SchemaB AUTHORIZATION UserB
go

-- create test tables
create table schemaA.TableA (fname char(5))
go
insert into schemaA.TableA (fname) values ('Kin-A')
go

create table SchemaB.TableB (fname char(5))
go
insert into SchemaB.TableB (fname) values ('Kin-B')
go

Now test :

--Test for UserA in SchemaA
EXEC('select * from schemaA.TableA') AS USER = 'UserA'
go
--Kin-A

-- Test for UserB in SchemaB == this should fail
EXEC('select * from SchemaB.TableB') AS USER = 'UserA'
go
--Msg 229, Level 14, State 5, Line 1
--The SELECT permission was denied on the object 'TableB', database 'AdventureWorks2008R2', schema 'SchemaB'.

Now create Stored Procedures :

CREATE PROCEDURE SchemaB.proc_SelectUserB
AS
    select * from schemaA.TableA;
go
create procedure schemaA.proc_SchemaA
as 
    select * from schemaA.TableA

Now Grant execute permissions to UserA on schemaB's SP

GRANT EXECUTE ON OBJECT::[SchemaB].[proc_SelectUserB] TO [UserA] 
go

Test it .. to see if UserA is able to run SP from schemaB. This will PASS

EXECUTE AS LOGIN='UserA';
    Exec SchemaB.proc_SelectUserB;
    revert;
go
--- Kin-A

But UserA wont be able to see data from SchemaB

EXECUTE AS LOGIN='UserA';
    select * from SchemaB.TableB
revert;
go

--- Msg 229, Level 14, State 5, Line 3
--- The SELECT permission was denied on the object 'TableB', database 'AdventureWorks2008R2', schema 'SchemaB'.

Alternatively you can use DATABASE ROLE and just add users to it for better manageability of permissions:

EXEC sp_addrole 'SchemaBUsesSchemaAProc'
go
EXEC sp_addrolemember 'SchemaBUsesSchemaAProc','UserA';
go

Below statement will make sure that UserA is able to see schemaA and NOT schemaB. The good thing is that you can just add users to SchemaBUsesSchemaAProc role and they will inherit all the permissions granted to that role.

GRANT SELECT ON SCHEMA::SchemaA TO SchemaBUsesSchemaAProc;
go

If you only want to allow UserA to execute SP's which are owned by SchemaB then below statement will do the job:

GRANT EXECUTE ON OBJECT::[SchemaB].[proc_SelectUserB] TO [SchemaBUsesSchemaAProc] 
go

This way, UserA is not able to see SchemaB's tables, but still can execute procs from SchemaB.

Below will explain the permission hierarchy :

enter image description here

Sql-server – SQL Server user cannot select from a table it just created

Granting schema modification is not, and should not be, the same as granting DML rights. Grant the user the exact rights that user needs, including 'SELECT', etc, as necessary.

See https://msdn.microsoft.com/en-us/library/ms178569.aspx for details.

Best Answer

Related Solutions

Sql-server – Setting user permissions for different SQL Server schemas

Sql-server – SQL Server user cannot select from a table it just created

Related Question