SQL for pulling pay plans

blastPayPlans.sql

/*
1) What are all the payplans that exist in GoReg that are associated with all the 
   various Blast products (i.e. clubblast, clubblast_scholastic, ...)
   - break total down by product
   - How many invoices exist in total that include one of these payplans?  
     Identify payplans that do not have a corresponding invoice (i.e. never used)

*/
SELECT PAY_PLAN.*, AFFILIATE.name As aff_name, PRODUCT.*, 
       (SELECT Count(PAY_TERM.pay_plan_id) FROM PAY_TERM WHERE PAY_TERM.pay_plan_id=PAY_PLAN.pay_plan_id) 
       AS NumOfPayTerms 
FROM PAY_PLAN 
INNER JOIN PRODUCT_X_PAY_PLAN ON PRODUCT_X_PAY_PLAN.pay_plan_id=PAY_PLAN.pay_plan_id 
INNER JOIN PRODUCT ON PRODUCT_X_PAY_PLAN.product_id=PRODUCT.product_id 
INNER JOIN AFFILIATE ON AFFILIATE.affiliate_id=PRODUCT.affiliate_id 
WHERE PRODUCT_X_PAY_PLAN.product_id=1 
      /****** all club blast products */
      OR PRODUCT_X_PAY_PLAN.product_id=1   --clubblast product
      OR PRODUCT_X_PAY_PLAN.product_id=893 --DisneyBlast_Synacor product
      OR PRODUCT_X_PAY_PLAN.product_id=893 -- Blast_Scholastic product
      --OR PRODUCT_X_PAY_PLAN.product_id=896 --clubblast_EXT product
      --OR PRODUCT_X_PAY_PLAN.product_id=899 --clubblast_XFER product
ORDER by product_code

blastPayPlansReport.sql

/*Pay Plan Report
@Author: Chris Sanz
@Date:   8/15/2003 */
DECLARE @DateFrom varchar(20)
DECLARE @ActiveCode char(1)
Set @DateFrom = GETDATE()
Set @ActiveCode = 'A'
SELECT PAY_PLAN.pay_plan_id,
       PAY_PLAN.plan_code,
       PAY_PLAN.plan_name,
       PAY_PLAN.date_created,
       AFFILIATE.name As AffiliateName,
       PRODUCT.product_id, 
       PRODUCT.product_code, 
      (SELECT Count(PAY_TERM.pay_plan_id) 
       FROM PAY_TERM 
       WHERE PAY_TERM.pay_plan_id=PAY_PLAN.pay_plan_id) 
       AS NumOfPayTerms,
/*****************************************************************************
      GET TOTAL PRICE AND INCLUDE ALL TERMS
 *****************************************************************************/
      (SELECT Sum(PAY_TERM.amount) 
       FROM PAY_TERM 
       WHERE PAY_TERM.pay_plan_id=PAY_PLAN.pay_plan_id) 
       AS Total_Price_All_Terms,
/*****************************************************************************
      GET TOTAL OF INVOICES 
 *****************************************************************************/
      (SELECT Count(USER_PRODUCT.invoice_id) 
       FROM USER_PRODUCT 
       WHERE USER_PRODUCT.pay_plan_id=PAY_PLAN.pay_plan_id) 
       AS Total_Invoices_Including_Renew,
/*****************************************************************************
      GET TOTAL OF INVOICES / WHERE ACCOUNTS ARE ACTIVE
 *****************************************************************************/
      (SELECT Count(USER_PRODUCT.invoice_id) 
       FROM USER_PRODUCT 
       WHERE USER_PRODUCT.pay_plan_id=PAY_PLAN.pay_plan_id
       AND USER_PRODUCT.status=@ActiveCode --date ended is equal active
       AND (USER_PRODUCT.date_ended > @DateFrom --date ended is in the future
       OR USER_PRODUCT.date_ended IS NULL)) -- data ended is null
       AS Total_Invoices_Active,
