Nura111
asked on
anyone care to explain the follow query? Im lost
Hi, Im a little bit confused from the following query and will be happy if someone would care to help
Im not even sure which table is it refering to and escpecilly what all of
SELECT 0 SeqValue mean
Thank you/1
Im not even sure which table is it refering to and escpecilly what all of
SELECT 0 SeqValue mean
Thank you/1
query =<<<EOB
SELECT
timestamps.request_date, D.id, COUNT(SR.id) AS `numLeads`
FROM
(
SELECT
DATE_SUB(CURDATE(), INTERVAL SEQ.SeqValue DAY) AS request_date
FROM
(
SELECT
(HUNDREDS.SeqValue + TENS.SeqValue + ONES.SeqValue) SeqValue
FROM
(
SELECT 0 SeqValue UNION ALL
SELECT 1 SeqValue UNION ALL
SELECT 2 SeqValue UNION ALL
SELECT 3 SeqValue UNION ALL
SELECT 4 SeqValue UNION ALL
SELECT 5 SeqValue UNION ALL
SELECT 6 SeqValue UNION ALL
SELECT 7 SeqValue UNION ALL
SELECT 8 SeqValue UNION ALL
SELECT 9 SeqValue
) ONES
CROSS JOIN
(
SELECT 0 SeqValue UNION ALL
SELECT 10 SeqValue UNION ALL
SELECT 20 SeqValue UNION ALL
SELECT 30 SeqValue UNION ALL
SELECT 40 SeqValue UNION ALL
SELECT 50 SeqValue UNION ALL
SELECT 60 SeqValue UNION ALL
SELECT 70 SeqValue UNION ALL
SELECT 80 SeqValue UNION ALL
SELECT 90 SeqValue
) TENS
CROSS JOIN
(
SELECT 0 SeqValue UNION ALL
SELECT 100 SeqValue UNION ALL
SELECT 200 SeqValue UNION ALL
SELECT 300 SeqValue
) HUNDREDS
) SEQ
WHERE
SEQ.SeqValue <= {$days}
ORDER BY
SEQ.SeqValue DESC
) timestamps
LEFT OUTER JOIN Domain D ON id='{$safeDomainId}'
LEFT OUTER JOIN ServiceRequests SR ON
SR.timestamp >= timestamps.request_date AND
SR.timestamp <= ADDDATE(timestamps.request_date, INTERVAL 1 DAY) AND
SR.domain_id=D.id AND
SR.email_address != 'leads@orininc.com' AND
SR.email_address != 'leads@allyoucanmove.com'
GROUP BY timestamps.request_date, D.id
ORDER BY timestamps.request_date ASC, D.id ASC
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
By the way, the linked Article can be used to generate a util.numbers table in your MySQL server that you can reuse for purposes like this. The performance may be slightly better as it is indexed versus having to build a new sequence every time.
So to answer your question "where does he create it to which db?" It currently is a derived table and so not physically created to any db. I would recommend having a util db/schema for such things though.
It comes in handy when you are generating reports where you need all dates, but your data may have gaps. It looks like that is the scenario here. So using a table of numbers, the script is creating a table of dates and then LEFT OUTER JOINing those to your data. That way, you get NULL or 0 (in the case of COUNT) when missing data, but at least have a row. When there is a match on the date, then you get data.
So to answer your question "where does he create it to which db?" It currently is a derived table and so not physically created to any db. I would recommend having a util db/schema for such things though.
It comes in handy when you are generating reports where you need all dates, but your data may have gaps. It looks like that is the scenario here. So using a table of numbers, the script is creating a table of dates and then LEFT OUTER JOINing those to your data. That way, you get NULL or 0 (in the case of COUNT) when missing data, but at least have a row. When there is a match on the date, then you get data.
ASKER
Ok Ill try look deeper inot the article you linked because I have no idea what so ever on what is for and the purpose..
If you have any other good reference let me know
Thank you for the help
If you have any other good reference let me know
Thank you for the help
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you!
ASKER
Hi mwvisa1:
the problem with FROM util.numbers
is that Table 'util.numbers' doesn't exist
the problem with FROM util.numbers
is that Table 'util.numbers' doesn't exist
Nura111, the first post you selected has a link to an article where I give some code to create the util.numbers table. *smile* It is handy to have one in your system, but I have in the comments how to do this on the fly also -- similar to Mark's use of spt_values in his article.
ASKER