I have 2 tables to join where year and mth are keys ...
In both tables year is a 4 digit numeric. In one table mth is a 2 digit numeric, in the other it is a 2 character numeric (i.e. '07'). I have to maintain these formats, but how do I join on the mth fields ... ideas ??? My SQL is below, but it's not giving me what I want. Thanks!
SELECT tblitsupplieddata.countrycode,
tblitsupplieddata.mth,
b.mth,tblitsupplieddata.totalbiennialregistered,
b.unusablecountfrom tblitsupplieddata inner join
(SELECT LEFT(child_key,2) as countrycode,
FORMAT(date_received,'mm') as mth,
COUNT(Primary_Unusable_id) as unusablecount
FROM tblUnusables
WHERE ((FORMAT(date_received,'YYYY') = 2003) AND (FORMAT(date_received,'mm') >= 7)OR(FORMAT(date_received,'YYYY') = 2004) AND (FORMAT(date_received,'mm')<= 6))AND Throw_Out = No AND Unusable_type = 'NCH'GROUP BY LEFT child_key,2),FORMAT(date_received,'mm')) as b on (tblitsupplieddata.countrycode = b.countrycode and tblitsupplieddata.mth = b.mth )
WHERE ((tblitsupplieddata.year = 2003 AND tblitsupplieddata.mth >= 7)OR(tblitsupplieddata.year = 2004 AND tblitsupplieddata.mth <= 6))