/*****************************************************************************
      GET TOTAL OF INVOICES / WHERE ACCOUNTS ARE NOT ACTIVE
 *****************************************************************************/
      (SELECT Count(USER_PRODUCT.invoice_id) 
       FROM USER_PRODUCT 
       WHERE USER_PRODUCT.pay_plan_id=PAY_PLAN.pay_plan_id
       AND USER_PRODUCT.status<>@ActiveCode --data ended is not equal active
       AND (USER_PRODUCT.date_ended < @DateFrom --date ended is in the pass      
       OR USER_PRODUCT.date_ended IS NOT NULL)) -- data ended is not null
       AS Total_Invoices_InActive,
/*****************************************************************************
      GET TOTAL OF INVOICES RENEW
 *****************************************************************************/
      ((SELECT Count(USER_PRODUCT.invoice_id) 
       FROM USER_PRODUCT 
       WHERE USER_PRODUCT.pay_plan_id=PAY_PLAN.pay_plan_id)-(
             (SELECT Count(USER_PRODUCT.invoice_id) 
              FROM USER_PRODUCT 
              WHERE USER_PRODUCT.pay_plan_id=PAY_PLAN.pay_plan_id
              AND USER_PRODUCT.status=@ActiveCode --date ended is equal active
              AND (USER_PRODUCT.date_ended > @DateFrom --date ended is in the future
              OR USER_PRODUCT.date_ended IS NULL))+
             (SELECT Count(USER_PRODUCT.invoice_id) 
              FROM USER_PRODUCT 
              WHERE USER_PRODUCT.pay_plan_id=PAY_PLAN.pay_plan_id
              AND USER_PRODUCT.status<>@ActiveCode --data ended is not equal active
              AND (USER_PRODUCT.date_ended < @DateFrom --date ended is in the pass      
              OR USER_PRODUCT.date_ended IS NOT NULL)))) 
       AS Total_Invoices_Renew
FROM PAY_PLAN 
INNER JOIN PRODUCT_X_PAY_PLAN ON PRODUCT_X_PAY_PLAN.pay_plan_id=PAY_PLAN.pay_plan_id 
INNER JOIN PRODUCT ON PRODUCT_X_PAY_PLAN.product_id=PRODUCT.product_id 
INNER JOIN AFFILIATE ON AFFILIATE.affiliate_id=PRODUCT.affiliate_id 
WHERE PRODUCT_X_PAY_PLAN.product_id=1 
/*****************************************************************************
      ALL BLAST PRODUCTS
 *****************************************************************************/
      OR PRODUCT_X_PAY_PLAN.product_id=1   --clubblast product
      OR PRODUCT_X_PAY_PLAN.product_id=893 --DisneyBlast_Synacor product
      OR PRODUCT_X_PAY_PLAN.product_id=893 -- Blast_Scholastic product
ORDER by plan_code


/*select PAY_PLAN.plan_code, PAY_PLAN.plan_name, PRODUCT.product_id, PRODUCT.product_code from PAY_PLAN 
inner join PRODUCT_X_PAY_PLAN on PRODUCT_X_PAY_PLAN.pay_plan_id=PAY_PLAN.pay_plan_id 
inner JOIN PRODUCT on PRODUCT_X_PAY_PLAN.product_id=PRODUCT.product_id 
where PAY_PLAN.plan_code='FFL99Regular'*/

blastProducts.sql

/*
  Disney's Blast products
  after running a LIKE (keyword 'blast')  I found more products but those need to
  be removed from qa and production.
*/
SELECT PRODUCT.*,
       (SELECT Count(PRODUCT_X_PAY_PLAN.product_id) 
        FROM PRODUCT_X_PAY_PLAN
        WHERE PRODUCT_X_PAY_PLAN.product_id=PRODUCT.product_id) 
        AS NumOfPayPlans  
FROM PRODUCT 
INNER JOIN PRODUCT_X_TOKEN ON PRODUCT.product_id=PRODUCT_X_TOKEN.product_id
INNER JOIN TOKEN ON PRODUCT_X_TOKEN.token_id=TOKEN.token_id
WHERE TOKEN.token_id=163