How to write this select query in asp.net code behind
DECLARE @calls_table TABLE
(
ID INT ,
Division CHAR(1) ,
[STATUS] VARCHAR(50)
)
INSERT INTO @calls_table
( ID, Division, STATUS )
VALUES ( 1001, 'A', 'Open' ),
( 1002, 'B', 'Closed' ),
( 1003, 'A', 'Closed' ),
( 1004, 'C', 'Open' ),
( 1005, 'C', 'Open' )
SQL Query:
SELECT Division ,
SUM(CASE WHEN STATUS = 'Open' THEN 1
ELSE 0
END) AS Open_Calls ,
SUM(CASE WHEN STATUS = 'Closed' THEN 1
ELSE 0
END) AS Closed_Calls ,
COUNT(*) AS 'Total_Calls' ,
CONVERT(DECIMAL(5, 2), SUM(CASE WHEN STATUS = 'Closed' THEN 1
ELSE 0
END)) / COUNT(*) AS Closed_Calls_Perc
FROM @calls_table
GROUP BY Division
Please kindly give me the answer as soon as possible.
Thanks
Sathik