I want to display a result like this:
My data for each column is as follows:
CustomerID FirstName Total_Count_Status ResultMessage 1 Noor 10 Completed Calls 2 ALi 6 InProgress Calls 3 Jhon 9 CallBack Calls 4 Maaan 1 MoneyCall Calls 5 samuel 5 Cancelled Calls 6 Khan 3 Dispatched Calls
In my workorder table I have the
OrderStatusID column data:
OrderStatusID 3 2 3 5 7
I have the following Hardcoded values for my Case:
for Pending = 0, for Dispatched = 1, InProgress = 2, Complete = 3, Estimate = 4, CallBack = 5, MoneyCall = 6, Cancelled = 7,
This will be used for show up their status.
i have my table name : WorkOrder which has column OrderStatusID i want to count OrderstatusID for each
if OrderStatusID='3' we should have to count all 3 values of orderstatusID column and display result like in the above table . same is for other ..values
How i can create Proc for situation like this ??Thanks
Any help will be highly appreciated.
Part of the syntax is:
CASE WHEN <condition> THEN <expression> ELSE <expression> END
The trick is to convert counts into sums and to evaluate the expressions as 0 or 1:
SELECT ..., COUNT(CASE WHEN OrderStatusID = 3 THEN 1 ELSE 0) AS CompletedCalls, COUNT(CASE WHEN OrderStatusID = 2 THEN 1 ELSE 0) AS InProgressCalls, ...
You'll need your FROM, WHERE, and GROUP BY clauses as usual.