Same Calculation, different results HELP!

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)

Same Calculation, different results HELP!

Author

Topic

RageQuit
Starting Member

2 Posts

Posted - 2011-04-14 : 17:41:33

Hello all, 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.

IF @vchReportingPeriod in ('January 2011', 'February 2011','March 2011')

SELECT
[Global Dimension 1 Code]
,[Global Dimension 1 Code] as Dept -- Dept Code
,[Global Dimension 1 Code]+':'+(select [Name]
from dbo.[SEIU$Dimension Value] DV
where dv.Code = GLE.[Global Dimension 1 Code]) as "DepartmentName" -- DeptName
,[Global Dimension 2 Code] -- Project Code
,[G_L Account No_]
,'Project' = dv.[Name]
,[LineItem]= case
when [G_L Account No_] between 500000 and 500999 then 'Salary'
when [G_L Account No_] between 510000 and 510999 then 'Benefits'
when [G_L Account No_] between 540100 and 540999 then 'Empl Travel'
when [G_L Account No_] between 550100 and 550599 then 'Empl Travel'
when [G_L Account No_] between 570100 and 570199 then 'Prof Fees Legal'
when [G_L Account No_] between 571100 and 571199 then 'Prof Fees Consulting'
when [G_L Account No_] between 560000 and 560199 then 'Non-Empl Exp'
when [G_L Account No_] not in ('580000','580099','580001') and [G_L Account No_] between 579900 and 639999 then 'Admin Exp Other'
when [G_L Account No_] between 670100 and 670199 then 'Subsidies'
when [G_L Account No_] between 640000 and 645999 then 'Subsidies'
--when [G_L Account No_] between 402000 and 499999 then 'Income/ Transfer'
when [G_L Account No_] between 650500 and 650599 then 'Conference'
when [G_L Account No_] between 670400 and 670499 then 'Media'
when [G_L Account No_] between 670000 and 670099 or [G_L Account No_] in ('470020','480010') then 'Contributions'
when [G_L Account No_] in ('580000','580099','580001') then 'Admin Exp Rent'
when [G_L Account No_] in ('470000','489999') then 'Other Income'
when [G_L Account No_] in ('650000','571115','680000','680299') then 'Other Expense'

else
'Other'
end
,[Transaction Type] -- 0 = Actual 1 = Budget
--,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]
where [G_L Account No_] between 402000 and 999998
and [Posting Date] between '01/01/2011' and '01/31/2011'
and [Transaction Type] in (0)
and [Fund No_] in ('genus','copus','peius')
and [Global Dimension 1 Code] = @vchDepartment
--and [Global Dimension 1 Code] in ('10S','11S','12S','13S','20G','20S','20U','21G','21S','21U','22G','22S','22U','23G'
-- ,'23S','23U','30G','32G','33G','34G','35G','36S','39U','40S','41G','41S'
-- ,'42G','42S','43G','43S','44C','44P','44S','45S','46S','47S','50S','51S','52S','53S','54S','55G','55S','56S')
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
[Global Dimension 1 Code]
,[Global Dimension 1 Code] as Dept
,[Global Dimension 1 Code]+':'+(select [Name]
from dbo.[SEIU$Dimension Value] DV
where dv.Code = GLE.[Global Dimension 1 Code]) as "DepartmentName" -- DeptName
,[Global Dimension 2 Code]-- Project Code
,[G_L Account No_]
,'Project' = dv.[Name]
,[LineItem]= case
when [G_L Account No_] between 500000 and 500999 then 'Salary'
when [G_L Account No_] between 510000 and 510999 then 'Benefits'
when [G_L Account No_] between 540100 and 540999 then 'Empl Travel'
when [G_L Account No_] between 550100 and 550599 then 'Empl Travel'
when [G_L Account No_] between 570100 and 570199 then 'Prof Fees Legal'
when [G_L Account No_] between 571100 and 571199 then 'Prof Fees Consulting'
when [G_L Account No_] between 560000 and 560199 then 'Non-Empl Exp'
when [G_L Account No_] not in ('580000','580099','580001') and [G_L Account No_] between 579900 and 639999 then 'Admin Exp Other'
when [G_L Account No_] between 670100 and 670199 then 'Subsidies'
when [G_L Account No_] between 640000 and 645999 then 'Subsidies'
--when [G_L Account No_] between 402000 and 499999 then 'Income/ Transfer'
when [G_L Account No_] between 650500 and 650599 then 'Conference'
when [G_L Account No_] between 670400 and 670499 then 'Media'
when [G_L Account No_] between 670000 and 670099 or [G_L Account No_] in ('470020','480010') then 'Contributions'
when [G_L Account No_] in ('580000','580099','580001') then 'Admin Exp Rent'
when [G_L Account No_] in ('470000','489999') then 'Other Income'
when [G_L Account No_] in ('650000','571115','680000','680299') then 'Other Expense'

