Click here to Skip to main content
15,881,852 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
Posted
Updated 10-Dec-18 3:20am
Comments
CHill60 10-Dec-18 9:18am    
What happened when you used UNION?
Mish1337 11-Dec-18 3:17am    
It works fine, but both results shows on top of each other or as separate results
CHill60 10-Dec-18 9:24am    
You are also saying that these have nothing in common - yet they are identical. The only thing changing is the date. I'm not even sure why you are using that NOT IN - you will never hit it due to the test on YEAR(a.DATE_)... or is it possible to have a VATNUMBER re-used the following year and you do not want to include those records?
Mish1337 11-Dec-18 4:32am    
Correct, you can ignore the the not in statement, since it's only job is to look for new vatnumbers which didnt exist the past years.
I know both tables are identical (right now)
But when I add 2019 statistics then they wont be identical, since 2019 will start off with January and will grow over time
Richard Deeming 11-Dec-18 11:02am    
That's still identical - you're just changing the date range.

1 solution

 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900