Detail
SELECT lt_jcr006_table_plan.*,
CASE WHEN ord_nbr = 0 THEN 'AVAILABLE' ELSE 'BOOKED'
END AVAILABLE,
CONVERT(VARCHAR(10), ev700_start_date_iso, 103) FUNCTION_DATE,
Datename(dw, ev700_start_date_iso) FUNCTION_DAY
FROM lt_jcr006_table_plan
JOIN ev700_func_master
ON ev700_org_code = org_code
AND ev700_evt_id = evt_id
AND ev700_func_id = func_id
WHERE org_code = @ORGANIZATION
AND evt_id = @EVENT
AND Isnull(assign_code, ' ') != ' '
ORDER BY ev700_start_date_iso,
CASE
WHEN ord_nbr = 0 THEN 1
ELSE 2
END ASC,
assign_code ASC
View
CREATE VIEW
LT_JCR006_TABLE_PLAN
AS
SELECT DISTINCT
CASE WHEN ER100_ORG_CODE IS NULL THEN CC385_ORG_CODE ELSE ER100_ORG_CODE END ORG_CODE
,CASE WHEN ER100_ORG_CODE IS NULL THEN CC385_EVT_ID ELSE ER100_EVT_ID END EVT_ID
,CASE WHEN ER100_ORG_CODE IS NULL THEN CC385_FUNC_ID ELSE ER100_FUNC_ID END FUNC_ID
,CASE WHEN ER100_ORG_CODE IS NULL THEN CC385_ASSIGN_CODE ELSE ER100_BOOTH_NBR END ASSIGN_CODE
,CASE WHEN ER100_ORG_CODE IS NULL THEN CC385_ORD_NBR ELSE ER100_ORD_NBR END ORD_NBR
,SUM(CC385_SIZE) CC385_SIZE
FROM
CC385_CHART_INVEN
FULL OUTER JOIN
ER100_ACCT_ORDER
ON
ER100_ORG_CODE = CC385_ORG_CODE AND
ER100_EVT_ID = CC385_EVT_ID AND
ER100_FUNC_ID = CC385_FUNC_ID AND
(ER100_BOOTH_NBR = CC385_ASSIGN_CODE OR
ER100_BOOTH_NBR LIKE '%' + CC385_ASSIGN_CODE + ',%' OR
ER100_BOOTH_NBR LIKE '%,' + CC385_ASSIGN_CODE + '%')
--ER100_ORD_NBR = CC385_ORD_NBR
AND ER100_ORD_ACCT = CC385_ACCT_CODE
WHERE ER100_BOOTH_NBR IS NOT NULL
OR ISNULL(CC385_ORD_NBR, 0) = 0
GROUP BY
CASE WHEN ER100_ORG_CODE IS NULL THEN CC385_ORG_CODE ELSE ER100_ORG_CODE END
,CASE WHEN ER100_ORG_CODE IS NULL THEN CC385_EVT_ID ELSE ER100_EVT_ID END
,CASE WHEN ER100_ORG_CODE IS NULL THEN CC385_FUNC_ID ELSE ER100_FUNC_ID END
,CASE WHEN ER100_ORG_CODE IS NULL THEN CC385_ASSIGN_CODE ELSE ER100_BOOTH_NBR END
,CASE WHEN ER100_ORG_CODE IS NULL THEN CC385_ORD_NBR ELSE ER100_ORD_NBR END
HTML
Header
<div style="font-family: Calibri; font-size: 9.997pt;">
<p style="margin: 0px 0px 10px; text-align: left;">
</p>
<table border="1" cellpadding="1" cellspacing="1" style="width: 500px;">
<tbody>
<tr>
<td style="width: 100px;"> Day</td>
<td style="width: 100px;"> Date</td>
<td style="width: 100px;"> Availability</td>
<td style="width: 100px;"> Order #</td>
<td style="width: 100px;"> Table</td>
</tr>
</tbody>
</table>
<div>
</div>
</div>
Detail
<div style="font-family: Tahoma; font-size: 9.997pt;">
<p style="margin: 0px 0px 10px; text-align: left;">
</p>
<table border="1" cellpadding="1" cellspacing="1" style="width: 500px;">
<tbody>
<tr>
<td style="width: 100px;">*FUNCTION_DAY</td>
<td style="width: 100px;">*FUNCTION_DATE</td>
<td style="width: 100px;">*AVAILABLE</td>
<td style="width: 100px;">*ORD_NBR</td>
<td style="width: 100px;">*ASSIGN_CODE</td>
</tr>
</tbody>
</table>
<div>
</div>
</div>
*Content contributed by Tickett Enterprises Limited
Comments
0 comments
Please sign in to leave a comment.