CODEWITHSUNDEEP
sqldb2
Anthony
Anthony

Reputation: 169

Getting calculated percentages within group using SQL

I have a dataset:

Date

June 2011 
July 2011
Aug 2011
Sep 2011
Oct 2011
Jan 2012
Feb 2012
Mar 2012
Apr 2013
May 2013

that records down the date registered for each yearly project. (There are no project IDs however)

I would like to add in the additional variable Percentage, which represent the average progress made for that month. (For instance, if the project is registered for 4 months, then each month would progress incrementally by 25 %, (25,50,75,100)), specifically:

Percentage     Date
20             June 2011
40             July 2011
60             Aug 2011
80             Sep 2011
100            Oct 2011
33             Jan 2012
66             Feb 2012
100            Mar 2012
50             Apr 2013
100            May 2013

However, my main problem would be that I am unable to know the starting month (period) and ending month (period) for each project for each year.

Are there any functions in SQL to create the calculated percentages in this case? I thought of creating a year variable and further using an indicator to indicate the start/end of the progress, but could not move on further.

Thank you again!

Upvotes: 1

Views: 156

Answers (2)

D-Shih
D-Shih

Reputation: 46239

You can try this query.

Getting ROW_NUMBER() by year(dates) on subquery.

then get the percent.

SELECT (FLOOR(CAST(T2.RK AS decimal) * 100/(
    SELECT COUNT(1) AS Totle
    FROM T 
    WHERE year(dates) = T2.dates)))  [Percentage],
    T2.dates as [years],
    T2.months as [months]
FROM 
(
    SELECT ROW_NUMBER() OVER(PARTITION BY year(dates) ORDER BY dates DESC) AS RK,
           year(dates) as dates,
           month(dates) as months
    FROM T
) AS T2 
GROUP BY T2.dates,T2.RK,T2.months

Upvotes: 1

srp
srp

Reputation: 585

Here is a simple Pseudo sql to get what you want . ?

select Year(date), Month(date)  ,  
 (select sum(Progress_percentage) from dataset b  where  b.date <=a.date ) as 
subquery_percentage
from dataset a
group by Year(date), Month(date)

Upvotes: 1

Related Questions