August 7, 2013 at 9:39 am
Hi Guys,
I'm trying to accomplish the following : I have a table which has 2 different scenario's.
Now each scenario has a set of same fund, function, project etc..etc which differs only in value.
So I want to be able to do , group by Fund,Function,Project ,Account ,Department
Just need the select statement. I'll write the insert statement and put it in a stored proc with dynamic variables.
eg :
Fiscal Period Scenario Fund Function Project Account Department Value
2014 Jun Actual 10 40 000064000418 72.31
2014 Jun Jun (0+12) Fcst 10 40 000064000418 500
Result
Fiscal Period Scenario Fund Function Project Account Dept Value
2014 Jul Jun (0+12) Fcst 10 40 0000 64000 418427.69 (500-72.31)
Even if there is no pair it should still subtract and generate one set of row
DDL statement
USE [Test]
GO
/****** Object: Table [dbo].[Sub] Script Date: 08/07/2013 11:11:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Sub](
[Fiscal_Key] [int] NULL,
[Fiscal_Name] [varchar](80) NULL,
[Period_Key] [int] NULL,
[Period_Name] [varchar](80) NULL,
[Scenario_Key] [int] NULL,
[Scenario_Name] [varchar](80) NULL,
[Fund_Key] [int] NULL,
[Fund_Name] [varchar](80) NULL,
[Function_Key] [int] NULL,
[Function_Name] [varchar](80) NULL,
[Project_Key] [int] NULL,
[Project_Name] [varchar](80) NULL,
[Project_Alias] [varchar](500) NULL,
[Account_Key] [int] NULL,
[Account_Name] [varchar](80) NULL,
[Department_Key] [int] NULL,
[Department_Name] [varchar](80) NULL,
[Planning_Year_Key] [int] NULL,
[Planning_Year_Name] [varchar](80) NULL,
[Special_Key] [int] NULL,
[Special_Name] [varchar](80) NULL,
[value] [float] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 1, N'Actual', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 54, N'429', 1, N'None', 1, N'0', 722.08)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 1, N'Actual', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 45, N'420', 1, N'None', 1, N'0', 62.87)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 1, N'Actual', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 40, N'415', 1, N'None', 1, N'0', 657.85)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 27, N'402', 1, N'None', 1, N'0', 1400)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 46, N'421', 1, N'None', 1, N'0', 900)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 1, N'Actual', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 30, N'405', 1, N'None', 1, N'0', 1923.18)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 1, N'Actual', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 43, N'418', 1, N'None', 1, N'0', 72.31)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 60, N'435', 1, N'None', 1, N'0', 850)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 49, N'424', 1, N'None', 1, N'0', 1200)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 1, N'Actual', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 42, N'417', 1, N'None', 1, N'0', 1115.22)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 64, N'439', 1, N'None', 1, N'0', 550)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 36, N'411', 1, N'None', 1, N'0', 1200)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 59, N'434', 1, N'None', 1, N'0', 1725)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 1, N'Actual', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 59, N'434', 1, N'None', 1, N'0', 842.67)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 28, N'403', 1, N'None', 1, N'0', 2000)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 1, N'Actual', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 37, N'412', 1, N'None', 1, N'0', 565.49)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 41, N'416', 1, N'None', 1, N'0', 535)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 54, N'429', 1, N'None', 1, N'0', 800)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 48, N'423', 1, N'None', 1, N'0', 580)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 39, N'414', 1, N'None', 1, N'0', 800)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 58, N'433', 1, N'None', 1, N'0', 900)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 29, N'404', 1, N'None', 1, N'0', 1300)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 185, N'446', 1, N'None', 1, N'0', 500)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 1, N'Actual', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 66, N'441', 1, N'None', 1, N'0', 492.3)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 34, N'409', 1, N'None', 1, N'0', 900)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 1, N'Actual', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 41, N'416', 1, N'None', 1, N'0', 202.45)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 61, N'436', 1, N'None', 1, N'0', 650)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 1, N'Actual', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 52, N'427', 1, N'None', 1, N'0', 53.25)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 1, N'Actual', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 55, N'430', 1, N'None', 1, N'0', 620.86)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 56, N'431', 1, N'None', 1, N'0', 1300)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 26, N'401', 1, N'None', 1, N'0', 400)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 40, N'415', 1, N'None', 1, N'0', 1200)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 1, N'Actual', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 44, N'419', 1, N'None', 1, N'0', 3787.5)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 1, N'Actual', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 33, N'408', 1, N'None', 1, N'0', 635.55)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 53, N'428', 1, N'None', 1, N'0', 200)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 66, N'441', 1, N'None', 1, N'0', 350)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 47, N'422', 1, N'None', 1, N'0', 500)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 1, N'Actual', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 26, N'401', 1, N'None', 1, N'0', 433.23)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 42, N'417', 1, N'None', 1, N'0', 1028)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 68, N'443', 1, N'None', 1, N'0', 500)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 37, N'412', 1, N'None', 1, N'0', 650)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 1, N'Actual', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 31, N'406', 1, N'None', 1, N'0', 772.07)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 55, N'430', 1, N'None', 1, N'0', 800)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 1, N'Actual', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 60, N'435', 1, N'None', 1, N'0', 1155.78)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 32, N'407', 1, N'None', 1, N'0', 1000)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 1, N'Actual', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 49, N'424', 1, N'None', 1, N'0', 1123.31)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 63, N'438', 1, N'None', 1, N'0', 675)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 1, N'Actual', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 25, N'400', 1, N'None', 1, N'0', 1288.5)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 156, N'444', 1, N'None', 1, N'0', 450)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 1, N'Actual', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 68, N'443', 1, N'None', 1, N'0', 575.49)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 1, N'Actual', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 63, N'438', 1, N'None', 1, N'0', 681.92)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 1, N'Actual', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 34, N'409', 1, N'None', 1, N'0', 1308.75)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 31, N'406', 1, N'None', 1, N'0', 900)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 1, N'Actual', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 58, N'433', 1, N'None', 1, N'0', 49.45)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 1, N'Actual', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 48, N'423', 1, N'None', 1, N'0', 546.2)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 1, N'Actual', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 38, N'413', 1, N'None', 1, N'0', 1462.71)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 131, N'596', 1, N'None', 1, N'0', 80)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 1, N'Actual', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 67, N'442', 1, N'None', 1, N'0', 540.03)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 1, N'Actual', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 35, N'410', 1, N'None', 1, N'0', 1112.29)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 65, N'440', 1, N'None', 1, N'0', 600)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 1, N'Actual', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 27, N'402', 1, N'None', 1, N'0', 1439.67)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 38, N'413', 1, N'None', 1, N'0', 2100)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 1, N'Actual', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 53, N'428', 1, N'None', 1, N'0', 9.43)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 1, N'Actual', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 62, N'437', 1, N'None', 1, N'0', 1140.5)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 25, N'400', 1, N'None', 1, N'0', 2500)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 1, N'Actual', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 32, N'407', 1, N'None', 1, N'0', 539.83)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 35, N'410', 1, N'None', 1, N'0', 1200)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 44, N'419', 1, N'None', 1, N'0', 1800)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 1, N'Actual', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 29, N'404', 1, N'None', 1, N'0', 1173.09)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 1, N'Actual', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 64, N'439', 1, N'None', 1, N'0', 563.38)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 1, N'Actual', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 36, N'411', 1, N'None', 1, N'0', 794.12)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 45, N'420', 1, N'None', 1, N'0', 991.56)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 30, N'405', 1, N'None', 1, N'0', 2000)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 1, N'Actual', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 61, N'436', 1, N'None', 1, N'0', 525.98)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 43, N'418', 1, N'None', 1, N'0', 500)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 62, N'437', 1, N'None', 1, N'0', 1100)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 57, N'432', 1, N'None', 1, N'0', 900)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 33, N'408', 1, N'None', 1, N'0', 1200)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 67, N'442', 1, N'None', 1, N'0', 900)
INSERT [dbo].[Sub] ([Fiscal_Key], [Fiscal_Name], [Period_Key], [Period_Name], [Scenario_Key], [Scenario_Name], [Fund_Key], [Fund_Name], [Function_Key], [Function_Name], [Project_Key], [Project_Name], [Project_Alias], [Account_Key], [Account_Name], [Department_Key], [Department_Name], [Planning_Year_Key], [Planning_Year_Name], [Special_Key], [Special_Name], [value]) VALUES (7, N'2014', 16, N'Jun', 11, N'Jun (0+12) Forecast', 1, N'10', 8, N'40', 1, N'0000', N'0000 - Default', 274, N'64000', 184, N'445', 1, N'None', 1, N'0', 500)
Hope it makes sense. Thanks for your help.
August 7, 2013 at 10:49 am
Couple of questions:
1. Will it always have, at most, 2 pair of record?
2. Will the resulting record have the period + 1 of the paired records? Will there be two different periods? Will the result be max() + 1?
August 7, 2013 at 11:18 am
ck9663 (8/7/2013)
Couple of questions:1. Will it always have, at most, 2 pair of record?
2. Will the resulting record have the period + 1 of the paired records? Will there be two different periods? Will the result be max() + 1?
1. Hmm, It may not be the case as always.
2. So so if the period is Jun , the result set should be Jun + 1 = Jul.
Hope this helps
August 7, 2013 at 2:42 pm
Any other thoughts guys ?
August 7, 2013 at 4:09 pm
You have given us the table definition and sample data, and that is great.
However, there is not really any good description of the business rules and what is the expected results. Do you want the three rows in the first Code box, or only the second? And whichever case, why?
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 7, 2013 at 7:59 pm
hi erland,
I'm sorry if the requirement was not clear.
I'll rephrase it :
The data is divided by Scenario : Jun (0+12) forecast and Actual.
And every Scenario will have a Fund, Function, Dept, Project Code etc etc which will also be present in the other Scenario
so for eg : Giving only 2 rows , but my actual table has 13000+ , so its a snapshot of the eg
Fiscal Period Scenario Fund Function Project Account Department Value
2014 Jun Actual 10 40 000064000418 72.31
2014 Jun Jun (0+12) Fcst 10 40 000064000418 500
IN the above eg you can see that it differs in Scenario and Values only, where as the combination of
Fund Function Project Account Department is the same
Now the expected Result set for these 2 rows.
Fiscal Period Scenario Fund Function Project Account Dept Value
2014 Jul Jun (0+12) Fcst 10 40 0000 64000 418427.69 (500-72.31)
Please note that there are 13000 so each rows may have 0 to many combinations. I would like to simplify it into a unique list with only the variance or difference amounts in the value column by doing subtraction.
Please let me know if it is still not clear. I'll upload and excel file with the expected results.
Thanks for looking into this
August 7, 2013 at 9:55 pm
I think this will do what you require, but you'll need to check it as I have made a number of assumptions on you requirements.
Please note that there are 13000 so each rows may have 0 to many combinations. I would like to simplify it into a unique list with only the variance or difference amounts in the value column by doing subtraction.
To handle this I have summed the actuals (scenario_key 1) and removed that from the summed forecast(scenario_key 2). I've also done a full outer join to allow for no actual or forecast.
Hope this helps
with actual as (
select Fiscal_name, Period_name, scenario_name, Fund_Name,Function_Name,Project_name ,Account_name ,Department_name, sum(value ) value
from #sub
where scenario_key = 1
group by Fiscal_name, Period_name, scenario_name, Fund_Name,Function_Name,Project_name ,Account_name ,Department_name
)
,forecast as (
select Fiscal_name, Period_name, scenario_name, Fund_Name,Function_Name,Project_name ,Account_name ,Department_name, sum(value ) value
from #sub
where scenario_key = 11
group by Fiscal_name, Period_name, scenario_name, Fund_Name,Function_Name,Project_name ,Account_name ,Department_name
)
select coalesce(a.Fiscal_name, f.Fiscal_name),
coalesce(a.Period_name, f.Period_name),
a.scenario_name,
f.scenario_name,
coalesce(a.Fund_Name, f.Fund_Name),
coalesce(a.Function_Name, f.Function_Name),
coalesce(a.Project_name , f.Project_name),
coalesce(a.Account_name , f.Account_name),
coalesce(a.Department_name, f.Department_name),
isnull(f.value,0) - isnull(a.value,0) diff_value
from forecast f
full outer join actual a on
a.Fiscal_name = f.Fiscal_name and
a.Period_name = f.Period_name and
a.Fund_Name = f.Fund_Name and
a.Function_Name = f.Function_Name and
a.Project_name = f.Project_name and
a.Account_name = f.Account_name and
a.Department_name = f.Department_name;
August 8, 2013 at 5:42 pm
Thanks this works Changed the join to left outer to exclude the null values.
August 9, 2013 at 4:17 am
venkyzrocks (8/7/2013)
hi erland,I'm sorry if the requirement was not clear.
I'll rephrase it :
The data is divided by Scenario : Jun (0+12) forecast and Actual.
And every Scenario will have a Fund, Function, Dept, Project Code etc etc which will also be present in the other Scenario
so for eg : Giving only 2 rows , but my actual table has 13000+ , so its a snapshot of the eg
Fiscal Period Scenario Fund Function Project Account Department Value
2014 Jun Actual 10 40 000064000418 72.31
2014 Jun Jun (0+12) Fcst 10 40 000064000418 500
IN the above eg you can see that it differs in Scenario and Values only, where as the combination of
Fund Function Project Account Department is the same
Now the expected Result set for these 2 rows.
Fiscal Period Scenario Fund Function Project Account Dept Value
2014 Jul Jun (0+12) Fcst 10 40 0000 64000 418427.69 (500-72.31)
Please note that there are 13000 so each rows may have 0 to many combinations. I would like to simplify it into a unique list with only the variance or difference amounts in the value column by doing subtraction.
Please let me know if it is still not clear. I'll upload and excel file with the expected results.
Thanks for looking into this
Try this:
SELECT
Fiscal_name,
Period_name,
scenario_name = MAX(scenario_name),
Fund_Name,
Function_Name,
Project_name,
Account_name,
Department_name,
value = SUM(CASE WHEN scenario_key = 1 THEN 0-value ELSE value END) -- subtract the Actual
FROM (
SELECT
Fiscal_name,
Period_name,
scenario_key,
scenario_name,
Fund_Name,
Function_Name,
Project_name,
Account_name,
Department_name,
value = SUM(value)
FROM sub
WHERE scenario_key IN (1,11)
GROUP BY Fiscal_name, Period_name, Fund_Name, Function_Name, Project_name, Account_name, Department_name, scenario_key, scenario_name
) d
GROUP BY Fiscal_name, Period_name, Fund_Name, Function_Name, Project_name, Account_name, Department_name
ORDER BY Fiscal_name, Period_name, Fund_Name, Function_Name, Project_name, Account_name, Department_name
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 9, 2013 at 8:35 am
Thanks Chris, your solution is slick and works well.
I have a silly question , just for my understanding
How does you subtraction work when your case has only Value in the else part
SUM(CASE WHEN scenario_key = 1 THEN 0-value ELSE value END)
scenario_key scenario_name Value
1 Actual 1288.5
11 Jun (0+12) Forecast 2500
So based on the select if its scenario_key = 1, then it will do 0-1288.5 which is perfect.
But in the else since it's defined value , it should get 2500. How does it do 2500-1288.5 perfectly, because even if were to sum it should done 2500+1288.5.
Thanks for your help
August 9, 2013 at 1:27 pm
Chris first computes the sums over (Fiscal_Name, Period_Name, Fund_Name, Function_Name, Project_Name, Account_Name, Department_Name, Scenario_Key, Scenario_Name)
With these sums he then goes on to compute the sum over all columns but the last two, and then he assigns a negative value when session_key is 1, so that these values are subtracted.
Then again, as far as I can see, the query can be simplified to:
SELECT
Fiscal_Name,
Period_Name,
Scenario_Name = MAX(Scenario_Name),
Fund_Name,
Function_Name,
Project_Name,
Account_Name,
Department_Name,
Value = SUM(CASE WHEN Scenario_Key = 1 THEN -1 ELSE 1 END * value)
FROM Sub
WHERE Scenario_Key IN (1,11)
GROUP BY Fiscal_Name, Period_Name, Fund_Name, Function_Name, Project_Name, Account_Name, Department_Name
ORDER BY Fiscal_Name, Period_Name, Fund_Name, Function_Name, Project_Name, Account_Name, Department_Name
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 12, 2013 at 1:00 am
Thanks Mr Sommarskog, that question came in after hometime.
It's good to see an "old master" at work here on ssc. I've been using your website - and recommending it to others - for many years.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply