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
|
SELECT ORGANIZATIONID,
DAYPART,
SUM(PRDNETSALES) PRDNETSALES,
100/decode((SUBDLYGST.NETSLS/DECODE(SUM(PRDNETSALES),0,1,SUM(PRDNETSALES))),0,1,(SUBDLYGST.NETSLS/DECODE(SUM(PRDNETSALES),0,1,SUM(PRDNETSALES)))) PRDNETRATE,
SUM(PRDGROSSSALES) PRDGROSSSALES,
100/decode((SUBDLYGST.GROSSSLS/DECODE(SUM(PRDGROSSSALES),0,1,SUM(PRDGROSSSALES))),0,1,(SUBDLYGST.GROSSSLS/DECODE(SUM(PRDGROSSSALES),0,1,SUM(PRDGROSSSALES)))) PRDGROSSRATE,
SUM(PRDGUESTS) PRDGUESTS,
100/decode((SUBDLYGST.NUMGUESTS/DECODE(SUM(PRDGUESTS),0,1,SUM(PRDGUESTS))),0,1,(SUBDLYGST.NUMGUESTS/DECODE(SUM(PRDGUESTS),0,1,SUM(PRDGUESTS)))) PRDGUESTSRATE,
SUM(PRDNETSALES)/decode(SUM(PRDGUESTS),0,1,SUM(PRDGUESTS)) PRDNETTM,
SUM(PRDGROSSSALES)/decode(SUM(PRDGUESTS),0,1,SUM(PRDGUESTS)) PRDGROSSTM,
TRUNC(AVG(PRDDININGTIME),0) PRDAVGDININGTIME
FROM (SELECT ORGANIZATIONID,
CASE
WHEN FIXEDPERIOD BETWEEN 25 AND 62 THEN
DECODE(TO_CHAR (BUSINESSDATE,'DY'),'MON', 'Midi Semaine','TUE', 'Midi Semaine','WED', 'Midi Semaine','THU', 'Midi Semaine','FRY', 'Midi Semaine','Midi Week End')
WHEN FIXEDPERIOD BETWEEN 63 AND 72 THEN
DECODE(TO_CHAR (BUSINESSDATE,'DY'),'MON', 'Midi Semaine','TUE', 'Midi Semaine','WED', 'Midi Semaine','THU', 'Midi Semaine','FRY', 'Midi Semaine','Midi Week End')
WHEN (FIXEDPERIOD BETWEEN 73 AND 96) OR
(FIXEDPERIOD BETWEEN 1 AND 24) THEN
DECODE(TO_CHAR (BUSINESSDATE,'DY'),'MON', 'Midi Semaine','TUE', 'Midi Semaine','WED', 'Midi Semaine','THU', 'Midi Semaine','FRY', 'Midi Semaine','Midi Week End')
END DAYPART,
NETSALESTOTAL PRDNETSALES,
NETSALESTOTAL - DISCOUNTTOTAL PRDGROSSSALES,
NUMGUESTS PRDGUESTS,
DININGTIME/60 PRDDININGTIME
FROM OPERATIONS_FIXED_PERIOD_TOTAL OFPT
WHERE ORGANIZATIONID = 10001
AND BUSINESSDATE = '10-mar-10'
AND LOCATIONID = 2043 ) SUBMAIN,
(SELECT NVL(NUMGUESTS,0) NUMGUESTS,
NVL(ITEMDISCOUNTTOTAL,0) DSCTTL,
NVL(NETSALESTOTAL,0) NETSLS,
NVL(NETSALESTOTAL,0) - NVL(ITEMDISCOUNTTOTAL,0) GROSSSLS
FROM OPERATIONS_DAILY_TOTAL
WHERE ORGANIZATIONID = 10001
AND LOCATIONID = 2043
AND BUSINESSDATE = '10-mar-10') SUBDLYGST
GROUP BY ORGANIZATIONID,
DAYPART,
SUBDLYGST.NETSLS,
SUBDLYGST.GROSSSLS,
SUBDLYGST.NUMGUESTS |