Solved

Need help with a Stored Procedure

Posted on 2011-02-25
10
783 Views
Last Modified: 2012-05-11
I've got this wonderful Stored Procedure that is about to make me punt my laptop.

Yesterday, it worked fine.  This morning I made one small tweak (changing a parameter to a variable), and now it runs for well over five minutes without returning anything.

When I take out the SQL and run it directly, it runs in under a minute.  When I comment out the SQL, the Stored Procedure runs just fine in under a second.

Could someone please review this Stored Procedure and look to see if you can find any glaring errors ?  

Thanks!

- Flynn
--DROP PROCEDURE [dbo].[stp_Crystal_EndingInventoryTotals_AllStores_FGK]
--GO
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
--CREATE PROCEDURE [dbo].[stp_Crystal_EndingInventoryTotals_AllStores_FGK]
ALTER PROCEDURE [dbo].[stp_Crystal_EndingInventoryTotals_AllStores_FGK]
		@InventoryDateParam					smalldatetime = null,
		@IncludeNullDept					bit = 0--,
		
		--@YESTERDAY							smalldatetime , 
		--@RTNCODE							char 
AS

DECLARE 
		@YESTERDAY							smalldatetime , 
		@RTNCODE							char 
/*
Create table #tblStoreAttributes				(
	StoreNbr						smallint,
	[Address]						varchar(50),
	City							varchar(50),
	[State]							char(2),
	PostalCode						char(10),
	County							char(30),
	CompanyStoreFlag				bit,
	WPLAreaName						varchar(50)
)
*/
--Retrieve store attributes
--Insert Into #tblStoreAttributes
--Execute @RtnCode = stp_RetrieveActiveStoreAttributes_AdBook

If @RtnCode <> 0
Begin
	Print 'Return code ' + convert(varchar(12), @RtnCode) + ' received from stp_RetrieveActiveStoreAttributes_AdBook.'
	Return 5
End

	Select @Yesterday = Dateadd(d, -1, @InventoryDateParam)	
	
	;WITH RUN1 AS (
	SELECT ID,StoreConfiguration_StoreNbr, InventoryDate 
	FROM Inventory.HistoricalInventory 
	WHERE InventoryDate = @Yesterday)
	, RUN2 AS (
	SELECT ID,StoreConfiguration_StoreNbr, InventoryDate 
	FROM Inventory.HistoricalInventory 
	WHERE InventoryDate = @InventoryDateParam)
	, ST AS (
	SELECT ID,StoreConfiguration_StoreNbr, InventoryDate FROM RUN1
	UNION ALL
	SELECT ID,StoreConfiguration_StoreNbr, InventoryDate FROM RUN2)
	, GTIN AS (
    SELECT DISTINCT I.GTINCode, V.StoreConfiguration_StoreNbr AS STORENBR
	FROM [Control].SBTVendors V WITH (NOLOCK)
		INNER JOIN Inventory.ItemAttributes I WITH (NOLOCK)
			ON I.VendorNbr = V.VendorNbr
			AND I.WhseCode = 0
			,ST
	WHERE V.StoreConfiguration_StoreNbr = ST.StoreConfiguration_StoreNbr		
	GROUP BY I.GTINCode, V.StoreConfiguration_StoreNbr 
	UNION 
	SELECT DISTINCT I.GTINCode, S.StoreNbr
	FROM [Control].ExclusionVendors E WITH (NOLOCK)
		INNER JOIN Inventory.ItemAttributes I WITH (NOLOCK)
			ON I.VendorNbr = E.VendorNbr
		,[Control].StoreConfiguration S 
		,ST
	WHERE S.StoreNbr = ST.StoreConfiguration_StoreNbr
	UNION 
	SELECT GTINCode, S.StoreNbr
	FROM [Control].ExclusionGTINs
		,[Control].StoreConfiguration S
	    ,ST
	WHERE S.StoreNbr = ST.StoreConfiguration_StoreNbr)
    --, ATTRIB AS (Select STORENBR,City + ',' + State as DESCR From #tblStoreAttributes) 
	, MAIN AS(
	SELECT	HI.StoreConfiguration_StoreNbr AS StoreNbr, HI.InventoryDate,
			(CASE WHEN HI.InventoryDate <> DATEADD(DAY,-1,@InventoryDateParam) 
			      THEN Sum(HI.BalanceOnHand) ELSE 0 END)							as CQOH,
			(CASE WHEN HI.InventoryDate <> DATEADD(DAY,-1,@InventoryDateParam) 
			      THEN Sum(HI.BalanceOnHand * Cast(HI.Cost as money)) ELSE 0 END)	as CETC,
			(CASE WHEN HI.InventoryDate <> DATEADD(DAY,-1,@InventoryDateParam) 
			      THEN Sum(HI.BalanceOnHand * Cast(HI.Retail as money)) ELSE 0 END)	as CETR,
			(CASE WHEN HI.InventoryDate = DATEADD(DAY,-1,@InventoryDateParam) 
			      THEN Sum(HI.BalanceOnHand) ELSE 0 END)							as PQOH,
			(CASE WHEN HI.InventoryDate = DATEADD(DAY,-1,@InventoryDateParam) 
			      THEN Sum(HI.BalanceOnHand * Cast(HI.Cost as money)) ELSE 0 END)	as PETC,
			(CASE WHEN HI.InventoryDate = DATEADD(DAY,-1,@InventoryDateParam) 
			      THEN Sum(HI.BalanceOnHand * Cast(HI.Retail as money)) ELSE 0 END)	as PETR
	FROM Inventory.HistoricalInventory as HI with (nolock)
	LEFT OUTER JOIN GTIN ON GTIN.STORENBR = HI.StoreConfiguration_StoreNbr 
	AND HI.GTINCODE = GTIN.GTINCODE
	, ST
	WHERE HI.SourceFlag = 'U'
	AND HI.Department IN ('0','6','H') 
	AND HI.Category NOT IN (620,621) 
	AND ST.StoreConfiguration_StoreNbr = HI.StoreConfiguration_StoreNbr
	AND ST.InventoryDate = HI.InventoryDate
	AND ST.ID = HI.ID
	AND GTIN.GTINCode IS NULL
	GROUP BY HI.StoreConfiguration_StoreNbr,HI.InventoryDate) 
	/* 
	,PRETTYITUP AS ( 
	SELECT MAIN.STORENBR, DESCR, SUM(CQOH) AS CQOH, SUM(CETC) AS CETC, 
	SUM(CETR) AS CETR, SUM(PQOH) AS PQOH, SUM(PETC) AS PETC, SUM(PETR) AS PETR 
	FROM MAIN LEFT OUTER JOIN ATTRIB ON MAIN.StoreNbr = ATTRIB.STORENBR
	GROUP BY MAIN.STORENBR, DESCR)
	SELECT STORENBR, DESCR, PQOH, CQOH, (CQOH - PQOH) AS QCHANGE, PETC, CETC
	, (CETC - PETC) AS CCHANGE, CETR
	FROM PRETTYITUP ORDER BY 1
	*/
	,PRETTYITUP AS ( 
	SELECT MAIN.STORENBR
	--, DESCR
	, SUM(CQOH) AS CQOH, SUM(CETC) AS CETC, 
	SUM(CETR) AS CETR, SUM(PQOH) AS PQOH, SUM(PETC) AS PETC, SUM(PETR) AS PETR 
	FROM MAIN --LEFT OUTER JOIN ATTRIB ON MAIN.StoreNbr = ATTRIB.STORENBR
	GROUP BY MAIN.STORENBR
	--, DESCR
	)
	SELECT STORENBR
	--, DESCR
	, PQOH, CQOH, (CQOH - PQOH) AS QCHANGE, PETC, CETC, (CETC - PETC) AS CCHANGE, CETR
	FROM PRETTYITUP ORDER BY 1
	
Return 0

Open in new window

0
Comment
Question by:Flynnster
  • 5
  • 2
10 Comments
 
LVL 11

Expert Comment

by:jasonduan
Comment Utility
This might be something to do with "Parameter Sniffing".

http://www.databasejournal.com/features/mssql/article.php/3841271/T-SQL-Best-Practices--Parameter-Sniffing.htm

You can try this:
ALTER PROCEDURE [dbo].[stp_Crystal_EndingInventoryTotals_AllStores_FGK]
		@InventoryDateParam_IN	smalldatetime = null,
		@IncludeNullDept_IN	bit = 0
AS

	DECLARE 
		@InventoryDateParam	smalldatetime,
		@IncludeNullDept		bit,
		@YESTERDAY		smalldatetime , 
		@RTNCODE			char 

	SET @InventoryDateParam = @InventoryDateParam_IN
	SET @IncludeNullDept = @IncludeNullDept_IN

         .... the rest of your procedure ...

Open in new window

0
 

Author Comment

by:Flynnster
Comment Utility
Unfortunately, it is still running with no resulting rows as of thre minutes...so I'm not certain this is the issue.  However, I will review the article in the link you sent me!

- Flynn
0
 

Author Comment

by:Flynnster
Comment Utility
Ok, I'm now certain that it *IS* a case of parameter sniffing.  I just went into the code, commented out the declaration statements for the input parameters, and changed the private variables within the SQL to literal strings.  It ran in under a minute.

However, I am still unable to get it working with the common workaround of declaring the parameters then using SET to assign their value to a local private variable, as jason had suggested.

Any thoughts ?  -Flynn
0
 

Author Comment

by:Flynnster
Comment Utility
Also, I am using 2005, so using an OPTION for OPTIMIZER UNKNOWN won't work here :(

- Flynn
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 7

Accepted Solution

by:
rmm2001 earned 500 total points
Comment Utility
A few things. I explicitly defined the joins. It seemed to work better that way. All around.

For ST, you can get rid of RUN1 and RUN2 and combine that into one statement using the BETWEEN command.

Down a little bit on MAIN you select a "HI.InvetoryDate". But in PrettyItUp or StoreNbr it's not called in the other PrettyItUp, so you can completely get rid of that. It's also assumed since your inputs are a date range. So I summed it up and rolled everything into MAIN

So that's what the first one is.
ALTER PROCEDURE [dbo].[stp_Crystal_EndingInventoryTotals_AllStores_FGK]
		@InventoryDateParam					smalldatetime = null,
		@IncludeNullDept					bit = 0--,
		
		--@YESTERDAY							smalldatetime , 
		--@RTNCODE							char 
AS

DECLARE 
		@YESTERDAY							smalldatetime , 
		@RTNCODE							char 
/*
Create table #tblStoreAttributes				(
	StoreNbr						smallint,
	[Address]						varchar(50),
	City							varchar(50),
	[State]							char(2),
	PostalCode						char(10),
	County							char(30),
	CompanyStoreFlag				bit,
	WPLAreaName						varchar(50)
)
*/
--Retrieve store attributes
--Insert Into #tblStoreAttributes
--Execute @RtnCode = stp_RetrieveActiveStoreAttributes_AdBook

If @RtnCode <> 0
Begin
	Print 'Return code ' + convert(varchar(12), @RtnCode) + ' received from stp_RetrieveActiveStoreAttributes_AdBook.'
	Return 5
End

	Select @Yesterday = Dateadd(d, -1, @InventoryDateParam)	
	
	;WITH ST AS 
	(
	    SELECT ID,StoreConfiguration_StoreNbr, InventoryDate 
	    FROM Inventory.HistoricalInventory 
	    WHERE InventoryDate BETWEEN @Yesterday AND @InventoryDateParam
	)
	,GTIN AS 
	(
        SELECT DISTINCT I.GTINCode, V.StoreConfiguration_StoreNbr AS STORENBR
	    FROM [Control].SBTVendors V WITH (NOLOCK)
		INNER JOIN Inventory.ItemAttributes I WITH (NOLOCK) ON I.VendorNbr = V.VendorNbr AND I.WhseCode = 0
	    JOIN ST ON ST.StoreConfiguration_StoreNbr = V.StoreConfiguration_StoreNbr
	    WHERE V.StoreConfiguration_StoreNbr = ST.StoreConfiguration_StoreNbr		
	    UNION 
	    SELECT DISTINCT I.GTINCode, S.StoreNbr
	    FROM [Control].ExclusionVendors E WITH (NOLOCK)
	    INNER JOIN Inventory.ItemAttributes I WITH (NOLOCK) ON I.VendorNbr = E.VendorNbr
	    JOIN ST ON ST.StoreConfiguration_StoreNbr = E.StoreConfiguration_StoreNbr
	    UNION 
	    SELECT E.GTINCode, S.StoreNbr
	    FROM [Control].ExclusionGTINs E
	    JOIN [Control].StoreConfiguration S ON S.GTINCode = E.GTINCode
	    JOIN ST ON ST.StoreConfiguration_StoreNbr = S.StoreConfiguration_StoreNbr
	)
    --, ATTRIB AS (Select STORENBR,City + ',' + State as DESCR From #tblStoreAttributes) 
	, MAIN AS
	(
	    SELECT	HI.StoreConfiguration_StoreNbr AS StoreNbr, 
			SUM(CASE WHEN HI.InventoryDate <> DATEADD(DAY,-1,@InventoryDateParam) 
			      THEN Sum(HI.BalanceOnHand) ELSE 0 END))	as CQOH,
			SUM((CASE WHEN HI.InventoryDate <> DATEADD(DAY,-1,@InventoryDateParam) 
			      THEN Sum(HI.BalanceOnHand * Cast(HI.Cost as money)) ELSE 0 END))	as CETC,
			SUM((CASE WHEN HI.InventoryDate <> DATEADD(DAY,-1,@InventoryDateParam) 
			      THEN Sum(HI.BalanceOnHand * Cast(HI.Retail as money)) ELSE 0 END))	as CETR,
			SUM((CASE WHEN HI.InventoryDate = DATEADD(DAY,-1,@InventoryDateParam) 
			      THEN Sum(HI.BalanceOnHand) ELSE 0 END))	as PQOH,
			SUM((CASE WHEN HI.InventoryDate = DATEADD(DAY,-1,@InventoryDateParam) 
			      THEN Sum(HI.BalanceOnHand * Cast(HI.Cost as money)) ELSE 0 END))	as PETC,
			SUM((CASE WHEN HI.InventoryDate = DATEADD(DAY,-1,@InventoryDateParam) 
			      THEN Sum(HI.BalanceOnHand * Cast(HI.Retail as money)) ELSE 0 END))	as PETR
	    FROM Inventory.HistoricalInventory as HI with (nolock)
	    LEFT OUTER JOIN GTIN ON GTIN.STORENBR = HI.StoreConfiguration_StoreNbr 
	                        AND HI.GTINCODE = GTIN.GTINCODE
	    LEFT JOIN ST ON ST.StoreConfiguration_StoreNbr = HI.StoreConfiguration_StoreNbr 
	                AND ST.InventoryDate = HI.InventoryDate
	                AND ST.ID = HI.ID
	    WHERE HI.SourceFlag = 'U'
	      AND HI.Department IN ('0','6','H') 
	      AND HI.Category NOT IN (620,621) 
	      AND GTIN.GTINCode IS NULL
	    GROUP BY HI.StoreConfiguration_StoreNbr
	) 
	SELECT *
	FROM MAIN
	
Return 0

Open in new window


If you're still having problems, I would highly consider rewriting the query. CTEs are nice but not always the best choice.

I don't know the amount of data you're querying or the schema of your tables. So this one may work a little better depending on what you have. If you want - paste the scripts and maybe tell me how much data you're dealing with. So that is #2
CREATE TABLE #ST
(
    ID VARCHAR(50),
    StoreConfiguration_StoreNbr VARCHAR(50),
    InventoryDate SMALLDATETIME
)	

CREATE NONCLUSTERED INDEX IX_ST_StoreConfiguration_StoreNbr ON #ST (StoreConfiguration_StoreNbr)

INSERT #ST
SELECT ID,StoreConfiguration_StoreNbr, InventoryDate 
FROM Inventory.HistoricalInventory 
WHERE InventoryDate BETWEEN @Yesterday AND @InventoryDateParam

CREATE TABLE #GTIN
(
    GTINCode VARCHAR(50),
    STORENBR VARCHAR(50)
)

CREATE NONCLUSTERED INDEX IX_GTIN_STORENMR_GTINCode ON #GTIN (STORENBR, GTINCode)

INSERT #GTIN
SELECT DISTINCT I.GTINCode, V.StoreConfiguration_StoreNbr AS STORENBR
FROM [Control].SBTVendors V WITH (NOLOCK)
INNER JOIN Inventory.ItemAttributes I WITH (NOLOCK) ON I.VendorNbr = V.VendorNbr AND I.WhseCode = 0
JOIN ST ON ST.StoreConfiguration_StoreNbr = V.StoreConfiguration_StoreNbr
WHERE V.StoreConfiguration_StoreNbr = ST.StoreConfiguration_StoreNbr		
UNION 
SELECT DISTINCT I.GTINCode, S.StoreNbr
FROM [Control].ExclusionVendors E WITH (NOLOCK)
INNER JOIN Inventory.ItemAttributes I WITH (NOLOCK) ON I.VendorNbr = E.VendorNbr
JOIN ST ON ST.StoreConfiguration_StoreNbr = E.StoreConfiguration_StoreNbr
UNION 
SELECT E.GTINCode, S.StoreNbr
FROM [Control].ExclusionGTINs E
JOIN [Control].StoreConfiguration S ON S.GTINCode = E.GTINCode
JOIN ST ON ST.StoreConfiguration_StoreNbr = S.StoreConfiguration_StoreNbr


SELECT	HI.StoreConfiguration_StoreNbr AS StoreNbr, 
	SUM(CASE WHEN HI.InventoryDate <> DATEADD(DAY,-1,@InventoryDateParam) 
	      THEN Sum(HI.BalanceOnHand) ELSE 0 END))	as CQOH,
	SUM((CASE WHEN HI.InventoryDate <> DATEADD(DAY,-1,@InventoryDateParam) 
	      THEN Sum(HI.BalanceOnHand * Cast(HI.Cost as money)) ELSE 0 END))	as CETC,
	SUM((CASE WHEN HI.InventoryDate <> DATEADD(DAY,-1,@InventoryDateParam) 
	      THEN Sum(HI.BalanceOnHand * Cast(HI.Retail as money)) ELSE 0 END))	as CETR,
	SUM((CASE WHEN HI.InventoryDate = DATEADD(DAY,-1,@InventoryDateParam) 
	      THEN Sum(HI.BalanceOnHand) ELSE 0 END))	as PQOH,
	SUM((CASE WHEN HI.InventoryDate = DATEADD(DAY,-1,@InventoryDateParam) 
	      THEN Sum(HI.BalanceOnHand * Cast(HI.Cost as money)) ELSE 0 END))	as PETC,
	SUM((CASE WHEN HI.InventoryDate = DATEADD(DAY,-1,@InventoryDateParam) 
	      THEN Sum(HI.BalanceOnHand * Cast(HI.Retail as money)) ELSE 0 END))	as PETR