else
'Other'
end
,[Transaction Type] -- 0 = Actual 1 = Budget
,'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]

where [G_L Account No_] between 402000 and 999998
and [Posting Date] between '01/01/2011' and '01/31/2011'
and [Transaction Type] in (0,1)
and [Fund No_] in ('genus','copus','peius')
and [Global Dimension 1 Code] = @vchDepartment
--and [Global Dimension 1 Code] in ('10S','11S','12S','13S','20G','20S','20U','21G','21S','21U','22G','22S','22U','23G'
-- ,'23S','23U','30G','32G','33G','34G','35G','36S','39U','40S','41G','41S'
-- ,'42G','42S','43G','43S','44C','44P','44S','45S','46S','47S','50S','51S','52S','53S','54S','55G','55S','56S')

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

Union
-- This select statment collects MTD actuals and budgets amount for the department variable
-- This data is also loaded into the temporary table "#TempTableTwo"
-- Month to Date (MTD) Totals
SELECT
[Global Dimension 1 Code]
,[Global Dimension 1 Code] as Dept
,[Global Dimension 1 Code]+':'+(select [Name]
from dbo.[SEIU$Dimension Value] DV
where dv.Code = GLE.[Global Dimension 1 Code]) as "DepartmentName" -- DeptName
,[Global Dimension 2 Code]-- Project Code
,[G_L Account No_]
,'Project' = dv.[Name]
,[LineItem]= case
when [G_L Account No_] between 500000 and 500999 then 'Salary'
when [G_L Account No_] between 510000 and 510999 then 'Benefits'
when [G_L Account No_] between 540100 and 540999 then 'Empl Travel'
when [G_L Account No_] between 550100 and 550599 then 'Empl Travel'
when [G_L Account No_] between 570100 and 570199 then 'Prof Fees Legal'
when [G_L Account No_] between 571100 and 571199 then 'Prof Fees Consulting'
when [G_L Account No_] between 560000 and 560199 then 'Non-Empl Exp'
when [G_L Account No_] not in ('580000','580099','580001') and [G_L Account No_] between 579900 and 639999 then 'Admin Exp Other'
when [G_L Account No_] between 670100 and 670199 then 'Subsidies'
when [G_L Account No_] between 640000 and 645999 then 'Subsidies'
--when [G_L Account No_] between 402000 and 499999 then 'Income/ Transfer'
when [G_L Account No_] between 650500 and 650599 then 'Conference'
when [G_L Account No_] between 670400 and 670499 then 'Media'
when [G_L Account No_] between 670000 and 670099 or [G_L Account No_] in ('470020','480010') then 'Contributions'
when [G_L Account No_] in ('580000','580099','580001') then 'Admin Exp Rent'
when [G_L Account No_] in ('470000','489999') then 'Other Income'
when [G_L Account No_] in ('650000','571115','680000','680299') then 'Other Expense'

else
'Other'
end
,[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]

where [G_L Account No_] between 402000 and 999998
and [Posting Date] between '01/01/2011' and '01/31/2011'
and [Transaction Type] in (0,1)
and [Fund No_] in ('genus','copus','peius')
and [Global Dimension 1 Code] = @vchDepartment
--and [Global Dimension 1 Code] in ('10S','11S','12S','13S','20G','20S','20U','21G','21S','21U','22G','22S','22U','23G'
-- ,'23S','23U','30G','32G','33G','34G','35G','36S','39U','40S','41G','41S'
-- ,'42G','42S','43G','43S','44C','44P','44S','45S','46S','47S','50S','51S','52S','53S','54S','55G','55S','56S')

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]
,[Global Dimension 1 Code] as Dept
,[Global Dimension 1 Code]+':'+(select [Name]
from dbo.[SEIU$Dimension Value] DV
where dv.Code = GLE.[Global Dimension 1 Code]) as "DepartmentName" -- DeptName
,[Global Dimension 2 Code] -- Project Code
,[G_L Account No_]
,'Project' = dv.[Name]
,[LineItem]= case
when [G_L Account No_] between 500000 and 500999 then 'Salary'
when [G_L Account No_] between 510000 and 510999 then 'Benefits'
when [G_L Account No_] between 540100 and 540999 then 'Empl Travel'
when [G_L Account No_] between 550100 and 550599 then 'Empl Travel'
when [G_L Account No_] between 570100 and 570199 then 'Prof Fees Legal'
when [G_L Account No_] between 571100 and 571199 then 'Prof Fees Consulting'
when [G_L Account No_] between 560000 and 560199 then 'Non-Empl Exp'
when [G_L Account No_] not in ('580000','580099','580001') and [G_L Account No_] between 579900 and 639999 then 'Admin Exp Other'
when [G_L Account No_] between 670100 and 670199 then 'Subsidies'
when [G_L Account No_] between 640000 and 645999 then 'Subsidies'
--when [G_L Account No_] between 402000 and 499999 then 'Income/ Transfer'
when [G_L Account No_] between 650500 and 650599 then 'Conference'
when [G_L Account No_] between 670400 and 670499 then 'Media'
when [G_L Account No_] between 670000 and 670099 or [G_L Account No_] in ('470020','480010') then 'Contributions'
when [G_L Account No_] in ('580000','580099','580001') then 'Admin Exp Rent'
when [G_L Account No_] in ('470000','489999') then 'Other Income'
when [G_L Account No_] in ('650000','571115','680000','680299') then 'Other Expense'

else
'Other'
end
,[Transaction Type] -- 0 = Actual 1 = Budget
,'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]
where [G_L Account No_] between 402000 and 999998
and [Posting Date] between '01/01/2011' and '03/31/2011'
and [Transaction Type] in (1)
and [Fund No_] in ('genus','copus','peius')
and [Global Dimension 1 Code] = @vchDepartment
--and [Global Dimension 1 Code] in ('10S','11S','12S','13S','20G','20S','20U','21G','21S','21U','22G','22S','22U','23G'
-- ,'23S','23U','30G','32G','33G','34G','35G','36S','39U','40S','41G','41S'
-- ,'42G','42S','43G','43S','44C','44P','44S','45S','46S','47S','50S','51S','52S','53S','54S','55G','55S','56S')
group by [G_L Account No_],[Global Dimension 1 Code]
,[Global Dimension 2 Code]
,dv.[Name]
,[Transaction Type]

/**LineItemOrder**/
Alter table #TempTableTwo
Add LineItemOrder tinyint

Update #TempTableTwo
Set [LineItemOrder]=
case
when LineItem = 'Salary' then 1
when LineItem = 'Benefits' then 2
when LineItem = 'Empl Travel' then 3
when LineItem = 'Prof Fees Consulting' then 4
when LineItem = 'Prof Fees Legal' then 5
when LineItem = 'Non-Empl Exp' then 6
when LineItem = 'Admin Exp Rent' then 7
when LineItem = 'Admin Exp Other' then 8
when LineItem = 'Conference' then 9
when LineItem = 'Contributions' then 10
when LineItem = 'Subsidies' then 11
when LineItem = 'Media' then 12
when LineItem = 'Other Expenses' then 13
when LineItem = 'Other Income' then 14
when LineItem = 'Other' then 15
--when LineItem = 'Income/ Transfer' then 8
end

/***LineItem**/
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

-- drop temporary table
drop table #TempTableTwo

end

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-14 : 20:23:01

Rage, it's hard for anyone to look at the code and understand the logic because it is so long. Can you do couple of things?

First, if at all possible, remove the irrelevant columns and make the code a lot shorter.

Second, try to define the variables etc. so someone can copy it to their computer and at least parse it. Then, repost the question.

Also, Brett's page here might help you in posting the question in a way that would get someone to understand and offer suggestions easily. http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources