| View previous topic :: View next topic |
| Author |
Message |
Rahull
Beginner
Joined: 29 Jan 2004
Posts: 62
Topics: 19
|
 Posted: Tue May 24, 2005 7:08 pm Post subject: SQL Query needed |
 |
|
Hi!! Please help me getting the SQL for the below problem
I have 4 tables AAAA,BBBB,CCCC & DDDD
1) Columns of AAAA :: Account Number, Tran Amount
2) Columns of BBBB :: Policy Number, Basic Amount, Charge Amount, Pay Plan status
3) Columns of CCCC :: Account Number, Policy Number (Its basically a connection table)
4) Columns of DDDD :: Account Number, Account Status
Given
1) Table DDDD will have unique record for each Account. Let say Account Y
2) Table CCCC will have unique record for each account and Policy combination. ( One to one mapping). Let say Account Y and Policy Z
3) Table BBBB can have multiple records for Policy for different Pay Plan Status( Either 'PP' or 'PC'). So Policy Z can have multiple records. But there will be only one 'PP' record at any moment. 'PC' records can have duplicate.
4) Table AAAA can have multiple records for Account Number. Let sat Account Y can have multiple records.
Problem Stmt
Fetch a policy and account combination for the following requirements
a) Sum tran amount of all the records of unique account Table AAAA.
b) Sum Basic amount + Charge amount for all the records of single policy
c) Sum (a) + Sum (b) and if the sum comes out to be < 0 , fetch that combination of Policy and Account
Its tough to get accomodate all the above condition in a single query because of multiple records in AAAA and BBBB. Please try and suggest me for other solutions (Except writing a program)
I need to run the above SQL in batch and if I get duplicate combination also its OK with me... |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Tue May 24, 2005 7:25 pm Post subject: |
|
|
Rahull,
It would be great if you can provide some sample data.
Table: AAAA
| Code: |
Account Number Tran Amount
111 100.00
111 200.00
222 400.00
333 500.00
333 700.00
|
Now you said
| Quote: |
a) Sum tran amount of all the records of unique account Table AAAA
|
What exactly do you mean unique. In the sample data provided above, the only unique record is account number 222 record.
Do you want this record? or you do you want something like
| Code: |
Account Number Tran Amount
111 300.00 (100.00 + 200.00)
222 400.00
333 1200.00 (500.00 + 700.00)
|
So please provide sample data for all tables and may be we can help you
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
Rahull
Beginner
Joined: 29 Jan 2004
Posts: 62
Topics: 19
|
| Posted: Tue May 24, 2005 7:40 pm Post subject: |
|
|
Hi Kolusu,
Yes you are right.
Now let us discuss the example you took. So for the account number 111 we found that 300 is the resultant amount in table AAAA. Now for account number 111 there will be a unique policy number associated in table CCCC. Get that Policy Number and Sum up all the basic and charge amount for that policy number in table BBBB (Irrespective of the pay plan status).
Sum up for account 111 and policy number (let say P111) like this
a) Sum Tran Amount (111) + Sum (Basic Amount + Charge Amount)(P111)
b) and if it comes less then 0. I need to fetch 111 and P111
Similiary for all other accounts.
Hope I am clear... I am online ..please revert back with ur doubts.
Hey I think I forgot to mention : Table AAAA has one more column Status Type While summing up the TRAN amount of AAAA we need to sum up only for the records whose Status type = 'OK' |
|
| Back to top |
|
|
Rahull
Beginner
Joined: 29 Jan 2004
Posts: 62
Topics: 19
|
| Posted: Tue May 24, 2005 8:10 pm Post subject: |
|
|
Sample data
Table : AAAA
| Code: |
---------+---------+---------+---
ACT_NO TRAN_AMT STAT_TYPE
---------+---------+---------+---
1111 20.00 RC
1111 -40.00 OK
1111 20.00 OK
2222 -10.00 OK
2222 10.00 OK
|
Table : BBBB
| Code: |
---------+---------+---------+---------+---------
POLICY_NO BASIC_AMT CHARG_AMT PLAN_STAT
---------+---------+---------+---------+---------
P1111 -10.00 -1.00 PP
P1111 -10.00 -10.00 PC
P1111 .00 -1.00 PC
P2222 -10.00 -10.00 PP
P2222 .00 .00 PC
|
Table : CCCC
| Code: |
---------+---------+--
ACT_NO POLICY_NO
---------+---------+--
1111 P1111
2222 P2222
|
Table : DDDD
| Code: |
---------+---------+--
ACT_NO ACT_STAT
---------+---------+--
1111 OK
2222 OK
|
My Output
| Code: |
Account Policy Resultant amount
1111 P1111 (-40+20-10-1-10-10) = -51
2222 p2222 (10-10-10-10) = -20
|
Hope I am clear. In the above calculation I ignore 20 for 1111 as the staus type is RC, |
|
| Back to top |
|
|
Rahull
Beginner
Joined: 29 Jan 2004
Posts: 62
Topics: 19
|
| Posted: Wed May 25, 2005 7:11 am Post subject: |
|
|
Hi Kolusu,
Any thought or idea how should I go ahead for my above prob ? |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Wed May 25, 2005 9:29 am Post subject: |
|
|
Rahul,
where does table DDDD come into picture ? Your totals were off for the first policy i.e P1111.
You forgot to add the -1 value from the 3rd record of table BBBB.
here is the sql. However I did not try for any optimization. you can fine tune it if you find the query is a cpu hog.
| Code: |
SELECT BBBB.POLICY_NO
,D.ACT_NO
,SUM(BBBB.BASIC_AMT + BBBB.CHARG_AMT) +
SUM(D.T_AMT) / SUM(D.T_CNT) TOTAL
FROM BBBB
,(SELECT CCCC.POLICY_NO
,CCCC.ACT_NO
,SUM(AAAA.TRAN_AMT) T_AMT
,INT(1) T_CNT
FROM CCCC
,AAAA
WHERE AAAA.STAT_TYPE = 'OK'
AND CCCC.ACT_NO = AAAA.ACT_NO
GROUP BY CCCC.POLICY_NO
,CCCC.ACT_NO) D
WHERE BBBB.POLICY_NO = D.POLICY_NO
GROUP BY BBBB.POLICY_NO
,D.ACT_NO
;
|
The results from this query are:
| Code: |
---------+---------+---------+---------+-
POLICY_NO ACT_NO TOTAL
---------+---------+---------+---------+-
P1111 1111 -52.00
P2222 2222 -20.00
|
Hope this helps...
Cheers
kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
Rahull
Beginner
Joined: 29 Jan 2004
Posts: 62
Topics: 19
|
| Posted: Wed May 25, 2005 10:58 am Post subject: |
|
|
Hey thanks Kolusu.
I will execute this in my environment to see if it works.
But please help me understand the query. It seems to me very complex one. .
For eg
SUM(D.T_AMT) / SUM(D.T_CNT) what is T_cnt ???
,SUM(AAAA.TRAN_AMT) T_AMT
,INT(1) T_CNT
what is INT(1) and why we are using it ?? |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Wed May 25, 2005 11:25 am Post subject: |
|
|
Rahul,
| Quote: |
But please help me understand the query. It seems to me very complex one. .
|
ofcourse it is complex sql.
TO better understand the query, you need to split the query into 2 parts. First let me start with the outer SELECT.
| Code: |
SELECT CCCC.POLICY_NO
,CCCC.ACT_NO
,SUM(AAAA.TRAN_AMT) T_AMT
,INT(1) T_CNT
FROM CCCC
,AAAA
WHERE AAAA.STAT_TYPE = 'OK'
AND CCCC.ACT_NO = AAAA.ACT_NO
GROUP BY CCCC.POLICY_NO
,CCCC.ACT_NO
|
In the above sql we are first joing two tables AAAA and CCCC and for the matching account numbers we are summing the transaction amount.
You can name a column in your select to your own name, else DB2 will provide the default column name. However the column name is blank for column functions like max, min , sum... So I name the sum of transaction amount as T_AMT
I also select another column (name as T_cnt) with Integer of 1.
When you execuete the above query it will return 4 columns,i.e
| Code: |
---------+---------+---------+---------+---------+---
POLICY_NO ACT_NO T_AMT T_CNT
---------+---------+---------+---------+---------+---
P1111 1111 -20.00 1
P2222 2222 .00 1
|
see for every record you will have constant of 1.
Now I am joining this resultant set as another table as D with Table BBBB. By doing so every record in table will have the above 4 columns attached to them.
Run this query to see how it works
| Code: |
SELECT *
FROM BBBB
,(SELECT CCCC.POLICY_NO
,CCCC.ACT_NO
,SUM(AAAA.TRAN_AMT) T_AMT
,INT(1) T_CNT
FROM CCCC
,AAAA
WHERE AAAA.STAT_TYPE = 'OK'
AND CCCC.ACT_NO = AAAA.ACT_NO
GROUP BY CCCC.POLICY_NO
,CCCC.ACT_NO) D
WHERE BBBB.POLICY_NO = D.POLICY_NO
;
|
The result of the above query will be as follows. I am spliting as 2 boxes for better readability. Visualize these results as single result set side by side.
| Code: |
---------+---------+---------+---------+-------
POLICY_NO BASIC_AMT CHARG_AMT PLAN_STAT
---------+---------+---------+---------+-------
P1111 -10.00 -1.00 PP
P1111 .00 -1.00 PC
P1111 -10.00 -10.00 PC
P2222 .00 .00 PC
P2222 -10.00 -10.00 PP
|
| Code: |
-+---------+---------+---------+---------+-------
POLICY_NO ACT_NO T_AMT T_CNT
-+---------+---------+---------+---------+-------
P1111 1111 -20.00 1
P1111 1111 -20.00 1
P1111 1111 -20.00 1
P2222 2222 .00 1
P2222 2222 .00 1
|
| Quote: |
SUM(D.T_AMT) / SUM(D.T_CNT) what is T_cnt ???
what is INT(1) and why we are using it ??
|
You already performed the sum on transaction amount in table A, but when you join with Table BBBB , it is repeated for every row.
Your intention is to sum the basic_amount and charg_amount for every policy and then add the trans_amt.
But when you perform the sum on basic_amount and charg_amout , you only need 1 record value of trans_amount.
That is where the T_cnt comes to picture. We sum trans_amount and then divide it by the no: of times (sum of T_cnt). By doing so we are only adding trans_amount once.
i.e from the above example.
P1111 has 3 records and sum of trans_amount is -20 for every record.
so -20-20-20/3 = -60/3 = -20 which will added to the sum of basic_amount and charge_amount.
ufff ! writting the query was easy , but the explaining as to how it works is tough especially over the internet.
Hope this helps...
Cheers
kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
Rahull
Beginner
Joined: 29 Jan 2004
Posts: 62
Topics: 19
|
| Posted: Wed May 25, 2005 1:11 pm Post subject: |
|
|
I am still in a process of understand... yep understood major chunk of it..
But do u know sth Kolusu... U r genious...!! |
|
| Back to top |
|
|
Rahull
Beginner
Joined: 29 Jan 2004
Posts: 62
Topics: 19
|
| Posted: Wed May 25, 2005 1:42 pm Post subject: |
|
|
| Hey one quick question. Can i use the comparision in SQL for less then ZERO.. As of now its fetching all the records.. I want whose total sum is negative |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Wed May 25, 2005 1:59 pm Post subject: |
|
|
| Quote: |
Can i use the comparision in SQL for less then ZERO.. As of now its fetching all the records.. I want whose total sum is negative
|
Rahull,
Yes ! you can use but you will need another select stmt . Try this
| Code: |
SELECT E.*
FROM (SELECT BBBB.POLICY_NO
,D.ACT_NO
,SUM(BBBB.BASIC_AMT + BBBB.CHARG_AMT) +
SUM(D.T_AMT) / SUM(D.T_CNT) TOTAL
FROM BBBB
,(SELECT CCCC.POLICY_NO
,CCCC.ACT_NO
,SUM(AAAA.TRAN_AMT) T_AMT
,INT(1) T_CNT
FROM CCCC
,AAAA
WHERE AAAA.STAT_TYPE = 'OK'
AND CCCC.ACT_NO = AAAA.ACT_NO
GROUP BY CCCC.POLICY_NO
,CCCC.ACT_NO) D
WHERE BBBB.POLICY_NO = D.POLICY_NO
GROUP BY BBBB.POLICY_NO
,D.ACT_NO) E
WHERE E.TOTAL < 0
;
|
Hope this helps...
Cheers
Kolusu
PS: I would really appreciate if you can stop using chat lingo on this board. Even though it appears cool, remember that you are not in a chat room , but on a professional helpboard
Btw does "sth" mean "something" in english?
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
Mervyn
Moderator
Joined: 02 Dec 2002
Posts: 415
Topics: 6
Location: Hove, England
|
| Posted: Wed May 25, 2005 2:42 pm Post subject: |
|
|
"sth" is obviously about "SainTHood".
From now on, you shall be known as "Saint Kolusu"
_________________
The day you stop learning the dinosaur becomes extinct |
|
| Back to top |
|
|
Rahull
Beginner
Joined: 29 Jan 2004
Posts: 62
Topics: 19
|
| Posted: Thu May 26, 2005 10:44 am Post subject: |
|
|
Hi Kolusu,
I have got one problem while executing the query.
Case:
-- When I have basic amount and charge amount for a policy in BBBB. But I dont have any record for that account in AAAA. In that case interim table D does not have data and it comes out without fetching the record.
Given : I have record for account and policy in CCCC and DDDD.
Any view for the above case ? |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Thu May 26, 2005 11:47 am Post subject: |
|
|
Rahull,
| Quote: |
I have got one problem while executing the query.
When I have basic amount and charge amount for a policy in BBBB. But I dont have any record for that account in AAAA. In that case interim table D does not have data and it comes out without fetching the record.
|
The solution posted by me fits exactly to the requirements you posted in post # 3 from top. You never answered to my question about the relation to table DDDD ( refer post 6 from the top)
Post detailed information on what you're trying to accomplish. Do not make people guess what you mean. This will give you a much better chance of getting a good answer to your question.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
Rahull
Beginner
Joined: 29 Jan 2004
Posts: 62
Topics: 19
|
| Posted: Thu Jun 02, 2005 2:02 pm Post subject: |
|
|
Hi Kolusu,
Your query is working fine, though I have got one issue today.
Case :: When I dont have any record in TABLE AAAA for some account but I do have record in BBBB with negative amount. So again if we sum up the amount its comes out to be negative and eligible for refund.
The below part of the query is failing. Since it doesn't fetch any record, the outer query comes out without summing the amount of BBBB.
| Quote: | SELECT CCCC.POLICY_NO
,CCCC.ACT_NO
,SUM(AAAA.TRAN_AMT) T_AMT
,INT(1) T_CNT
FROM CCCC
,AAAA
WHERE AAAA.STAT_TYPE = 'OK'
AND CCCC.ACT_NO = AAAA.ACT_NO
GROUP BY CCCC.POLICY_NO
,CCCC.ACT_NO |
Could you suggest some solution. Can we create some default record in case the inner query does not result in any record so that outer query still calculate the amount from BBBB table ??? |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
