CODEWITHSUNDEEP
sqlsql-server-2008
princevezt
princevezt

Reputation: 77

SQL query to JOIN two tables with no relationship

I am having trouble getting the records on the Monthly average Cost per Item with this table values. enter image description here

enter image description here

I need a result like this:

enter image description here

What I want to get is all the month from January to December even if there is no transaction on that month of that item.

Is this possible ?

Upvotes: 0

Views: 1289

Answers (2)

Mackers
Mackers

Reputation: 1050

Here you go (assuming T-SQL):

select b.ItemName, a.MonthName, round(coalesce(avg(c.Cost), 0)) as avg_cost
from table_1 a
join (select ItemName from table_2 group by ItemName) b
on 1 = 1
left join table_2 c
on month(c.TransactionDate) = a.MonthID
and b.ItemName = c.ItemName
group by b.ItemName, a.MonthName
order by b.ItemName, a.MonthID;

Upvotes: 1

NonProgrammer
NonProgrammer

Reputation: 1387

I am away from my computer, but I would do the following in terms of concept:

  1. Generate a table/temp table that uses a min and max dates from your transaction date field. This table has at least three columns: 1. Month name 2. Start date (along with year if doing this multiple years) for that month 3. End date in same manners as start date. URL to use for Step 1: https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/

  2. Join on the table from step 1 where transaction date between start date and end date

  3. Finally, group / avg it.

Hope this helps. Tomorrow, if you still need help with this, I'll post the query.

Upvotes: 1

Related Questions