Need to add Variance to this SQL

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

Need to add Variance to this SQL

Author

Topic

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-11-30 : 16:36:55

HI I have this SQL statement as a stored procedure, that is used in a SSRS report to show which items have gone over budget (the top 5) for a customer. I need now to add a variance column, to show the difference between the amount over budget and the budget amount.

USE [SUDatabase]
GO
/****** Object: StoredProcedure [dbo].[_DASHBOARD_USP_CHART_4_TOP_ITEMS] Script Date: 11/30/2009 15:55:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* --------------------------------------------------------------------------------------------------------*/
ALTER PROCEDURE [dbo].[_DASHBOARD_USP_CHART_4_TOP_ITEMS]
(
@CUSTNMBR VARCHAR(50),
@CURRDATE DATETIME
)
AS
/*
UNIT TESTING
============
EXEC [dbo].[_DASHBOARD_USP_CHART_4_TOP_ITEMS] @custnmbr = 'northern ', @currdate = '2009-01-01'
*/
BEGIN

DECLARE @Custnmbr1 varchar(50)
SELECT TOP 1 @Custnmbr1 = CUSTNMBR FROM RM00101 WHERE CUSTNAME = @CUSTNMBR

SELECT TOP 5 v.ITEMNMBR
, v.ITEMDESC
, ISNULL(v.EXPENSE, 0) AS EXPENSE
, [AVG6M] =
(
SELECT ISNULL(AVG(a.EXPENSE), 0)
FROM dbo.[View_Item_Expense] a
WHERE v.ITEMNMBR = a.ITEMNMBR
AND a.CUSTNMBR = @Custnmbr1
AND
(
(a.DOC_YEAR = YEAR(DATEADD(MONTH, -1, @CURRDATE)) AND a.DOC_MONTH = MONTH(DATEADD(MONTH, -1, @CURRDATE)))
OR (a.DOC_YEAR = YEAR(DATEADD(MONTH, -2, @CURRDATE)) AND a.DOC_MONTH = MONTH(DATEADD(MONTH, -2, @CURRDATE)))
OR (a.DOC_YEAR = YEAR(DATEADD(MONTH, -3, @CURRDATE)) AND a.DOC_MONTH = MONTH(DATEADD(MONTH, -3, @CURRDATE)))
OR (a.DOC_YEAR = YEAR(DATEADD(MONTH, -4, @CURRDATE)) AND a.DOC_MONTH = MONTH(DATEADD(MONTH, -4, @CURRDATE)))
OR (a.DOC_YEAR = YEAR(DATEADD(MONTH, -5, @CURRDATE)) AND a.DOC_MONTH = MONTH(DATEADD(MONTH, -5, @CURRDATE)))
OR (a.DOC_YEAR = YEAR(DATEADD(MONTH, -6, @CURRDATE)) AND a.DOC_MONTH = MONTH(DATEADD(MONTH, -6, @CURRDATE)))
)
)
--, LEFT(DATENAME(MONTH,DATEADD(MONTH, v.DOC_MONTH - 1, 0)),3) AS MONTH_NAME
--, v.DOC_YEAR
--, v.CUSTNMBR
--, v.CUSTNAME
FROM dbo.[View_Item_Expense] v
WHERE v.DOC_YEAR = YEAR(@CURRDATE) AND v.DOC_MONTH = MONTH(@CURRDATE) AND v.CUSTNMBR = @Custnmbr1
ORDER BY v.EXPENSE DESC

END

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2009-12-01 : 02:36:12

first of all, tell us your table and columns, then we can help you. Giving us the above does not tell us anything about where your budget and expense table come from... So it is not possible to help you.

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-12-01 : 11:29:33

OK the base data comes from here:

USE [L]
GO
/****** Object: StoredProcedure [dbo].[_DASHBOARD_USP_CHART_4_1] Script Date: 12/01/2009 11:29:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[_DASHBOARD_USP_CHART_4_1]
@CUSTNMBR VARCHAR(50),
@CURRDATE DATETIME
AS

CREATE TABLE #TEMP (CUSTNMBR VARCHAR(100), BUDGET_MONTH INT, BUDGET_YEAR INT, BUDGET_AMOUNT MONEY)

DECLARE @USCATVAL VARCHAR(50), @I INT, @SQL VARCHAR(100), @Custnmbr1 varchar(50)

select top 1 @Custnmbr1 = Custnmbr from rm00101 where custname = @CUSTNMBR

SET @I = 1
WHILE (@I <= 12)
BEGIN

INSERT INTO #TEMP
SELECT @CUSTNMBR, @I, YEAR(@CURRDATE), Sum(ISNULL(BUDGETAMOUNT,0)) BUDGETAMOUNT FROM CustomerBudget WHERE CUSTNMBR = @Custnmbr1 AND CONVERT(DATETIME,CONVERT(VARCHAR(2),@I) + '-' + '01-' + CONVERT(VARCHAR(4),YEAR(@CURRDATE))) BETWEEN BUDGETDATEFROM AND BUDGETDATETO
Group by CUSTNMBR

SET @I = @I + 1
END

DECLARE @temp2 as TABLE
(
ID int identity(1,1),
CUSTNMBR varchar(100),
[MonthName] varchar(3),
BUDGET_AMOUNT money,
EXPENSE money
)

INSERT INTO @temp2 (CUSTNMBR, [MonthName], BUDGET_AMOUNT, EXPENSE)
SELECT A.CUSTNAME, left(datename(month,dateadd(month, A.DOC_MONTH - 1, 0)),3) as MonthName, B.BUDGET_AMOUNT, A.EXPENSE
FROM dbo.View_CHART1 A Right Outer JOIN #TEMP B ON
A.CUSTNAME = B.CUSTNMBR AND A.DOC_MONTH = B.BUDGET_MONTH AND A.DOC_YEAR = B.BUDGET_YEAR
WHERE A.DOC_YEAR = YEAR(@CURRDATE) AND A.CUSTNAME = @CUSTNMBR ORDER BY BUDGET_MONTH

DECLARE @iterator int, @id int, @budget_ytd money, @expense_ytd money
SET @iterator = 1
SET @budget_ytd = 0
SET @expense_ytd = 0
SELECT @id = ID FROM @temp2 WHERE [MonthName] = left(datename(month,dateadd(month, MONTH(@CURRDATE) - 1, 0)),3)

WHILE (@iterator <= @id)
BEGIN

SELECT @budget_ytd = (@budget_ytd + ISNULL(BUDGET_AMOUNT, 0)),
@expense_ytd = (@expense_ytd + ISNULL(EXPENSE, 0))
FROM @temp2
WHERE ID = @iterator

SET @iterator = @iterator + 1
END

SELECT CUSTNMBR
, [MonthName]
, BUDGET_AMOUNT
, EXPENSE
, @budget_ytd as [BUDGET_YTD]
, @expense_ytd as [EXPENSE_YTD]
FROM @temp2

DROP TABLE #TEMP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources