I am a newbie in SQL query, I tried to extract a report from 3 different tables, and I merged then in two different different temp table first and finally join then to extract merged report, I found that the two separate report works fine and show exact report too but when i join then i show some un real record in report.
so the query that i used and the report how exactly i need to extract is attached with this post, I request you ppls to help to to find exact report and please tell me where i am wrong in my query too.
Instead of the table you gave for exact report, can you tell us what is the data you want in the exact report?
thank you djconnexion for ur attention on my query..
I want the
Customer list who paid Principal, Interest, Penal and PenalInterest group by Paid date
suppose; if in a day customer paid only one column (i.e. principal or interest or Penal or penalInterest) the record should show the paid figure in resp. Column and rest of the Paid column should be null and TotalAmount field is the sum of Paid Amount in that day.
select distinct paidprincipal,paidint,paidpenal,paidintonint,total amount,date from (
select fl.paidprincipal as Paidprincipal ,sc.paidint as paidint ,sc.paidpenal as paidpenal ,sc.paidintonint as paidintonint,sum(isnull(fl.paidprincipal,0)+isnull (sc.paidint,0)+isnull(sc.paidpenal,0))+isnull(sc.p aidintonint,0) as TotalAmount ,fl.valuedate as date
from first fl
left join second sc
on fl.valuedate = sc.calcdate
group by fl.paidprincipal ,sc.paidint ,sc.paidpenal ,sc.paidintonint,fl.valuedate
select fl.paidprincipal as Paidprincipal ,sc.paidint as paidint,sc.paidpenal as paidpenal ,sc.paidintonint as paidintonint,sum(isnull(fl.paidprincipal,0)+isnull (sc.paidint,0)+isnull(sc.paidpenal,0))+isnull(sc.p aidintonint,0) as TotalAmount,sc.calcdate as date from second sc
left join first fl
on fl.valuedate = sc.calcdate
group by fl.paidprincipal ,sc.paidint ,sc.paidpenal , sc.paidintonint,sc.calcdate) as A
order by date
This query is only for the third table which involves merging. Kindly check it and let me know. I did it very quickly since I had to do something else. So there might be errors, but I got the output.
In the above query table 'first' refers to the first table in screen shot and second refers to second.
it is not working yet. You define that ValueDate=CalcDate but this condition is not true always, because some time customer pay only Principal which is in second table, you'll not find that date in first table, so in this condition
record should show,
branch,actype, maincode, customer, Name, paidprincipal, totalamt and date and other three column shoud be null
So you are saying that you will not be able to see 14/05/2013 in the output, because it is present in the 2nd table but not in the first. If that is the case then the query will handle it.
If you check for the select statement after the union all, you will find that the order of the table in the join is reversed which will get you the dates present in the second table but not in the first.