I work for an independent company that integrates with POS software to collect sales information. As of recently, I moved to a territory where Micros modifies prices(for Rocks and Double upcharges, for example) by means of price levels vs. individual line items. I currently have the following SQL query that was sent to me by the CSM for the local Micros provider:
As I know close to nothing about SQL other than what I am quickly trying to teach myself, I understand the above code for the most part. Unfortunately, I do not know how to manipulate it so that the name of each price level is added to the value of its menuitem_name to create a unique name which then has the sales_qty and sales_total calculated.
Essentially, I am trying to export sales for each price level for each menuitem vs summing the sales for each price level under each menuitem.
My apologies for not conveying thoughts in a programmatic language. I have reached out to the local provider but have received nothing other than the above code in the past 5 weeks.
Code:
PARAMETERS period_begin , period_end;
SELECT
MIN(DATE(business_date)),
MAX(DATE(business_date)),
mi_seq,
menuitem_number,
menuitem_name1,
menuitem_name2,
group_number,
group_name,
CAST((SUM( P1_Sales_total + P2_Sales_total + P3_Sales_total + P4_Sales_total + P5_Sales_total + P6_Sales_total + P7_Sales_total + P8_Sales_total + P9_Sales_total + P10_Sales_total) / SUM( P1_Sales_qty + P2_Sales_qty + P3_Sales_qty + P4_Sales_qty + P5_Sales_qty + P6_Sales_qty + P7_Sales_qty + P8_Sales_qty + P9_Sales_qty + P10_Sales_qty)) AS NUMERIC(8,2)) AS PriceTotal,
SUM( P1_Sales_qty + P2_Sales_qty + P3_Sales_qty + P4_Sales_qty + P5_Sales_qty + P6_Sales_qty + P7_Sales_qty + P8_Sales_qty + P9_Sales_qty + P10_Sales_qty) AS QtyTotal,
SUM( P1_Sales_total + P2_Sales_total + P3_Sales_total + P4_Sales_total + P5_Sales_total + P6_Sales_total + P7_Sales_total + P8_Sales_total + P9_Sales_total + P10_Sales_total) AS SalesTotal
FROM micros.v_R_rvc_menuitem_fam_grp
WHERE
DATE(business_date) >= DATE('{period_begin}')
AND DATE(business_date) <= DATE('{period_end}')
AND
(P1_Sales_qty + P2_Sales_qty + P3_Sales_qty + P4_Sales_qty + P5_Sales_qty + P6_Sales_qty + P7_Sales_qty + P8_Sales_qty + P9_Sales_qty + P10_Sales_qty) > 0
GROUP BY
mi_seq,
menuitem_number,
menuitem_name1,
menuitem_name2,
group_number,
group_name
ORDER BY menuitem_number ;
OUTPUT TO 'MenuSalesExport.txt' FORMAT ASCII;As I know close to nothing about SQL other than what I am quickly trying to teach myself, I understand the above code for the most part. Unfortunately, I do not know how to manipulate it so that the name of each price level is added to the value of its menuitem_name to create a unique name which then has the sales_qty and sales_total calculated.
Essentially, I am trying to export sales for each price level for each menuitem vs summing the sales for each price level under each menuitem.
My apologies for not conveying thoughts in a programmatic language. I have reached out to the local provider but have received nothing other than the above code in the past 5 weeks.
