datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
How to fill gaps in data?

Execute the following Microsoft SQL Server T-SQL script in SSMS Query Editor to generate a monthly sales summary by categories. Since gap filling used, $0.0 will show for a month when there was no sale.

USE AdventureWorks;

GO

 

CREATE PROCEDURE procSalesDatawithGapFills ( 

  @StartDate smalldatetime,

  @EndDate smalldatetime

)

AS

BEGIN

DECLARE @RunningDate smalldatetime

DECLARE @CategoriesMonths TABLE   (     

  ProductCategoryID   tinyint,  

  OrderDate    smalldatetime   

  )             

 

SET   @RunningDate = @EndDate

 

-- Insert a single date from each month in the range

WHILE (@StartDate <= @RunningDate)    

BEGIN             

  INSERT INTO @CategoriesMonths        

  SELECT PC.ProductCategoryID,   @RunningDate   

  FROM Production.ProductCategory   PC

   

  SET @RunningDate = DATEADD(mm, -1, @RunningDate)

END;

 

-- Apply a CTE to merge the place holder and available data

WITH cteSales

AS

(

 

-- Place holder sales data ($0.00) just in case no real data

SELECT     

DS.ProductCategoryID,

PC.Name as ProductCategory,

OrderDate AS Date,

0.0 AS Sales

FROM      @CategoriesMonths DS

INNER JOIN Production.ProductCategory PC

    ON DS.ProductCategoryID=PC.ProductCategoryID

 

UNION ALL

 

-- Merge in the available data          

SELECT    

PC.ProductCategoryID,

PC.Name AS ProductCategory,

SOH.OrderDate AS [Date],

SUM(SOD.UnitPrice * SOD.OrderQty) AS Sales

FROM         Production.ProductSubCategory PSC

INNER JOIN   Production.ProductCategory PC

    ON PSC.ProductCategoryID = PC.ProductCategoryID

INNER JOIN   Production.Product P

    ON PSC.ProductSubCategoryID = P.ProductSubCategoryID

INNER JOIN   Sales.SalesOrderDetail SOD

    ON P.ProductID = SOD.ProductID

INNER JOIN   Sales.SalesOrderHeader SOH

    ON SOH.SalesOrderID = SOD.SalesOrderID

 

WHERE     (SOH.OrderDate BETWEEN @StartDate AND @EndDate)

GROUP BY SOH.OrderDate, PC.Name, PSC.Name, PC.ProductCategoryID

)

 

-- Generate summaries for each month, each category

SELECT [Year]=Year([Date]),

[Month]=Month([Date]),

ProductCategory,

TotalSales = Sum(Sales)

FROM cteSales

GROUP BY Year([Date]),Month([Date]), ProductCategory

ORDER BY Year([Date]),Month([Date]), ProductCategory

END

GO

 

-- Test the stored procedure

exec procSalesDatawithGapFills '2001-01-01','2002-04-30'

GO

 

Exam Prep 70-461
Exam 70-461