March 19, 2012 at 8:11 am
Hello everyone. I've been struggling with this procedure for about two weeks now. I have some information I want to view as a pivot to display expenses for each month. I've looked into using the PIVOT function, but can't use it because of compatibility issues with SQL Server 2000 servers linked to my SQL Server 2005 instance. I've tried everything I know to work around this issue but just can't figure it out. I've attached a script that produces a test database with the procedure and test data. Can someone please provide some guidance in solving this issue?
Thanks.
USE [Test_Scripts]
GO
/****** Object: StoredProcedure [dbo].[test_expenses] Script Date: 03/19/2012 10:16:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
--execute the procedure to see how the data is currently returned
--EXEC test_expenses
ALTER PROC [dbo].[test_expenses] /* Version 3.1 - 8/19/2005 */
@beg_bal_date int = 731489, /* Fiscal Year Begin date */
@end_bal_date int = 734349, /* Ending date of 'Through Period' */
@budget_code varchar(16) = 'BB2011', /* Budget set */
@begin_program char(3)='001',/*beginning program*/
@end_program char(3)='999',/*ending program*/
@division char(4)=''/*ending program*/
AS
CREATE TABLE #gl_data_ytd (
account_number varchar(32) null,
state_account_number char(7) null,
budgetunit_number varchar(32) null,
program_number char(3) null,
balance_date char(12) null,
balance_date_int int null,
credits money null,
debits money null,
net_change money null,
balance money null,
account_type smallint null,
div_no varchar(4) null,
AcctType char(1) null
)
CREATE TABLE #gl_data (
state_account_number char(7) null,
program_number char(3) null,
scoa_description varchar(40) null,
prd_net_change money null,
ytd_net_change money null,
ytd_budget money null,
prior_applications money null,
prd_applications money null,
prog_title varchar(40) null,
div_no varchar(4) null,
AcctType char(1) null,
Inactive_Flag int null
)
CREATE TABLE #budget_data (
program_number varchar(32) null,
state_code char(7) null,
net_change money null,
AcctType char(1) null,
Inactive_Flag int null
)
INSERT #budget_data (
program_number,
state_code,
net_change,
AcctType,
Inactive_Flag
)
SELECT custom_glseg2.prog_no,
custom_glseg1.state_code,
net_change,
AcctType =
case
when glseg1.account_type IN ('400', '410', '420', '440') then '1'
else '2'
end,
custom_glseg2.Inactive_Flag
FROM glbuddet,
custom_glseg1,
custom_glseg2,
glseg1
WHERE ( budget_code = @budget_code
OR budget_code = @budget_code + 'REV'
OR budget_code = @budget_code + ' REV' )
AND LEFT(glbuddet.account_code, 4) = custom_glseg1.seg_code
AND SUBSTRING(glbuddet.account_code, 5, 3) = custom_glseg2.seg_code
AND LEFT(glbuddet.account_code, 4) = glseg1.seg_code
/* Insert rows (one per program/account into gl_data temp table by selecting all budget detail records for the selected budget year */
INSERT #gl_data (
state_account_number,
program_number,
AcctType,
Inactive_flag,
ytd_budget
)
SELECT state_code,
program_number,
AcctType,
Inactive_Flag,
SUM(net_change)
FROM #budget_data
GROUP BY program_number,
state_code,
AcctType,
Inactive_Flag
INSERT #gl_data_ytd (
account_number,
budgetunit_number,
balance_date,
balance_date_int,
credits,
debits,
net_change,
balance,
account_type,
AcctType
)
SELECT seg1_code,
seg2_code,
convert(char(12), dateadd(dd, balance_date - 639906, "1/1/1753"), 101) as balance_date,
balance_date,
home_credit,
home_debit,
home_net_change,
home_current_balance,
account_type,
AcctType =
case
when account_type IN ('400', '410', '420', '440') then '1'
else '2'
end
FROM glbal
WHERE balance_date BETWEEN @beg_bal_date and @end_bal_date
AND balance_type = 1
AND account_type IN ('400', '410', '420', '440', '500', '540')
UPDATE #gl_data_ytd
SET state_account_number = COALESCE(( SELECT custom_glseg1.state_code
FROM custom_glseg1
WHERE custom_glseg1.seg_code = #gl_data_ytd.account_number ), '0'),
program_number = ( SELECT custom_glseg2.prog_no
FROM custom_glseg2
WHERE custom_glseg2.seg_code = #gl_data_ytd.budgetunit_number ),
div_no = ( SELECT custom_glseg2.div_no
FROM custom_glseg2
WHERE custom_glseg2.seg_code = #gl_data_ytd.budgetunit_number )
UPDATE #gl_data
SET ytd_net_change = COALESCE(( SELECT SUM(net_change)
FROM #gl_data_ytd
GROUP BY state_account_number,
program_number
HAVING program_number = #gl_data.program_number
AND state_account_number = #gl_data.state_account_number ), 0)
INSERT #gl_data (
program_number,
state_account_number,
ytd_net_change,
AcctType,
div_no,
ytd_budget
)
SELECT program_number,
state_account_number,
SUM(net_change),
AcctType,
div_no,
0
FROM #gl_data_ytd
GROUP BY program_number, state_account_number, AcctType, div_no
HAVING program_number + state_account_number NOT IN ( SELECT program_number + state_account_number
FROM #gl_data )
CREATE TABLE #extra_data (
state_account_number char(7) null,
AcctType char(1) null
)
INSERT #extra_data (
state_account_number,
AcctType
)
SELECT DISTINCT state_account_number,
AcctType
FROM #gl_data_ytd
UPDATE #gl_data
SET AcctType = ( SELECT AcctType
FROM #extra_data
WHERE state_account_number = #gl_data.state_account_number )
WHERE AcctType IS NULL
UPDATE #gl_data
SET div_no = ( SELECT DISTINCT custom_glseg2.div_no
FROM custom_glseg2
WHERE custom_glseg2.prog_no = #gl_data.program_number )
DELETE
FROM #gl_data_ytd
WHERE balance_date_int <> @end_bal_date
If @begin_program <> '' and @end_program <> ''
begin
DELETE
FROM #gl_data
WHERE program_number < @begin_program or program_number > @end_program
end
If @division<>''
begin
DELETE
FROM #gl_data
WHERE div_no <> @division
end
DELETE
FROM #gl_data
WHERE Inactive_flag = 1
UPDATE #gl_data
SET prd_net_change = COALESCE(( SELECT SUM(net_change)
FROM #gl_data_ytd
GROUP BY state_account_number,
program_number
HAVING program_number = #gl_data.program_number
AND state_account_number = #gl_data.state_account_number ), 0)
UPDATE #gl_data
SET scoa_description = ( SELECT DISTINCT custom_glseg1.description
FROM custom_glseg1
WHERE custom_glseg1.state_code = #gl_data.state_account_number )
UPDATE #gl_data
SET ytd_budget = ytd_budget * -1,
prd_net_change = prd_net_change * -1,
ytd_net_change = ytd_net_change * -1
Where AcctType = '1'
UPDATE #gl_data
--I would like to set the period_no as a range of dates but I can't do this in a sub-query
SET prd_applications = COALESCE((SELECT mtd_application
FROM custom_appliedfunds
WHERE custom_appliedfunds.prog_no = #gl_data.program_number
and custom_appliedfunds.period_no = @end_bal_date
and custom_appliedfunds.state_code = #gl_data.state_account_number ) ,0)
UPDATE #gl_data
SET prior_applications = COALESCE((SELECT sum(mtd_application)
FROM custom_appliedfunds
WHERE custom_appliedfunds.period_no >= @beg_bal_date
AND custom_appliedfunds.period_no < @end_bal_date
GROUP BY prog_no, state_code
HAVING custom_appliedfunds.prog_no = #gl_data.program_number
AND custom_appliedfunds.state_code = #gl_data.state_account_number ), 0)
UPDATE #gl_data
SET prog_title = (Select max(prog_title)
From custom_glseg2
Where #gl_data.program_number = custom_glseg2.prog_no)
SELECT program_number,
prog_title,
state_account_number,
scoa_description,
ytd_budget,
prd_net_change,
ytd_net_change,
prior_applications,
prd_applications,
div_no,
AcctType
FROM #gl_data
--WHERE state_account_number = '8001'
ORDER BY program_number, AcctType, state_account_number
March 19, 2012 at 11:49 pm
You've certainly provided extensive DDL here but without running all the code you've provided, I don't see an expected results set.
I can understand about the 12 months you want to see across but what other columns are required?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 21, 2012 at 7:01 am
I just figured it out. Thanks for looking.
March 21, 2012 at 7:27 am
Could you post your solution.I have a similar problem and attacked it with cross tabs. I don't like pivot but have used it. Cross tabs seam simpler to me.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply