Don Trembly Don Trembly - 1 month ago 6
SQL Question

SQL: Add Column from table to another

I have two tables that count records and displays the count by month.

I want to show both counts on the same table, but I'm not sure how to combine them.

enter image description here

enter image description here

I just want to add the "IM Count" column to the first table, to the right of "CR Count."

Below is the code for the first table. The second table is similar code, but it draws its data from another table. My main issue is that I don't have a column that matches between tables. (The Year and Month columns technically do match, but only because they are both counts per month.)

Use sm

select
year(planned_start) Year,

Case
When month(planned_start) = 1 then 'January'
When month(planned_start) = 2 then 'February'
When month(planned_start) = 3 then 'March'
When month(planned_start) = 4 then 'April'
When month(planned_start) = 5 then 'May'
When month(planned_start) = 6 then 'June'
When month(planned_start) = 7 then 'July'
When month(planned_start) = 8 then 'August'
When month(planned_start) = 9 then 'September'
When month(planned_start) = 10 then 'October'
When month(planned_start) = 11 then 'November'
When month(planned_start) = 12 then 'December'
end as Month,
count(*) 'CR Count'

from dbo.cm3rm1
where planned_start between dateadd(Year,-1,getdate()) and getdate()
and
category !='OAS Normal'
group by year(planned_start), month(planned_start)
order by year(planned_start), month(planned_start)

Answer

Well, for the "idea" :

SELECT tmp_CR.Year, tmp_CR.month, tmp_CR.CR_Count, tmp_IM.IM_Count
FROM (
 select 
  year(planned_start) Year, 
 Case
    When month(planned_start) = 1 then 'January'
    When month(planned_start) = 2 then 'February'
    When month(planned_start) = 3 then 'March'
    When month(planned_start) = 4 then 'April'
    When month(planned_start) = 5 then 'May'
    When month(planned_start) = 6 then 'June'
    When month(planned_start) = 7 then 'July'
    When month(planned_start) = 8 then 'August'
    When month(planned_start) = 9 then 'September'
    When month(planned_start) = 10 then 'October'
    When month(planned_start) = 11 then 'November'
    When month(planned_start) = 12 then 'December'
 end as Month,
 count(*) as CR_Count
 from dbo.TABLE_1
 where planned_start between dateadd(Year,-1,getdate()) and getdate()
   and
    category !='OAS Normal'
 group by year(planned_start), month(planned_start) 
) tmp_CR
INNER JOIN (
 select 
  year(planned_start) Year, 
 Case
    When month(planned_start) = 1 then 'January'
    When month(planned_start) = 2 then 'February'
    When month(planned_start) = 3 then 'March'
    When month(planned_start) = 4 then 'April'
    When month(planned_start) = 5 then 'May'
    When month(planned_start) = 6 then 'June'
    When month(planned_start) = 7 then 'July'
    When month(planned_start) = 8 then 'August'
    When month(planned_start) = 9 then 'September'
    When month(planned_start) = 10 then 'October'
    When month(planned_start) = 11 then 'November'
    When month(planned_start) = 12 then 'December'
 end as Month,
 count(*) as IM_Count
 from dbo.TABLE_2
 where planned_start between dateadd(Year,-1,getdate()) and getdate()
   and
    category !='OAS Normal'
 group by year(planned_start), month(planned_start) 
) tmp_IM ON tmp_CR.year = tmp_IM.year and tmp_CR.month = tmp_IM.month
ORDER BY tmp_CR.Year, tmp_CR.month
Comments