SQL Server Performance

Select Query

Discussion in 'General Developer Questions' started by ralphhome, Dec 29, 2008.

  1. ralphhome New Member

    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
  2. FrankKalis Moderator

    Can you please post how the tables are related and what the final resultset should look like?
  3. ralphhome New Member

    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
  4. FrankKalis Moderator

    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

Share This Page