returning data from microsoft query

**mintmin** · 09-07-2009, 12:13 AM

hi,
I have a query which is giving me some trouble when returning the data from ms query.
The query was written in mysql query browser and returns 2 columns of data - a date and a number.
MS Query correctly processes the query but when the data is returned to excel only the second column is returned.

Here is the query:

SELECT
(SELECT
max(l.the_date)
FROM
nc_view_date_functions AS l
WHERE
l.week_of_year = d.week_of_year
AND l.yyyy = d.yyyy
) AS week_end_date,
count(r.consent_id) AS weekly_count
FROM
rg_resource_consents AS r
INNER JOIN nc_view_date_functions AS d
ON r.application_date = d.the_date
GROUP BY
d.yyyy, d.week_of_year;

Can anyone shed any light on this for me?

Many thanks

**mintmin** · 09-07-2009, 06:04 PM

Hi all,

I have solved this one now - the issue was that excel did not like the way the subquery data was being returned. By using a CAST( AS DATE) around the entire subquery things now work as required.

Correct query:
SELECT
CAST((SELECT max(l.the_date) FROM nc_view_date_functions AS l WHERE l.week_of_year = d.week_of_year AND l.yyyy = d.yyyy ) AS DATE) AS week_end_date,
count(r.consent_id) AS weekly_count
FROM rg_resource_consents AS r
INNER JOIN nc_view_date_functions AS d ON r.application_date = d.the_date
GROUP BYd.yyyy, d.week_of_year;

Many thanks.

**DonkeyOte** · 09-08-2009, 02:13 AM

Thank you for taking the time to post your resolution - this is sure to help someone in the future who may stumble across your post whilst trying to resolve their own issue(s).

Incidentally please also mark thread as solved (see FAQ / How To for more info.)