Need to add a Budget calculation

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 a Budget calculation

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-02-18 : 09:12:15

I have the following SQL as a Stored procedure that returns the sales per category group by date selected. I would like to be able to add the budget data to this and create a result that would indicated how much over or under budget the item is for a certain month, by amount and if possible by percent. I am not sure how to make this integration. I don't know how to start on this.

THe SP is:

USE [PSS]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
--[_USP_QuotesSummary_ByWeek] '01/04/2009', '01/08/2009', 'ALL', 'ALL' , 'ALL', '1'

CREATE Proc [dbo].[_USP_QuotesSummary_ByWeek]
@StartDate varchar(25), @EndDate varchar(25), @CustName varchar(250), @Department varchar(4000), @Category varchar(4000),
@SalesTypes Varchar(50)
as

DECLARE @SQL VARCHAR(8000)

if @CustName <> 'ALL'
begin
set @CustName = replace(@CustName,',', ''',''')
set @CustName = '''' + @CustName + ''''
end

if @Department <> 'ALL'
begin
set @Department = replace(@Department,',', ''',''')
set @Department = '''' + @Department + ''''
end

if @Category <> 'ALL'
begin
set @Category = replace(@Category,',', ''',''')
set @Category = '''' + @Category + ''''
end

set @SalesTypes = replace(@SalesTypes,',', ''',''')
set @SalesTypes = '''' + @SalesTypes + ''''

SET @SQL = 'SELECT SOP10200.XTNDPRCE AS Price, SOP10200.QUANTITY AS quantity,
DATEADD(WK, DATEDIFF(WK, 6, SOP10100.DOCDATE), 6) AS ID,
IV40600.UserCatLongDescr as CatD,
SOP10200.ITEMNMBR + CHAR(13) + IV00101.ITEMDESC + CHAR(13) + ''Par Level:'' AS ITEMDESC, IV00101.ITMGEDSC
FROM dbo.SOP10200 AS SOP10200 INNER JOIN
dbo.IV00101 AS IV00101 ON SOP10200.ITEMNMBR = IV00101.ITEMNMBR INNER JOIN
dbo.SOP10100 AS SOP10100 ON SOP10200.SOPTYPE = SOP10100.SOPTYPE AND SOP10200.SOPNUMBE = SOP10100.SOPNUMBE INNER JOIN
dbo.RM00101 ON SOP10100.CUSTNMBR = dbo.RM00101.CUSTNMBR INNER JOIN
dbo.SOP10106 ON SOP10200.SOPTYPE = dbo.SOP10106.SOPTYPE AND SOP10200.SOPNUMBE = dbo.SOP10106.SOPNUMBE
inner join dbo.IV40600 on IV40600.USCATVAL = IV00101.ITMGEDSC
Where SOP10100.DOCDATE between ''' + @StartDate + ''' and ''' + @EndDate + ''''

IF @Category <> 'ALL'
BEGIN
SET @SQL = @SQL + ' AND IV40600.Usercatlongdescr in (' + @Category+ ')'
END

IF @Department <> 'ALL'
BEGIN
SET @SQL = @SQL + ' AND sop10106.USRDEF05 in (' + @Department + ')'
END

IF @CustName <> 'ALL'
BEGIN
SET @SQL = @SQL + ' AND dbo.rm00101.CUSTNAME in (' + @CustName + ')'
END

IF @SalesTypes <> ''
BEGIN
SET @SQL = @SQL + ' AND SOP10200.SOPTYPE in (' + @SalesTypes + ')'
END

SET @SQL = @SQL + ' order by IV00101.ITMGEDSC, IV00101.ITEMDESC, DATEADD(WK,DATEDIFF(WK,6,SOP10100.DOCDATE),6)'
--print @SQL
EXEC (@SQL)

GO

The table that has the Budget amount is this: THe Budget amount is for each month per month. Budget date from meaning that each month the budget is that amount, until supersceded by a new month.

CustomerBudgetID CUSTNMBR USCATVAL BudgetDateFrom BudgetDateTo BudgetAmount Dept
827 BHCC02 Diagnostic 2007-01-01 00:00:00.000 5000-01-01 00:00:00.000 29.36 NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 09:31:35

you need post some sample data and then explain what you want rather than posting the query

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-02-18 : 10:16:22

Visak, this is the data from the stored procedure. The field ITMGEDSC is used as the product category.

"Price" "Quantity" "ID" "CatID"
5.63000 0.50000 2009-04-12 00:00:00.000 Baked Goods "Description" RB-CM Mini Corn Muffins Per Dz "ITMGEDSC" Par Level: BakedGood

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 10:23:07

quote:
Originally posted by AdamWest

Visak, this is the data from the stored procedure. The field ITMGEDSC is used as the product category.

"Price" "Quantity" "ID" "CatID"
5.63000 0.50000 2009-04-12 00:00:00.000 Baked Goods "Description" RB-CM Mini Corn Muffins Per Dz "ITMGEDSC" Par Level: BakedGood

Read the posted link and give data in required format if you really want some body to help you out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources