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: 367
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: 359
Size:  7.2 KB