Welcome, This post will help you to make a chart for Success Pending order count datewise. This Sales chart shows the business summary of a month date-wise in just a glimpse. It takes a few seconds the get an idea of the whole month's business, It is a better choice to written business summary.
Developer Information
Language : C#
Framework : MVC ASP.Net
Database : SQL Server
Inline : HTML,CSS,Java script
Chart : Google Chart
Application : Show Success-Pending order datewise for a current month
Database Table
Let's get Start
At the beginning I thought it was a simple task but when I dived into dept, I found it a complicated task because success and pedning order is detected by the status field from the database. 1 indicates success, 0 indicates pending. The main problem was counting separately success,pending on the same date
I have used google chart for render chart. Chart data is three column format given below. First parameter is date,second , third for show chart lines for show pending or success order .
data.addRows([
[1, 0, 0],
[2, 2, 0],
[3, 0, 3],
[4, 18, 10]
]);
Requirement
As you see in the above chart data format, it is divided into three parts. The main challenge is to get pending and success counts datewise. I will show you the query step by step because to explanation of the entire query will be difficult to understand. Each sub-query will render output for the next query.
1. Get the pending and success order list of the current month datewise. Pending-Success valie save in status filed. 1 for success and 0 for pending.
with str1 as
(
select day(orderdate) as day,case when status=1 then sum(status) end as success,case when status=0 then count(status) end as pending from [Order]
group by (OrderDate),status having month(OrderDate)=month(getdate())
) select * from str1
I have saved query in CTE to output in str1 variable, which will be used output for next query.
Now we have the list of success and pending order list datewise. We have to combine this in a single row for datewise.
2. Now it is time to sum the pending and success order datewise by combining the above query output to other CTE variable str2
with str1 as
(
select day(orderdate) as day,case when status=1 then sum(status) end as success,case when status=0 then count(status) end as pending from [Order]
group by (OrderDate),status
having month(OrderDate)=month(getdate())
),
str2 as
(
select day,sum(success) as success,count(pending) as pending from str1 group by day,success,pending
) select * from str2
3. We got a list of success-pending order sum in above list. Now, it's time to remove NULL column and merge row into a single row by the query given below.
with str1 as
(
select day(orderdate) as day,case when status=1 then sum(status) end as success,case when status=0 then count(status) end as pending from [Order]
group by (OrderDate),status
having month(OrderDate)=month(getdate())
),
str2 as
(
select day,sum(success) as success,count(pending) as pending from str1 group by day,success,pending
)
select day,max(success) s,max(pending) as p from (
select day,isnull(success,'') as success,isnull(pending,'') as pending from str2 group by day,success,pending) tbl
group by day
Next Topic