FROM Inventory.HistoricalInventory as HI with (nolock)
LEFT OUTER JOIN GTIN ON GTIN.STORENBR = HI.StoreConfiguration_StoreNbr 
                    AND HI.GTINCODE = GTIN.GTINCODE
LEFT JOIN ST ON ST.StoreConfiguration_StoreNbr = HI.StoreConfiguration_StoreNbr 
            AND ST.InventoryDate = HI.InventoryDate
            AND ST.ID = HI.ID
WHERE HI.SourceFlag = 'U'
  AND HI.Department IN ('0','6','H') 
  AND HI.Category NOT IN (620,621) 
  AND GTIN.GTINCode IS NULL
GROUP BY HI.StoreConfiguration_StoreNbr


DROP TABLE #ST
DROP TABLE #GTIN

Open in new window


#3 is me getting rid of the unnecessary join to ST in "MAIN" since you don't need it I don't think. Since ST is built off of Inventory.HistoricalInventory, you can just put the where between date clause in the "Main" query instead of joining to that table.  
CREATE TABLE #ST
(
    ID VARCHAR(50),
    StoreConfiguration_StoreNbr VARCHAR(50),
    InventoryDate SMALLDATETIME
)	

CREATE NONCLUSTERED INDEX IX_ST_StoreConfiguration_StoreNbr ON #ST (StoreConfiguration_StoreNbr)

