VbzCart/docs/queries/qryStk lines remaining: Difference between revisions

From Woozle Writes Code
< VbzCart‎ | docs‎ | queries
Jump to navigation Jump to search
imported>Woozle
m (used by qryStk_lines_remaining_forSale)
imported>Woozle
(another used-by)
Line 5: Line 5:
** items neither for shipping nor for sale (probably some kind of recordkeeping purpose)
** items neither for shipping nor for sale (probably some kind of recordkeeping purpose)
* '''Requires''': {{vbzcart/table|stk_items}}, {{vbzcart/table|stk_places}}
* '''Requires''': {{vbzcart/table|stk_items}}, {{vbzcart/table|stk_places}}
* '''Used by''': {{vbzcart/query|qryStk_lines_remaining_forSale}}
* '''Used by''': {{vbzcart/query|qryStk_lines_remaining_forSale}}, {{vbzcart/query|qryStk_lines_remaining_byBin}}
==SQL==
==SQL==
<section begin=sql /><mysql>CREATE OR REPLACE VIEW v_stk_lines_remaining AS
<section begin=sql /><mysql>CREATE OR REPLACE VIEW v_stk_lines_remaining AS

Revision as of 22:56, 29 November 2009

Details

SQL

<section begin=sql /><mysql>CREATE OR REPLACE VIEW v_stk_lines_remaining AS

 SELECT
   st.ID,
   st.ID_Bin,
   st.ID_Item,
   IF(sb.isForSale,st.Qty,0) AS QtyForSale,
   IF(sb.isForShip,st.Qty,0) AS QtyForShip,
   st.Qty AS QtyExisting,
   st.CatNum,
   st.WhenAdded,
   st.WhenChanged,
   st.WhenCounted,
   st.Notes,
   sb.ID_Place,
   sp.Name AS WhName
   FROM
     (
       stk_items AS st
       LEFT JOIN stk_bins AS sb
         ON sb.ID=st.ID_Bin
      )
      LEFT JOIN stk_places AS sp
        ON sb.ID_Place=sp.ID
   WHERE (st.WhenRemoved IS NULL) AND (sb.WhenVoided IS NULL) AND (st.Qty <> 0);</mysql>

<section end=sql />