SQL Select where two condition

Question

I have three tables, as in the example: How to join three tables to get Sum

I have added the field date to the tables Purchase and Invoce and filter all purchases and invoce minor to certain date provided as parameter in vb:

SELECT P.Product_no, Inv.InvProdSum, Pur.PurProdSum
FROM Product P
LEFT JOIN (SELECT Product_no, SUM(Qty) AS InvProdSum
          FROM Invoice 
          WHERE date < @DateParameter
          GROUP BY Product_no) AS Inv
ON P.Product_no = Inv.Product_no
LEFT  JOIN (SELECT Product_no, SUM(Qty) AS PurProdSum
            FROM Purchase 
            WHERE date < @DateParameter
            GROUP BY Product_no) AS Pur
ON P.Product_no = Pur.Product_no

I also want to get two more columns in the results table: the sum of invoice and the sum of purchase where date = @DateParameter. My goal is to obtain, for example, if the date is 2017-January-27, the sum of Purchase prior to that date and the sum of Purchase of January only, and so for any date provided as a parameter. Same for invoce. I'm sorry for my English

Add another join to a subquery where date = @DateParameter. – NoAlias Mar 24 '17 at 18:17 — NoAlias, Mar 24 '17 at 18:17

score 1 · Accepted Answer · answered Mar 24 '17 at 19:26

Add additional joins to the same tables with the different condition to get what you want:

SELECT P.Product_no, Inv.InvProdSum, Pur.PurProdSum. InvCurrent.InvProdSum AS [CurrentInvoiceProdSum], PurCurrent.PurProdSum AS [CurrentProductSum]
FROM Product P
LEFT JOIN (SELECT Product_no, SUM(Qty) AS InvProdSum
          FROM Invoice 
          WHERE date < @DateParameter
          GROUP BY Product_no) AS Inv
ON P.Product_no = Inv.Product_no
LEFT  JOIN (SELECT Product_no, SUM(Qty) AS PurProdSum
            FROM Purchase 
            WHERE date < @DateParameter
            GROUP BY Product_no) AS Pur
ON P.Product_no = Pur.Product_no
LEFT JOIN (SELECT Product_no, SUM(Qty) AS InvProdSum
          FROM Invoice 
          WHERE date = @DateParameter
          GROUP BY Product_no) AS InvCurrent
ON P.Product_no = InvCurrent.Product_no
LEFT  JOIN (SELECT Product_no, SUM(Qty) AS PurProdSum
            FROM Purchase 
            WHERE date = @DateParameter
            GROUP BY Product_no) AS PurCurrent
ON P.Product_no = PurCurrent.Product_no

If going against the transaction tables is just too much of a performance hit you can try getting your initial results that include current and past values and then query from that:

SELECT Product_no, Qty AS InvProdSum
INTO #tmpInvoice
FROM Invoice 
WHERE date <= @DateParameter

SELECT Product_no, Qty AS PurProdSum
INTO #tmpPurchase
FROM Purchase 
WHERE date <= @DateParameter

SELECT P.Product_no, Inv.InvProdSum, Pur.PurProdSum. InvCurrent.InvProdSum AS [CurrentInvoiceProdSum], PurCurrent.PurProdSum AS [CurrentProductSum]
FROM Product P
LEFT JOIN (SELECT Product_no, SUM(Qty) AS InvProdSum
          FROM #tmpInvoice 
          WHERE date < @DateParameter
          GROUP BY Product_no) AS Inv
ON P.Product_no = Inv.Product_no
LEFT  JOIN (SELECT Product_no, SUM(Qty) AS PurProdSum
            FROM #tmpPurchase 
            WHERE date < @DateParameter
            GROUP BY Product_no) AS Pur
ON P.Product_no = Pur.Product_no
LEFT JOIN (SELECT Product_no, SUM(Qty) AS InvProdSum
          FROM #tmpInvoice 
          WHERE date = @DateParameter
          GROUP BY Product_no) AS InvCurrent
ON P.Product_no = InvCurrent.Product_no
LEFT  JOIN (SELECT Product_no, SUM(Qty) AS PurProdSum
            FROM #tmpPurchase 
            WHERE date = @DateParameter
            GROUP BY Product_no) AS PurCurrent
ON P.Product_no = PurCurrent.Product_no

ok, `#tmpInvoice` and `#tmpPurchase` Thank you very very much — Raúl Olvera, Mar 24 '17 at 20:31

SQL Select where two condition

1 Answers1