between date range ignoring 0000-00-00

[tyutghf]

Hi

I’ve been successfully using the code on thread436-1721678 (closed thread) since the answer was given, but have run in to a problem now that the date field has changed.

SELECT u . * , r.c
FROM products p
LEFT OUTER JOIN (
SELECT productid, COUNT( reviewid ) AS c
FROM reviews
WHERE (reviewdate BETWEEN CAST('2013-01-01' AS DATE) AND CAST('2014-06-16' AS DATE) )
GROUP BY productid
)r ON r.productid = p.productid
WHERE (r.c IS NULL || r.c =0)
AND manu = ‘xyz'
GROUP BY p.productid

I have added an extra date column after reviewdate for authoriseddate.

The powers that be want to see a report that shows the same report as above but based on authoriseddate, not reviewdate.

The problem is that when I added the new column, all existing records placed 0000-00-00 into the authoriseddate column, only new records have a real date in this field.

Running the report above replacing authoriseddate with reviewdate is also returning the results where authoriseddate = 0000-00-00 i.e. all of the results before this new field was added.

I tried to add a new clause to exclude the early comments

SELECT u . * , r.c
FROM products p
LEFT OUTER JOIN (
SELECT productid, authoriseddate, COUNT( reviewid ) AS c
FROM reviews
WHERE (authoriseddate BETWEEN CAST('2013-01-01' AS DATE) AND CAST('2014-06-16' AS DATE) )
AND authoriseddate <> ‘0000-00-00’
GROUP BY productid
)r ON r.productid = p.productid
WHERE (r.c IS NULL || r.c =0)
AND manu = ‘xyz'
GROUP BY p.productid

But this had no effect on the results returned, also in the results the authoriseddate column is showing NULL.

Can anyone help?

 

[tyutghf]

I have tried using just the join part

SELECT productid, COUNT( reviewid ) AS c
FROM reviews
WHERE (reviewdate BETWEEN CAST('2013-01-01' AS DATE) AND CAST('2014-06-16' AS DATE) )
GROUP BY productid

and this ignores the 0000-00-00 values on its own, so why when using it as a whole is the resultset returning 0000-00-00 rows? aarrghhh

 

[jpadie]

this doesn't look right

SELECT productid, authoriseddate, COUNT( reviewid ) AS c
FROM reviews
WHERE (authoriseddate BETWEEN CAST('2013-01-01' AS DATE) AND CAST('2014-06-16' AS DATE) )
AND authoriseddate <> ‘0000-00-00’
GROUP BY productid

if you are grouping by productID that presupposes that all products will be authorised on the same date.

i wonder whether you mean this

SELECT productid, COUNT( reviewid ) AS c
FROM reviews
GROUP BY productid
HAVING (authoriseddate BETWEEN CAST('2013-01-01' AS DATE) AND CAST('2014-06-16' AS DATE) )

in total

SELECT              u.*, r.c
FROM                products p
LEFT OUTER JOIN (

     SELECT         productid, COUNT( reviewid ) AS c
     FROM           reviews
     GROUP BY       productid
     HAVING         (authoriseddate BETWEEN CAST('2013-01-01' AS DATE) AND CAST('2014-06-16' AS DATE) )
) AS r 
ON                  r.productid = p.productid
WHERE               (r.c IS NULL || r.c = 0)
                    AND u.manu = 'xyz'
GROUP BY            p.productid

 

[SkipVought]

why convert to date?

You're not doing any date calculation just between one string and another, were the string is in the right structure to do a between or a collation, it seems to me. That way the string 0000-00-00 never qualifies in the between nor does it need any other manipulation to avoid a conversion error.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[jpadie]

i always cast strings to date when used in date calculations. i recall spending days trying to debug some wordpress core problems when some versions of mysql made wrong implicit castings. seems safest to do it properly.


anyway I suspect the nub of the OP's problem is that the query contained an unnecessary field and was using a where clause rather than a having clause.