Joining 2 queries in one table

I am trying to join 2 queries within one table, but as the results return 2 columns and 12 rows for each query, I am having problems working out how to join them together. (This is actually part of a far larger join of many queries, but this is the bit I am stuck on!)

Any help would be appreciated.

Thanks

SELECT CONVERT(nvarchar,MONTH(Job.job_cdate))+'/'+CONVERT(nvarchar,YEAR(Job.job_cdate)) AS 'Month',COUNT(*) AS 'TotalJobsIn Year' FROM Job
WHERE Job.job_cdate BETWEEN DATEADD(mm, DATEDIFF(mm,0,GETDATE()-341), 0)
AND DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1, 0))
GROUP BY MONTH(Job.job_cdate),YEAR(Job.job_cdate)

SELECT CONVERT(nvarchar,MONTH(Appointment.actual_end_date))+'/'+CONVERT(nvarchar,YEAR(Appointment.actual_end_date)) AS 'Month',COUNT(*) AS 'TotalAppsPerMonth' FROM Appointment
WHERE Appointment.actual_end_date BETWEEN DATEADD(mm, DATEDIFF(mm,0,GETDATE()-341), 0)
AND DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1, 0))
GROUP BY MONTH(Appointment.actual_end_date),YEAR(Appointment.actual_end_date)

You want to UNION them from what I can see.

SELECT CONVERT(nvarchar,MONTH(Job.job_cdate))+'/'+CONVERT(nvarchar,YEAR(Job.job_cdate)) AS 'Month',COUNT(*) AS 'TotalJobsIn Year' FROM Job
WHERE Job.job_cdate BETWEEN DATEADD(mm, DATEDIFF(mm,0,GETDATE()-341), 0)
AND DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1, 0))
GROUP BY MONTH(Job.job_cdate),YEAR(Job.job_cdate)
UNION ALL
SELECT CONVERT(nvarchar,MONTH(Appointment.actual_end_date))+'/'+CONVERT(nvarchar,YEAR(Appointment.actual_end_date)) AS 'Month',COUNT(*) AS 'TotalAppsPerMonth' FROM Appointment
WHERE Appointment.actual_end_date BETWEEN DATEADD(mm, DATEDIFF(mm,0,GETDATE()-341), 0)
AND DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1, 0))
GROUP BY MONTH(Appointment.actual_end_date),YEAR(Appointment.actual_end_date)

Adamus

|||

Here You Go...

Code Snippet

Select

Isnull(JobData.[Month],AppointmentData.[Month]) as [Month]

,JobData.[TotalJobsIn Month]

,AppointmentData.[TotalAppsPer Month]

From

(

SELECT

CONVERT(nvarchar,MONTH(Job.job_cdate))+'/'+CONVERT(nvarchar,YEAR(Job.job_cdate)) AS 'Month'

,COUNT(*) AS 'TotalJobsIn Month'

FROM

Job

WHERE

Job.job_cdate BETWEEN DATEADD(mm, DATEDIFF(mm,0,GETDATE()-341), 0)

AND DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1, 0))

GROUP BY

MONTH(Job.job_cdate),YEAR(Job.job_cdate)

) as JobData

FULL OUTER Join

(

SELECT

CONVERT(nvarchar,MONTH(Appointment.actual_end_date))+'/'+CONVERT(nvarchar,YEAR(Appointment.actual_end_date)) AS 'Month'

,COUNT(*) AS 'TotalAppsPer Month'

FROM

Appointment

WHERE

Appointment.actual_end_date BETWEEN DATEADD(mm, DATEDIFF(mm,0,GETDATE()-341), 0)

AND DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1, 0))

GROUP BY

MONTH(Appointment.actual_end_date),YEAR(Appointment.actual_end_date)

) as AppointmentData On JobData.[Month] = AppointmentData.[Month]

|||Thanks Manivannan. That is exactly what I needed - I knew I couldn't use the union clause as I wanted the results next to each other.
Thanks so much
K