INSERT #ST
SELECT ID,StoreConfiguration_StoreNbr, InventoryDate 
FROM Inventory.HistoricalInventory 
WHERE InventoryDate BETWEEN @Yesterday AND @InventoryDateParam

CREATE TABLE #GTIN
(
    GTINCode VARCHAR(50),
    STORENBR VARCHAR(50)
)

CREATE NONCLUSTERED INDEX IX_GTIN_STORENMR_GTINCode ON #GTIN (STORENBR, GTINCode)

INSERT #GTIN
SELECT DISTINCT I.GTINCode, V.StoreConfiguration_StoreNbr AS STORENBR
FROM [Control].SBTVendors V WITH (NOLOCK)
INNER JOIN Inventory.ItemAttributes I WITH (NOLOCK) ON I.VendorNbr = V.VendorNbr AND I.WhseCode = 0
JOIN ST ON ST.StoreConfiguration_StoreNbr = V.StoreConfiguration_StoreNbr
WHERE V.StoreConfiguration_StoreNbr = ST.StoreConfiguration_StoreNbr		
UNION 
SELECT DISTINCT I.GTINCode, S.StoreNbr
FROM [Control].ExclusionVendors E WITH (NOLOCK)
INNER JOIN Inventory.ItemAttributes I WITH (NOLOCK) ON I.VendorNbr = E.VendorNbr
JOIN ST ON ST.StoreConfiguration_StoreNbr = E.StoreConfiguration_StoreNbr
UNION 
SELECT E.GTINCode, S.StoreNbr
FROM [Control].ExclusionGTINs E
JOIN [Control].StoreConfiguration S ON S.GTINCode = E.GTINCode
JOIN ST ON ST.StoreConfiguration_StoreNbr = S.StoreConfiguration_StoreNbr


