I have stored procedures that perform different tasks, but I'd like to put some of them together to reduce the amount of Stored procedures created in the database. Many of the procedures are based on years statistics and here is an example:
Select
DATENAME(MONTH, DATE_) AS Date,
count(distinct VATNUMBER) As Count
from
(
select VATNUMBER, DATE_ = min(DATE_)
from
ACCOUNTS a inner join CUSTTABLE b
on a.ID= b.ID
where ITEMNUMBER in(7055,7056,7059) AND YEAR(a.DATE_) = '2017'
AND VATNUMBER NOT IN (
select VATNUMBER
from
ACCOUNTS a inner join CUSTTABLE b
on a.ID= b.ID
where ITEMNUMBER in(7055,7056,7059)
AND DATE_ < '2017-01-01'
)
group by VATNUMBER
) d
group by MONTH(DATE_), DATENAME(MONTH, DATE_)
Order by MONTH(DATE_)
Result for year 2017:
Date | Count
January | 61
February | 43
March | 23
April | 48
etc....
And now I have same Procedure for 2018:
Select
DATENAME(MONTH, DATE_) AS Date,
count(distinct VATNUMBER) As Count
from
(
select VATNUMBER, DATE_ = min(DATE_)
from
ACCOUNTS a inner join CUSTTABLE b
on a.ID= b.ID
where ITEMNUMBER in(7055,7056,7059) AND YEAR(a.DATE_) = '2018'
AND VATNUMBER NOT IN (
select VATNUMBER
from
ACCOUNTS a inner join CUSTTABLE b
on a.ID= b.ID
where ITEMNUMBER in(7055,7056,7059)
AND DATE_ < '2018-01-01'
)
group by VATNUMBER
) d
group by MONTH(DATE_), DATENAME(MONTH, DATE_)
Order by MONTH(DATE_)
Result for year 2018:
Date | Count
January | 119
February | 200
March | 112
April | 87
etc....
What I'd like to do is to put both select statements under the same Stored procedure. A result I'd like to see is:
Date 2017| Count | Date 2018| Count
January | 61 | January | 119
February | 43 | February | 200
March | 23 | March | 112
April | 48 | April | 87
etc....
What I have tried:
I have tried UNION, UNION ALL with no success or not the anticipated result