Difference between revisions of "VbzCart/queries/qryRstkReq by status"
Jump to navigation
Jump to search
(New page: ==About== * '''Purpose''': One of two queries which are UNIONed to create {{vbzcart/query|qryRstkReq}} * '''Requires''': {{vbzcart/table|rstk_req}}, {{vbzcart/table|cat_supp}...) |
m (corrected name of final query) |
||
| Line 1: | Line 1: | ||
==About== | ==About== | ||
| − | * '''Purpose''': One of two queries which are [[SQL/UNION|UNION]]ed to create {{vbzcart/query| | + | * '''Purpose''': One of two queries which are [[SQL/UNION|UNION]]ed to create {{vbzcart/query|qryCbx_RstkReq}} |
* '''Requires''': {{vbzcart/table|rstk_req}}, {{vbzcart/table|cat_supp}} | * '''Requires''': {{vbzcart/table|rstk_req}}, {{vbzcart/table|cat_supp}} | ||
* '''History''': | * '''History''': | ||
Latest revision as of 18:59, 22 December 2008
About
- Purpose: One of two queries which are UNIONed to create
- REDIRECT Template:l/vc/query
- Requires:
- REDIRECT Template:l/vc/table, cat_supp
- History:
- 2008-12-22 Adapted from the discarded query qryCbxRestock_by_status
SQL
<mysql>CREATE OR REPLACE VIEW qryRstkReq_by_status AS SELECT
r.ID,
CAST(
CONCAT(
CAST(COALESCE(
CONCAT('X ',DATE_FORMAT(r.WhenKilled,'%Y-%m-%d')),
CONCAT('O ',DATE_FORMAT(r.WhenOrdered,'%Y-%m-%d')),
CONCAT('C ',DATE_FORMAT(r.WhenCreated,'%Y-%m-%d'))
) AS BINARY),
' ',
s.CatKey,
IFNULL(CONCAT(' [',r.PurchOrdNum,']'),)
) AS CHAR
) AS Descr,
NOT IsLocked AS IsOpen,
r.WhenOrdered,
r.WhenConfirmed,
r.ID_Supplier
FROM rstk_req AS r LEFT JOIN cat_supp AS s ON r.ID_Supplier=s.ID ORDER BY COALESCE(r.WhenKilled,r.WhenOrdered,r.WhenCreated) DESC;</mysql>