July 23, 2006 at 6:50 pm
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 |0 |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?
July 24, 2006 at 5:24 am
Something like this?
--data
declare @t table (MAJOR int, MINOR char(4), PRODMGR char(2), CST_USD int, REV_USD int, LCTRYNUM int, AMT_TYPE char(1))
insert @t
select 538, '1616', 'LN', 0, 250, 834, 'I'
union all select 538, '1641', 'OT', 0, 300, 834, 'I'
union all select 548, '1616', 'LN', 100, 0, 834, 'I'
union all select 548, '1641', 'OT', 120, 0, 834, 'I'
union all select 400, '0100', 'LV', 50, 0, 888, 'D'
union all select 402, '0200', 'LO', 80, 0, 888, 'D'
union all select 404, '0110', 'LJ', 30, 0, 333, 'J'
--calculation
select
PRODMGR,
sum(case when substring(MINOR, 2, 3) = '616' then REV_USD - CST_USD else 0 end) as '616',
sum(case when substring(MINOR, 2, 3) = '641' then REV_USD - CST_USD else 0 end) as '641'
from @t
WHERE LCTRYNUM = '834' AND MAJOR IN ('538','548') AND AMT_TYPE = 'I'
group by PRODMGR
/*results
PRODMGR 616 641
------- ----------- -----------
LN 150 0
OT 0 180
*/
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
July 24, 2006 at 5:41 am
See also
http://p2p.wrox.com/topic.asp?TOPIC_ID=47422
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69501
N 56°04'39.16"
E 12°55'05.25"
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply