[FONT="]Hi I’m looking at trying to query fold. I have read that this will speed things up for us.[/FONT]
[FONT="]The Qry below is from EXCEL and filters only selected dates. But when I try to convert the SQL statement to M in excel it doesn’t like the “WHERE” function. I am connecting a few tables from our SQL SERVER to run this query.[/FONT]
[FONT="]SELECT dbo_tblSale.sale_link, dbo_tblSale.loc_code AS [LOC#], dbo_tblSale.Date AS [TRANS DATE], dbo_tblSale.Time, dbo_tblSale.trans_no AS [TRANS#], dbo_tblSale.customer_code AS [CUST#], dbo_tblSaleCommission.salesperson, dbo_tblSaleItem.sku_no AS [SKU#], dbo_tblSaleItem.description, Min(dbo_tblSaleDiscount.disc_code) AS [DISCOUNT CODE], dbo_tblSaleItem.qty AS [UNITS SOLD], dbo_tblSaleItem.ext_cost AS COST, [unit_ext_price]-[unit_ext_best_price] AS DISCOUNT, dbo_tblSaleItem.unit_ext_price, dbo_tblSaleItem.unit_ext_best_price AS [NET RETAIL], dbo_tblSaleItem.line_no, dbo_tblSale.trans_type, dbo_tblSale.void_code, dbo_tblSale.suspended, dbo_tblSaleItem.affect_inv, dbo_tblSaleItem.affect_total, dbo_tblSale.note AS [SALE NOTE], dbo_tblSaleItem.note AS [SALE ITEM NOTE]
FROM dbo_tblSaleDiscount RIGHT JOIN (dbo_tblSale INNER JOIN (dbo_tblSaleItem INNER JOIN dbo_tblSaleCommission ON (dbo_tblSaleItem.line_no = dbo_tblSaleCommission.line_no) AND (dbo_tblSaleItem.sale_link = dbo_tblSaleCommission.sale_link)) ON dbo_tblSale.sale_link = dbo_tblSaleItem.sale_link) ON (dbo_tblSaleDiscount.line_no = dbo_tblSaleItem.line_no) AND (dbo_tblSaleDiscount.sale_link = dbo_tblSaleItem.sale_link)
GROUP BY dbo_tblSale.sale_link, dbo_tblSale.loc_code, dbo_tblSale.Date, dbo_tblSale.Time, dbo_tblSale.trans_no, dbo_tblSale.customer_code, dbo_tblSaleCommission.salesperson, dbo_tblSaleItem.sku_no, dbo_tblSaleItem.description, dbo_tblSaleItem.qty, dbo_tblSaleItem.ext_cost, [unit_ext_price]-[unit_ext_best_price], dbo_tblSaleItem.unit_ext_price, dbo_tblSaleItem.unit_ext_best_price, dbo_tblSaleItem.line_no, dbo_tblSale.trans_type, dbo_tblSale.void_code, dbo_tblSale.suspended, dbo_tblSaleItem.affect_inv, dbo_tblSaleItem.affect_total, dbo_tblSale.note, dbo_tblSaleItem.note
HAVING (((dbo_tblSale.Date)>#1/31/2016#));[/FONT]
[FONT="]The Qry below is from EXCEL and filters only selected dates. But when I try to convert the SQL statement to M in excel it doesn’t like the “WHERE” function. I am connecting a few tables from our SQL SERVER to run this query.[/FONT]
[FONT="]SELECT dbo_tblSale.sale_link, dbo_tblSale.loc_code AS [LOC#], dbo_tblSale.Date AS [TRANS DATE], dbo_tblSale.Time, dbo_tblSale.trans_no AS [TRANS#], dbo_tblSale.customer_code AS [CUST#], dbo_tblSaleCommission.salesperson, dbo_tblSaleItem.sku_no AS [SKU#], dbo_tblSaleItem.description, Min(dbo_tblSaleDiscount.disc_code) AS [DISCOUNT CODE], dbo_tblSaleItem.qty AS [UNITS SOLD], dbo_tblSaleItem.ext_cost AS COST, [unit_ext_price]-[unit_ext_best_price] AS DISCOUNT, dbo_tblSaleItem.unit_ext_price, dbo_tblSaleItem.unit_ext_best_price AS [NET RETAIL], dbo_tblSaleItem.line_no, dbo_tblSale.trans_type, dbo_tblSale.void_code, dbo_tblSale.suspended, dbo_tblSaleItem.affect_inv, dbo_tblSaleItem.affect_total, dbo_tblSale.note AS [SALE NOTE], dbo_tblSaleItem.note AS [SALE ITEM NOTE]
FROM dbo_tblSaleDiscount RIGHT JOIN (dbo_tblSale INNER JOIN (dbo_tblSaleItem INNER JOIN dbo_tblSaleCommission ON (dbo_tblSaleItem.line_no = dbo_tblSaleCommission.line_no) AND (dbo_tblSaleItem.sale_link = dbo_tblSaleCommission.sale_link)) ON dbo_tblSale.sale_link = dbo_tblSaleItem.sale_link) ON (dbo_tblSaleDiscount.line_no = dbo_tblSaleItem.line_no) AND (dbo_tblSaleDiscount.sale_link = dbo_tblSaleItem.sale_link)
GROUP BY dbo_tblSale.sale_link, dbo_tblSale.loc_code, dbo_tblSale.Date, dbo_tblSale.Time, dbo_tblSale.trans_no, dbo_tblSale.customer_code, dbo_tblSaleCommission.salesperson, dbo_tblSaleItem.sku_no, dbo_tblSaleItem.description, dbo_tblSaleItem.qty, dbo_tblSaleItem.ext_cost, [unit_ext_price]-[unit_ext_best_price], dbo_tblSaleItem.unit_ext_price, dbo_tblSaleItem.unit_ext_best_price, dbo_tblSaleItem.line_no, dbo_tblSale.trans_type, dbo_tblSale.void_code, dbo_tblSale.suspended, dbo_tblSaleItem.affect_inv, dbo_tblSaleItem.affect_total, dbo_tblSale.note, dbo_tblSaleItem.note
HAVING (((dbo_tblSale.Date)>#1/31/2016#));[/FONT]