SELECT * FROM ( SELECT ROW_NUMBER() OVER (order by BIZ_MODL_ID desc) AS PAGE_ROWS, * FROM
(
SELECT DISTINCT BIZ_INFO.BIZ_NM, BIZ_INFO.BIZ_TYPE, BIZ_INFO.BIZ_MODL_ID, BIZ_INFO.ADDRESS, BIZ_INFO.MIN_GUEST, BIZ_INFO.AVRG_SPENT,PLZ,
BIZ_INFO.PRICE_CATG, CASE
WHEN BIZ_DESC_INFO.DESC_TXT IS NULL THEN BIZ_INFO.BIZ_DESC
ELSE BIZ_DESC_INFO.DESC_TXT
END AS BIZ_DESC , BIZ_INFO.IMG_URL, CASE BIZ_INFO.TRIL_RUN
WHEN 'False' THEN 'No'
ELSE 'Yes' END AS TRIAL_RUN, BIZ_INFO.BIZ_ID,BIZ_INFO.GMAP_LATT,BIZ_INFO.GMAP_LANG,BIZ_INFO.RGSVN_CONF, BIZ_INFO.MOBL_CONF, BIZ_INFO.FAX_CONF, BIZ_INFO.EML_CONF,
BIZ_OFFR_INFO.FROM_DT, BIZ_OFFR_INFO.TO_DT, CASE WHEN BIZ_OFFR_DESC.OFFR_DESC IS NULL THEN BIZ_OFFR_INFO.OFFR_DESC ELSE BIZ_OFFR_DESC.OFFR_DESC END AS OFFR_DESC, BIZ_INFO.SEAT_SHARE, BIZ_INFO.NO_OF_SEAT,
BIZ_SLOT_INFO.NO_OF_SEATS,CTRY_MSTR.CURR_CD

FROM BIZ_INFO INNER JOIN
BIZ_SLOT_INFO ON BIZ_INFO.BIZ_ID = BIZ_SLOT_INFO.BIZ_ID
INNER JOIN
CTRY_MSTR ON BIZ_INFO.CTRY_ID = CTRY_MSTR.CTRY_ID LEFT OUTER JOIN
RSVN_INFO ON BIZ_INFO.BIZ_ID = RSVN_INFO.BIZ_ID AND
BIZ_SLOT_INFO.SHFT = RSVN_INFO.SHFT_TYPE AND
BIZ_SLOT_INFO.SHFT_SLOT_NO = RSVN_INFO.SLOT_NO AND RSVN_INFO.SERVE_DT = @RDATE AND RSVN_INFO.STATUS IN ('RSOCP','RSTOCP','RSACT','RSTRL')
LEFT OUTER JOIN
BIZ_OFFR_INFO ON BIZ_INFO.BIZ_ID = BIZ_OFFR_INFO.BIZ_ID AND @RDATE BETWEEN BIZ_OFFR_INFO.FROM_DT AND
BIZ_OFFR_INFO.TO_DT LEFT OUTER JOIN RSVN_CUTOFF ON RSVN_CUTOFF.BIZ_ID = BIZ_INFO.BIZ_ID LEFT OUTER JOIN BIZ_OFFR_DESC ON BIZ_OFFR_INFO.OFFR_ID = BIZ_OFFR_DESC.OFFR_ID AND BIZ_OFFR_DESC.LCID = @LCID LEFT OUTER JOIN
BIZ_DESC_INFO ON BIZ_INFO.BIZ_ID = BIZ_DESC_INFO.BIZ_ID AND BIZ_DESC_INFO.LCID = @LCID

WHERE (BIZ_INFO.BIZ_MODL_ID = @BIZ_MODL_ID) AND (BIZ_INFO.CTRY_ID = @CTRY_ID) AND (BIZ_INFO.STATE_ID = @STATE_ID) AND (BIZ_INFO.CITY_ID = @CITY_ID) AND (BIZ_SLOT_INFO.SHFT = @SHFT_NM) AND (BIZ_INFO.RGN_ID = @RGN_ID) AND

( case ({ fn DAYNAME(@RDATE) } )
when 'Montag' then 'Monday'
when 'Dienstag' then 'Tuesday'
when 'Mittwoch' then 'Wednesday'
when 'Donnerstag' then 'Thursday'
when 'Freitag' then 'Friday'
when 'Samstag' then 'Saturday'
WHEN 'Sonntag' then 'Sunday'
END
NOT IN
(SELECT DAY
FROM BIZ_CLOSING
WHERE (BIZ_ID = BIZ_INFO.BIZ_ID) AND (SHIFT IN (SELECT Vals FROM @ShiftNums)))) AND
( case ({ fn DAYNAME(@RDATE) } )
when 'Montag' then 'Monday'
when 'Dienstag' then 'Tuesday'
when 'Mittwoch' then 'Wednesday'
when 'Donnerstag' then 'Thursday'
when 'Freitag' then 'Friday'
when 'Samstag' then 'Saturday'
WHEN 'Sonntag' then 'Sunday'
END
NOT IN
(SELECT DAY
FROM BIZ_NO_OFFR
WHERE (BIZ_ID = BIZ_INFO.BIZ_ID) AND (SHIFT IN (SELECT Vals FROM @ShiftNums)))) AND (BIZ_INFO.SEAT_SHARE = 2) AND BIZ_INFO.RGSN_STAT_CD NOT IN('PCE','OCO','DEL','POC','INC','NBD','OTHM')
AND
BIZ_INFO.biz_id NOT IN
(SELECT distinct BIZ_ID FROM RSVN_CUTOFF WHERE @RDATE BETWEEN DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) AND DATEADD(s, 0, DATEADD(dd, DATEDIFF(d, 1, getdate()) + CUTOFF_DAYS, 0)) )--MODIFIED FOR RESERVATION CUT OFF

GROUP BY BIZ_INFO.BIZ_NM, BIZ_INFO.BIZ_TYPE, BIZ_INFO.BIZ_MODL_ID, BIZ_INFO.ADDRESS, BIZ_INFO.MIN_GUEST, BIZ_INFO.AVRG_SPENT,PLZ,
BIZ_INFO.PRICE_CATG, BIZ_INFO.BIZ_DESC, BIZ_INFO.IMG_URL, BIZ_INFO.TRIL_RUN, BIZ_INFO.BIZ_ID, BIZ_SLOT_INFO.SHFT_SLOT_NO,
BIZ_OFFR_INFO.FROM_DT, BIZ_OFFR_INFO.TO_DT, BIZ_OFFR_INFO.OFFR_DESC, BIZ_SLOT_INFO.NO_OF_SEATS,
BIZ_INFO.SEAT_SHARE, BIZ_INFO.NO_OF_SEAT, BIZ_SLOT_INFO.NO_OF_SEATS,BIZ_INFO.GMAP_LATT,BIZ_INFO.GMAP_LANG,BIZ_INFO.RGSVN_CONF, BIZ_INFO.MOBL_CONF, BIZ_INFO.FAX_CONF, BIZ_INFO.EML_CONF,BIZ_OFFR_DESC.OFFR_DESC,BIZ_DESC_INFO.DESC_TXT,CTRY_MSTR.CURR_CD
HAVING (BIZ_SLOT_INFO.NO_OF_SEATS - SUM(ISNULL(RSVN_INFO.NO_OF_PPL, 0)) >= @NO_PEOPLE)

UNION

SELECT DISTINCT BIZ_INFO.BIZ_NM, BIZ_INFO.BIZ_TYPE, BIZ_INFO.BIZ_MODL_ID, BIZ_INFO.ADDRESS, BIZ_INFO.MIN_GUEST,
BIZ_INFO.AVRG_SPENT,PLZ, BIZ_INFO.PRICE_CATG, CASE
WHEN BIZ_DESC_INFO.DESC_TXT IS NULL THEN BIZ_INFO.BIZ_DESC
ELSE BIZ_DESC_INFO.DESC_TXT
END AS BIZ_DESC, BIZ_INFO.IMG_URL, CASE BIZ_INFO.TRIL_RUN
WHEN 'False' THEN 'No'
ELSE 'Yes' END AS TRIAL_RUN, BIZ_INFO.BIZ_ID,BIZ_INFO.GMAP_LATT,BIZ_INFO.GMAP_LANG,BIZ_INFO.RGSVN_CONF, BIZ_INFO.MOBL_CONF, BIZ_INFO.FAX_CONF, BIZ_INFO.EML_CONF,
BIZ_OFFR_INFO.FROM_DT, BIZ_OFFR_INFO.TO_DT, CASE WHEN BIZ_OFFR_DESC.OFFR_DESC IS NULL THEN BIZ_OFFR_INFO.OFFR_DESC ELSE BIZ_OFFR_DESC.OFFR_DESC END AS OFFR_DESC, BIZ_INFO.SEAT_SHARE,
BIZ_INFO.NO_OF_SEAT, 0 AS NO_OF_SEATS,CTRY_MSTR.CURR_CD

FROM BIZ_INFO

INNER JOIN BIZ_SHFT_INFO ON BIZ_SHFT_INFO.BIZ_ID = BIZ_INFO.BIZ_ID
AND BIZ_SHFT_INFO.SHFT = @SHFT_NM
LEFT OUTER JOIN RSVN_INFO AS RSVN_INFO ON BIZ_INFO.BIZ_ID = RSVN_INFO.BIZ_ID AND (RSVN_INFO.SHFT_TYPE = @SHFT_NM) AND
RSVN_INFO.SERVE_DT = @RDATE AND RSVN_INFO.STATUS IN ('RSOCP','RSTOCP','RSACT','RSTRL') INNER JOIN
CTRY_MSTR ON BIZ_INFO.CTRY_ID = CTRY_MSTR.CTRY_ID LEFT OUTER JOIN
BIZ_OFFR_INFO AS BIZ_OFFR_INFO ON BIZ_INFO.BIZ_ID = BIZ_OFFR_INFO.BIZ_ID AND @RDATE BETWEEN
BIZ_OFFR_INFO.FROM_DT AND BIZ_OFFR_INFO.TO_DT LEFT OUTER JOIN RSVN_CUTOFF ON RSVN_CUTOFF.BIZ_ID = BIZ_INFO.BIZ_ID LEFT OUTER JOIN BIZ_OFFR_DESC ON BIZ_OFFR_INFO.OFFR_ID = BIZ_OFFR_DESC.OFFR_ID AND BIZ_OFFR_DESC.LCID = @LCID LEFT OUTER JOIN
BIZ_DESC_INFO ON BIZ_INFO.BIZ_ID = BIZ_DESC_INFO.BIZ_ID AND BIZ_DESC_INFO.LCID = @LCID

WHERE (BIZ_INFO.BIZ_MODL_ID = @BIZ_MODL_ID) AND (BIZ_INFO.CTRY_ID = @CTRY_ID) AND (BIZ_INFO.STATE_ID = @STATE_ID) AND (BIZ_INFO.CITY_ID = @CITY_ID) AND (BIZ_INFO.RGN_ID = @RGN_ID) AND /*(BIZ_SLOT_INFO.SHFT = ''Morning'') AND */
( case ({ fn DAYNAME(@RDATE) } )
when 'Montag' then 'Monday'
when 'Dienstag' then 'Tuesday'
when 'Mittwoch' then 'Wednesday'
when 'Donnerstag' then 'Thursday'
when 'Freitag' then 'Friday'
when 'Samstag' then 'Saturday'
WHEN 'Sonntag' then 'Sunday'
END
NOT IN
(SELECT DAY
FROM BIZ_CLOSING AS BIZ_CLOSING
WHERE (BIZ_ID = BIZ_INFO.BIZ_ID) AND (SHIFT IN (SELECT Vals FROM @ShiftNums)))) AND
( case ({ fn DAYNAME(@RDATE) } )
when 'Montag' then 'Monday'
when 'Dienstag' then 'Tuesday'
when 'Mittwoch' then 'Wednesday'
when 'Donnerstag' then 'Thursday'
when 'Freitag' then 'Friday'
when 'Samstag'then 'Saturday'
WHEN 'Sonntag' then 'Sunday'
END
NOT IN
(SELECT DAY
FROM BIZ_NO_OFFR AS BIZ_NO_OFFR
WHERE (BIZ_ID = BIZ_INFO.BIZ_ID) AND (SHIFT IN (SELECT Vals FROM @ShiftNums)))) AND (BIZ_INFO.SEAT_SHARE = 1) AND BIZ_INFO.RGSN_STAT_CD NOT IN('PCE','OCO','DEL','POC','INC','NBD','OTHM')
AND
BIZ_INFO.biz_id NOT IN
(SELECT distinct BIZ_ID FROM RSVN_CUTOFF WHERE @RDATE BETWEEN DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) AND DATEADD(s, 0, DATEADD(dd, DATEDIFF(d, 1, getdate()) + CUTOFF_DAYS, 0)) )
GROUP BY BIZ_INFO.BIZ_NM, BIZ_INFO.BIZ_TYPE, BIZ_INFO.BIZ_MODL_ID, BIZ_INFO.ADDRESS, BIZ_INFO.MIN_GUEST,
BIZ_INFO.AVRG_SPENT,PLZ, BIZ_INFO.PRICE_CATG, BIZ_INFO.BIZ_DESC, BIZ_INFO.IMG_URL, BIZ_INFO.TRIL_RUN, BIZ_INFO.BIZ_ID,
BIZ_OFFR_INFO.FROM_DT, BIZ_OFFR_INFO.TO_DT, BIZ_OFFR_INFO.OFFR_DESC,
BIZ_INFO.SEAT_SHARE, BIZ_INFO.NO_OF_SEAT, BIZ_INFO.GMAP_LATT, BIZ_INFO.GMAP_LANG, BIZ_INFO.RGSVN_CONF, BIZ_INFO.MOBL_CONF, BIZ_INFO.FAX_CONF, BIZ_INFO.EML_CONF,BIZ_OFFR_DESC.OFFR_DESC,BIZ_DESC_INFO.DESC_TXT,CTRY_MSTR.CURR_CD
HAVING (BIZ_INFO.NO_OF_SEAT - SUM(ISNULL(RSVN_INFO.NO_OF_PPL, 0)) >= @NO_PEOPLE))AS Res_table)TBL
WHERE PAGE_ROWS BETWEEN (@PageIndex -1)* @PageSize + 1 AND @PageIndex* @PageSize and
PLZ= nullif(@plz ,'blank')
and
case @Offercheck
when @Offercheck = 1 then PRICE_CATG = 'Exclusive'
when @Offercheck = 2 then PRICE_CATG = 'Exclusive' or PRICE_CATG = 'Medium'
when @Offercheck = 3 then PRICE_CATG = 'Exclusive' or PRICE_CATG = 'Medium' or PRICE_CATG = 'Budget'
when @Offercheck = 4 then PRICE_CATG = 'Medium'
when @Offercheck = 5 then PRICE_CATG = 'Medium' or PRICE_CATG = 'Budget'
when @Offercheck = 6 then PRICE_CATG = 'Budget'
when @Offercheck = 7 then PRICE_CATG = 'Exclusive' or PRICE_CATG = 'Budget'
END

order by BIZ_MODL_ID desc