July 27, 2007 at 7:44 am
Hi
I have table data as below.
| Date | REGION | CALL NO | HOURS |
| 1/1/2007 | AA | 12 | 7.00 |
| 1/15/2007 | AA | 13 | 8.50 |
| 1/29/2007 | BB | 14 | 5.50 |
| 2/12/2007 | CC | 15 | 3.00 |
| 2/26/2007 | DD | 16 | 9.00 |
| 3/12/2007 | AA | 17 | 15.00 |
| 3/26/2007 | EE | 18 | 2.00 |
| 4/9/2007 | FF | 19 | 5.00 |
| 4/23/2007 | FF | 20 | 10.50 |
| 5/7/2007 | CC | 21 | 16.00 |
| 5/21/2007 | CC | 22 | 4.00 |
Requirement is for Each Region how many No of calls below 8 hours count in One column, above 8 hours count in another column and the last column should be No of calls for that region.
Parameters should be date range (Stardate & EndDate)
I want to display data as follows in the new temporary table.
| REGION | Below_8_NOCALLS | Above_8_NO_CALLS | NO OF CALLS |
| AA | 1 | 2 | 3 |
| BB | 1 | 0 | 1 |
| CC | 2 | 1 | 3 |
| DD | 0 | 1 | 1 |
| EE | 1 | 0 | 1 |
| FF | 1 | 1 | 2 |
Any help / thoughts will be very helpful.
Thank you,
Vijay
July 27, 2007 at 8:13 am
DECLARE @St SMALLDATETIME, @Ed SMALLDATETIME
DECLARE @Source table (Dt SMALLDATETIME, Region varchar(2), CallNo varchar(2), Hrs decimal(10,2) )
INSERT INTO @Source
SELECT '1/1/2007','AA','12',7.00
UNION
SELECT '1/15/2007','AA','13', 8.50
UNION
SELECT '1/29/2007','BB','14', 5.50
UNION
SELECT '2/12/2007','CC','15', 3.00
UNION
SELECT '2/26/2007','DD','16', 9.00
UNION
SELECT '3/12/2007','AA','17', 15.00
UNION
SELECT '3/26/2007','EE','18', 2.00
UNION
SELECT '4/9/2007','FF','19', 5.00
UNION
SELECT '4/23/2007','FF','20', 10.50
UNION
SELECT '5/7/2007','CC','21', 16.00
UNION
SELECT '5/21/2007','CC','22', 4.00
SET @St = '2007-01-01'
SET @Ed = '2007-06-30'
select s.Region, SUM(CASE WHEN Hrs > 8 THEN 0 ELSE 1 END) Below_8,
SUM(CASE WHEN Hrs > 8 THEN 1 ELSE 0 END) Above_8,
COUNT(*) TotalCalls
FROM @Source s
WHERE Dt >= @St
AND Dt <= @Ed
GROUP BY [Region]
July 27, 2007 at 2:41 pm
Hi
It worked perfectly.
Thank you so much.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply