SQL david meffe

gistfile1.txt

SELECT *,
       NULL AS extra,
       0 AS AnnualBusExpenses,
       0 AS MonthExtraBusExpenses,
       0 AS AnnualExtraBusExpenses,
       0 AS MonthBusExpenses
FROM
  (SELECT Id,
          Name,DATE_FORMAT(Date, "%Y-%m-%d") as Date,COALESCE(SupplierExpenses,0) as SupplierExpenses,
                    COALESCE(MiscEventExpenses,0) as MiscEventExpenses ,
                    COALESCE(Income,0) as Income,
                    c.cash AS cash
   FROM
     (SELECT EventOne,
             SupplierExpenses,
             MiscEventExpenses,
             sum(Amount) AS Income,
             b.Cash
      FROM
        (SELECT a.Event_fk AS eventOne,
                ee.event_fk eventTwo,
                SupplierExpenses,
                sum(Amount) AS MiscEventExpenses,
                a.Cash
         FROM
           (SELECT Event_fk,
                   sum(Amount) AS SupplierExpenses,
                   Cash
            FROM Suppliers
            GROUP BY Event_fk,
                     Cash) a
         LEFT JOIN EventExpenses ee ON a.Event_fk = ee.Event_fk
         GROUP BY a.Event_fk,
                  ee.event_fk,
                  SupplierExpenses,
                  a.Cash) b
      JOIN EventIncome ei ON b.EventOne = ei.Event_fk
      GROUP BY EventOne,
               EventTwo,
               SupplierExpenses,
               MiscEventExpenses,
               Cash) c
   JOIN Event e ON c.EventOne = e.Id where Status = 'Booked') AS d 


UNION ALL



SELECT Id,
       Details AS Name,
       DATE_FORMAT(curr_date, "%Y-%m-%d") AS Date,
       0 AS SupplierExpenses,
       0 AS MiscEventExpenses,
       0 AS Income,
       cash,
       Extra,
       AnnualBusExpenses,
       MonthExtraBusExpenses,
       AnnualExtraBusExpenses,
       MonthBusExpenses
FROM (SELECT * 
      FROM
  (SELECT *
   FROM
     (SELECT *
      FROM
        (SELECT Id,
                concat(YEAR(Date), '-', LPAD(MONTH, 2, '00'), '-01') AS curr_date,
                details,
                COALESCE(Amount / (13 - month(Date)),0) AS AnnualBusExpenses,
                (13 - MONTH) AS months_remain,
                Cash,
                Extra,
                0 AS MonthExtraBusExpenses,
                0 AS AnnualExtraBusExpenses,
                0 AS MonthBusExpenses
         FROM BusinessExpenses,
              MonthPool
         WHERE MONTH >= month(Date)
           AND yearly = 1
           AND extra = 0
         ORDER BY Id,
                  months_remain DESC) AS a
      UNION ALL SELECT Id,
                       Date AS curr_date,
                       Details,
                       0 AS AnnualBusExpenses,
                       0 AS months_remain,
                       Cash,
                       Extra,
                       Amount AS MonthExtraBusExpenses,
                       0 AS AnnualExtraBusExpenses,
                       0 AS MonthBusExpenses
      FROM BusinessExpenses
      WHERE yearly = 0
        AND extra = 1) AS b
   UNION ALL SELECT Id,
                    curr_date,
                    Details,
                    0 AS AnnualBusExpenses,
                    months_remain,
                    Cash,
                    Extra,
                    0 AS MonthExtraBusExpenses,
                    AnnualExtraBusExpenses,
                    0 AS MonthBusExpenses
   FROM
     (SELECT *
      FROM
        (SELECT Id,
                concat(YEAR(Date), '-', LPAD(MONTH, 2, '00'), '-01') AS curr_date,
                details,
                0 AS AnnualBusExpenses,
                (13 - MONTH) AS months_remain,
                Cash,
                Extra,
                0 AS MonthExtraBusExpenses,
                Amount / (13 - month(Date)) AS AnnualExtraBusExpenses
         FROM BusinessExpenses,
              MonthPool
         WHERE MONTH >= month(Date)
           AND yearly = 1
           AND extra = 1
         ORDER BY Id,
                  months_remain DESC) AS c) AS d) AS e
  UNION ALL SELECT Id,
                   Date, 
                   Details, 
                   0 AS AnnualBusExpenses,
                   0 AS months_remain,
                   Cash,
                   Extra,
                   0 AS MonthExtraBusExpenses,
                   0 AS AnnualExtraBusExpenses,
                   Amount as MonthBusExpenses
                   FROM BusinessExpenses
                   WHERE Yearly = 0 and Extra = 0) as f