Budget Plan Table

[Hops]

Shows your budget-plan for each category per Month, Day, Week And Year.

Select the budget year using the last line of this script

Code: Select all

SELECT
    CategName    AS 'Budget Category',
    SubCategName AS 'Sub-Category',
    CASE Period WHEN 'Daily'   THEN round(Amount/1    ,2)
                WHEN 'Weekly'  THEN round(Amount/7    ,2)
                WHEN 'Monthly' THEN round(Amount/30.42,2)
                WHEN 'Yearly'  THEN round(Amount/365  ,2)
    END AS 'Daily Budget',
    CASE Period WHEN 'Daily'   THEN round(Amount*7    ,2)
                WHEN 'Weekly'  THEN round(Amount*1    ,2)
                WHEN 'Monthly' THEN round(Amount/4.333,2)
                WHEN 'Yearly'  THEN round(Amount/52   ,2)
    END AS 'Weekly Budget',
    CASE Period WHEN 'Daily'   THEN round(Amount*30.42 ,2)
                WHEN 'Weekly'  THEN round(Amount*4.3333,2)
                WHEN 'Monthly' THEN round(Amount*1     ,2)
                WHEN 'Yearly'  THEN round(Amount/12    ,2)
    END AS 'Monthly Budget',
    CASE Period WHEN 'Daily'   THEN round(Amount*365   ,2)
                WHEN 'Weekly'  THEN round(Amount*52    ,2)
                WHEN 'Monthly' THEN round(Amount*12    ,2)
                WHEN 'Yearly'  THEN round(Amount*1     ,2)
    END AS 'Yearly Budget'
FROM BUDGETTABLE_V1 , BUDGETYEAR_V1 INNER JOIN  CATEGORY_V1    ON CATEGORY_V1.CATEGID=BUDGETTABLE_V1.CATEGID
                                    INNER JOIN  SUBCATEGORY_V1 ON SUBCATEGORY_V1.SUBCATEGID=BUDGETTABLE_V1.SUBCATEGID
WHERE BudgetYearName = '2009'


[Nikolay]

Hi,

Good idea. I like it.
But this script need to be improved. If some category in the budget without subcategory then record is missing.
Category "Others" (subcategory is NULL) in my case.

On the other hand, I would be willing to MMEX worked so subcategories were required. Then all SQL scripts will be easier.

[Hops]

Hi,

Good idea. I like it.
But this script need to be improved. If some category in the budget without subcategory then record is missing.
Category "Others" (subcategory is NULL) in my case.

On the other hand, I would be willing to MMEX worked so subcategories were required. Then all SQL scripts will be easier.

Could you offer some advice on how to handle that case? I'm new to SQL scripting. Thanks!

[Nikolay]

Code: Select all

       SELECT
        ca.CategName    AS 'Budget Category',
        (select SubCategName from subcategory_v1 where subcategid=b.subcategid and categid=ca.categid) AS 'Sub-Category',
        round(Amount/CASE Period WHEN 'Daily'   THEN 1
                    WHEN 'Weekly'  THEN 7
                    WHEN 'Monthly' THEN 30.42
                    WHEN 'Yearly'  THEN 365
                    WHEN 'Quarterly' THEN 365/4 END,2)
-- 'Half-Yearly' 'Bi-Monthly' 'Bi-Weekly'
                    AS 'Daily Budget',
        CASE Period WHEN 'Daily'   THEN round(Amount*7    ,2)
                    WHEN 'Weekly'  THEN round(Amount*1    ,2)
                    WHEN 'Monthly' THEN round(Amount/4.333,2)
                    WHEN 'Yearly'  THEN round(Amount/52   ,2)
        END AS 'Weekly Budget',
        CASE Period WHEN 'Daily'   THEN round(Amount*30.42 ,2)
                    WHEN 'Weekly'  THEN round(Amount*4.3333,2)
                    WHEN 'Monthly' THEN round(Amount*1     ,2)
                    WHEN 'Yearly'  THEN round(Amount/12    ,2)
        END AS 'Monthly Budget',
        CASE Period WHEN 'Daily'   THEN round(Amount*365   ,2)
                    WHEN 'Weekly'  THEN round(Amount*52    ,2)
                    WHEN 'Monthly' THEN round(Amount*12    ,2)
                    WHEN 'Yearly'  THEN round(Amount*1     ,2)
        END AS 'Yearly Budget'
    FROM BUDGETTABLE_V1 b, BUDGETYEAR_V1 y,CATEGORY_V1  ca 
where b.budgetyearid=y.budgetyearid and b.categid=ca.categid
   and y.BudgetYearName = '2009' and b.period<>'None'
order by 1,2


[Hops]

Here's an update with the changes Nikolay suggested with some other updates. There are now two separate scripts for income and expense budget plans, and the total is calculated on the last row. Please double check my math!

Here's the expense-script (displays the expenses as positive numbers)

Code: Select all

SELECT
    ca.CategName    AS 'Budget Category',
    (select SubCategName from subcategory_v1 where subcategid=b.subcategid and categid=ca.categid) AS 'Sub-Category',

    CASE Period WHEN 'Daily'       THEN round(-Amount/1    ,2)
                WHEN 'Bi-Weekly'   THEN round(-Amount/3.5  ,2)
                WHEN 'Weekly'      THEN round(-Amount/7    ,2)
                WHEN 'Bi-Monthly'  THEN round(-Amount/15.21,2)
                WHEN 'Monthly'     THEN round(-Amount/30.42,2)
                WHEN 'Quarterly'   THEN round(-Amount/365/4,2)
                WHEN 'Half-Yearly' THEN round(-Amount/365/2,2)
                WHEN 'Yearly'      THEN round(-Amount/365  ,2)
    END AS 'Daily Budget',
   
    CASE Period WHEN 'Daily'       THEN round(-Amount*7       ,2)
                WHEN 'Bi-Weekly'   THEN round(-Amount*2       ,2)
                WHEN 'Weekly'      THEN round(-Amount*1       ,2)
                WHEN 'Bi-Monthly'  THEN round(-Amount/4.333/2 ,2)
                WHEN 'Monthly'     THEN round(-Amount/4.333   ,2)
                WHEN 'Quarterly'   THEN round(-Amount/52/4    ,2)
                WHEN 'Half-Yearly' THEN round(-Amount/52/2    ,2)
                WHEN 'Yearly'      THEN round(-Amount/52      ,2)
    END AS 'Weekly Budget',
   
    CASE Period WHEN 'Daily'       THEN round(-Amount*30.42    ,2)
                WHEN 'Bi-Weekly'   THEN round(-Amount*4.3333*2 ,2)
                WHEN 'Weekly'      THEN round(-Amount*4.3333   ,2)
                WHEN 'Bi-Monthly'  THEN round(-Amount*2        ,2)
                WHEN 'Monthly'     THEN round(-Amount*1        ,2)
                WHEN 'Quarterly'   THEN round(-Amount/12/4     ,2)
                WHEN 'Half-Yearly' THEN round(-Amount/12/2     ,2)
                WHEN 'Yearly'      THEN round(-Amount/12       ,2)
    END AS 'Monthly Budget',
   
    CASE Period WHEN 'Daily'       THEN round(-Amount*365   ,2)
                WHEN 'Bi-Weekly'   THEN round(-Amount*52*2  ,2)
                WHEN 'Weekly'      THEN round(-Amount*52    ,2)
                WHEN 'Bi-Monthly'  THEN round(-Amount*12*2  ,2)
                WHEN 'Monthly'     THEN round(-Amount*12    ,2)
                WHEN 'Quarterly'   THEN round(-Amount*4     ,2)
                WHEN 'Half-Yearly' THEN round(-Amount*2     ,2)
                WHEN 'Yearly'      THEN round(-Amount*1     ,2)
    END AS 'Yearly Budget'
FROM BUDGETTABLE_V1 b,
     BUDGETYEAR_V1  y,
     CATEGORY_V1   ca
where b.budgetyearid=y.budgetyearid and
      b.categid=ca.categid          and
      y.BudgetYearName = '2009'     and
      b.period <> 'None'            and
      Amount < 0