SELECT	HI.StoreConfiguration_StoreNbr AS StoreNbr, 
	SUM(CASE WHEN HI.InventoryDate <> DATEADD(DAY,-1,@InventoryDateParam) 
	      THEN Sum(HI.BalanceOnHand) ELSE 0 END))	as CQOH,
	SUM((CASE WHEN HI.InventoryDate <> DATEADD(DAY,-1,@InventoryDateParam) 
	      THEN Sum(HI.BalanceOnHand * Cast(HI.Cost as money)) ELSE 0 END))	as CETC,
	SUM((CASE WHEN HI.InventoryDate <> DATEADD(DAY,-1,@InventoryDateParam) 
	      THEN Sum(HI.BalanceOnHand * Cast(HI.Retail as money)) ELSE 0 END))	as CETR,
	SUM((CASE WHEN HI.InventoryDate = DATEADD(DAY,-1,@InventoryDateParam) 
	      THEN Sum(HI.BalanceOnHand) ELSE 0 END))	as PQOH,
	SUM((CASE WHEN HI.InventoryDate = DATEADD(DAY,-1,@InventoryDateParam) 
	      THEN Sum(HI.BalanceOnHand * Cast(HI.Cost as money)) ELSE 0 END))	as PETC,
	SUM((CASE WHEN HI.InventoryDate = DATEADD(DAY,-1,@InventoryDateParam) 
	      THEN Sum(HI.BalanceOnHand * Cast(HI.Retail as money)) ELSE 0 END))	as PETR
