Posted: Thu Jun 02, 2005 5:46 am Post subject: Query for reconciliation report
Hi,
Please help me to write the following query
Consider following table.
ACCTFIN
Code:
ACCOUNT ID SEQ_NO SHRS DOLS
A M 1 100 100
B M 2 200 200
A P 1 100 100
C P 3 300 300
Output should be
Code:
ACCOUNT SEQ_NO M_SHRS M_DOLS P_SHRS P_DOLS
A 1 100 100 100 100
B 2 200 200 0 0
C 3 0 0 300 300
Note:
If ID = 'M' and SHRS not null then return M_SHRS else 0
If ID = 'P' and SHRS not null then return P_SHRS else 0
If ID = 'M' and DOLS not null then return M_DOLS else 0
If ID = 'P' and DOLS not null then return P_DOLS else 0
Thanks in advance,
Showkath _________________ Regards,
Showkath
-----------------------------------------------------
Our opinions do not really blossom into fruition until we have expressed them to someone else - Mark Twain
Joined: 26 Nov 2002 Posts: 12375 Topics: 75 Location: San Jose
Posted: Thu Jun 02, 2005 7:18 am Post subject:
Showkath,
Try this sql. I assumed that SHRS and DOLS columns are defined as decimal columns.
Code:
SELECT A.ACCOUNT
,A.SEQ_NO
,SUM(A.M_SHRS) M_SHRS
,SUM(A.M_SHRS) M_DOLS
,SUM(A.P_SHRS) P_SHRS
,SUM(A.P_SHRS) P_DOLS
FROM (SELECT ACCOUNT
,SEQ_NO
,CASE ID WHEN 'M' THEN SHRS ELSE DECIMAL(0) END M_SHRS
,CASE ID WHEN 'M' THEN DOLS ELSE DECIMAL(0) END M_DOLS
,CASE ID WHEN 'P' THEN SHRS ELSE DECIMAL(0) END P_SHRS
,CASE ID WHEN 'P' THEN DOLS ELSE DECIMAL(0) END P_DOLS
FROM ACCTFIN) A
GROUP BY A.ACCOUNT
,A.SEQ_NO
;
Thanks for ur prompt reply. That query is working as per the requirement. Thanks a lot.
Regards,
Showkath _________________ Regards,
Showkath
-----------------------------------------------------
Our opinions do not really blossom into fruition until we have expressed them to someone else - Mark Twain
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
