March 22, 2019 at 8:09 am
Hi,
I am trying to write a stored procedure that calculates a total at each point in time.
This is my dataset below
| Type | User | Rate(%) | Start Date | End Date |
| 1 | User1 | 30 | 01-Jan | 31-Dec |
| 1 | User2 | 30 | 01-Jan | 31-Dec |
| 1 | User3 | 20 | 01-Jan | 31-Dec |
| 1 | User4 | 20 | 01-Jan | 30-Jun |
| 1 | User5 | 20 | 01-Jul | 31-Dec |
At each point in time, the total rate is 100%.
How can I write a code that runs that check? Can anyone help?
Let me know if you need more clarification.
March 22, 2019 at 8:25 am
You could join a date-table with your information.
pseudocode:
select mydate,sum(rate) total
from datetable
inner join ratetable
on datetable.datekey>=ratetable.startdate
and dataetable.datekey<=ratetable.enddate
group by mydate
Didn't try to type over the table information.
March 22, 2019 at 8:36 am
Hey Jo
Thanks a lot.
I am not sure I follow your reasoning.
What do you mean " a date table"? I already have the start and end date for each rate by user in the dataset.
So you mean a table with every dates of the year?
March 22, 2019 at 9:08 am
anikeh - Friday, March 22, 2019 8:36 AMHey Jo
Thanks a lot.
I am not sure I follow your reasoning.
What do you mean " a date table"? I already have the start and end date for each rate by user in the dataset.
So you mean a table with every dates of the year?
It would be helpful if you write down what the output should be, I can't make sense of the question, the rate column in the data says 30% or 20% then in your text you say "At each point in time, the total rate is 100%."??
March 22, 2019 at 9:40 am
hi Jonathan
I am looking for an output like this
| Date | Sum Rate |
| 31-Jan | 100 |
| 28-Feb | 100 |
| 31-Mar | 100 |
| 30-Apr | 100 |
| 31-May | 100 |
| 30-Jun | 100 |
| 31-Jul | 100 |
| 31-Aug | 100 |
| 30-Sep | 100 |
| 31-Oct | 100 |
| 30-Nov | 100 |
| 31-Dec | 100 |
Does this makes sense?
March 22, 2019 at 9:51 am
anikeh - Friday, March 22, 2019 9:40 AMhi JonathanI am looking for an output like this
Date Sum Rate 31-Jan 100 28-Feb 100 31-Mar 100 30-Apr 100 31-May 100 30-Jun 100 31-Jul 100 31-Aug 100 30-Sep 100 31-Oct 100 30-Nov 100 31-Dec 100 Does this makes sense?
It's easy enough to do that, but what's the relevance of including 20% and 30% in your original question?
March 22, 2019 at 9:57 am
Because i am adding up the rates. I thought it would be easy to know what is being added up.
You said it is easy.
How would you do it?
March 22, 2019 at 10:21 am
anikeh - Friday, March 22, 2019 9:57 AMBecause i am adding up the rates. I thought it would be easy to know what is being added up.
You said it is easy.
How would you do it?
There is a bit of a problem with your dates. They don't have the year, and the 28th February isn't always the end of the month. So do you have the column stored as datetime on your original dataset?
March 22, 2019 at 10:23 am
As Jo said, use some variation on a calendar table. (Since your ranges all start on month starts and end on month ends, it easy. If that's not actually the case, it becomes a bit more complicated.)
WITH beginning_of_month AS
(
SELECT DATEADD(MONTH, ROW_NUMBER() OVER( ORDER BY @@VERSION ), '2018-12-01') AS BOM FROM ( VALUES(0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0) ) v(n)
)
, YourData AS
(
SELECT *
FROM
(
VALUES
(1, 'User1', 30, CAST('2019-01-01' AS DATE), CAST('2019-12-31' AS DATE))
, (1, 'User2', 30, '2019-01-01', '2019-12-31')
, (1, 'User3', 20, '2019-01-01', '2019-12-31')
, (1, 'User4', 20, '2019-01-01', '2019-06-30')
, (1, 'User5', 20, '2019-07-01', '2019-12-31')
) v([Type], [User], Rate, Start_dt, End_dt)
)
SELECT bom.BOM, SUM(yd.Rate) AS Total_Rate
FROM YourData yd
INNER JOIN beginning_of_month bom
ON yd.Start_dt <= bom.BOM
AND bom.BOM < yd.End_dt
GROUP BY bom.BOM
Drew
PS: You didn't have valid dates, so I converted those to valid dates. If those are supposed to represent recurring intervals, it, again, becomes more complicated.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply