CODEWITHSUNDEEP
mysql
kyuzon
kyuzon

Reputation: 45

How to get where clause to return rows where value of one column is prior to other column date but also still return ones where value is null

I am trying to build report pulling all rows where the columns of 2 of the tables billing.billed_date or indiv_response.date_submitted that show date later then say 10/20/2021 but then also want to narrow that result down to show only rows where the billed_date is prior to the date_submitted and leave any rows where the date submitted also might be blank or null. so for my initial where clause i had it as WHERE billing.billed_date >= '2021-10-20' OR indiv_responses.date_submitted >= '2021-10-20' and this gives desired initial result of showing all rows where either billed_date or date_submitted are greater than or = to the given date. If i then add an AND to limit to rows (billings.billed_date >= '2021-10-25'OR indiv_responses.date_submitted >= '2021-10-25') AND billings.billed_date < indiv_responses.date_submitted where the billed_date is < date_submitted this pares it down further but i lose all the entries where no date was put into date_submitted yet. is there some way to word the where clause here to show the rows where billed_date is < date_submitted and also show the date_submitted that are null

As an example these are few rows that return for billed_date after 10/20/2021 but there wasnt a submitted_date yet. but if i try to add the billed_date is < date_submitted i lose all these rows ,since null value is not < an actual date.

FILE #              Billed Date Submitted Date
ALL-B32224552       10/28/2021  null
GRR-BSD344335       10/28/2021  null
PPW-GRE349955       10/28/2021  null
GOW-QAS234543       10/28/2021  null
FSC-HKL334450       10/28/2021  null

So is there some way in the where clause to do this ? or do i need to have to look at some subquery or something ? just hoping the sql experts might know a simple solution im not seeing

thanks for any help here is example of query running, this returns all the rows that meet that where clause, but if i try to pare down after that to just show from those results ones that billed_date is < submitted_date i lose all the rows where submitted date is currently null and we want to keep those

    SELECT
  dd.file_acceptance,
  billing.billed_date,
  indiv_responses.date_submitted
FROM ds
  LEFT OUTER JOIN dd
    ON ds.dd_id = dd.id
  LEFT OUTER JOIN billing
    ON ds.billing_id = billing.id
  LEFT OUTER JOIN indiv_responses
    ON indiv_responses.ds_id = ds.id
WHERE (billing.billed_date >= '2021-10-25'OR indiv_responses.date_submitted >= '2021-10-25')

Upvotes: 0

Views: 33

Answers (1)

ProDec
ProDec

Reputation: 5410

Looks like adding another condition as following will do

AND ( indiv_responses.date_submitted IS NULL OR indiv_responses.date_submitted > billing.billed_date )

so that date_submitted is either NULL or after billed_date

Upvotes: 1

Related Questions