-
Sep 8th, 2018, 10:18 AM
#1
Thread Starter
Lively Member
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
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
-
Sep 8th, 2018, 05:11 PM
#2
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.
-
Sep 9th, 2018, 12:14 AM
#3
Thread Starter
Lively Member
Re: SQL 2008 R2 - Generate Report using SQL Sub Query with Left Join
Originally Posted by wes4dbt
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|