Aggrevated aggregates and other harrowing tales of steaming statements.

Aggrevated aggregates and other harrowing tales of steaming statements.

posted 2/22/2012  by RCharCox -  Views: [229] 

                Then, Right-click the key icon and select “Relationships…”

                This will bring up a window that, on the left side, lists all the relationships to this Primary Key.  Again, very good information for us since we were pretty much fried at the day’s end and out of “Mental Reserves,” so to speak.

Getting back to concatenations, I was very excited to have the opportunity to pull multiple columns’ worth of information but to only have to display one field (for example, instead of having a field for one’s first and last name, have one field for their whole name).  However, when I wanted to do so with a date, it gave me some trouble.  Specifically, I wanted to show CalendarQuarter and Calendar Year as a QQ-YYYY value.  Unfortunately, when I went to concatenate the values, the software summed the two int’s together.  After doing some research, however, I have found the CAST command.  This command is used to tell the software that the integer we are selecting is to be treated as a varchar, and thus not subject to the automatic mathematical operations that the Management Studio likes to initiate.  For example:

Select SalesAmountQuota

      , CAST(CalendarQuarter as Char(2))+'-'+CAST(CalendarYear as Char(4)) as QYYYY

From FactSalesQuota

Now, I like having each field be at least two digits, so I’m going to have to research how to pad a variable such as this (I have a jumping off point, knowing I need to change the data from an int to a char, but from there… tomorrow night).  Zip codes would be another example of where this would come in handy (if you live in the New England area and your zip starts with a zero).

Good night, all.  Here’s my homework.  As always, feedback is greatly appreciated!  Also, I apologize for the absence of screen shots (I was going to put them in, I swear!) and funky formatting below.  I'll get better at this blog stuff, I promise!

Scenario One:

1. 1.             Write a TSQL statement that brings back the following columns from the FactResellerSales table:
2. a.                   SalesAmount (summed)
   1. 2.             Left Join the table DimEmployee and return the following columns:
   2. a.                   EmployeeKey
   3. b.                  LastName
   4. c.                   FirstName
      1. 3.             Use the appropriate command to group the query by all the columns from the DimEmployee table.
      2. 4.             Add a having clause to only bring back the employees with a summed sales amount greater than $1MM.
      3. 5.             Lastly, order the sales amount by the summed sales amount in descending order so the highest sales are on top.

SELECT Sum(FRS.SalesAmount) as TotalSales

, E.EmployeeKey

, E.LastName

, E.FirstName

FROM FactResellerSales as FRS

Left Join DimEmployee as E

on E.EmployeeKey = FRS.EmployeeKey

Group by E.EmployeeKey,E.LastName,E.FirstName

Having Sum(FRS.SalesAmount) > 1000000

Order by Sum(FRS.SalesAmount) Desc

Scenario Two:

1. 1.             Write a TSQL statement that brings back the following columns from the DimProduct table:
2. a.                   EnglishProductName
   1. 2.             Join the table DimProductSubcategory and return the following column:
   2. a.                   EnglishProductSubcategoryName
      1. 3.             Join the table DimProductCategory and return the following column:
      2. a.                   EnglishProductCategoryName
         1. 4.             Join the FactResellerSales and return the following column:
         2. a.                   SalesAmount (summed) and rename this column ResellerSales
            1. 5.             Join the table FactInternetSales and return the following column:
            2. a.                   SalesAmount (summed) and rename this column Internet Sales
               1. 6.             Use the appropriate command to group the query by all columns that are not aggregated.
               2. 7.             Last, Order by EnglishProductCategoryName, EnglishProductSubcategoryName, EnglishProductName.

SELECT EnglishProductName

, EnglishProductSubCategoryName

, EnglishProductCategoryName

, Sum(RS.SalesAmount) as ResellerSales

, Sum(INS.SalesAmount) as InternetSales

FROM DimProduct as P

Join DimProductSubcategory as PSC

on P.ProductSubcategoryKey = PSC.ProductSubcategoryKey

Join DimProductCategory as PC

on PC.ProductCategoryKey = PSC.ProductCategoryKey

Join FactResellerSales as RS

on P.ProductKey = RS.ProductKey

Join FactInternetSales as INS

on INS.ProductKey = P.ProductKey

Group By EnglishProductName, EnglishProductSubcategoryName, EnglishProductCategoryName

Order By EnglishProductName, EnglishProductSubcategoryName, EnglishProductCategoryName

Scenario 3:

1. 1.             Write a TSQL statement that brings back the following columns from the FactResellerSales table:
2. a.                   OrderQuantity (summed)
   1. 2.             Left join the table DimSalesTerritory and return the following column:
   2. a.                   SalesTerritoryRegion
      1. 3.             Left join the DimDate table, but do not return any rows
      2. 4.             Add a filter to return only rows that have United States as a SalesTerritoryCountry
      3. 5.             Also, add a filter that returns only rows with a FullDateAlternateKey of 1-1-2006
      4. 6.             Last, Group the results by SalesTerritoryRegion.

SELECT sum(FRS.OrderQuantity) as TotalOrderQty

, ST.SalesTerritoryRegion

FROM FactResellerSales as FRS

 Left Join DimSalesTerritory as ST

 on ST.SalesTerritoryKey = FRS.SalesTerritoryKey

 Left join DimDate as D

 on FRS.OrderDateKey = D.DateKey

Where ST.SalesTerritoryCountry = 'United States'

 And D.FullDateAlternateKey = '01-01-2006'

Group By St.SalesTerritoryRegion