Results 1 to 6 of 6
  1. #1
    Join Date
    May 2013
    Posts
    3

    Red face Unanswered: Help in SQL Query

    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.

    Thank you
    Attached Thumbnails Attached Thumbnails query result.jpg  
    Attached Files Attached Files
    Last edited by ajshrestha; 05-28-13 at 03:06.

  2. #2
    Join Date
    May 2013
    Posts
    6
    Instead of the table you gave for exact report, can you tell us what is the data you want in the exact report?

  3. #3
    Join Date
    May 2013
    Posts
    3
    Quote Originally Posted by djconnexion View Post
    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.
    Attached Thumbnails Attached Thumbnails Untitled.jpg  
    Last edited by ajshrestha; 05-28-13 at 05:40.

  4. #4
    Join Date
    May 2013
    Posts
    6
    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
    union all
    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.

  5. #5
    Join Date
    May 2013
    Posts
    3
    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

  6. #6
    Join Date
    May 2013
    Posts
    6
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •