Merging 2 SQL Queries

Question

I've got 2 queries I'd like to merge into 1 result set without using union.

Query 1

select  datepart(yy,dateclosed)as 'Year',
    datepart(mm,dateclosed) as 'Month',
    count(*)as 'Total' 
from bug 
where projectid = 44 
and ifclosed = 1
and isnull(createdbyperson,1) <> '-1111111110'
and datepart(yy,dateclosed) > '2000'
group by datepart(yy,dateclosed), datepart(mm,dateclosed)
order by 1,2

Query 2

select  datepart(yy,dateclosed)as 'Year',
    datepart(mm,dateclosed) as 'Month',
    count(*)as 'SameDay' 
from bug 
where   projectid = 44 
     and ifclosed = 1
     and isnull(createdbyperson,1) <> '-1111111110'
     and datepart(yy,dateclosed) > '2000' 
     and CONVERT(VARCHAR(10), dateclosed, 101) = CONVERT(VARCHAR(10), datecreated, 101) 
group by datepart(yy,dateclosed),datepart(mm,dateclosed)
order by 1,2

Id like it to return the values as Year,Month,SameDay,Total. How do I achieve this? Union doesn't do what I want it to do. Do I have to do a join and a table alias? Subquery?

Thanks in advance.

Answer 1

OK...what about this one:

select a.Year, a.Month, b.SameDay, a.Total
from
(
select  datepart(yy,dateclosed)as 'Year',
    datepart(mm,dateclosed) as 'Month',
    count(*)as 'Total' 
from bug 
where projectid = 44 
and ifclosed = 1
and isnull(createdbyperson,1) <> '-1111111110'
and datepart(yy,dateclosed) > '2000'
group by datepart(yy,dateclosed), datepart(mm,dateclosed)
) a
inner join
(
select  datepart(yy,dateclosed)as 'Year',
    datepart(mm,dateclosed) as 'Month',
    count(*)as 'SameDay' 
from bug 
where   projectid = 44 
        and ifclosed = 1
        and isnull(createdbyperson,1) <> '-1111111110'
        and datepart(yy,dateclosed) > '2000' 
        and CONVERT(VARCHAR(10), dateclosed, 101) = CONVERT(VARCHAR(10), datecreated, 101) 
group by datepart(yy,dateclosed),datepart(mm,dateclosed)
) b
on a.Year = b.Year AND a.Month = b.Month
order by 1,2

Answer 2

Without using a union you could create a temporary table, write the results of the two queries into it and then query the temporary table.

I suspect that you shouldn't be doing this, but rather working out why the union isn't working for you.

What have you tried?

What happened?

Answer 3

Try this:

SELECT DATEPART(yy,dateclosed) AS 'Year',
    DATEPART(mm,dateclosed) AS 'Month',
    SUM(IF(CONVERT(VARCHAR(10), dateclosed, 101) = CONVERT(VARCHAR(10), datecreated, 101), 1, 0)) AS SameDay,
    COUNT(*) AS 'Total' 
FROM bug 
WHERE projectid = 44 
    AND ifclosed = 1
    AND ISNULL(createdbyperson,1) <> '-1111111110'
    AND DATEPART(yy,dateclosed) > '2000'
GROUP BY DATEPART(yy,dateclosed), DATEPART(mm,dateclosed)
ORDER BY 1,2

Answer 4

Why is it that you want to do this? Are you aware that you can just have the query return multiple result sets:

SELECT -- query 1

SELECT -- query 2

Your client can then read the 2 result sets independently - it depends on the langauge you use but in C# using SqlDataReader you use NextResult

SqlDataReader reader = GetReader();
while (reader.Read())
{
    // Process result of query 1
}
reader.NextResult();
while (reader.Read())
{
    // Process result of query 2
}