Click here to Skip to main content
15,908,437 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

Anybody can help? I am joining four tables to get the required result. Referring to the first script below, I want to get the summary of Charges which is one of the fields of table1.SUM value should be grouped by month according to the adddate field from table4. How can i change of below script to get the expected result.

Hope anyone can help me. :)

SQL
SELECT * FROM Table1 WHERE Table1.evidencenumber IN 
(SELECT DISTINCT table2.evidencenumber FROM table3 rs, table2
WHERE table3.rep_id IN (SELECT DISTINCT rep_id FROM table4 WHERE adddate>=TO_DATE('010109','ddmmyy') 
AND adddate<=TO_DATE('311209','ddmmyy') AND dept='Engineering')
AND table3.evidencenumber=table2.evidencenumber
AND table2.pareantid IS NULL)
Posted
Updated 22-May-11 23:51pm
v2
Comments
udayakumarcgvak 12-Oct-11 3:54am    
Helo Friend,

Your Sql Code is Not clear to me,So U give Table & Field details and Your Requirement.I think that is good for some one understand..

regards
Udayakumar.P

1 solution

If I correctly understand the purpose you need only summary by the values of Table1, then you can filter distinct results by simply joining all the subqueries in outer "where" clause and adding the "rowid" marker of the row from corresponding table (this is possible in Oracle), and thus calculating the result in the outer query:

SQL
select T.adddate, sum(T.Charges) from
(
SELECT distinct Table1.rowid, Table1.Charges, table4.adddate FROM
  Table1,
  table3,
  table2,
  table4
WHERE
 Table1.evidencenumber = table2.evidencenumber 
 AND table3.rep_id = table4.rep_id
 and table4.adddate>=TO_DATE('010109','ddmmyy') 
 AND table4.adddate<=TO_DATE('311209','ddmmyy')
 AND table4.dept='Engineering'
 AND table3.evidencenumber=table2.evidencenumber
 AND table2.pareantid IS NULL
) T 
group by T.adddate


Still if your SQL engine doesnt have the built-in row marker (like rowid in Oracle) you can add a unique field to the Table1 and use it as a rowid-marker.
 
Share this answer
 
v3

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900