Hello, I want to write a select query to get the info to build a purchase & sales report. The next structure table exists Product table ID Description Purchase table ID ArtID QTY Sales table ID ArtID QTY Transfers table ID ArtID QTY The user wants to analyse the data starting from the purchases. Either between 2 dates or a specific one. There can be more than 1 record in the purchase, sales and transfer tables. Is there anyone who can give me information to write this into 1 query? So that it is performant. Also does anyone knows a tool to convert SQL to LINQ? Thanks in advance Ralph
Hello, The tables are linked by the ArtID from all the tables to the ID of the articles There always the possibility that there are no records in the other tables or more than 1 Result set should look like ID (article) Desc #PUR #SOLD #TRANS #STOCK 1 ABC 10 5 1 4 2 CDE 5 5 0 0 3 FGH 100 0 1 99 4 IJK 5 0 0 5
Not sure if I understood you correctly, but what about something like this? select Prod.ID, min(Prod.Description), sum(isnull(Pur.Qty, 0)) AS [#PUR], sum(isnull(Sal.Qty, 0)) AS [#SOLD], sum(isnull(Trans.Qty, 0)) AS [#TRANS], sum(isnull(Pur.Qty, 0)) - sum(isnull(Trans.Qty, 0)) - sum(isnull(Sal.Qty, 0)) AS [#STOCK] from dbo.Product Prod left join (select ArtID, sum(Qty) AS Qty from dbo.Purchase group by ArtID) Pur on Prod.ID = Pur.ArtID left join (select Artid, sum(Qty) AS Qty from dbo.Sales group by ArtID) Sal on Prod.ID = Sal.ArtID left join (select ArtID, sum(isnull(Qty, 0)) as Qty from dbo.Transfers group by ArtID) Trans on Prod.ID = Trans.ArtId group by Prod.ID
