March 4, 2010 at 5:55 pm
HI ALL,
I am in process of creating a report. Below query gives me a report like this
YEARMONTH[highlight=#ffff11]COMPLETECANCELLEDPERCENTAGE %[/highlight]
2009624348 20 99.9178577295876459
Ask is i want to add one more where condition in the select statement( The highligted above result is only for one condition in the selevct query ex where codename=TRAIN. If i want to add one more condition to the query like " where codename=BUS how do i achive this.
DECLARE @tbl TABLE
([MONTH] INT, [COUNT] INT, code VARCHAR(30)
)
INSERT INTO @tbl
SELECT 6, 8425, 'DELIVERY' UNION ALL
SELECT 6, 20, 'CANCELLED'
-- option 1: PIVOT
SELECT [MONTH],[DELIVERY], [CANCELLED]
FROM
(
SELECT [MONTH] , [COUNT] , code
FROM @tbl
) p
PIVOT
(
SUM ([COUNT])
FOR code IN ( [DELIVERY], [CANCELLED])
) AS pvt
-- option 2: "classic" CASE statement
SELECT
[MONTH],
SUM(CASE WHEN code ='DELIVERY' THEN [COUNT] ELSE 0 END) AS [DELIVERY],
SUM(CASE WHEN code ='CANCELLED' THEN [COUNT] ELSE 0 END) AS [CANCELLED]
FROM @tbl
GROUP BY [MONTH]
March 4, 2010 at 6:07 pm
Duplicate post. No replies to this thread please. Direct replies to:http://www.sqlservercentral.com/Forums/Topic877313-145-1.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply