| Sql query [message #324609] |
Tue, 03 June 2008 05:39  |
someswar1
Messages: 53
Registered: January 2008
|
Member |
|
|
Hi,
I have a table sdp with following field account_number,adjustment_date,adjustment_time,balance_before,balance_after,cdr_id
and value like this
Insert into sdp Values
('978362369', '20071210', '061413', '708.000000', '5708.000000', '45813627');
I need to finding
The minimum date time among the combination of (ADJUSTMENT_TIME, ADJUSTMENT_DATE) with respect to ACCOUNT_NUMBER is to be considered and the corresponding BALANCE_BEFORE is to be mapped to OPENING_BALANCE.
In case of multiple row with same min(ADJUSTMENT_TIME, ADJUSTMENT_DATE), the record with least ‘CDR_ID’ should be considered.
e.g if for ACCOUNT_NUMBER 97813437 the ADJUSTMENT_TIME, ADJUSTMENT_DATE, BALANCE_BEFORE is
i> 070332, 20080129, 0.123569
ii> 190356, 20080129, 1.236598
iii> 000029, 20080130, 2.569874
then OPENING_BALANCE will be 0.123569
I write the query but not working properly
CASE
WHEN BALANCE_BEFORE IS not NULL
THEN (SELECT BALANCE_BEFORE
FROM sdp
WHERE ACCOUNT_NUMBER IN ( SELECT a.ACCOUNT_NUMBER
FROM sdp a
WHERE A.ACCOUNT_NUMBER =B.ACCOUNT_NUMBER)
AND CDR_ID IN (SELECT Min (CDR_ID)
FROM sdp
GROUP BY ACCOUNT_NUMBER)
GROUP BY ACCOUNT_NUMBER,
BALANCE_BEFORE,
ROWID,
CDR_ID)
ELSE (SELECT BALANCE_BEFORE FROM sdp
WHERE ACCOUNT_NUMBER IN (SELECT a.ACCOUNT_NUMBER
FROM sdp a
WHERE ACCOUNT_NUMBER =b.ACCOUNT_NUMBER)
AND ROWID IN (SELECT Min (ROWID)
FROM sdp
GROUP BY ACCOUNT_NUMBER)
GROUP BY ACCOUNT_NUMBER,
BALANCE_BEFORE,
ROWID,
CDR_ID)
END
can any one help me.
Its Urgent
Regards:
somu
[mod-edit: added code tags to case statement]
[Updated on: Tue, 03 June 2008 13:33] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Sql query [message #324636 is a reply to message #324627] |
Tue, 03 June 2008 07:24  |
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
|
Senior Member |
|
|
Come on Michael. You cannot expect the OP to follow the guidelines. OP has said Its Urgent. OP is not having any time to follow the guidelines.
Regards
Raj
|
|
|
|
|
|
|
|
|
|
| Re: Sql query [message #324953 is a reply to message #324787] |
Wed, 04 June 2008 10:51 |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
If you want help, then you need to read and follow the forum guidelines, as previously instructed, including posting create table and insert statements for sample data, the results that you want based on that data, and a copy and paste of what you have tried, which means a complete select statement, not just a case statement, including line numbers and results or error message.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