FROM Inventory.HistoricalInventory as HI with (nolock)
LEFT OUTER JOIN GTIN ON GTIN.STORENBR = HI.StoreConfiguration_StoreNbr 
                    AND HI.GTINCODE = GTIN.GTINCODE
WHERE HI.SourceFlag = 'U'
  AND HI.Department IN ('0','6','H') 
  AND HI.Category NOT IN (620,621) 
  AND GTIN.GTINCode IS NULL
  WHERE BETWEEN @Yesterday AND @InventoryDateParam
GROUP BY HI.StoreConfiguration_StoreNbr


DROP TABLE #ST
DROP TABLE #G

Open in new window


So try some of those and see what happens!
0
 

Author Comment

by:Flynnster
Comment Utility
USE [StoreInventory]
GO
/****** Object:  StoredProcedure [dbo].[stp_Crystal_EndingInventoryTotals_AllStores_FGK]    Script Date: 03/01/2011 08:18:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            Flynn Kinkade      
-- Create date: 02/28/2011
-- Description:      Stored procedure to drive an
-- exception report for the current report titled
-- SPI Ending Inventory Store Totals.
-- Code takes 45-55 seconds to run.
-- =============================================
--CREATE PROCEDURE [dbo].[stp_Crystal_EndingInventoryTotals_AllStores_FGK]

ALTER PROCEDURE [dbo].[stp_Crystal_EndingInventoryTotals_AllStores_FGK]
            @InventoryDateParam_IN      smalldatetime = null
            ,@IncludeNullDept_IN      bit = 0
AS

      DECLARE
            @InventoryDateParam      smalldatetime,
            @IncludeNullDept            bit,
            @YESTERDAY            smalldatetime ,
            @RTNCODE                  char

      SET @InventoryDateParam = @InventoryDateParam_IN
      SET @IncludeNullDept = @IncludeNullDept_IN

If @RtnCode <> 0
Begin
      Print 'Return code ' + convert(varchar(12), @RtnCode) + ' received from stp_RetrieveActiveStoreAttributes_AdBook.'
      Return 5
End

      Select @Yesterday = Dateadd(d, -1, @InventoryDateParam)       
/*CREATING TABLE tblStoreAttributes */
Create table #tblStoreAttributes                        (
      StoreNbr                                    smallint,
      [Address]                                    varchar(50),
      City                                          varchar(50),
      [State]                                          char(2),
      PostalCode                                    char(10),
      County                                          char(30),
      CompanyStoreFlag                        bit,
      WPLAreaName                                    varchar(50)
)
Insert Into #tblStoreAttributes
Execute @RtnCode = stp_RetrieveActiveStoreAttributes_AdBook      
/* FINISHED CREATING TABLE tblStoreAttributes */      
/* CREATING TABLE GTIN */      
CREATE TABLE #GTIN
(
    GTINCode VARCHAR(50),
    STORENBR VARCHAR(50)
)
CREATE NONCLUSTERED INDEX IX_GTIN_StoreNbr ON #GTIN (StoreNbr)

