November 12, 2013 at 7:49 pm
Hi Guys,
I need some help here. I have posted the DDL statements below.
In my report I have 2 scenario's overlap each other based on when we upload information. But in the database they are 2 separate line items. I need to find the difference between 2 same intersections in different scenario's and move the variance ahead into the next month
If there is only a unique intersection then just pass that if scenario is Jul.
Lets work on moving Nov variances into Dec. Let me know if you have questions. Thanks.
Database :
fundscenario Year accountfunctiondeptOct NovDec Jan
10Actual 2014 6500090 420200 1000 0
10July 2014 6500090 422150 200444 555
Report Structure
fundscenario Year accountfunctiondeptOct NovDec Jan
10July 2014 6500090 420200 100444 555
DDL Statement
Expected Results :
fundscenario Year accountfunctiondeptOct NovDec Jan
10July 2014 6500090 420200 100 544(=200-100+444) 555
Report :
CREATE TABLE [dbo].[test1](
[fund_name] [varchar](80) NULL,
[scenario_name] [varchar](80) NULL,
[fiscal_name] [varchar](80) NULL,
[project_name] [varchar](80) NULL,
[account_name] [varchar](80) NULL,
[function_name] [varchar](80) NULL,
[department_name] [varchar](80) NULL,
[planning_year_name] [varchar](80) NULL,
[special_name] [varchar](80) NULL,
[Jun] [float] NOT NULL,
[Jul] [float] NOT NULL,
[Aug] [float] NOT NULL,
[Sep] [float] NOT NULL,
[Oct] [float] NOT NULL,
[Nov] [float] NOT NULL,
[Dec] [float] NOT NULL,
[Jan] [float] NOT NULL,
[Feb] [float] NOT NULL,
[Mar] [float] NOT NULL,
[Apr] [float] NOT NULL,
[May] [float] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[test1] ([fund_name], [scenario_name], [fiscal_name], [project_name], [account_name], [function_name], [department_name], [planning_year_name], [special_name], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec], [Jan], [Feb], [Mar], [Apr], [May]) VALUES (N'10', N'Actual', N'2014', N'0000', N'65000', N'90', N'420', N'None', N'0', 0, 0, 0, 0, 149.92, 0, 0, 0, 0, 0, 0, 0)
INSERT [dbo].[test1] ([fund_name], [scenario_name], [fiscal_name], [project_name], [account_name], [function_name], [department_name], [planning_year_name], [special_name], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec], [Jan], [Feb], [Mar], [Apr], [May]) VALUES (N'10', N'Actual', N'2014', N'0000', N'65000', N'90', N'421', N'None', N'0', 0, 85.49, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
INSERT [dbo].[test1] ([fund_name], [scenario_name], [fiscal_name], [project_name], [account_name], [function_name], [department_name], [planning_year_name], [special_name], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec], [Jan], [Feb], [Mar], [Apr], [May]) VALUES (N'10', N'Actual', N'2014', N'0000', N'65000', N'90', N'422', N'None', N'0', 0, 0, 249.28, 15, 10.64, 100.12, 0, 0, 0, 0, 0, 0)
INSERT [dbo].[test1] ([fund_name], [scenario_name], [fiscal_name], [project_name], [account_name], [function_name], [department_name], [planning_year_name], [special_name], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec], [Jan], [Feb], [Mar], [Apr], [May]) VALUES (N'10', N'Jul (1+11) Forecast', N'2014', N'0000', N'65000', N'90', N'420', N'None', N'0', 0, 100, 0, 100, 100, 16.67, 33.33, 50, 0, 0, 0, 0)
INSERT [dbo].[test1] ([fund_name], [scenario_name], [fiscal_name], [project_name], [account_name], [function_name], [department_name], [planning_year_name], [special_name], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec], [Jan], [Feb], [Mar], [Apr], [May]) VALUES (N'10', N'Jul (1+11) Forecast', N'2014', N'0000', N'65000', N'90', N'422', N'None', N'0', 100, 0, 0, 0, 0, 250, 250, 200, 0, 0, 0, 0)
INSERT [dbo].[test1] ([fund_name], [scenario_name], [fiscal_name], [project_name], [account_name], [function_name], [department_name], [planning_year_name], [special_name], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec], [Jan], [Feb], [Mar], [Apr], [May]) VALUES (N'10', N'Jul (1+11) Forecast', N'2014', N'0200', N'65000', N'90', N'422', N'None', N'0', 0, 0, 0, 250, 0, 250, 0, 0, 0, 0, 0, 0)
INSERT [dbo].[test1] ([fund_name], [scenario_name], [fiscal_name], [project_name], [account_name], [function_name], [department_name], [planning_year_name], [special_name], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec], [Jan], [Feb], [Mar], [Apr], [May]) VALUES (N'10', N'Jul (1+11) Forecast', N'2014', N'0210', N'65000', N'90', N'422', N'None', N'0', 0, 0, 0, 0, 0, 250, 0, 0, 0, 0, 0, 0)
November 13, 2013 at 7:33 am
Your sample data does not match the explanation of desired output. This sounds like you want a type of running total but I can't quite figure out what you are trying to do here. Can you explain the desired output using the sample data you provided?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 13, 2013 at 10:27 am
Hi Sean,
I tried give a snapshot of the data. Sorry if it wasn't helpful. here you go. Posting everything again.
yes like running total but I want to move only Nov values and add it to Dec and leave the remaining columns as it
Data in The Database[/color]
fundscenario fiscal project account function dept planning special Jun Jul Aug SepOctNov Dec Jan Feb Mar Apr May
10Actual2014 06500090420None00000149.920000000
10Actual2014 06500090421None0085.490000000000
10Actual2014 06500090422None000249.281510.64100.12000000
10July2014 06500090420None00100010010016.6733.33500000
10July2014 06500090422None010000002502502000000
10July2014 2006500090422None00002500250000000
10July2014 2106500090422None000000250000000
Logic : Perform Scenario Value (July) - Actual for duplicate intersections, else move the values forward and move the variance in the month of Nov and add it to Dec value.
Explanation of the bold values :
For this intersection :
Duplicate Intersections
Nov Dec
Actual July Actual July
201406500090420 0 16.67 0 (16.67-0+33.33(Already existing value))
Else still move the number and add it to Dec in case of unique intersection.
Expected results : (See numbers in bold. Thats where I want changes to reflect)
fundscenario fiscal project account function dept planning special Jun Jul Aug SepOctNov Dec Jan Feb Mar Apr May
10Actual201406500090420None00000149.920000000
10Actual201406500090421None0085.490000000000
10Actual201406500090422None000249.281510.64100.12000000
10July201406500090420None00100010010016.6750500000
10Jul y201406500090422None01000000250399.882000000
10July20142006500090422None0000250025025000000
10July20142106500090422None00000025025000000
DDL Statements
CREATE TABLE [dbo].[test1](
[fund] [varchar](80) NULL,
[scenario] [varchar](80) NULL,
[fiscal] [varchar](80) NULL,
[project] [varchar](80) NULL,
[account] [varchar](80) NULL,
[function] [varchar](80) NULL,
[dept] [varchar](80) NULL,
[planning] [varchar](80) NULL,
[special] [varchar](80) NULL,
[Jun] [float] NOT NULL,
[Jul] [float] NOT NULL,
[Aug] [float] NOT NULL,
[Sep] [float] NOT NULL,
[Oct] [float] NOT NULL,
[Nov] [float] NOT NULL,
[Dec] [float] NOT NULL,
[Jan] [float] NOT NULL,
[Feb] [float] NOT NULL,
[Mar] [float] NOT NULL,
[Apr] [float] NOT NULL,
[May] [float] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[test1] ([fund], [scenario], [fiscal], [project], [account], [function], [dept], [planning], [special], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec], [Jan], [Feb], [Mar], [Apr], [May]) VALUES (N'10', N'Actual', N'2014', N'0000', N'65000', N'90', N'420', N'None', N'0', 0, 0, 0, 0, 149.92, 0, 0, 0, 0, 0, 0, 0)
INSERT [dbo].[test1] ([fund], [scenario], [fiscal], [project], [account], [function], [dept], [planning], [special], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec], [Jan], [Feb], [Mar], [Apr], [May]) VALUES (N'10', N'Actual', N'2014', N'0000', N'65000', N'90', N'421', N'None', N'0', 0, 85.49, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
INSERT [dbo].[test1] ([fund], [scenario], [fiscal], [project], [account], [function], [dept], [planning], [special], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec], [Jan], [Feb], [Mar], [Apr], [May]) VALUES (N'10', N'Actual', N'2014', N'0000', N'65000', N'90', N'422', N'None', N'0', 0, 0, 249.28, 15, 10.64, 100.12, 0, 0, 0, 0, 0, 0)
INSERT [dbo].[test1] ([fund], [scenario], [fiscal], [project], [account], [function], [dept], [planning], [special], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec], [Jan], [Feb], [Mar], [Apr], [May]) VALUES (N'10', N'Jul (1+11) Forecast', N'2014', N'0000', N'65000', N'90', N'420', N'None', N'0', 0, 100, 0, 100, 100, 16.67, 33.33, 50, 0, 0, 0, 0)
INSERT [dbo].[test1] ([fund], [scenario], [fiscal], [project], [account], [function], [dept], [planning], [special], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec], [Jan], [Feb], [Mar], [Apr], [May]) VALUES (N'10', N'Jul (1+11) Forecast', N'2014', N'0000', N'65000', N'90', N'422', N'None', N'0', 100, 0, 0, 0, 0, 250, 250, 200, 0, 0, 0, 0)
INSERT [dbo].[test1] ([fund], [scenario], [fiscal], [project], [account], [function], [dept], [planning], [special], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec], [Jan], [Feb], [Mar], [Apr], [May]) VALUES (N'10', N'Jul (1+11) Forecast', N'2014', N'0200', N'65000', N'90', N'422', N'None', N'0', 0, 0, 0, 250, 0, 250, 0, 0, 0, 0, 0, 0)
INSERT [dbo].[test1] ([fund], [scenario], [fiscal], [project], [account], [function], [dept], [planning], [special], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec], [Jan], [Feb], [Mar], [Apr], [May]) VALUES (N'10', N'Jul (1+11) Forecast', N'2014', N'0210', N'65000', N'90', N'422', N'None', N'0', 0, 0, 0, 0, 0, 250, 0, 0, 0, 0, 0, 0)
November 13, 2013 at 11:48 am
Well the sample data seems to match up with the description. However, the description is totally unclear. Why do values start changing in July? You mention something about multiple intersections, what are these intersections? Keep in mind that I have no idea what your project is supposed to do or what any of these values mean.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 13, 2013 at 12:22 pm
Hi Sean,
Here I agree it's tough to figure out without background knowledge.
Please see below :
Intersections are a combination of the following below
fund fiscal project account function dept planning special
10 2014 06500090420None 0
why would the numbers change ?
Assume a person says he need 100$ to spend for a month. But actually ends up spending 80$. So both these transactions are captured.
100$ would be captured in the Scenario 'July' - So this basically a projection of future spending.
80$ would be captured in the Scenario 'Actual'
So what we would like to do is take the 100-80 = 20 and add them to the next month. But I want the subtraction to happen on Nov and move the variances to december and not change any of the other values.
Hope it's clear now. Thanks.
November 13, 2013 at 6:31 pm
I think I have figured this out with an update statement. Wonder if a Merge statement would be a slicker operation on millions of rows.
update b
set b.Dec = b.Dec + a.Nov
from dbo.Test1 b
INNER JOIN (
SELECT
Fiscal,
Scenario = MAX(Scenario),
Fund,
[Function],
Project,
Account,
Dept,
Nov = SUM(CASE WHEN Scenario = 'Actual' THEN -1 ELSE 1 END * Nov)
,[Dec] = SUM(Dec)
FROM dbo.test1
WHERE Scenario IN ('Actual','July')
GROUP BY Fiscal, Fund, [Function], Project, Account, dept
--ORDER BY Fiscal, Fund, [Function], Project, Account, dept
) a
ON
b.fiscal = a.fiscal and
b.Scenario = a.Scenario and
b.fund = a.fund and
b.project = a.Project and
b.account = a.account and
b.dept = a.dept
November 13, 2013 at 6:34 pm
Thank you Sean for your help though. Gotta love this forum and the people here.
November 13, 2013 at 7:10 pm
As you appear to be using SQL 2012, have you looked at using the LAG function to solve this?
It would probably be a lot faster than what you settled on.
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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply