Need to calculate percentage to plot delivered cases against total dispatches

Question

Hello, 

I have 2 tables that has data for the articles that is being dispatched through different modes like courier and speed post. I have an SQL query below that pulls out few columns from the 2 tables i have. 

select a.article_id, a.product_code, 
cast (null as varchar(50))product_name, a.sub_product, cast (null as varchar (50))sub_product_name, 
cast (null as varchar(50)) product_type, a.status,a.DATA_DATE,a.wbn_id, b.DESTINATION_POSTCODE, b.SOURCE_POSTCODE, b.DESPATCH_MODE, b.DESPATCH_AGENCY, 
b.SOURCE_SERVICE_POINT, cast (null as varchar(30)) as Source_city, b.DESTINATION_SERVICE_POINT, cast (null as varchar (30)) as destination_city
into #delvry_strike_rate from RECONP.MAIN_T_ARTICLE_MASTER a left join RECONP.MAIN_T_WBN b on (a.wbn_id=b.wbn_id) where status in (560) and b. WBN_TYPE  in ('speed post','courier');
commit;

What i need is to calculate the percentage to plot delivered cases against total dispatches that is being done and then break it day wise. I'm stuck with this and not able to move further. Can someone help me complete this? 

Thanks in advance. 

Answer 1

It would be very helpful if you could give metadata of the tables involved, better yet, some input data and the desired output. In order to help you, we would have to copy your SQL code, from the code derive the schemas of RECONP.MAIN_T_ARTICLE_MASTER and RECONP.MAIN_T_WBN, make up some sample data and then write the SQL. 

In order for us to help you, you need to enable us to work on your issue. 

---

Benjamin Kettner

Answer 2

Hi Sharaschandra,

>> What i need is to calculate the percentage to plot delivered cases against total dispatches that is being done and then break it day wise. I'm stuck with this and not able to move further.

Since I’m not sure the table structures, I have created a demo as following code shows to calculate the percentage. You could have a look at it and then modify your code to achieve your goal.

declare @t table (col1 int, col2 varchar(20))
insert into @t values (1,'A'),(2,'A'),(3,'A'),(4,'A'),(5,'A'),(6,'B'),(7,'B')

;with cte
as
(
	select col2, concat(CAST(count(*) * 100.0 / sum(count(*)) over() as decimal(4,2)),'%') as percentage
	from @t  
	group by col2
)
select * from cte where col2 = 'B'

Best Regards,

Albert Zhang

Answer 3

Hi,

Thank you for the reply and sorry for responding so late. I'm in need of the below mentioned output. I'm sorry i can't provide more data as these information is pretty sensitive. 

product_name	Dispatch type	status	count()	Day 1%	Day 2%	Day 3%
Card-	Courier	560	5213719
Card-	speedpost	550	24128
Card-	Courier	590	959
Card-	speedpost	600	3366
Card-	Courier	750	18
Card-	speedpost	580	841
Card-	Courier	510	2505
Card-	speedpost	530	354153

Need to calculate the %of only delivered over the total dispatches on a daily basis. Consider 560 to be the delivered dispatches.

Hope this is helpful. 

Thank you,

Sharaschandra. 

Answer 4

Hi Sharaschandra,

>> I'm in need of the below mentioned output

Could you clarify what the “Day 1% Day 2% Day 3%” means? Do you want to calculate the percentage for each day?

>>Need to calculate the %of only delivered over the total dispatches on a daily basis. Consider 560 to be the delivered dispatches.

If you want to get the result 93.12% (= (5213719/ 5599689)*100%, 5599689=5213719+24128+..+354153), you could refer to my above code to change the “group by col2” to “group by status” and the “col2 = 'B'” to “status = 560” as following code shows.

;with cte
as
(
       select 
              product_name, [Dispatch type],[count]
              concat(CAST(SUM([count]) * 100.0 / SUM([count]) over() as decimal(4,2)),'%') as percentage
       from YourTable  
       group by [status]
)
select * from cte where [Dispatch type] = 'Courier'

If I’m wrong, please let me know and share a sample data and the desired output to clarify your need so that we could better help you.

Best Regards,

Albert Zhang

Answer 5

Hi Albert, 

Thank you for the input. Yes, i need to calculate the % for each day. Like i said before, for the delivered dispatches to the total dispatches. 

Day 1 can be the 1st day of the month and so on. 

Thank you,

Sharaschandra. 

Answer 6

Hi Sharaschandra,

>> Yes, i need to calculate the % for each day. Like i said before, for the delivered dispatches to the total dispatches. Day 1 can be the 1st day of the month and so on

If you want to calculate the percentage for each day, it would need some date related record to get it. So, you could show some sample data like this thread to clarify your need.

I have created a demo as following code shows to get the percentage for each day and you could have a look at it. For more things, please click here to learn the PIVOT.

declare @t table(DispatchType varchar(20), rdate date, rcount int)
insert into @t 
	select 'Courier', '2016-08-01', 2 
	union all 
	select 'speedpost', '2016-08-01', 2 
	union all  
	select 'Courier', '2016-08-02', 6 
	union all 
	select 'speedpost', '2016-08-02', 3 
	union all  
	select 'Courier', '2016-08-03', 3 
	union all 
	select 'speedpost', '2016-08-03', 4 
;with cte
as
(
	SELECT 
		DispatchType,rdate,concat(CAST((SUM(rcount) OVER(PARTITION BY DispatchType,rdate)) * 100.0 / SUM(rcount) over() as decimal(4,2)),'%') as percentage
	FROM @t
)

select
DispatchType, [2016-08-01] as day1,[2016-08-02]  as day2,[2016-08-03]  as day3
from cte
pivot
(
	max(percentage)
	for rdate
	in([2016-08-01],[2016-08-02],[2016-08-03])
)p

The result

Besides, I have made some changes in my above code and you’d better have a look at it.

Best Regards,

Albert Zhang

Answer 7

Hi Albert, 

Thank you again for the input. this code is pretty helpful. However, i just got to know that I'll have to calculate the percentage number of days it has taken to be delivered. 

Please find the desired output in the below screen shot. I was able to calculate the number of days to the delivery but not fit in the percentage. 

Answer 8

Hi Sharaschandra,

>> However, i just got to know that I'll have to calculate the percentage number of days it has taken to be delivered.

If you want to get the percentage follow following rule, you could refer to my T-SQL code below to achieve your goal.

Total day count = 16+3+1+4+2+29+8+6 +28= 97

First line result: 16/ 97 * 100% = 16.49%

Second line result = 3/97 * 100% = 3.09%

…..

Last line result = 28/ 97 * 100% = 28.87%

T-SQL code

select
 *, concat(CAST(NumberOfDaysToDelivery* 100.0/SUM(NumberOfDaysToDelivery) over() as decimal(4,2)),'%') as percentage
from Test

If the above it not what you want, please share your calculation rule as I shown above.

Best Regards,

Albert Zhang

Answer 9

Hi Albert, 

Thank you for the input. This was really helpful and i got my output this time. 

Regards,

Sharaschandra.