Values not being calculated - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Values not being calculated

Author

Topic

RageQuit
Starting Member

2 Posts

Posted - 2011-04-15 : 11:24:21

I'm creating a financial report and my stored procedure is returning zeros for my month-to-date column. The year-to-date column populates just fine and is a carbon copy of the month to date code except for the dates. In the code below MTDActual and MTDBudget show as zeros all the way down, while YTDActual and YTDBudget populate correctly. I've included all code that I think to be relevant. Thanks for any assistance.

SELECT

--,cast(sum([Amount]) AS numeric (10,2)) as MTDActual
,'MTDActual' = 0
,'MTDBudget' = 0
,'YTDActual' = 0
,'YTDBudget' = 0
,'BudgetVariance' = 0
,'TotalAnnualBudget' = 0
,'RemainingBudget' = 0
into #TempTableTwo
FROM [dbo].[SEIU$G_L Entry] GLE
join dbo.[SEIU$Dimension Value]DV
on dv.Code = GLE.[Global Dimension 2 Code]

group by [G_L Account No_],[Global Dimension 1 Code]
,[Global Dimension 2 Code]
,dv.[Name]
,[Transaction Type]

Union

-- This select statment collects YTD actuals and budgets amount for the department variable
-- This data is also loaded into the temporary table "#TempTableOne"
-- Year to Date (YTD) Totals
SELECT

,'MTDActual' = 0
,'MTDBudget' = 0
,CASE WHEN [Transaction Type] = 0
THEN sum(cast(isnull([Amount],0) AS numeric (10,2)))
END AS 'YTDActual'
,CASE WHEN [Transaction Type] = 1
THEN sum(cast(isnull([Amount],0) AS numeric (10,2)))
else 0
END AS 'YTDBUDGET'
,'BudgetVariance' = 1
,'TotalAnnualBudget' = 0
,'RemainingBudget' = 0
--into #TempTableTwo
FROM [dbo].[SEIU$G_L Entry] GLE
join dbo.[SEIU$Dimension Value]DV
on dv.Code = GLE.[Global Dimension 2 Code]

group by [G_L Account No_], [Global Dimension 1 Code]
,[Global Dimension 2 Code]
,dv.[Name]
,[Transaction Type]
-- ,DV.CODE

Union

,[Transaction Type] -- 0 = Actual 1 = Budget
,'YTDActual' = 0
,'YTDBudget' = 0
,CASE WHEN [Transaction Type] = 0
THEN sum(cast(isnull([Amount],0) AS numeric (10,2)))
END AS 'MTDActual'
,CASE WHEN [Transaction Type] = 1
THEN sum(cast(isnull([Amount],0) AS numeric (10,2)))
else 0
END AS 'MTDBUDGET'
,'BudgetVariance' = 1
,'TotalAnnualBudget' = 0
,'RemainingBudget' = 0
FROM [dbo].[SEIU$G_L Entry] GLE
join dbo.[SEIU$Dimension Value]DV
on dv.Code = GLE.[Global Dimension 2 Code]

group by [G_L Account No_], [Global Dimension 1 Code]
,[Global Dimension 2 Code]
,dv.[Name]
,[Transaction Type]
-- ,DV.CODE

Union

-- This will get data
-- Total Annual Budget and Remaining Annual Budget

SELECT [Global Dimension 1 Code]

,'MTDActual' = 0
,'MTDBudget' = 0
, 'YTDActual' = 0
, 'YTDBUDGET' =0
,'BudgetVariance' = 1
,CASE WHEN [Transaction Type] = 1
THEN sum(cast(isnull([Amount],0) AS numeric (10,2)))
else 0
END AS 'TotalAnnualBudget'
,'RemainingBudget' = 0
FROM [dbo].[SEIU$G_L Entry] GLE
join dbo.[SEIU$Dimension Value]DV
on dv.Code = GLE.[Global Dimension 2 Code]

group by [G_L Account No_],[Global Dimension 1 Code]
,[Global Dimension 2 Code]
,dv.[Name]
,[Transaction Type]

SELECT dept
,[Global Dimension 1 Code]
,[Global Dimension 2 Code]
,DepartmentName
--,case when Project = ''
-- Then '<No Project Code>'
-- else Project
-- end as Project
,Project
,[LineItem]
,[LineItemOrder]
,SUM(MTDActual) as MTDActual
,SUM(MTDBudget) as MTDBudget
,SUM(YTDActual) as YTDActual
,SUM(YTDBudget) as YTDBudget
,[BudgetVariance] = (Select [dbo].[BudgetVariance2](sum([YTDActual]),sum([YTDBudget])))
,SUM(TotalAnnualBudget) as TotalAnnualBudget
,(SUM(TotalAnnualBudget) - SUM(YTDActual)) as RemainingBudget
,sum(isnull(MTDActual,0)) as sumMTDActual
,SUM(ISNULL(MTDBudget,0)) as sumMTDBudget
,sum(isnull(YTDActual, 0)) as sumYTDActual
,sum(isnull(YTDBudget, 0)) as sumYTDBudget
,sum(BudgetVariance) as sumbudgetvariance
,sum(isnull(TotalAnnualBudget,0)) as sumTotalAnnualBudget
,sum((TotalAnnualBudget) - (YTDActual)) as sumRemainingBudget
--,SUM([LineItem]) as sumLineItem
--,(sum((TotalAnnualBudget) - (YTDActual))/sum(isnull(TotalAnnualBudget,0))) as RemainingBudgetPercent
from #TempTableTwo
where Project <> ''
and (isnull(YTDActual,0) + isnull(YTDBudget,0) + isnull(TotalAnnualBudget,0))<>0
--and (ISNULL(MTDActual,0) + ISNULL(MTDBudget,0) + ISNULL(MTDBudget,0))<>0
group by dept
,[Global Dimension 1 Code]
,[Global Dimension 2 Code]
,DepartmentName
,Project
,[LineItem]
,[LineItemOrder]
Having (sum([YTDActual])+sum([YTDBudget])+sum([TotalAnnualBudget]))<>0
order by [LineItemOrder] Asc

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-15 : 11:27:11

Dupe: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=159487

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources