[fdtoo]

I have the following fields in Table A running in MS SQL Server 2000:

MAJOR|	MINOR|	PRODMGR|CST_USD	|REV_USD|LCTRYNUM |AMT_TYPE
============================================================
538  |	1616 |	LN     |0	|250	|834	  |  I
538  |	1641 |	OT     |0	|300	|834	  |  I
548  |	1616 |	LN     |100	|0	|834	  |  I
548  |	1641 |	OT     |120	|834	  |  I
400  |	0100 |	LV     |50	|0	|888	  |  D
402  |	0200 |	LO     |80	|0	|888	  |  D
404  |	0110 |	LJ     |30	|0	|333	  |  J

I would like to query these fields so that i can have the below result:

              |616  |  641 | 
============================
Gross Profit  |150  |  180 

PRODMGR       |616  |  641 
===========================
LN            |150  |--> (250-100)  
OT            | 0   |  180--> (300-120}

My query is as follows:

SELECT
SUM(REV_USD)-SUM(CST_USD) AS [616]
FROM TABLE A
WHERE LCTRYNUM='834' AND MAJOR IN ('538','548') AND 
STR(MINOR,2,3)='616' AND AMT_TYPE='I'
UNION ALL
SELECT
SUM(REV_USD)-SUM(CST_USD) AS [641]
FROM TABLE A
WHERE LCTRYNUM='834' AND MAJOR IN ('538','548') AND 
STR(MINOR,2,3)='641' AND AMT_TYPE='I'
UNION ALL
SELECT DISTINCT PRODMGR,
(SELECT SUM(REV_USD)-SUM(CST_USD) 
 FROM TABLE A
 WHERE LCTRYNUM='834' AND MAJOR IN ('538','548') AND 
 STR(MINOR,2,3)='616' AND AMT_TYPE='I'AND PRODMGR=MAIN.PRODMGR) AS [616],
(SELECT SUM(REV_USD)-SUM(CST_USD) 
 FROM TABLE A
 WHERE LCTRYNUM='834' AND MAJOR IN ('538','548') AND 
 STR(MINOR,2,3)='641' AND AMT_TYPE='I'AND PRODMGR=MAIN.PRODMGR) AS [641]
FROM TABLE A AS MAIN

I can't seem to get the result i want, can anyone help?

[shabbir]

I couldn't get what your query does but you can try something like

SELECT
PRODMGR, 
SUM(REV_USD)-SUM(CST_USD) AS [616],
SUM(REV_USD)-SUM(CST_USD) AS [641]
FROM TABLE A
WHERE PRODMGR IN ('LN','OT')
GROUP BY PRODMGR