Results 1 to 3 of 3

Thread: SQL 2008 R2 - Generate Report using SQL Sub Query with Left Join

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Oct 2011
    Posts
    76

    SQL 2008 R2 - Generate Report using SQL Sub Query with Left Join

    Hi All,

    I am currently doing an Library Books Tracking System for internal purpose. I have table design and I created SQL query to achieve the desire output but i unable to get the required output as attached

    Please find below table structure and SQL. I need guidance to get report as required. Thanks in Advance...

    Note: Getting Book Group name yet to frame in my SQL query

    Name:  tables.PNG
Views: 788
Size:  25.5 KB

    Code:
    SELECT TT.BookId, RM.BookName, Opening AS Opening, SUM(TT.LibIn) AS Inward,  
    SUM(TT.LibOut) AS Outward, (Opening+(SUM(TT.LibIn))-(SUM(TT.LibOut))) AS Closing  
    FROM ((BookTrns TT  
    LEFT JOIN BookMaster RM  ON TT.BookId = RM.BookId)  
    LEFT JOIN BookReg TR ON TR.TRegId = TT.TRegId)  
    LEFT JOIN (SELECT TT.BookId,(SUM(TT.LibIn)- SUM(TT.LibOut)) AS Opening  
    FROM BookTrns TT LEFT JOIN BookReg TR  
    ON TR.TRegId = TT.TRegId 
    WHERE TR.TDate < '2018-09-01' 
    AND TT.Libraryid = 2 
    GROUP BY TT.BookId) AS Stock  
    ON Stock.BookId = TT.BookId 
    WHERE(TT.Libraryid = 2)  
    AND TR.TDate BETWEEN '2018-09-01' AND '2018-09-30'   
    GROUP BY TT.BookId, RM.BookName, Opening

    Name:  output.PNG
Views: 533
Size:  7.2 KB

  2. #2
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: SQL 2008 R2 - Generate Report using SQL Sub Query with Left Join

    I'd suggest postin g this in the Database development Forum or the VB .Net/VB6 (which ever this is written in). This is more about retrieving data than printing a report. You would probably have more help there.

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Oct 2011
    Posts
    76

    Re: SQL 2008 R2 - Generate Report using SQL Sub Query with Left Join

    Quote Originally Posted by wes4dbt View Post
    I'd suggest postin g this in the Database development Forum or the VB .Net/VB6 (which ever this is written in). This is more about retrieving data than printing a report. You would probably have more help there.
    Hi, Thanks for your suggestion, I will post my question in appropriate forum.

Posting Permissions

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



Click Here to Expand Forum to Full Width