How to create a sql script to consider two columns?

Question

Lets say today's date is Jan 22, 2014 and i run a report having this table below (Table) but input range of SIdate from Jan 01, 2014 to Jan 18, 2014 only.

I want to display all rows based on the SIdate i entered which is jan 01,2014 to Jan 18, 2014 but CMdate should be considered also and should pass the date parameter.

As you've noticed in Table 2 (My desired output), the 6th row is not there anymore bcore CMdate is Jan 21, 2014

Table 1

Emp   SIDate    item  TotQty      TotAmt     CMDate     CMAmt 
-------------------------------------------------------
CLO   01-01-14   item1  120       1500.00      null     null
CLO   01-02-14   item2   80        500.00     01-05-14   20.00
CLO   01-05-14   item6   21       1100.00      null     null
CLO   01-10-14   item5  100       2000.00     01-10-14  200.00
CLO   01-12-14   item9  300        100.00      null     null
CLO   01-16-14   item3  150        650.00     01-21-14  150.00

Table 2 (desired output)

Emp   SIDate    item  TotQty      TotAmt     CMDate     CMAmt 
-------------------------------------------------------
CLO   01-01-14   item1  120       1500.00      null     null
CLO   01-02-14   item2   80        500.00     01-05-14   20.00
CLO   01-05-14   item6   21       1100.00      null     null
CLO   01-10-14   item5  100       2000.00     01-10-14  200.00
CLO   01-12-14   item9  300        100.00      null     null

Any input would be very much appreciated.

Answer 1

You'll need to repeat the filter on both columns, optionally allowing for a NULL CMDate

SELECT 
    Emp, SIDate, item, TotQty, TotAmt, CMDate, CMAmt 
FROM 
    Table1
WHERE 
   SIDate BETWEEN '2014-01-01' AND '2014-01-18'
   AND (CMDate IS NULL OR CMDate BETWEEN '2014-01-01' AND '2014-01-18');

Fiddle here

(As an aside, using a standard like ISO for your date formats will go a long way to avoiding date format related issues)

Answer 2

select * from table where (cmdate between '01-01-2014' and '18-01-2014' or cmdate  is null) and sidate between '01-01-2014' and '18-01-2014' 

Answer 3

SELECT tbl.*
FROM Table1 AS tbl
WHERE tbl.SIDate >= '01-01-14' AND tbl.SIDate <='01-18-14'
AND tbl.CMDate >= '01-01-14' AND tbl.CMDate <='01-18-14'

OR

SELECT tbl.*
FROM Table1 AS tbl
WHERE tbl.SIDate BETWEEN '01-01-14' AND '01-19-14'
AND tbl.CMDate BETWEEN '01-01-14' AND '01-19-14'

Answer 4

Try this:

SELECT a.Emp, a.SIDate, a.item, a.TotQty, a.TotAmt, a.CMDate, a.CMAmt 
FROM tableA 
WHERE DATE_FORMAT(a.SIDate, '%m-%d-%Y') BETWEEN '2014-01-01' AND '2014-01-18' AND 
      1 = (CASE WHEN a.CMDate IS NULL THEN 1 
                WHEN DATE_FORMAT(a.CMDate, '%m-%d-%Y') BETWEEN '2014-01-01' AND '2014-01-18' THEN 1 
                ELSE 0
           END)

Answer 5

This should do:

SELECT * FROM TABLE WHERE (CMDate IS NULL OR (CMDate >= MIN(SIDate) AND CMDate <= MAX(SIDate));