 PToN
join:2001-10-04
Houston, TX | Yet Another MySQL Help Inquiry.Hello,
I have 2 queries that independently they work fine and provide accurate results. I would like to combine them in 1 query, but i am having difficulties adding a date field to the select list.
The query has a couple of sub-queries that provide results to two of the items listed in the select list and they both take the results from a date range based on different date fields.
The firs one is based on Complete_at:
SELECT
COUNT( DISTINCT LEFT( serial_number, LOCATE( '-', serial_number ) -1 ) ) AS qtyOut,
dlog_sequence_log.sequence_name_id,
DATE_FORMAT( completed_at, '%Y-%m' ) AS MONTH
FROM
dlog_sequence_log
WHERE
DATE_FORMAT(completed_at, '%Y-%m')
BETWEEN
DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 2 MONTH), '%Y-%m')
AND
DATE_FORMAT(CURDATE(), '%Y-%m')
GROUP BY
dlog_sequence_log.sequence_name_id,
MONTH
Provides results like:
QtyOut Seq_name_id Date
448 10000 2011-09
806 10001 2011-09
297 10003 2011-09
333 10004 2011-09
121 10005 2011-09
87 10006 2011-09
218 10007 2011-09
6 10008 2011-09
6 10009 2011-09
315 10000 2011-10
577 10001 2011-10
193 10003 2011-10
251 10004 2011-10
79 10005 2011-10
73 10006 2011-10
110 10007 2011-10
9 10008 2011-10
2 10009 2011-10
The second subquery just changes "completed_at" to "created_at" and produces similar results as above, but QtyIn instead of QtyOut.
The main complete query looks like:
SELECT
qtyOut,
qtyIn,
dlog_sequence_name.sequence_name_id,
sequence_name
FROM
dlog_sequence_name
INNER JOIN
(
SELECT
COUNT( DISTINCT LEFT( serial_number, LOCATE( '-', serial_number ) -1 ) ) AS qtyOut,
dlog_sequence_log.sequence_name_id,
DATE_FORMAT( completed_at, '%Y-%m' ) AS month
FROM
dlog_sequence_log
WHERE
DATE_FORMAT(completed_at, '%Y-%m')
BETWEEN
DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 2 MONTH), '%Y-%m')
AND
DATE_FORMAT(CURDATE(), '%Y-%m')
GROUP BY
month,
dlog_sequence_log.sequence_name_id
) as QueryOut
ON
dlog_sequence_name.sequence_name_id = QueryOut.sequence_name_id
INNER JOIN
(
SELECT
COUNT( DISTINCT LEFT( serial_number, LOCATE( '-', serial_number ) -1 ) ) AS qtyIn,
dlog_sequence_log.sequence_name_id,
DATE_FORMAT( created_at, '%Y-%m' ) AS month
FROM
dlog_sequence_log
WHERE
DATE_FORMAT(created_at, '%Y-%m')
BETWEEN
DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 2 MONTH), '%Y-%m')
AND
DATE_FORMAT(CURDATE(), '%Y-%m')
GROUP BY
month,
dlog_sequence_log.sequence_name_id
) as QueryIn
ON
dlog_sequence_name.sequence_name_id = QueryIn.sequence_name_id
ORDER BY sequence_name_id;
The problem is that when i run the above the subqueries only return 1 result so i am looking for a way to add the dates that where used in the subquery and add them to the main select list so that i have 5 columns
QtyIN QtyOUT DATE SEQ_NAME SEQ_NAME_ID
I could do another subquery and join that return the dates used, but is this the most effective way??
Any ideas on the most effective way to add the dates used in the subqueries to the result..??
Thanks |
PToN
join:2001-10-04
Houston, TX | I thought about that, but i dont think it will work if i have to change a few things on the fly, like INTERVAL (it could be days, hours) and the date format (it could be formatted differently depending on the interval). |
Reviews:
 ·AT&T U-Verse
| reply to PToN
DROP TEMPORARY TABLE IF EXISTS tbl_in;
DROP TEMPORARY TABLE IF EXISTS tbl_out;
CREATE TEMPORARY TABLE tbl_in (
seq int,
datein varchar(10),
qtyin int
) ENGINE=MEMORY;
CREATE TEMPORARY TABLE tbl_out (
seq int,
dateout varchar(10),
qtyout int
) ENGINE=MEMORY;
insert into tbl_out (qtyout, seq, dateout)
SELECT
COUNT( DISTINCT LEFT( serial_number, LOCATE( '-', serial_number ) -1 ) ) AS qtyOut,
dlog_sequence_log.sequence_name_id,
[...]
#same for tbl_in
select i.seq, i.date_in, i.qtyin, o.qtyout
from tbl_in i
left join tbl_out o
on (i.seq=o.seq and i.datein=o.dateout)
|
