1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52
|
SELECT ORGANIZATIONID,
DAYPART,
SUM(PRDNETSALES) PRDNETSALES,
100/(SUBDLYGST.NETSLS/DECODE(SUM(PRDNETSALES),0,1,SUM(PRDNETSALES))) PRDNETRATE,
SUM(PRDGROSSSALES) PRDGROSSSALES,
100/(SUBDLYGST.GROSSSLS/DECODE(SUM(PRDGROSSSALES),0,1,SUM(PRDGROSSSALES))) PRDGROSSRATE,
SUM(PRDGUESTS) PRDGUESTS,
100/(SUBDLYGST.NUMGUESTS/DECODE(SUM(PRDGUESTS),0,1,SUM(PRDGUESTS))) PRDGUESTSRATE,
SUM(PRDNETSALES)/SUM(PRDGUESTS) PRDNETTM,
SUM(PRDGROSSSALES)/SUM(PRDGUESTS) PRDGROSSTM,
TRUNC(AVG(PRDDININGTIME),0) PRDAVGDININGTIME
FROM (SELECT ORGANIZATIONID,
CASE
WHEN FIXEDPERIOD BETWEEN 25 AND 62 THEN
CASE
WHEN (TO_CHAR (BUSINESSDATE,'DY') IN ('MON','TUE','WED','THU','FRY')) OR
(TO_CHAR (BUSINESSDATE,'DY') IN ('LUN.','MAR.','MER.','JEU.','VEN.')) THEN 'Midi Semaine' ELSE 'Midi Week End'
END
WHEN FIXEDPERIOD BETWEEN 63 AND 72 THEN
CASE
WHEN (TO_CHAR (BUSINESSDATE,'DY') IN ('MON','TUE','WED','THU','FRY')) OR
(TO_CHAR (BUSINESSDATE,'DY') IN ('LUN.','MAR.','MER.','JEU.','VEN.')) THEN 'Après Midi Semaine' ELSE 'Après Midi Week End'
END
WHEN (FIXEDPERIOD BETWEEN 73 AND 96) OR
(FIXEDPERIOD BETWEEN 1 AND 24) THEN
CASE
WHEN (TO_CHAR (BUSINESSDATE,'DY') IN ('MON','TUE','WED','THU','FRY')) OR
(TO_CHAR (BUSINESSDATE,'DY') IN ('LUN.','MAR.','MER.','JEU.','VEN.')) THEN 'Soir Semaine' ELSE 'Soir Week End'
END
END DAYPART,
NETSALESTOTAL PRDNETSALES,
NETSALESTOTAL - DISCOUNTTOTAL PRDGROSSSALES,
NUMGUESTS PRDGUESTS,
DININGTIME/60 PRDDININGTIME
FROM OPERATIONS_FIXED_PERIOD_TOTAL OFPT
WHERE ORGANIZATIONID = 10001
AND BUSINESSDATE = '12-nov-09'
AND LOCATIONID = 2043 ) SUBMAIN,
(SELECT SUM(NUMGUESTS) NUMGUESTS,
SUM(DISCOUNTTOTAL) DSCTTL,
NVL(SUM(NETSALESTOTAL),0) NETSLS,
NVL(SUM(NETSALESTOTAL) - SUM(DISCOUNTTOTAL),0) GROSSSLS
FROM OPERATIONS_FIXED_PERIOD_TOTAL
WHERE ORGANIZATIONID = 10001
AND LOCATIONID = 2043
AND BUSINESSDATE = '12-nov-09') SUBDLYGST
GROUP BY ORGANIZATIONID,
DAYPART,
SUBDLYGST.NETSLS,
SUBDLYGST.GROSSSLS,
SUBDLYGST.NUMGUESTS |
Partager