MySQL to MSSQL conversion of stmt

tim_gunning

hi all im converting a load of queries from MySQL to MSSQL and have run into a problem and was wondering if anyone had any ideas..... I currently have:

SELECT 'PI' AS transaction_type, t1.supplier_code, t1.nominal_code, '01-01-2006' AS my_dispatch_date, t2.name AS resourcename, t2.reference, '1.20' AS royalty_amount,
t3.code, '1.21' AS tax_amount, '1' AS blank_field_2, '_' AS blank_field_3, '_' AS blank_field_4
FROM order_line AS t4 INNER JOIN
resource AS t2 ON t4.resource_id = t2.resource_id INNER JOIN
supplier_codes AS t1 ON t2.reference = t1.reference AND t4.dispatch_date >= t1.payment_start_date AND t4.dispatch_date <= t1.payment_end_date INNER JOIN
partners AS t5 ON t1.partner_id = t5.partner_id INNER JOIN
tax_codes AS t3 ON t5.tax_code = t3.id INNER JOIN
partner_type AS t6 ON t1.partner_type_id = t6.partner_type_id
WHERE (t4.status = 1) AND (t4.dm <> 'ZX') AND (t4.dispatch_date >= @from) AND (t4.dispatch_date <= @to)
ORDER BY t4.dispatch_date, t2.reference

but i need to group by t1.reference, t1.supplier_code but obviously it keeps telling me I can't do that, my brains a bit fried atm, if anyones got any great ideas it would be really helpful ta tim

Tim Carmichael

To use group by, you need an aggregate... sum(...) or count(...) or avg(...), etc. Tim

tim_gunning

hi tim problem is...I dont want to do anything with them apart from get the values in the same way you do in MySQL, I just want to get rid of the duplicates that get pulled based on certain criteria. this is what I'm converting:

SELECT

'PI' as transaction_type,
supplier_codes.supplier_code,
supplier_codes.nominal_code,
COUNT(o.orderline_id) as sale_volume,
IF ( EXTRACT(MONTH FROM STR_TO_DATE(CONCAT(YEARWEEK(dispatch_date),'0'),'%x%v%w')) = MONTH(dispatch_date),
DATE_FORMAT(STR_TO_DATE(CONCAT(YEARWEEK(dispatch_date),'0'),'%x%v%w'),'%d/%m/%Y'),
DATE_FORMAT(STR_TO_DATE(CONCAT('01/',EXTRACT(MONTH FROM dispatch_date),'/',YEAR(dispatch_date)),'%d/%m/%Y'),'%d/%m/%Y')) as my_dispatch_date,

resource.name as resourcename,
resource.reference,
SUM(supplier_codes.license_fee) as royalty_amount,
tax_codes.code,
ROUND(((SUM(supplier_codes.license_fee) / 100) * tax_codes.multiplier),2) as tax_amount,
"1" as blank_field_2,
"_" as blank_field_3,
"_" as blank_field_4

FROM order_line o

LEFT JOIN resource ON o.resource_id = resource.resource_id
LEFT JOIN supplier_codes ON resource.reference = supplier_codes.reference
LEFT JOIN partners ON supplier_codes.partner_id = partners.partner_id
LEFT JOIN partner_type ON supplier_codes.partner_type_id = partner_type.partner_type_id
LEFT JOIN tax_codes ON partners.tax_code = tax_codes.id

WHERE supplier_codes.payment_start_date <= dispatch_date
AND supplier_codes.payment_end_date >= dispatch_date
AND o.status = 1
AND DATE_FORMAT(o.dispatch_date,'%Y-%m-%d') >= '$from'
AND DATE_FORMAT(o.dispatch_date,'%Y-%m-%d') < '$to'
AND o.dm !='ZX'

GROUP BY reference, supplier_code, my_dispatch_date
ORDER BY dispatch_date, reference

dont worry about the difference in some of the vars in it, its only the group by thats doing my head in

Tim Carmichael

Ok.... in SQLServer, a group by is used if there are any aggregate fields (sum, count, etc). In your original query (from MySQL), there is a COUNT and 2 SUM aggregate fields. If you want the sales volumne (COUNT), royalty amount (SUM) and tax amount (SUM) in your SQLServer query, GROUP BY any non-aggregate fields: GROUP BY supplier_codes.supplier_code, supplier_codes.nominal_code, resource.name, resource.reference, tax_codes.code The GROUP BY clause must go after the WHERE and before the ORDER BY If you are currently getting duplicate records, change the SELECT to SELECT DISTINCT. Hope that helps... Tim

tim_gunning

cheers tim that helped a lot,i've been looking at this (migration overall) for about a week now and i can't really see the trees for the woods anymore, my brains withered. ta tim