# 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?

Tuesday, August 9, 2016 5:57 AM

• 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

• Proposed as answer by Wednesday, August 17, 2016 10:03 AM
• Marked as answer by Friday, August 19, 2016 7:06 AM
Wednesday, August 17, 2016 9:59 AM

### All replies

• 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

Tuesday, August 9, 2016 12:11 PM
• 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

Wednesday, August 10, 2016 11:07 AM
• 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.

Thank you,

Sharaschandra.

Friday, August 12, 2016 6:15 AM
• 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

• Proposed as answer by Monday, August 15, 2016 10:55 AM
• Edited by Tuesday, August 16, 2016 9:05 AM Change “[status] = 560” to “[Dispatch type] = ‘Courier’” in the where clause
Friday, August 12, 2016 9:06 AM
• 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.

Tuesday, August 16, 2016 4:04 AM
• 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

Tuesday, August 16, 2016 9:40 AM
• 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.

Wednesday, August 17, 2016 4:17 AM
• 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

• Proposed as answer by Wednesday, August 17, 2016 10:03 AM
• Marked as answer by Friday, August 19, 2016 7:06 AM
Wednesday, August 17, 2016 9:59 AM
• Hi Albert,

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

Regards,

Sharaschandra.

Friday, August 19, 2016 7:06 AM