Meal Plan Queries

bbts-meal-plans.sql

SELECT LPAD(CAST(ENVISION.CUSTOMER.custnum AS INTEGER),7,'0'),
  ENVISION.BOARDPLAN.boardplan
FROM ENVISION.BOARDPLAN
LEFT OUTER JOIN ENVISION.CUSTOMERBOARD ON ENVISION.CUSTOMERBOARD.boardplan_id=ENVISION.BOARDPLAN.boardplan_id
LEFT OUTER JOIN ENVISION.CUSTOMER ON ENVISION.CUSTOMER.CUST_ID=ENVISION.CUSTOMERBOARD.CUST_ID
WHERE CAST(ENVISION.CUSTOMER.custnum AS INTEGER) > 0
  AND ENVISION.BOARDPLAN.boardplan IS NOT NULL
ORDER BY CAST(ENVISION.CUSTOMER.custnum AS INTEGER)

bbts-stored-value.sql

SELECT LPAD(CAST(ENVISION.CUSTOMER.custnum AS INTEGER),7,'0'),
  ENVISION.SV_ACCOUNT_HISTORY.datetime,
  TO_CHAR((ENVISION.SV_ACCOUNT_HISTORY.amount/1000),'FM9999999.90'),
  ENVISION.SV_ACCOUNT_HISTORY.debit_credit_type,
  CASE
    WHEN ENVISION.SV_ACCOUNT_HISTORY.debit_credit_type=0 THEN
      TO_CHAR((ENVISION.SV_ACCOUNT_HISTORY.amount/-1000),'FM9999999.90')
    ELSE TO_CHAR((ENVISION.SV_ACCOUNT_HISTORY.amount/1000),'FM9999999.90')
  END CASE
FROM ENVISION.SV_ACCOUNT_HISTORY
LEFT OUTER JOIN ENVISION.CUSTOMER_SV_ACCOUNT ON ENVISION.CUSTOMER_SV_ACCOUNT.sv_account_id=ENVISION.SV_ACCOUNT_HISTORY.sv_account_id
LEFT OUTER JOIN ENVISION.CUSTOMER ON ENVISION.CUSTOMER.CUST_ID=ENVISION.CUSTOMER_SV_ACCOUNT.CUST_ID
WHERE CAST(ENVISION.CUSTOMER.custnum AS INTEGER) > 0
  AND ENVISION.CUSTOMER_SV_ACCOUNT.sv_account_type_id=2
  AND ENVISION.SV_ACCOUNT_HISTORY.datetime>=42373.2083333333
  AND (ENVISION.SV_ACCOUNT_HISTORY.amount/1000) IN (25,200,150,250,325)
  AND ENVISION.SV_ACCOUNT_HISTORY.tran_merchant_name='High Point U'
ORDER BY CAST(ENVISION.CUSTOMER.custnum AS INTEGER), ENVISION.SV_ACCOUNT_HISTORY.datetime

service-enrollment.sql

SELECT meal_plan_assignments.mpas_person_id as id,
  meal_plans.mp_colleague_id,
  meal_plans.mp_description,
  meal_plans.mp_cost,
  DATE_FORMAT(meal_plan_assignments.mpas_last_updated,'%m/%d/%Y %T') as mpas_last_updated,
  mp_bbt_id
FROM meal_plan_assignments
LEFT OUTER JOIN bio ON bio.id=meal_plan_assignments.mpas_person_id
LEFT OUTER JOIN meal_plans ON meal_plans.mp_id=meal_plan_assignments.mpas_meal_plan_id
WHERE mpas_meal_plan_id IS NOT NULL
ORDER BY meal_plan_assignments.mpas_person_id