INSERT #GTIN
SELECT DISTINCT I.GTINCode, V.StoreConfiguration_StoreNbr as StoreNbr
FROM [Control].SBTVendors V WITH (NOLOCK)
      INNER JOIN Inventory.ItemAttributes I WITH (NOLOCK)
            ON I.VendorNbr = V.VendorNbr
            AND I.WhseCode = 0       
GROUP BY I.GTINCode, V.StoreConfiguration_StoreNbr
UNION
SELECT DISTINCT I.GTINCode, S.StoreNbr
FROM [Control].ExclusionVendors E WITH (NOLOCK)
      INNER JOIN Inventory.ItemAttributes I WITH (NOLOCK)
            ON I.VendorNbr = E.VendorNbr
      ,[Control].StoreConfiguration S  
UNION
SELECT GTINCode, S.StoreNbr
FROM [Control].ExclusionGTINs
      ,[Control].StoreConfiguration S
/*FINISHED CREATING TABLE GTIN */

/*CREATING TABLE DATES */      
CREATE TABLE #DATES
(
    STORENBR VARCHAR(50),
    INVENTORYDATE SMALLDATETIME
)
CREATE NONCLUSTERED INDEX IX_DATES_StoreNbr ON #DATES (StoreNbr)
      
INSERT #DATES
SELECT DISTINCT HI.StoreConfiguration_StoreNbr AS StoreNbr, HI.InventoryDate  
FROM Inventory.HistoricalInventory as HI with (nolock)       
WHERE HI.InventoryDate = @Yesterday
UNION ALL
SELECT DISTINCT HI.StoreConfiguration_StoreNbr AS StoreNbr, HI.InventoryDate  
FROM Inventory.HistoricalInventory as HI with (nolock)       
WHERE HI.InventoryDate = @InventoryDateParam
/* FINISHED CREATING TABLE DATES */
/* Create Table HI_SUBSET */
CREATE TABLE #HI_SUBSET
( StoreNbr VARCHAR(50)
 ,DESCR VARCHAR(50)
 ,CQOH numeric
 ,CETC decimal
 ,CETR decimal
 ,PQOH numeric
 ,PETC decimal
 ,PETR decimal)
 
