SQL Query to Calculate Monthly Budget
-
CREATE TABLE [dbo].[EmployeeMonthlyBudget]([Name] [nvarchar](50) NULL,[Budget_Day] [money] NULL,[DateCreated] [datetime] NULL,[DateDeleted] [datetime] NULL) INSERT [dbo].[EmployeeMonthlyBudget] ([ID], [Name], [Budget_Day], [DateCreated], [DateDeleted]) VALUES (1, N'SAM', 1.0000, CAST(N'2018-01-10T00:00:00.000' AS DateTime), CAST(N'2018-05-20T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[EmployeeMonthlyBudget] ([ID], [Name], [Budget_Day], [DateCreated], [DateDeleted]) VALUES (2, N'ROB', 2.0000, CAST(N'2018-02-15T00:00:00.000' AS DateTime), NULL)
GO
INSERT [dbo].[EmployeeMonthlyBudget] ([ID], [Name], [Budget_Day], [DateCreated], [DateDeleted]) VALUES (3, N'BOB', 3.0000, CAST(N'2018-01-15T00:00:00.000' AS DateTime), CAST(N'2018-02-22T00:00:00.000' AS DateTime))
GOWe are able to calculate the budget per month for each Employee Name, but unable to figure out how to calculate them for consecutive months.
So we need to calculate the budget for each month until DateDeleted has valuePlease, anyone, help me on this How to write SQL for the above approach. Please sample table data
So the final output like as in the attached need to calculate the monthly spend based on the number of days user per each month
-
This doesn't make any sense. The data doesn't help with what you want. Can you explain? This is what the sample data produced
ID Name Budget_Day DateCreated DateDeleted
1 SAM 1.00 2018-01-10 00:00:00.000 2018-05-20 00:00:00.000
2 ROB 2.00 2018-02-15 00:00:00.000 NULL
3 BOB 3.00 2018-01-15 00:00:00.000 2018-02-22 00:00:00.000For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ - Mike01 - Thursday, December 27, 2018 12:03 PM
Mike,
We need to calculate total budget for each person based on number of days in each month, until date deleted column has a value.
The formula would be number of days per month * budgetday
Suppose if you look at the BOB record date created is Jan 15th so we need to calculate Bob budget starting from Jan 15th to 31 for that month and for February we need to calculate until 22 days.
If you look at Rob record the budget calculation will start from February 15 up to current date because date deleted has null value.
So I need to data for each employee for each month between any two date give ranges input - madanvenkat535 3293 - Thursday, December 27, 2018 12:56 PM
Got that but, because this is December, why did Rob's entries in your desired output stop in in October? Or is that just a posting anomaly?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Thursday, December 27, 2018 3:39 PM
Jeff,
So we need to get the records from two given dates I had taken up to october in that image suppose if we give December we need to get up to December:) - madanvenkat535 3293 - Thursday, December 27, 2018 7:26 PM
Understood. I'll be back.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
First things first. We need to generate date sequences. There are a whole lot of methods to do that but it's pretty simple when you use the "Swiss Army Knife" (as a good friend of mine calls it) of T-SQL. Here's the code for the iTVF (Inline Table Valued Function) that I'm talking about. Read the documentation. And, it's good for a whole lot more that just this one type of thing. It won't go to waste and it's much faster than a While Loop or rCTE (Recursive CTE) to do the same thing.
Also, if you have to get this stuff past a DBA, tell them that this is a high performance iTVF and has none of the slothfulness associated with scalar or mTVFs (Multi-statement Table Valued Functions).
Here's the code for the function called dbo.fnTally. I don't normally use Hungarian Notation (object type is part of the object name) but I also have a dbo.Tally table and needed to make the differentiation.
CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
Purpose:
Return a column of BIGINTs from @ZeroOrOne up to and including @MaxN with a max value of 10 Quadrillion.Usage:
--===== Syntax example
SELECT t.N
FROM dbo.fnTally(@ZeroOrOne,@MaxN) t
;Notes:
1. This code works for SQL Server 2005 and up.
2. Based on Itzik Ben-Gan's cascading CTE (cCTE) method for creating a "readless" Tally Table source of BIGINTs.
Refer to the following URL for how it works. http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers
3. To start a sequence at 0, @ZeroOrOne must be 0 or NULL. Any other value that's convertable to the BIT data-type
will cause the sequence to start at 1.
4. If @ZeroOrOne = 1 and @MaxN = 0, no rows will be returned.
5. If @MaxN is negative or NULL, a "TOP" error will be returned.
6. @MaxN must be a positive number from >= the value of @ZeroOrOne up to and including 10 Quadrillion. If a larger
number is used, the function will silently truncate after 10 Quadrillion. If you actually need a sequence with
that many values, you should consider using a different tool. ;-)
7. There will be a substantial reduction in performance if "N" is sorted in descending order. If a descending
sort is required, use code similar to the following. Performance will decrease by about 27% but it's still
very fast especially compared with just doing a simple descending sort on "N", which is about 20 times slower.
If @ZeroOrOne is a 0, in this case, remove the "+1" from the code.DECLARE @MaxN BIGINT;
SELECT @MaxN = 1000;
SELECT DescendingN = @MaxN-N+1
FROM dbo.fnTally(1,@MaxN);8. There is no performance penalty for sorting "N" in ascending order because the output is explicity sorted by
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))Revision History:
Rev 00 - Unknown - Jeff Moden
- Initial creation with error handling for @MaxN.
Rev 01 - 09 Feb 2013 - Jeff Moden
- Modified to start at 0 or 1.
Rev 02 - 16 May 2013 - Jeff Moden
- Removed error handling for @MaxN because of exceptional cases.
Rev 03 - 07 Sep 2013 - Jeff Moden
- Change the max for @MaxN from 10 Billion to 10 Quadrillion to support an experiment.
This will also make it much more difficult for someone to actually get silent truncation in the future.
**********************************************************************************************************************/
(@ZeroOrOne BIT, @MaxN BIGINT)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH
E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) --up to 10 rows
, E4(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d) --up to 10 Thousand rows
,E16(N) AS (SELECT 1 FROM E4 a, E4 b, E4 c, E4 d) --up to 10 Quadrillion rows
SELECT N = 0 WHERE ISNULL(@ZeroOrOne,0)= 0 --Conditionally start at 0.
UNION ALL
SELECT TOP(@MaxN) N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E16
;
GONext, the cleaned up and corrected version of your sample data. You were missing the ID that's in data from the table and, of course, that caused an error. I usually don't even bother with posts that have such errors. It's the ol' "If you don't care enough to help me help you, then I'll move on" thing that I talk about in the article at the first link in my signature line below under "Helpful Links". 😉
I also used a temp table instead of a permanent table just because it's my nature to do so for these types of demonstrations. It's also a bit safer that way because it also eliminates the possibility of name collisions for folks that want to try out the code.
--=============================================================================
-- Create the test environment.
-- This is NOT a part of the solution.
--=============================================================================
--===== Create the test table.
-- I added the missing ID column that appears in the test data that follows.
CREATE TABLE #EmployeeMonthlyBudget --DROP TABLE #EmployeeMonthlyBudget
(
ID INT NOT NULL
,Name NVARCHAR(50) NULL
,Budget_Day MONEY NULL
,DateCreated DATETIME NULL
,DateDeleted DATETIME NULL
)
;
--===== Populate the test table with the cleaned up test data provided
INSERT #EmployeeMonthlyBudget
(ID, Name, Budget_Day, DateCreated, DateDeleted)
VALUES (1, N'SAM', 1.0000, '2018-01-10', '2018-05-20')
,(2, N'ROB', 2.0000, '2018-02-15', NULL)
,(3, N'BOB', 3.0000, '2018-01-15', '2018-02-22')
;
Now that all that preliminary work is done, on with one possible solution...
--=============================================================================
-- Produce the desired posted output
--=============================================================================
--===== Assign a date to be used when DateDeleted is NULL.
DECLARE @CutoffDate DATETIME = '31 Oct 2018'
;
--===== Do the work in "DRY" (Don't Repeat Yourself) cascading CTEs (cCTE).
WITH
cteMonthStart AS
(--===== Generate the date each month of the span for each person starts
-- and carry forward some of the other columns we'll need
SELECT ID
,Name
,Budget_Day
,DateCreated
,DateDeleted
,MonthStart = DATEADD(mm,DATEDIFF(mm,0,DateCreated)+t.N,0)
FROM #EmployeeMonthlyBudget
CROSS APPLY dbo.fnTally(0,DATEDIFF(mm,DateCreated,ISNULL(DateDeleted,@CutoffDate)))t
)
,cteMonthEnd AS
(--==== Calculating the month end becomes trivial after that in a very "DRY" sense.
-- Note that I don't use EOMonth because it tends to be slow compared to the following.
-- We also carry forward the other columns that we need proving that SELECT * is NOT
-- poison everywhere it may be used.
SELECT *
,MonthEnd = DATEADD(mm,1,MonthStart)-1
FROM cteMonthStart
)--==== Once all those calculations are done, we can do the "presentation layer".
SELECT Name
,Budget_Day
,MonthName = DATENAME(mm,MonthStart)
,TotalSpent = Budget_Day
* DATEDIFF(--===== This is where we calculate the days of each month
dd
,CASE WHEN DateCreated > MonthStart THEN DateCreated ELSE MonthStart END
,CASE WHEN DateDeleted < MonthEnd THEN DateDeleted ELSE MonthEnd END +1
)
FROM cteMonthEnd
ORDER BY Name,ID,MonthStart
;
... and that produces exactly what you requested and, ostensibly, exactly what someone requested of you.But what happens if you have, say, more than 1 "Sam"? And once the original requester sees the output, what do you suppose the next questions/requirements will be? How about "Can you provide a sub-total by person as well as a Grand Total"? And then the next question after they see that... "Ok... we want this for multiple years and we need sub-totals by each person by year with a Grand Total for each person and a sub-total by each year for all persons that year and that Grand Total.
Are any of those other things I mentioned a possibility? You can say "No" but have you actually asked?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Thursday, December 27, 2018 10:17 PM
Jeff, Sorry for a missing ID part.
Are any of those other things I mentioned a possibility- Yes
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply