I'm struggling to build a query comparing budget to actual expense items.
The budget table has a single record per month/category on the first day of the month whereas the expense table has many records throughout the days of the month.

My desired result:

YEAR	MONTH	category	budgetAmt	sumExpenseAmt
2021	1	daily	100	49
2021	1	monthly	42	87
2021	2	daily	101	36
2021	2	monthly	55	82

What I'm getting:

YEAR	MONTH	category	budgetAmt	sumExpenseAmt
2021	1	daily	100	85
2021	1	monthly	42	169
2021	2	daily	101	85
2021	2	monthly	55	169

The amounts in "sumExpenseAmt" are wrong AND they're repeating.
(85 is the sum of all expense-daily items (jan feb): 40 9 32 4)
(169 is the sum of all expense-monthly items (jan feb): 83 4 75 7)

MY SQL:

SELECT YEAR( "b"."date" ) AS "Year"
, MONTH( "b"."date" ) AS "Month"
, "b"."category"
, "b"."budgetAmt"
, SUM( "e"."expenseAmt" ) AS "sumExpenseAmt"
FROM "budget" AS "b"
JOIN "expense" AS "e" ON "b"."category" = "e"."category"
GROUP BY YEAR( "b"."date" ), MONTH( "b"."date" ), "b"."category", "b"."budgetAmt"

table: budget

date	category	budgetAmt
2021-01-01	daily	100
2021-01-01	monthly	42
2021-02-01	daily	101
2021-02-01	monthly	55

table: expense

date	category	expenseAmt
2021-01-04	daily	40
2021-01-07	daily	9
2021-01-08	monthly	83
2021-01-25	monthly	4
2021-02-01	daily	32
2021-02-05	daily	4
2021-02-15	monthly	75
2021-02-20	monthly	7

I've tried aggregating the expense table with a query and feeding the result into my initial SQL query, but that gives me the same result.

query: qry_summary_expense

date	category	budgetAmt
2021-01-01	daily	49
2021-01-01	monthly	87
2021-02-01	daily	36
2021-02-01	monthly	82

SELECT YEAR( "b"."date" ) AS "Year"
, MONTH( "b"."date" ) AS "Month"
, "b"."category", "b"."budgetAmt"
, SUM( "e"."expenseAmt" ) AS "sumExpenseAmt" 
FROM "budget" AS "b" 
JOIN "qry_summary_expense" AS "e" ON "b"."category" = "e"."category" 
GROUP BY YEAR( "b"."date" ), MONTH( "b"."date" ), "b"."category", "b"."budgetAmt"

CodePudding user response：

I'd join on both month and category

select year(b.date) as year,
month(b.date) as month,
b.category,
avg(budgetAmt) as budgetAmt,
sum(expenseAmt) as expenseAmt
from expense e
join budget b
on (month(b.date) = month(e.date)
and b.category = e.category)
group by year(b.date), month(b.date), b.category

CodePudding user response：

You need to join the two tables on category AND month (using eomonth (end of month) does the trick).

SELECT Year(Eomonth(e.date))  AS year,
       Month(Eomonth(e.date)) AS month,
       e.category,
       Avg(budgetamt)         AS budgetAmt,
       Sum(expenseamt)        AS sumExpenseAmt
FROM   expense e
       INNER JOIN budget b
               ON Eomonth(e.date) = Eomonth(b.date)
                  AND e.category = b.category
GROUP  BY e.category,
          Eomonth(e.date); 

Fiddle

CodePudding user response：

Alternatively you can use left or right joins based on your need.

select b.Year,b.Month,b.category,Budget, Expenses
from(
    Select year(date) [Year] ,month(date) [Month] ,category,sum(budgetAmt)  Budget
    from budget
    group by year(date),month(date),category
    ) b 
 Join
   (
    Select year(date) [Year] ,month(date) [Month] ,category,sum(expenseAmt) Expenses
    from expense
    group by year(date),month(date),category
   ) e
on b.Month = e.Month and b.Year = e.Year and b.category = e.category

Another Approach Using EOMONTH Function:

select Year(a.dates) Year ,month(a.dates) Month,a.category,sum(Budget)     Budget, sum(Expenses) Expenses
from(
     Select EOMONTH(date) dates,category,sum(budgetAmt)  Budget
    from #budget
    group by EOMONTH(date),category
    ) a 
Join
   (
    Select EOMONTH(date) dates,category,sum(expenseAmt) Expenses
    from #expense
    group by EOMONTH(date),category
   ) b
 on a.dates = b.dates and a.category = b.category
 group by Year(a.dates),month(a.dates),a.category