question

--2) How many sales orders (Headers) used Vista credit cards in October 2012 select * FROM [Sales].[SalesOrderHeader] H select * from [Sales].[CreditCard] where year([ModifiedDate]) = 2007 S SELECT h.SalesOrderID, h.[CreditCardID], s.[CardType] FROM [Sales].[SalesOrderHeader] h JOIN [Sales].[CreditCard] s ON s.CreditCardID = h.CreditCardID WHERE [CardType] = 'Vista' AND s.ModifiedDate BETWEEN '2012-10-01' AND '2012-10-31' --3) --4) Create UDF that accepts start date and end date. The function will return the number of sales (using vista credit cards) that took place --between the start date and end date entered by the user --SELECT COUNT (*) AS CountOfSales --FROM [Sales].[SalesOrderHeader] h --JOIN [Sales].[CreditCard] s --ON s.CreditCardID = h.CreditCardID --WHERE [CardType] = 'Vista' --AND s.ModifiedDate BETWEEN '2007-01-01' AND '2007-10-31'; CREATE FUNCTION dbo.Fx_CountOfSales ( @Startdate datetime, @EndDate datetime ) RETURNS int AS BEGIN DECLARE @Fx_CountOfSales AS int SET @Fx_CountOfSales = ( SELECT COUNT (*) AS CountOfSales FROM [Sales].[SalesOrderHeader] h JOIN [Sales].[CreditCard] s ON s.CreditCardID = h.CreditCardID WHERE [CardType] = 'Vista' AND s.ModifiedDate BETWEEN '2007-01-01' AND '2007-10-31' ) RETURN @Fx_CountOfSales END --5) Using the SalesOrderHeader table- Find out how much revenue (TotalDue) was brought in by the North American Territory Group from 2002 --through 2004 select * FROM [AdventureWorks2008R2].[Sales].[CreditCard] SELECT [TotalDue] FROM [Sales].[SalesOrderHeader] WHERE TerritoryID IN (1,2,3,4,5,6) --AND [OrderDate] BETWEEN 2005-01-01 AND 2007-12-31 ORDER BY ModifiedDate select * from [Sales].[SalesTerritory] s SELECT h.[TotalDue] FROM [Sales].[SalesOrderHeader] h INNER JOIN [Sales].[SalesTerritory] s ON h.[TerritoryID] = s.TerritoryID WHERE h.TerritoryID IN (1,2,3,4,5,6) AND h.[OrderDate] BETWEEN YEAR(2005) AND YEAR(2007) --6) What is the sales taxrate, StateProvinceCode and CountryRegionCode for Texas SELECT * FROM [Sales].[SalesTaxRate] SELECT * FROM [Sales].[SalesTerritory] t ORDER BY ModifiedDate select * from [Sales].[SalesTaxRate] s SELECT s.[TaxRate], s.[StateProvinceID], t.[CountryRegionCode] FROM [Sales].[SalesTaxRate] s JOIN [Sales].[SalesTerritory] t ON s.[ModifiedDate] = t.[ModifiedDate] WHERE s.[Name] Like 'Texas%' --7) Store the information from Q5 in a variable DECLARE @GrandTotalDue MONEY SET @GrandTotalDue = ( SELECT h.[TotalDue] FROM [Sales].[SalesOrderHeader] h INNER JOIN [Sales].[SalesTerritory] s ON h.[TerritoryID] = s.TerritoryID WHERE h.TerritoryID IN (1,2,3,4,5,6) AND h.[OrderDate] BETWEEN YEAR(2005) AND YEAR(2007) ) SELECT @GrandTotalDue as GrandTotal --8) Create A UDF that accepts the State Province and returns the associated sales taxrate. StateProvince Code and Country Region Code CREATE FUNCTION Fx_Utility ( @StateProvinceID int ) RETURNS TABLE AS RETURN ( SELECT s.[TaxRate], s.[StateProvinceID], t.[CountryRegionCode] FROM [Sales].[SalesTaxRate] s JOIN [Sales].[SalesTerritory] t ON s.[ModifiedDate] = t.[ModifiedDate] WHERE s.[Name] Like '%@State%' ) SELECT * FROM Fx_Utility('California') --9) Show a list of Product Colors. For each color show how many sales details there are and the total sales Amount(Unit*OrderQty) --Only show colors with a Total Sales Amount more than 50,000.00 and eliminate the product that do not have a color; --SELECT * --FROM [Production].[Product] p --SELECT * --FROM [Sales].[SalesOrderDetail] s SELECT DISTINCT(p.color) AS Color, COUNT(s.SalesOrderID) AS OrderID, SUM(s.[UnitPrice]*s.OrderQty) AS TotalSalesAmt FROM [Sales].[SalesOrderDetail] S JOIN [Production].[Product] p ON s.[ProductID] = p.[ProductID] WHERE p.Color IS NOT NULL GROUP BY (p.color) HAVING SUM(s.[UnitPrice]*s.OrderQty) > 50000