SQL to find only those records which are before certain date

Question

I have the above set of 2 tables and their respective columns.

HEADER
    PO_Number (char)
    CUSTNMBR (char)
    Expiry_Date (datetime)
    PO_Status (tinyint, 0 = Open, 1 = Closed)
    STATUSDATE (datetime)

BATCH
    PORDNMBR (char)
    CUSTNMBR (char)
    BTCHID (char)
    DATE1 (datetime)

In he HEADER table the primary key is (PO_Number, CUSTNMBR). In the BATCH table BTCHID is the primary key. The DATE1 column is the date on which the respective batch was entered. Also, there could be Multiple BTCHIDs entered on different date for each (PO_Number, CUSTNMBR) combination in HEADER table. I have a cutoff date of 2016-07-12 and there are batches before the cutoff date and batches after the cutoff date.

I need to identify those (PO_Number, CUSTNMBR) combination which do not have any batches entered after the cutoff date, where the Expiry_Date form HEADER is < cutoff date.

I wrote the following query initially but the result is bringing records which also have batches after cutoff date. I have verified that using a SELECT * from BATCH query by keying on the PORDNMBR, and in result I can see batches before as well as after cutoff date. Is there any way to identify those which do not have any batches entered after the cutoff date and Expiry_Date form HEADER is < cutoff date?

SELECT BATCH.PORDNMBR, BATCH.CUSTNMBR, BATCH.BTCHID, BATCH.DATE1, 
HEADER.PO_Number, HEADER.CUSTNMBR, HEADER.Expiry_Date, HEADER.PO_Status, HEADER.STATUSDATE
FROM           BATCH
INNER JOIN
               HEADER
ON 
BATCH.CUSTNMBR = HEADER.CUSTNMBR 
AND 
BATCH.PORDNMBR = HEADER.PO_Number
WHERE BATCH.Expiry_Date < '2016-07-12 00:00:00'
AND BATCH.DATE1 < '2016-07-12 00:00:00'

Add some sample table data and the expected result. (As formatted text, not images.) Consider reading https://stackoverflow.com/help/mcve before you spend too much time! — jarlh, Jan 18 '18 at 10:28
Refer this link https://stackoverflow.com/questions/19924236/query-comparing-dates-in-sql — Sanchit Gupta, Jan 18 '18 at 10:29
BTW, which dbms are you using? When it comes to date/time, many products are far from ANSI SQL compliant. — jarlh, Jan 18 '18 at 10:30

score 0 · Answer 1 · answered Jan 18 '18 at 10:37

Join to a subquery which identifies the valid batches:

SELECT
    b1.PORDNMBR,
    b1.CUSTNMBR,
    b1.BTCHID,
    b1.DATE1, 
    h.PO_Number,
    h.CUSTNMBR,
    h.Expiry_Date,
    h.PO_Status,
    h.STATUSDATE
FROM BATCH b1
INNER JOIN
(
    SELECT PORDNMBR, CUSTNMBR
    FROM BATCH
    GROUP BY PORDNMBR, CUSTNMBR
    HAVING SUM(CASE WHEN Expiry_Date >= '2016-07-12 00:00:00' THEN 1 ELSE 0 END) = 0
) b2
    ON b1.PORDNMBR = b2.PORDNMBR AND
       b1.CUSTNMBR = b2.CUSTNMBR
INNER JOIN HEADER h
    ON b1.CUSTNMBR = h.CUSTNMBR AND 
       b1.PORDNMBR = h.PO_Number
WHERE
    b1.DATE1 < '2016-07-12 00:00:00';

The problem with your current logic is that you are applying the expiry date logic in the WHERE clause, against each record. But you really want to apply this logic over groups of records.

Sorry for being late, this worked. You are correct, I missed the group earlier. Thank you for all the help to all of you. — SMA, Jan 25 '18 at 13:14

score 0 · Answer 2 · answered Jan 18 '18 at 11:20

0

Hmm. I am thinking you just want header information so not exists comes to mind:

select h.*
from header h
where not exists (select 1
                  from batch b
                  where b.pordnmbr = h.prodnmbr and
                        b.custnmbr = h.custnmbr and
                        b.Expiry_Date < '2016-07-12' and
                        b.date1 > '2016-07-12'
                 );

The date arithmetic is slightly different from your query, matching the logic that you describe.

answered Jan 18 '18 at 11:20

Gordon Linoff

1,242,037
58
646
786

Expiry_Date is not in batch – paparazzo Jan 18 '18 at 14:10
@Paparazzi . . . It is in the question: `WHERE BATCH.Expiry_Date < '2016-07-12 00:00:00'`. – Gordon Linoff Jan 19 '18 at 02:49
It may be in the select but it is not in the table definition. – paparazzo Jan 19 '18 at 09:24

paparazzo · Answer 3 · 2018-01-18T14:24:10.300

Your query only eliminates rows from the batch - not the entire batch

I think this will work

declare @Expiry_Date datetime = '2016-07-12 00:00:00';

SELECT B.PORDNMBR, B.CUSTNMBR, B.BTCHID, B.DATE1, 
       H.PO_Number, H.CUSTNMBR, H.Expiry_Date, H.PO_Status, H.STATUSDATE
FROM   ( select * 
         from header 
         where Expiry_Date < @Expiry_Date
       ) H
JOIN BATCH B
  ON B.CUSTNMBR = H.CUSTNMBR 
 AND B.PORDNMBR = H.PO_Number
 AND NOT EXISTS ( SELECT 1 from batch 
                   WHERE BATCH.CUSTNMBR = H.CUSTNMBR 
                     AND BATCH.PORDNMBR = H.PO_Number 
                     AND BATCH.DATE1 >= @Expiry_Date 
                )
ORDER BY B.CUSTNMBR, B.PORDNMBR, B.BTCHID

SQL to find only those records which are before certain date

3 Answers3