CASE query... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

CASE query...

Author

Topic

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2010-05-17 : 09:38:29

Hi

I am getting result set not as per expected for below query..

SELECT DISTINCT
CASE A.[R_TYPE] WHEN 3 THEN 'N' ELSE 'Y' END AD_FLAG,
CASE A.[R_TYPE] WHEN 3 THEN B.[REV_USD]
ELSE
(CASE T.ADJ_TY WHEN 'GR'
THEN
SUM(T.[REV_USD])
END)
END AS [REV_USD],
CASE A.[R_TYPE] WHEN 3 THEN C.[REV_USD]
ELSE
(CASE T.ADJ_TY WHEN 'DR'
THEN
SUM(T.[REV_USD])
END)
END AS [EX_REV_USD]
FROM [dbo].[AC_DATA] A (NOLOCK)
LEFT JOIN [dbo].[IN_GROUP] IG
ON A.IN_GROUP = IG.IN_GR_NM
LEFT JOIN [dbo].[GR] B (NOLOCK)
ON IG.[AD_INV_GROUP_ID] = B.[INV_GROUP_ID]
LEFT JOIN [dbo].[DR] C (NOLOCK)
ON A.[INV_GROUP] = C.[INV_GROUP]
AND A.[REV_CODE] = C.[REV_CODE]
LEFT JOIN [dbo].[TA] T (NOLOCK)
ON A.[INV_GROUP] = T.[INV_GROUP]
AND A.[REV_CODE] = T.[REV_CODE]
where A.INV_GROUP = '02367C' and A.[REV_CODE] = '9599'
GROUP BY T.[INV_GROUP], A.[CLIENT], A.[MONTH],
B.[INV_GROUP_ID],A.[REV_CODE], A.[R_TYPE],C.[USD_REV], B.[REV_USD]
,T.ADJ_TY

Current Output:
AD_FLAG REV_USD EX_REV_USD
N 6320.00 6000.00
Y NULL 60.00
Y 15.00 NULL

BUT NEED OUTPUT LIKE

AD_FLAG REV_USD EX_REV_USD
N 6320.00 6000.00
Y 15.00 60.00

T.I.A

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-05-17 : 10:17:55

Please wrap any code in code tags (the word code with square brackets round the text. Here's what you actually posted.



SELECT 	DISTINCT 
		CASE A.[R_TYPE] WHEN 3 THEN 'N' ELSE 'Y' END AD_FLAG,
		CASE A.[R_TYPE] WHEN 3 THEN B.[REV_USD] 
			ELSE 
			(CASE T.ADJ_TY WHEN 'GR' 
				THEN 
				SUM(T.[REV_USD]) 
					END) 
					END AS [REV_USD],
		CASE A.[R_TYPE] WHEN 3 THEN C.[REV_USD] 
			ELSE 
			(CASE T.ADJ_TY WHEN 'DR' 
				THEN 
				SUM(T.[REV_USD]) 
					END) 
					END AS [EX_REV_USD]
		FROM [dbo].[AC_DATA] A (NOLOCK)
		LEFT JOIN [dbo].[IN_GROUP] IG
			ON A.IN_GROUP = IG.IN_GR_NM
		LEFT JOIN 	[dbo].[GR] B (NOLOCK) 
			ON IG.[AD_INV_GROUP_ID] = B.[INV_GROUP_ID]
		LEFT JOIN [dbo].[DR] C (NOLOCK)
			ON A.[INV_GROUP] = C.[INV_GROUP]
			AND A.[REV_CODE] = C.[REV_CODE]
		LEFT JOIN [dbo].[TA] T (NOLOCK)
			ON A.[INV_GROUP] = T.[INV_GROUP]
			AND A.[REV_CODE] = T.[REV_CODE]
			where A.INV_GROUP = '02367C' and A.[REV_CODE] = '9599'
		GROUP BY T.[INV_GROUP], A.[CLIENT], A.[MONTH], 
			B.[INV_GROUP_ID],A.[REV_CODE], A.[R_TYPE],C.[USD_REV], B.[REV_USD] 
			,T.ADJ_TY

Current Output: 
AD_FLAG   REV_USD	 EX_REV_USD
N	  6320.00	 6000.00
Y	  NULL	         60.00
Y	  15.00	         NULL

BUT NEED OUTPUT LIKE 

AD_FLAG 	REV_USD	EX_REV_USD
N	        6320.00	6000.00
Y	        15.00	60.00

T.I.A

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-17 : 12:06:52

[code]SELECT AD_FLAG,
SUM(REV_USD) AS REV_USD,
SUM(EX_REV_USD) AS EX_REV_USD
FROM
(
SELECT DISTINCT
CASE A.[R_TYPE] WHEN 3 THEN 'N' ELSE 'Y' END AD_FLAG,
CASE A.[R_TYPE] WHEN 3 THEN B.[REV_USD]
ELSE
(CASE T.ADJ_TY WHEN 'GR'
THEN
SUM(T.[REV_USD])
END)
END AS [REV_USD],
CASE A.[R_TYPE] WHEN 3 THEN C.[REV_USD]
ELSE
(CASE T.ADJ_TY WHEN 'DR'
THEN
SUM(T.[REV_USD])
END)
END AS [EX_REV_USD]
FROM [dbo].[AC_DATA] A (NOLOCK)
LEFT JOIN [dbo].[IN_GROUP] IG
ON A.IN_GROUP = IG.IN_GR_NM
LEFT JOIN [dbo].[GR] B (NOLOCK)
ON IG.[AD_INV_GROUP_ID] = B.[INV_GROUP_ID]
LEFT JOIN [dbo].[DR] C (NOLOCK)
ON A.[INV_GROUP] = C.[INV_GROUP]
AND A.[REV_CODE] = C.[REV_CODE]
LEFT JOIN [dbo].[TA] T (NOLOCK)
ON A.[INV_GROUP] = T.[INV_GROUP]
AND A.[REV_CODE] = T.[REV_CODE]
where A.INV_GROUP = '02367C' and A.[REV_CODE] = '9599'
GROUP BY T.[INV_GROUP], A.[CLIENT], A.[MONTH],
B.[INV_GROUP_ID],A.[REV_CODE], A.[R_TYPE],C.[USD_REV], B.[REV_USD]
,T.ADJ_TY
)t
GROUP BY AD_FLAG
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources