WITH ZS AS (SELECT A.HOTELID, COUNT( DISTINCT TABLENO) 桌数 FROM ODS_XMS_POS_TBLSTA BLEFT JOIN "ODS_XMS_POS_PCCODE" A ON A.PCCODE = B.PCCODE AND A.HOTELID = B.HOTELIDWHEREB.TYPE != '9'AND A.CHGCOD LIKE '71%'GROUP BY A.HOTELID)SELECT A.酒店名称,A.午餐接待桌数,A.晚餐接待桌数,ROUND((A.午餐接待桌数/ZS.桌数),2) 午餐上桌率,ROUND((A.晚餐接待桌数/ZS.桌数),2) 晚餐上桌率,A.午餐接待人数,A.晚餐接待人数,ZS.桌数,(A.午餐接待桌数+A.晚餐接待桌数) 接待总桌数,ROUND(((A.午餐接待桌数/ZS.桌数)+(A.晚餐接待桌数/ZS.桌数)),2) 总上桌率,(A.午餐接待人数+A.晚餐接待人数) AS 接待人数,A.DESC0,time,ROUND((CASEWHEN A.午餐接待人数 !=0 THENA.午餐收入/A.午餐接待人数ELSE 0 END),2) AS 午餐人均,ROUND((CASEWHEN A.晚餐接待人数 !=0 THENA.晚餐收入/A.晚餐接待人数ELSE 0 END),2) AS 晚餐人均,ROUND(((CASEWHEN A.午餐接待人数 !=0 THENA.午餐收入/A.午餐接待人数ELSE 0 END)+(CASEWHEN A.晚餐接待人数 !=0 THENA.晚餐收入/A.晚餐接待人数ELSE 0 END)),2) 总人均FROM(SELECT SUM(CASE WHEN A.SHIFT = 2 THEN 1 ELSE 0 END) AS 午餐接待桌数,SUM(CASE WHEN A.SHIFT = 3 THEN 1 ELSE 0 END) AS 晚餐接待桌数,SUM (CASE WHEN A.SHIFT = 2 THEN A.GST ELSE 0 END) 午餐接待人数,SUM (CASE WHEN A.SHIFT = 3 THEN A.GST ELSE 0 END) 晚餐接待人数,SUM(CASE WHEN A.SHIFT = 2 THEN A.V1 + A.V2 + A.V5 ELSE 0 END) AS 午餐收入, SUM(CASE WHEN A.SHIFT = 3 THEN A.V1 + A.V2 + A.V5 ELSE 0 END) AS 晚餐收入, B.DESCRIPT 酒店名称,A.HOTELID ID,DESC0,TO_CHAR(A."date", 'YYYY-MM-DD') timeFROM ODS_XMS_HOTELID BLEFT JOIN ODS_XMS_YPOS_OUTLET A ON A.HOTELID = B.HOTELID WHERE EXTRACT(YEAR FROM A."date") = EXTRACT(YEAR FROM TO_DATE('${date1}', 'YYYY-MM-DD'))AND EXTRACT(MONTH FROM A."date") = EXTRACT(MONTH FROM TO_DATE('${date1}', 'YYYY-MM-DD'))AND A.DESC2 is nullAND A.PCCODE LIKE '71%'AND A.PCCODE != '710'group by B.DESCRIPT,A.HOTELID,DESC0,A."date"ORDER BY A.HOTELID)ALEFT JOIN ZS ON ZS.HOTELID = A.ID
