Restricting WHERE clause to specific SELECT statement in sql

Question

I have the following sql code. I use a WHERE clause with a date interval. My problem is that I only want the date interval to be used on a specific part of the select statement.

I want to restrict the WHERE clause DATE to my select on QtyInPeriod and ProfitInPeriod and not the other columns selected (Lines.Item, Inventory.ItemAlternative, Inventory.OnHandPhys, Inventory.Allocated, Inventorry.CostPrice, Brand & StockStatus).

Do I need to fetch the data in 2 seperate sql querys or can I limit my WHERE DATE clause to only a part of the select statement?

SELECT lines.Item,
       Inventory.ItemAlternative,
       Inventory.OnHandPhys,
       Inventory.Allocated,
       Inventory.CostPrice,
       SUM(lines.Invoiced)*-1 AS QtySoldInPeriod,
       (SUM(lines.AmountBase-lines.CostAmount))*-1 AS ProfitInPeriod,
       replace(vInventoryOptional2Values.Value, '/', '') AS Brand,
       Inventory.Optional3 AS StockStatus
FROM lines
INNER JOIN inventory ON lines.item = Inventory.item
INNER JOIN vInventoryOptional2Values ON Inventory.Optional2 = vInventoryOptional2Values.RecordID
WHERE Inventory.Item = 'ITEM#'
  AND Lines.Date >= 'DATE'
  AND Lines.Date <= 'DATE'
  AND lines.Invoiced < 0
  AND Inventory.Status = 0
  AND Inventory.Optional3 <> 3
GROUP BY Lines.Item,
         Inventory.Optional3,
         Inventory.ItemAlternative,
         Inventory.OnHandPhys,
         Inventory.CostPrice,
         vInventoryOptional2Values.Value,
         Inventory.Allocated
HAVING SUM(lines.AmountBase-lines.CostAmount) < 0

EDIT: I have tried with a sub-query, but can't get it to work:

SELECT lines.Item,
       Inventory.ItemAlternative,
       Inventory.OnHandPhys,
       Inventory.Allocated,
       Inventory.CostPrice,
       replace(vInventoryOptional2Values.Value, '/', '') AS Brand,
       Inventory.Optional3 AS StockStatus
  (SELECT SUM(lines.Invoiced)*-1 AS QtySoldInPeriod
   FROM (lines.item
         WHERE Lines.Date >= '2014-01-01'
           AND Lines.Date <= '2014-01-31' ) )
FROM lines
INNER JOIN inventory ON lines.item = Inventory.item
INNER JOIN vInventoryOptional2Values ON Inventory.Optional2 = vInventoryOptional2Values.RecordID
WHERE Inventory.Item = 'a158wa'
  AND lines.Invoiced < 0
  AND Inventory.Status = 0
  AND Inventory.Optional3 <> 3
GROUP BY Lines.Item,
         Inventory.Optional3,
         Inventory.ItemAlternative,
         Inventory.OnHandPhys,
         Inventory.CostPrice,
         vInventoryOptional2Values.Value,
         Inventory.Allocated

http://sqlformat.org/ is a great way to format SQL for easier reading. I've done it for you this time. — Schwern, Mar 17 '15 at 04:12
There can be several answers depending on the specifics of what you want. Can you perhaps talk about the answer you want, rather than the SQL specifics? Like, are you looking to list all items regardless of whether they've been invoiced in the time period? — Schwern, Mar 17 '15 at 04:16
I am fetching data from a ERP database. The table "Inventory" has invetory levels, cost price etc. for a specific product. I need the up-to-date value for these columns for each product (todays date). But then I want to have qty sold per item and profit per item from the Lines table for a specific date interval. The problem is that the WHERE clause that sets the date interval affects the data from the "Inventory" table. If nothing was sold (no entries in table "Lines", then I get nothing from the "Invetory" table. I hope this explains it better. — John, Mar 17 '15 at 04:22
My first thought is to use a CASE statement in the sums To check the dates... if in the range, you return the value to sum, else you return zero. — Bob, Mar 17 '15 at 04:24
I think what you want is sub-queries just for the columns with the specific date intervals. Does this help? https://stackoverflow.com/questions/16317814/sql-subquery-in-aggregate-function That or make two queries. — Schwern, Mar 17 '15 at 04:26
Oh sorry: Msg 156, Level 15, State 1, Line 5 Incorrect syntax near the keyword 'WHERE'. — John, Mar 17 '15 at 05:11

amahfouz · Answer 1 · 2015-03-17T05:53:19.197

0

You can't. Formally, the query returns a set of rows, and the WHERE clause is applied to the set of rows as a filter. That is, it is the set of rows that is being filtered and not a particular column.

Consider using the Union operator to combine two select statements.

edited Mar 17 '15 at 05:53

answered Mar 17 '15 at 04:17

amahfouz

2,328
2
16
21

1

You're technically correct, but the OP's problem can be solved. Just not with a WHERE clause alone. – Schwern Mar 17 '15 at 04:27
So I would need to split the query in two or what would be the best solution? - did not see answers above – John Mar 17 '15 at 04:28
Consider using a Union operator. (Edited) – amahfouz Mar 17 '15 at 05:52

Mahesh · Accepted Answer · 2015-03-17T07:20:35.513

You should use the subquery and in the updated question your syntax is wrong, Try using,

SELECT lines.Item,
       Inventory.ItemAlternative,
       Inventory.OnHandPhys,
       Inventory.Allocated,
       Inventory.CostPrice,
       replace(vInventoryOptional2Values.Value, '/', '') AS Brand,
       Inventory.Optional3 AS StockStatus,
       (SELECT SUM(l.Invoiced) * -1 
        FROM lines l
        WHERE l.Date >= '2014-01-01'
           AND l.Date <= '2014-01-31' 
           AND l.Item = lines.Item) AS QtySoldInPeriod
FROM lines
INNER JOIN inventory ON lines.item = Inventory.item
INNER JOIN vInventoryOptional2Values ON Inventory.Optional2 = vInventoryOptional2Values.RecordID
WHERE Inventory.Item = 'a158wa'
  AND lines.Invoiced < 0
  AND Inventory.Status = 0
  AND Inventory.Optional3 <> 3
GROUP BY Lines.Item,
         Inventory.Optional3,
         Inventory.ItemAlternative,
         Inventory.OnHandPhys,
         Inventory.CostPrice,
         vInventoryOptional2Values.Value,
         Inventory.Allocated

I get an error: Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'AS'. If I remove the "AS QtySoldInPeriod" I get: Msg 156, Level 15, State 1, Line 14 Incorrect syntax near the keyword 'FROM'. — John, Mar 17 '15 at 07:19

Restricting WHERE clause to specific SELECT statement in sql

2 Answers2