If Statements in where clause?

Question

So I have to run a query against a database schema: http://sqlfiddle.com/#!9/d0b643 , but an example schema would look like:

Table 1
itemID          sale date     salesmanID   storeID
---------------------------------------------------
1                 1/2015        1             1
1                 3/2016        1             1
2                 5/2016        2             1
2                 1/2015        4             1

Table 2
itemID           colorID           price
--------------------------------------
1                 1                23
1                 2                10
1                 3                13
2                 1                11
2                 2                14
2                 3                18

Table 3
ColorID       color
---------------------------------------
 1             Red
 2             Blue
 3             Green

Table 4
SaleBegin       SaleEnd      ColorID      salesmanID     storeID
----------------------------------------------------------------
1/1/2014        12/31/2014      1            0             1
1/1/2015        12/31/2015      2            0             1
1/1/2016        12/31/2016      3            0             1
1/1/2014        12/31/2014      3            2             1
1/1/2015        12/31/2016      2            2             1

I need to have something in the where clause that pretty much says if there is a SalesmanID and the saleDate from Table1 falls between the StartDate and Enddate of Table4, use that color. Otherwise, if there is no salesmanID, use the StoreID (in this example they are all 1, but they could be different).

The current query I am adding this to is:

select t1.itemID,t3.color,t2.price
from table_1 t1
LEFT JOIN table_2 t2
ON t1.itemID = t2.itemID
LEFT JOIN table_3 t3
ON t2.colorID = t3.colorID
LEFT JOIN table_4 t4
ON t3.colorID = t4.colorID
WHERE t1.sale_date BETWEEN saleBegin and saleEnd;

How can I run this? The expected results should look like:

itemID color price

1           Blue        10
1           Green       13
2           Blue        14
2           Blue        14

`(SalesmanID between x and y or SalesmanID is NULL) AND (b between a and c OR b is NULL )` — Ivan Starostin, May 27 '16 at 14:27
try using case statement in the where clause might help you. — Nagaraj, May 27 '16 at 14:37
When the `salesmanID` is zero in table 4 then how do we work out what the `itemID` is? — onedaywhen, May 27 '16 at 15:16

Joe C · Answer 1 · 2016-05-27T16:36:51.387

0

I think this is what you want. You will get too many rows because of overlaps between store @ and Sale dates. Once I changed your sample data table4 to have the last two rows be store #2 I get the results you posted.

1/1/2014        12/31/2014      3            2             2 
1/1/2015        12/31/2016      2            2             2 

Select t1.itemID,t3.color,t2.price
    From table_1 t1
    LEFT JOIN table_4 t4 ON t1.sale_date BETWEEN t4.saleBegin And t4.saleEnd And t1.SalesmanID = t4.SalesmanID
    LEFT JOIN table_4 t4a ON t1.sale_date BETWEEN t4a.saleBegin And t4a.saleEnd And t1.StoreID = t4a.StoreID
    LEFT JOIN table_2 t2 ON t1.itemID = t2.itemID And t2.ColorID = t4a.ColorID
    LEFT JOIN table_3 t3 ON t2.colorID = t3.colorID
    Where t4.SalesmanID Is Null
    Group By t1.itemID,t3.color,t2.price
Union All
Select t1.itemID,t3.color,t2.price
    From table_1 t1
    LEFT JOIN table_4 t4 ON t1.sale_date BETWEEN t4.saleBegin And t4.saleEnd And t1.SalesmanID = t4.SalesmanID
    LEFT JOIN table_2 t2 ON t1.itemID = t2.itemID And t2.ColorID = t4.ColorID
    LEFT JOIN table_3 t3 ON t2.colorID = t3.colorID
    Where t4.SalesmanID Is Not Null

itemID  color   price
1       Blue    10.000
1       Green   13.000
2       Blue    14.000
2       Blue    14.000

edited May 27 '16 at 16:36

answered May 27 '16 at 15:21

Joe C

3,925
2
11
31

Unfortunately in the real world data, there will be overlap. The issue is I need to see if it has a salesmanID first, if it does, then grab the color/price from the row of the dates it falls between. If it doesn't, then grab the color/price of where it matches with the store from the row it falls between. It's not the best designed system, unfortunately I don't have the option of redesign and I didn't create the system. – Mike May 27 '16 at 16:10
What do you want to do when there is overlap, just choose one of the records at random? – Joe C May 27 '16 at 16:27
Let's know what's priority if there is overlap. – Jason Lou May 27 '16 at 16:30
I added a group by to the first query in the union which produces the desired results but it does not solve the problem if the same store has overlapping sales with different colors. Because of the date range nature of the sales this could get quite complicated. You might need to expand the range to individual dates to find the exact overlaps then use a function like row_number to prioritize which one you want to keep. – Joe C May 27 '16 at 16:39
http://stackoverflow.com/questions/9140308/get-all-dates-in-date-range-in-sql-server – Joe C May 27 '16 at 16:40
Ok, I will look into this. Thank you Joe C. – Mike May 27 '16 at 19:34

score 0 · Answer 2 · answered May 27 '16 at 16:20

I am trying to use MSSQL logic. and try it in SQL fiddle it run success. Just try this code yourself to see if this is what you want.

select t1.itemID,t3.color,t2.price from table_1 t1 
LEFT JOIN table_4 t41 
on t1.sale_date BETWEEN t41.saleBegin and t41.saleEnd 
and t41.salesmanID!=0 and t1.salesmanID=t41.salesmanID
LEFT JOIN table_4 t42 
on t1.sale_date BETWEEN t42.saleBegin and t42.saleEnd 
and t42.salesmanID=0 and t1.storeID=t42.storeID
INNER JOIN table_2 t2
ON t1.itemID = t2.itemID
INNER JOIN table_3 t3
ON t2.colorID = t3.colorID and t2.colorID=IF(ISNULL(t41.colorID),t42.colorID,t41.colorID)
order by t1.itemID,t3.color,t2.price;

If Statements in where clause?

itemID color price

2 Answers2