UNION
SELECT
    'zzTOTAL' AS 'Budget Category',
    'zzTOTAL' AS 'Sub-Category',
    SUM(CASE Period WHEN 'Daily'       THEN round(-Amount/1    ,2)
                WHEN 'Bi-Weekly'   THEN round(-Amount/3.5  ,2)
                WHEN 'Weekly'      THEN round(-Amount/7    ,2)
                WHEN 'Bi-Monthly'  THEN round(-Amount/15.21,2)
                WHEN 'Monthly'     THEN round(-Amount/30.42,2)
                WHEN 'Quarterly'   THEN round(-Amount/365/4,2)
                WHEN 'Half-Yearly' THEN round(-Amount/365/2,2)
                WHEN 'Yearly'      THEN round(-Amount/365  ,2)
    END) AS 'Daily Budget',
   
    SUM(CASE Period WHEN 'Daily'       THEN round(-Amount*7       ,2)
                WHEN 'Bi-Weekly'   THEN round(-Amount*2       ,2)
                WHEN 'Weekly'      THEN round(-Amount*1       ,2)
                WHEN 'Bi-Monthly'  THEN round(-Amount/4.333/2 ,2)
                WHEN 'Monthly'     THEN round(-Amount/4.333   ,2)
                WHEN 'Quarterly'   THEN round(-Amount/52/4    ,2)
                WHEN 'Half-Yearly' THEN round(-Amount/52/2    ,2)
                WHEN 'Yearly'      THEN round(-Amount/52      ,2)
    END) AS 'Weekly Budget',
   
    SUM(CASE Period WHEN 'Daily'       THEN round(-Amount*30.42    ,2)
                WHEN 'Bi-Weekly'   THEN round(-Amount*4.3333*2 ,2)
                WHEN 'Weekly'      THEN round(-Amount*4.3333   ,2)
                WHEN 'Bi-Monthly'  THEN round(-Amount*2        ,2)
                WHEN 'Monthly'     THEN round(-Amount*1        ,2)
                WHEN 'Quarterly'   THEN round(-Amount/12/4     ,2)
                WHEN 'Half-Yearly' THEN round(-Amount/12/2     ,2)
                WHEN 'Yearly'      THEN round(-Amount/12       ,2)
    END) AS 'Monthly Budget',
   
    SUM(CASE Period WHEN 'Daily'       THEN round(-Amount*365   ,2)
                WHEN 'Bi-Weekly'   THEN round(-Amount*52*2  ,2)
                WHEN 'Weekly'      THEN round(-Amount*52    ,2)
                WHEN 'Bi-Monthly'  THEN round(-Amount*12*2  ,2)
                WHEN 'Monthly'     THEN round(-Amount*12    ,2)
                WHEN 'Quarterly'   THEN round(-Amount*4     ,2)
                WHEN 'Half-Yearly' THEN round(-Amount*2     ,2)
                WHEN 'Yearly'      THEN round(-Amount*1     ,2)
    END) AS 'Yearly Budget'
FROM BUDGETTABLE_V1 b,
     BUDGETYEAR_V1  y,
     CATEGORY_V1   ca
where b.budgetyearid=y.budgetyearid and
      b.categid=ca.categid          and
      y.BudgetYearName = '2009'     and
      b.period <> 'None'            and
      Amount < 0


Here's the income plan:

Code: Select all

SELECT
    ca.CategName    AS 'Budget Category',
    (select SubCategName from subcategory_v1 where subcategid=b.subcategid and categid=ca.categid) AS 'Sub-Category',

    CASE Period WHEN 'Daily'       THEN round(Amount/1    ,2)
                WHEN 'Bi-Weekly'   THEN round(Amount/3.5  ,2)
                WHEN 'Weekly'      THEN round(Amount/7    ,2)
                WHEN 'Bi-Monthly'  THEN round(Amount/15.21,2)
                WHEN 'Monthly'     THEN round(Amount/30.42,2)
                WHEN 'Quarterly'   THEN round(Amount/365/4,2)
                WHEN 'Half-Yearly' THEN round(Amount/365/2,2)
                WHEN 'Yearly'      THEN round(Amount/365  ,2)
    END AS 'Daily Budget',
   
    CASE Period WHEN 'Daily'       THEN round(Amount*7       ,2)
                WHEN 'Bi-Weekly'   THEN round(Amount*2       ,2)
                WHEN 'Weekly'      THEN round(Amount*1       ,2)
                WHEN 'Bi-Monthly'  THEN round(Amount/4.333/2 ,2)
                WHEN 'Monthly'     THEN round(Amount/4.333   ,2)
                WHEN 'Quarterly'   THEN round(Amount/52/4    ,2)
                WHEN 'Half-Yearly' THEN round(Amount/52/2    ,2)
                WHEN 'Yearly'      THEN round(Amount/52      ,2)
    END AS 'Weekly Budget',
   
    CASE Period WHEN 'Daily'       THEN round(Amount*30.42    ,2)
                WHEN 'Bi-Weekly'   THEN round(Amount*4.3333*2 ,2)
                WHEN 'Weekly'      THEN round(Amount*4.3333   ,2)
                WHEN 'Bi-Monthly'  THEN round(Amount*2        ,2)
                WHEN 'Monthly'     THEN round(Amount*1        ,2)
                WHEN 'Quarterly'   THEN round(Amount/12/4     ,2)
                WHEN 'Half-Yearly' THEN round(Amount/12/2     ,2)
                WHEN 'Yearly'      THEN round(Amount/12       ,2)
    END AS 'Monthly Budget',
   
    CASE Period WHEN 'Daily'       THEN round(Amount*365   ,2)
                WHEN 'Bi-Weekly'   THEN round(Amount*52*2  ,2)
                WHEN 'Weekly'      THEN round(Amount*52    ,2)
                WHEN 'Bi-Monthly'  THEN round(Amount*12*2  ,2)
                WHEN 'Monthly'     THEN round(Amount*12    ,2)
                WHEN 'Quarterly'   THEN round(Amount*4     ,2)
                WHEN 'Half-Yearly' THEN round(Amount*2     ,2)
                WHEN 'Yearly'      THEN round(Amount*1     ,2)
    END AS 'Yearly Budget'
FROM BUDGETTABLE_V1 b,
     BUDGETYEAR_V1  y,
     CATEGORY_V1   ca
where b.budgetyearid=y.budgetyearid and
      b.categid=ca.categid          and
      y.BudgetYearName = '2009'     and
      b.period <> 'None'            and
      Amount > 0
UNION
SELECT
    'zzTOTAL' AS 'Budget Category',
    'zzTOTAL' AS 'Sub-Category',
    SUM(CASE Period WHEN 'Daily'       THEN round(Amount/1    ,2)
                WHEN 'Bi-Weekly'   THEN round(Amount/3.5  ,2)
                WHEN 'Weekly'      THEN round(Amount/7    ,2)
                WHEN 'Bi-Monthly'  THEN round(Amount/15.21,2)
                WHEN 'Monthly'     THEN round(Amount/30.42,2)
                WHEN 'Quarterly'   THEN round(Amount/365/4,2)
                WHEN 'Half-Yearly' THEN round(Amount/365/2,2)
                WHEN 'Yearly'      THEN round(Amount/365  ,2)
    END) AS 'Daily Budget',
   
    SUM(CASE Period WHEN 'Daily'       THEN round(Amount*7       ,2)
                WHEN 'Bi-Weekly'   THEN round(Amount*2       ,2)
                WHEN 'Weekly'      THEN round(Amount*1       ,2)
                WHEN 'Bi-Monthly'  THEN round(Amount/4.333/2 ,2)
                WHEN 'Monthly'     THEN round(Amount/4.333   ,2)
                WHEN 'Quarterly'   THEN round(Amount/52/4    ,2)
                WHEN 'Half-Yearly' THEN round(Amount/52/2    ,2)
                WHEN 'Yearly'      THEN round(Amount/52      ,2)
    END) AS 'Weekly Budget',
   
    SUM(CASE Period WHEN 'Daily'       THEN round(Amount*30.42    ,2)
                WHEN 'Bi-Weekly'   THEN round(Amount*4.3333*2 ,2)
                WHEN 'Weekly'      THEN round(Amount*4.3333   ,2)
                WHEN 'Bi-Monthly'  THEN round(Amount*2        ,2)
                WHEN 'Monthly'     THEN round(Amount*1        ,2)
                WHEN 'Quarterly'   THEN round(Amount/12/4     ,2)
                WHEN 'Half-Yearly' THEN round(Amount/12/2     ,2)
                WHEN 'Yearly'      THEN round(Amount/12       ,2)
    END) AS 'Monthly Budget',
   
    SUM(CASE Period WHEN 'Daily'       THEN round(Amount*365   ,2)
                WHEN 'Bi-Weekly'   THEN round(Amount*52*2  ,2)
                WHEN 'Weekly'      THEN round(Amount*52    ,2)
                WHEN 'Bi-Monthly'  THEN round(Amount*12*2  ,2)
                WHEN 'Monthly'     THEN round(Amount*12    ,2)
                WHEN 'Quarterly'   THEN round(Amount*4     ,2)
                WHEN 'Half-Yearly' THEN round(Amount*2     ,2)
                WHEN 'Yearly'      THEN round(Amount*1     ,2)
    END) AS 'Yearly Budget'
FROM BUDGETTABLE_V1 b,
     BUDGETYEAR_V1  y,
     CATEGORY_V1   ca
where b.budgetyearid=y.budgetyearid and
      b.categid=ca.categid          and
      y.BudgetYearName = '2009'     and
      b.period <> 'None'            and
      Amount > 0