How to do 2 queries and display results in 1 table

Transact-SQL
1

There's my 1st query and result AND 2nd query and result as the following. Now I want to put 2 query together to get results of 2 together instead of 2 separate result. Thank you in advance.

SELECT FiscalYear, SWORNCLASS, FiscalMonth, Nbr,
CASE SWORNCLASS
WHEN 'FIRE' THEN CAST((SUM(Nbr) / 363.0) * 100 AS NUMERIC(10, 2))
WHEN 'POLICE' THEN CAST((SUM(Nbr) / 676.0) * 100 AS NUMERIC(10, 2))
WHEN 'CIVILLIAN' THEN CAST((SUM(Nbr) / 1567.0) * 100 AS NUMERIC(10, 2))
END AS Percentage
FROM dbo.vw_Termination_Numbers
GROUP BY FiscalYear, FiscalMonth, SWORNCLASS, Nbr

FiscalYear SWORNCLASS FiscalMonth Nbr Percentage
2018 CIVILLIAN 1 14 0.89
2018 POLICE 1 3 0.44
2018 CIVILLIAN 2 7 0.45
2018 FIRE 2 1 0.28
2018 POLICE 2 1 0.15
2018 CIVILLIAN 3 9 0.57
2018 FIRE 3 1 0.28
2018 POLICE 3 1 0.15
2018 CIVILLIAN 4 10 0.64
2018 FIRE 4 1 0.28
2018 CIVILLIAN 5 11 0.70
2018 CIVILLIAN 6 12 0.77
2018 POLICE 6 3 0.44
2018 CIVILLIAN 7 5 0.32

Second Query and result.
SELECT
[FiscalYear],
[Quarter] = ([FiscalMonth]-1)/3 +1,
SwornClass,
[for Total] = SUM([Percentage])
FROM
Vw_termination_rates
GROUP BY
[FiscalYear],
([FiscalMonth]-1)/3 +1,
SWORNCLASS

FiscalYear Quarter SwornClass for Total
2018 1 CIVILLIAN 1.91
2018 1 FIRE 0.56
2018 1 POLICE 0.74
2018 2 CIVILLIAN 2.11
2018 2 FIRE 0.28
2018 2 POLICE 0.44
2018 3 CIVILLIAN 0.32

2

Any reason you don't just put the first query in as a subquery of 2?
Are you trying to get both sets of results to print? or just the final answer?
For final answer, something like this should work...

SELECT
q1. [FiscalYear],
[Quarter] = (q1.[FiscalMonth]-1)/3 +1,
q1.SwornClass,
[for Total] = SUM(q1.[Percentage])
FROM
( SELECT FiscalYear, SWORNCLASS, FiscalMonth, Nbr,
CASE SWORNCLASS
WHEN 'FIRE' THEN CAST((SUM(Nbr) / 363.0) * 100 AS NUMERIC(10, 2))
WHEN 'POLICE' THEN CAST((SUM(Nbr) / 676.0) * 100 AS NUMERIC(10, 2))
WHEN 'CIVILLIAN' THEN CAST((SUM(Nbr) / 1567.0) * 100 AS NUMERIC(10, 2))
END AS Percentage
FROM dbo.vw_Termination_Numbers
GROUP BY FiscalYear, FiscalMonth, SWORNCLASS, Nbr
)
as q1
GROUP BY
q1. [FiscalYear],
(q1.[FiscalMonth]-1)/3 +1,
q1.SWORNCLASS

3

Hi Paul,

I want to get both set of results to print for 2 above query. Can anybody help me with it.

I need to something like this.

OUTPUT 1 and OUTPUT 2 together.

OUTPUT 1 FOR MONTHLY(INDIVIDUAL MONTH) OUTPUT 2 FOR QUARTERLY

FiscalYear SWORNCLASS FiscalMonth Nbr Percentage FiscalYear Quarter SwornClass for Total

2018 CIVILLIAN 1 14 0.89 2018 1 CIVILIAN 1.91
2018 POLICE 1 3 0.44 2018 1 FIRE 0.56
2018 CIVILLIAN 2 7 0.45 2018 1 POLICE 0.74
2018 FIRE 2 1 0.28 2018 2 CIVILILAN 2.11
2018 POLICE 2 1 0.15 2018 2 FIRE 0.28
2018 CIVILLIAN 3 9 0.57 2018 2 POLICE 0.44
2018 FIRE 3 1 0.28 2018 3 CIVILIAN 0.32
2018 POLICE 3 1 0.15
2018 CIVILLIAN 4 10 0.64
2018 FIRE 4 1 0.28
2018 CIVILLIAN 5 11 0.70
2018 CIVILLIAN 6 12 0.77
2018 POLICE 6 3 0.44
2018 CIVILLIAN 7 5 0.32

4

use the UNION operator

5

Union didn’t work. I don’t know how. Can anyone help me with this question. Thank you!

6

I just two your two results and made tables of them then joined them:

DECLARE @t1 TABLE
(
    FiscalYear INT, 
    SWORNCLASS VARCHAR(20),
    FiscalMonth INT,
    Nbr INT,
    Percentage DECIMAL(3, 2)
)
INSERT INTO @t1
(
    FiscalYear,
    SWORNCLASS,
    FiscalMonth,
    Nbr,
    Percentage
)
VALUES
(2018, 'CIVILLIAN', 1, 14, 0.89),
(2018, 'POLICE', 1, 3, 0.44),
(2018, 'CIVILLIAN', 2, 7, 0.45),
(2018, 'FIRE', 2, 1, 0.28),
(2018, 'POLICE', 2, 1, 0.15),
(2018, 'CIVILLIAN', 3, 9, 0.57),
(2018, 'FIRE', 3, 1, 0.28),
(2018, 'POLICE', 3, 1, 0.15),
(2018, 'CIVILLIAN', 4, 10, 0.64),
(2018, 'FIRE', 4, 1, 0.28),
(2018, 'CIVILLIAN', 5, 11, 0.70),
(2018, 'CIVILLIAN', 6, 12, 0.77),
(2018, 'POLICE', 6, 3, 0.44),
(2018, 'CIVILLIAN', 7, 5, 0.32)

DECLARE @t2 TABLE(FiscalYear INT, Quarter INT, SWORNCLASS VARCHAR(20), Total DECIMAL(3, 2))
INSERT INTO @t2(FiscalYear, Quarter, SWORNCLASS, Total)
VALUES(2018, 1, 'CIVILLIAN', 1.91),
    (2018, 1, 'FIRE', 0.56),
    (2018, 1, 'POLICE', 0.74),
    (2018, 2, 'CIVILLIAN', 2.11),
    (2018, 2, 'FIRE', 0.28),
    (2018, 2, 'POLICE', 0.44),
    (2018, 3, 'CIVILLIAN', 0.32)

SELECT t1.FiscalYear, t1.SWORNCLASS, t1.FiscalMonth, t1.Nbr, t2.Quarter, t2.Total
FROM @t1 t1
     JOIN @t2 t2 ON t1.FiscalYear=t2.FiscalYear AND t1.SWORNCLASS=t2.SWORNCLASS;
7

I want to do dynamic not manually insert each value. Is there the way I can put in store procedure?

8

I just did that to illustrate the process.

For you, try this:

;with q1 as 
(your first query, enclosed in parentheses),
q2 as (your second query, enclosed in parentheses)

select ...
from q1
join q1 on ...