March 21, 2015 at 7:00 pm
Hi All,
I like to create an SQL view to divide amount 300,000 between 12 month starting from Month July 2014 to June 2015 as shown below
Amount Month Year
25,000 July 2014
25,000 August 2014
25,000 September 2014
25,000 October 2014
25,000 November 2014
25,000 December 2014
25,000 January 2015
25,000 February 2015
.
.
.
.
25,000 June 2015
Thanks
March 22, 2015 at 1:42 am
jaggy99 (3/21/2015)
Hi All,I like to create an SQL view to divide amount 300,000 between 12 month starting from Month July 2014 to June 2015 as shown below
Amount Month Year
25,000 July 2014
25,000 August 2014
25,000 September 2014
25,000 October 2014
25,000 November 2014
25,000 December 2014
25,000 January 2015
25,000 February 2015
.
.
.
.
25,000 June 2015
Thanks
Quick question, can you post DDL for the source table and consumable (insert statement) sample data? Makes it a lot easier to answer the question.
😎
March 22, 2015 at 6:18 am
Hi Hall of Fame,
Actually there is no table, I'm looking how I can create a view so I can join it with another view.
Thanks
March 22, 2015 at 6:29 am
jaggy99 (3/22/2015)
Hi Hall of Fame,Actually there is no table, I'm looking how I can create a view so I can join it with another view.
Thanks
So where is the data coming from then?
😎
March 22, 2015 at 7:01 am
The amount 300,000 is a budget figure which I'm given. I'm just trying to use sql to blend data so thinking if I can create a view to spread the amount across 12 months.
Thanks
March 22, 2015 at 7:48 am
jaggy99 (3/22/2015)
The amount 300,000 is a budget figure which I'm given. I'm just trying to use sql to blend data so thinking if I can create a view to spread the amount across 12 months.Thanks
Here is a quick solution
😎
USE tempdb;
GO
SET NOCOUNT ON;
GO
CREATE VIEW dbo.SPLIT_THE_YEAR_BUDGET AS
WITH YEAR_BUDGET AS
(
SELECT
CONVERT(NUMERIC(12,2),300000,0) AS BUDGET_AMOUNT
,CONVERT(DATE,'2014-07-01',0) AS BUDGET_FIRST_MONTH
)
,YEAR_MONTHS(MONTH_NO) AS
(
SELECT 0 UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 UNION ALL
SELECT 10 UNION ALL
SELECT 11
)
SELECT
CONVERT(NUMERIC(12,2),YB.BUDGET_AMOUNT / 12.0,0) AS Amount
,DATENAME(MONTH,DATEADD(MONTH,YM.MONTH_NO,YB.BUDGET_FIRST_MONTH)) AS [Month]
,DATEPART(YEAR,DATEADD(MONTH,YM.MONTH_NO,YB.BUDGET_FIRST_MONTH)) AS [Year]
FROM YEAR_BUDGET YB
CROSS APPLY YEAR_MONTHS YM;
GO
SELECT
SYB.[Amount]
,SYB.[Month]
,SYB.[Year]
FROM dbo.SPLIT_THE_YEAR_BUDGET SYB
GO
Results
Amount Month Year
---------- ----------- -----
25000.00 July 2014
25000.00 August 2014
25000.00 September 2014
25000.00 October 2014
25000.00 November 2014
25000.00 December 2014
25000.00 January 2015
25000.00 February 2015
25000.00 March 2015
25000.00 April 2015
25000.00 May 2015
25000.00 June 2015
March 22, 2015 at 8:30 pm
I assume that it doesn't matter to you, if you use Erikur's example to split 300001 into twelve months of 25000.08, it doesn't add up back to 300001.
On the other hand, if it does:
Financial Rounding of Allocations [/url]
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 23, 2015 at 2:58 am
jaggy99 (3/22/2015)
The amount 300,000 is a budget figure which I'm given. I'm just trying to use sql to blend data so thinking if I can create a view to spread the amount across 12 months.Thanks
An inline function works well too;
CREATE FUNCTION IF_SplitAmountOverFiscalYear
(@StartDate DATE,
@Amount DECIMAL (10,2))
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN (
SELECT
Amount = AmountFraction,
[Month] = DATENAME(MONTH,Datevalue),
[Year] = YEAR(Datevalue)
FROM (
SELECT
Datevalue = DATEADD(MONTH,n,@StartDate),
AmountFraction = @Amount/12.000
FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) d (n)
) d
)
GO
SELECT s.* FROM dbo.IF_SplitAmountOverFiscalYear ('20140701', 300000) s
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
November 19, 2015 at 3:57 am
I have a columns in SQL Server called TodayDate,EndDate,Amount and RemainingDays, where RemaningDays is deference of TodayDate and EndDate, and my query is I need to split or scatter a amount into number of months for a given remaining days in SQL server 2008
PO Balance Amount Today's DateNew End date Remaining daysDaily 2015-112015-122016-01
$14,117.64 10/29/2015 1/4/2016 67 $210.71 $6,321.33 $6,532.04 $842.84
67 days means 3 months 7 days like that, it has convert days to months and amount need to split into corresponding months
If Amount is 6500 and it has to split into 2000 for Jan, 2000 for Feb,2000 for Mar and 500 to April
can anyone help me.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply