Should I use derived field here? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Should I use derived field here?

Author

Topic

rv498
Yak Posting Veteran

60 Posts

Posted - 2014-03-10 : 20:52:35

I have these two tables.

FactInternetSales table

[dbo].[FactInternetSales](
[ProductKey] [int] NOT NULL,
[OrderDateKey] [int] NOT NULL,
[DueDateKey] [int] NOT NULL,
[ShipDateKey] [int] NOT NULL,
[CustomerKey] [int] NOT NULL,
[PromotionKey] [int] NOT NULL,
[CurrencyKey] [int] NOT NULL,
[SalesTerritoryKey] [int] NOT NULL,
[SalesOrderNumber] [nvarchar](20) NOT NULL,
[SalesOrderLineNumber] [tinyint] NOT NULL,
[RevisionNumber] [tinyint] NOT NULL,
[OrderQuantity] [smallint] NOT NULL,
[UnitPrice] [money] NOT NULL,
[ExtendedAmount] [money] NOT NULL,
[UnitPriceDiscountPct] [float] NOT NULL,
[DiscountAmount] [float] NOT NULL,
[ProductStandardCost] [money] NOT NULL,
[TotalProductCost] [money] NOT NULL,
[SalesAmount] [money] NOT NULL,
[TaxAmt] [money] NOT NULL,
[Freight] [money] NOT NULL,
[CarrierTrackingNumber] [nvarchar](25) NULL,
[CustomerPONumber] [nvarchar](25) NULL,
[OrderDate] [datetime] NULL,
[DueDate] [datetime] NULL,
[ShipDate] [datetime] NULL,

DimReseller table

[dbo].[DimReseller](
[ResellerKey] [int] IDENTITY(1,1) NOT NULL,
[GeographyKey] [int] NULL,
[ResellerAlternateKey] [nvarchar](15) NULL,
[Phone] [nvarchar](25) NULL,
[BusinessType] [varchar](20) NOT NULL,
[ResellerName] [nvarchar](50) NOT NULL,
[NumberEmployees] [int] NULL,
[OrderFrequency] [char](1) NULL,
[OrderMonth] [tinyint] NULL,
[FirstOrderYear] [int] NULL,
[LastOrderYear] [int] NULL,
[ProductLine] [nvarchar](50) NULL,
[AddressLine1] [nvarchar](60) NULL,
[AddressLine2] [nvarchar](60) NULL,
[AnnualSales] [money] NULL,
[BankName] [nvarchar](50) NULL,
[MinPaymentType] [tinyint] NULL,
[MinPaymentAmount] [money] NULL,
[AnnualRevenue] [money] NULL,
[YearOpened] [int] NULL,
CONSTRAINT [PK_DimReseller_ResellerKey] PRIMARY KEY CLUSTERED
(
[ResellerKey] ASC
)

Problem:
List the total dollar amount (SalesAmount) for sales to Resellers.
-- 80,450,596.9823

So I have this but this doesn't produce above answer.
SELECT SUM(SalesAmount)
FROM dbo.FactInternetSales

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-03-11 : 05:27:22

I assume CustomerKey in Fact corresponds to ResellerKey value in DImReseller as I cant see any other columns by which you can relate.
That being the case you can use below to get total dollar amount for reseller sales


SELECT SUM(SalesAmount)
FROM dbo.FactInternetSales f
WHERE EXISTS (SELECT 1
FROM DimReseller
WHERE ResellerKey = f.CustomerKey
)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources