WITH ZS AS 

(

SELECT A.HOTELID, COUNT( DISTINCT TABLENO) 桌数 FROM ODS_XMS_POS_TBLSTA B

LEFT JOIN "ODS_XMS_POS_PCCODE" A ON A.PCCODE = B.PCCODE  AND A.HOTELID = B.HOTELID

WHERE

B.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((CASE

WHEN A.午餐接待人数 !=0 THEN

A.午餐收入/A.午餐接待人数

ELSE 0 END),2) AS 午餐人均,

ROUND((CASE

WHEN A.晚餐接待人数 !=0 THEN

A.晚餐收入/A.晚餐接待人数

ELSE 0 END),2) AS 晚餐人均,

ROUND(((CASE

WHEN A.午餐接待人数 !=0 THEN

A.午餐收入/A.午餐接待人数

ELSE 0 END)+(CASE

WHEN A.晚餐接待人数 !=0 THEN

A.晚餐收入/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') time

FROM ODS_XMS_HOTELID B

LEFT 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 null

AND A.PCCODE LIKE '71%'

AND A.PCCODE != '710'

group by B.DESCRIPT,A.HOTELID,DESC0,A."date"

ORDER BY A.HOTELID

)A

LEFT JOIN ZS ON ZS.HOTELID = A.ID