INSERT #HI_SUBSET
SELECT      HI.StoreConfiguration_StoreNbr as StoreNbr,
            SA.City+','+SA.[State] as DESCR,
            (CASE WHEN HI.InventoryDate <> DATEADD(DAY,-1,@InventoryDateParam)
                  THEN Sum(HI.BalanceOnHand) ELSE 0 END)                                                            as CQOH,
            (CASE WHEN HI.InventoryDate <> DATEADD(DAY,-1,@InventoryDateParam)
                  THEN ROUND(Sum(HI.BalanceOnHand * Cast(HI.Cost as money)),2) ELSE 0 END)            as CETC,
            (CASE WHEN HI.InventoryDate <> DATEADD(DAY,-1,@InventoryDateParam)
                  THEN ROUND(Sum(HI.BalanceOnHand * Cast(HI.Retail as money)),2) ELSE 0 END)      as CETR,
            (CASE WHEN HI.InventoryDate = DATEADD(DAY,-1,@InventoryDateParam)
                  THEN Sum(HI.BalanceOnHand) ELSE 0 END)                                                            as PQOH,
            (CASE WHEN HI.InventoryDate = DATEADD(DAY,-1,@InventoryDateParam)
                  THEN ROUND(Sum(HI.BalanceOnHand * Cast(HI.Cost as money)),2) ELSE 0 END)            as PETC,
            (CASE WHEN HI.InventoryDate = DATEADD(DAY,-1,@InventoryDateParam)
                  THEN ROUND(Sum(HI.BalanceOnHand * Cast(HI.Retail as money)),2) ELSE 0 END)      AS PETR
FROM Inventory.HistoricalInventory as HI with (nolock)
      LEFT OUTER JOIN #tblStoreAttributes as SA
            ON HI.StoreConfiguration_StoreNbr = SA.StoreNbr
      LEFT OUTER JOIN #GTIN X
            ON X.StoreNbr = HI.StoreConfiguration_StoreNbr
            AND X.GTINCode = HI.GTINCode
    , #DATES Z
WHERE HI.SourceFlag = 'U'
AND ((@IncludeNullDept = 1 AND HI.Department IN ('0','6','H') OR HI.Department IS NULL)
  OR (@IncludeNullDept = 0 AND HI.Department IN ('0','6','H')))  
AND HI.Category NOT IN (620,621)
AND X.GTINCode IS NULL
AND HI.StoreConfiguration_StoreNbr = Z.STORENBR
AND HI.InventoryDate = Z.InventoryDate
GROUP BY      HI.StoreConfiguration_StoreNbr,
                  HI.InventoryDate,
                  SA.City+','+SA.[State]
ORDER BY      HI.StoreConfiguration_StoreNbr,3
/* Finished creating table HI_SUBSET */
/* Run SQL for report */
SELECT A.STORENBR,A.DESCR,B.PQOH,A.CQOH,(A.CQOH - B.PQOH) AS QOH_DIFF
      ,B.PETC,A.CETC, (A.CETC - B.PETC) AS ETC_DIFF,A.CETR
FROM #HI_SUBSET A, #HI_SUBSET B
WHERE A.STORENBR = B.STORENBR
AND A.CQOH > 0
AND B.PQOH > 0      
/* Finished running SQL for report */
/*Cleanup*/      
DROP TABLE #GTIN
DROP TABLE #DATES
DROP TABLE #HI_SUBSET
DROP TABLE #tblStoreAttributes
/*End Cleanup*/
Return 0
0
 
LVL 7

Expert Comment

by:rmm2001
Comment Utility
Um isn't that what I said and posted? I think points should be assigned.
0
 

Author Comment

by:Flynnster
Comment Utility
Southmod, please award the expert his points.  Chalk it up to lack of understanding of the system on my part.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now