Running Total Percentage

Window functions of SQl server are very useful. Many webpage like this one https://www.brentozar.com/sql-syntax-examples/window-function-examples-sql-server/  have written how to use them.  I want to share with you one segment of my sql code to get running total percentage using window functions.

I have this scenario and problem to solve: Every night, hundreds (or thousands) of our data warehouse table are updated. We want to know the updated time of those table that we are interested. So, I want to have the updated count and running total percentage for the update.

When I search for running total percentage in google, this two appears on the top of the list.   https://www.1keydata.com/sql/sql-cumulative-percent-to-total.html       https://stackoverflow.com/questions/12931118/calculate-running-percentage-in-sql

However, it seems that the total of these two examples are only one number.  In my example, I used windows function for both the total and the single row to get running total for different windows.

here is my code:


select Schema_nm, table_Name, Load_time, number_updt, hour_of_day

,sum(NUMBER_UPDT) OVER (PARTITION BY Schema_nm, table_Name ORDER BY Load_time) as Running_Total

,CAST(CAST(sum(NUMBER_UPDT) OVER (PARTITION BY Schema_nm, table_Name ORDER BY Load_time) as decimal(18,2))/CAST(sum(NUMBER_UPDT) OVER (PARTITION BY Schema_nm, table_Name) as decimal(18,2))*100 as int) as running_percentage

from

(

select Schema_nm, table_Name, case when HOUR_OF_DAY >17 Then HOUR_OF_DAY when HOUR_OF_DAY <=17 Then HOUR_OF_DAY+24 end as Load_time, number_updt, hour_of_day

from

(

SELECT 'Schema_1' as Schema_nm, 'table_1’ as table_Name,  DATEPART(HOUR, Last_Updated_Date) AS HOUR_OF_DAY, COUNT (Table1_id) AS NUMBER_UPDT FROM Schema_1.table_1 WHERE Last_Updated_Date>GETDATE()-90 GROUP BY DATEPART(HOUR, Last_Updated_Date) UNION All

SELECT  'sechema_2' as Schema_nm,'table_2' as table_Name,  DATEPART(HOUR, Last_Updated_Ts) AS HOUR_OF_DAY, COUNT (table2_id) AS NUMBER_UPDT FROM schema_2.table_2 WHERE Last_Updated_Ts>GETDATE()-90 GROUP BY DATEPART(HOUR,Last_Updated_Ts )

) as result_set

) as result_set1

Order by Schema_nm, table_Name, Load_time

The part:

when HOUR_OF_DAY >17 Then HOUR_OF_DAY when HOUR_OF_DAY <=17 Then HOUR_OF_DAY+24 end

is for: We want to see the update time starting from 18:00 when schedules job starts. Not from 0:00, although this is the natural number to start mathematically.

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *