July 4, 2012 at 10:43 pm
I have this table of budget values. I need to pivot the budget values to make a fact table. Then I need to generate the start of Financial period dates. Below is sample data. I have many rows in the source table. For each row in the source table I need to pivot and add the same set of Financial periods.
accountcoderep period01period02period03period04period05period06period07period08period09period10period11period12 fy
CA3ALB ADN 159.99 185.29 182.76 190.26 224.96 207.88 196.95 150.19 188.97 172.32 163.86 152.22 2012
and I need this
CA3ALB ADN 159.992012-07-01
CA3ALB ADN 185.292012-08-01
CA3ALB ADN 182.762012-09-01
CA3ALB ADN 190.262012-10-01
CA3ALB ADN 224.962012-11-01
CA3ALB ADN... xxx.xx2012-12-01
CA3ALB ADN... xxx.xx2013-01-01
CA3ALB ADN...
CA3ALB ADN...
CA3ALB ADN...
CA3ALB ADN...
CA3ALB ADN 152.222013-06-01
I have this code working, but it does not include the beginning of Financial period date. Please show my how to add the Start of Each Financial Period. Either in the one piece of code or an update on the resulting data.
truncate table dbo.bi_budget_fact_unpivot
insert into dbo.bi_budget_fact_unpivot (bi_bf_accountcode, bi_bf_rep, bi_bf_deb_sales_budget)
SELECT accountcode, rep, BudgetValue--, ROW_NUMBER() OVER(ORDER BY BudgetValue DESC) AS Period
FROM
(SELECT accountcode, rep, [period01],[period02],[period03],[period04],[period05],[period06],[period07],[period08],[period09],[period10],[period11],[period12]
FROM [bi_budget_fact_2012_temp]) p
UNPIVOT
(BudgetValue FOR Period IN
([period01],[period02],[period03],[period04],[period05],[period06],[period07],[period08],[period09],[period10],[period11],[period12])
) as unpvt
Source Table
CREATE TABLE [dbo].[bi_budget_fact_2012_temp](
[accountcode] [char](10) NULL,
[rep] [char](10) NULL,
[period01] [numeric](18, 2) NULL,
[period02] [numeric](18, 2) NULL,
[period03] [numeric](18, 2) NULL,
[period04] [numeric](18, 2) NULL,
[period05] [numeric](18, 2) NULL,
[period06] [numeric](18, 2) NULL,
[period07] [numeric](18, 2) NULL,
[period08] [numeric](18, 2) NULL,
[period09] [numeric](18, 2) NULL,
[period10] [numeric](18, 2) NULL,
[period11] [numeric](18, 2) NULL,
[period12] [numeric](18, 2) NULL,
[fy] [char](4) NULL
) ON [PRIMARY]
INSERT INTO [bi_budget_fact_2012_temp]
([accountcode],[rep],[period01],[period02],[period03],[period04],[period05],[period06],[period07],[period08],[period09],[period10],[period11],[period12],[fy])
VALUES ('A1','R1',10,20,30,40,50,60,70,80,90,100,110,120,'2012')
INSERT INTO [bi_budget_fact_2012_temp]
([accountcode],[rep],[period01],[period02],[period03],[period04],[period05],[period06],[period07],[period08],[period09],[period10],[period11],[period12],[fy])
VALUES ('A2','R2',100,200,300,400,500,600,700,800,900,1000,1100,1200,'2012')
Destination Table
CREATE TABLE [dbo].[bi_budget_fact_unpivot](
[bi_bf_accountcode] [char](10) NULL,
[bi_bf_rep] [char](3) NULL,
[bi_bf_deb_sales_budget] [numeric](18, 2) NULL,
[bi_bf_deb_budget_date] [smalldatetime] NULL
) ON [PRIMARY]
July 5, 2012 at 12:14 am
Thanks for the solution Artem!
Financial Year
IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t
SELECT
[accountcode], [rep], [BudgetValue], REPLACE([Period], 'Period', '')
as [Period]
INTO #t
FROM
(SELECT
[accountcode], [rep], [period07],[period08],[period09],[period10],[period11],
[period12],[period01],[period02],[period03],[period04],[period05],[period06]
FROM [bi_budget_fact_2012]) p
UNPIVOT
(
[BudgetValue] FOR [Period] IN
([period07],[period08],[period09],[period10],[period11],
[period12],[period01],[period02],[period03],[period04],[period05],[period06]
)
) as [unpvt]
--select * from #t
INSERT INTO dbo.bi_budget_fact_2012_temp (bi_bf_accountcode, bi_bf_rep, bi_bf_deb_budget_date,bi_bf_deb_sales_budget)
SELECT t.[accountcode], t.[rep], convert(datetime,
'01/'+t.[Period]+'/'+case when convert(int,t.[Period]) < 7 THEN convert(varchar(4),convert(int,orig.[fy]+1)) ELSE orig.[fy] END, 103), [BudgetValue]
FROM #t t
INNER JOIN [bi_budget_fact_2012_temp] orig
ON orig.[accountcode]=t.[accountcode] AND orig.[rep]=t.[rep]
Calendar Year
IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t
SELECT
[accountcode], [rep], [BudgetValue], REPLACE([Period], 'Period', '')
as [Period]
INTO #t
FROM
(SELECT
[accountcode], [rep], [period01],[period02],[period03],[period04],[period05],
[period06],[period07],[period08],[period09],[period10],[period11],[period12]
FROM [bi_budget_fact_2012_temp]) p
UNPIVOT
(
[BudgetValue] FOR [Period] IN
([period01],[period02],[period03],[period04],[period05],[period06],
[period07],[period08],[period09],[period10],[period11],[period12])
) as [unpvt]
SELECT t.[accountcode], t.[rep], convert(datetime,
'01/'+[Period]+'/'+[fy], 103), [BudgetValue]
FROM #t t
INNER JOIN [bi_budget_fact_2012_temp] orig
ON orig.[accountcode]=t.[accountcode] AND orig.[rep]=t.[rep]
July 9, 2012 at 11:56 pm
Hey Garry! Sorry for my late reply but I thought I'd suggest an arguably more readable and succinct approach that works in SQL 2008 (using CROSS APPLY VALUES instead of UNPIVOT):
SELECT accountcode, rep, period, [fy]=DATEADD(month, m-1, [fy])
FROM (
SELECT accountcode, rep, period, [fy]=CAST([fy]+'-01-01' AS SMALLDATETIME), m
FROM bi_budget_fact_2012_temp
CROSS APPLY (
VALUES ([period01], 1), ([period02], 2), ([period03], 3)
,([period04], 4), ([period05], 5), ([period06], 6)
,([period07], 7), ([period08], 8), ([period09], 9)
,([period10], 10), ([period11], 11), ([period12], 12)) p(period, m)) a
Up to you of course which you consider a more readable solution. From where I sit, I can always write this out without a lookup (in BOL) but whenever I need to use UNPIVOT I have to futz around with it for too long.
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply