CODEWITHSUNDEEP
sqldb2union
Geoff_S
Geoff_S

Reputation: 5107

UNION on 2 joined queries

I have 2 working queries that are joined by subselects using WITH in DB2.

They each work separately but I can't seem to find the best way to properly combine or union them.

The first query:

  with c as(
select
  employee,
  sum(salesPrice) as priorSpecifics
from schema1.orders g
    inner join dateSchema.dates d
    on g.dateField = d.dateField
where  d.newDate between '2018-01-01' and '2018-01-31'
and g.details = 'CategoryOne'
group by employee
), d as(
select
  employee,
  sum(salesPrice) as priorTotals
from schema1.orders g
    inner join dateSchema.dates d
    on g.dateField = d.dateField
where  d.newDate between '2018-01-01' and '2018-01-31'
group by employee
)
Select  ifnull(c.employee,d.employee) as employee
      ,c.priorSpecifics
      ,d.priorTotals
      ,cast(Round((DEC(c.priorSpecifics,12,2)/DEC(d.priorTotals,12,2)) * 100,2) as decimal(12,2)) as priorPercent
  from c full join d ON (c.employee = d.employee);

Returns 4 columns

and the 2nd query

with c as(
select
  employee,
  sum(salesPrice) as currentSpecifics
from schema1.orders g
    inner join dateSchema.dates d
    on g.dateField = d.dateField
where  d.newDate between '2019-01-01' and '2019-01-31'
and g.details = 'CategoryOne'
group by employee
), d as(
select
  employee,
  sum(salesPrice) as currentTotals
from schema1.orders g
    inner join dateSchema.dates d
    on g.dateField = d.dateField
where  d.newDate between '2019-01-01' and '2019-01-31'
group by employee
)
Select  ifnull(c.employee,d.employee) as employee
      ,c.currentSpecifics
      ,d.currentTotals
      ,cast(Round((DEC(c.currentSpecifics,12,2)/DEC(d.currentTotals,12,2)) * 100,2) as decimal(12,2)) as currentPercent
  from c full join d ON (c.employee = d.employee);

Returns 4 as well. The field employee is the only thing that is shared between all.

How can I combine this into one query to get one employee column and then all of my sum/percent columns following?

Upvotes: 0

Views: 77

Answers (2)

forpas
forpas

Reputation: 164099

If all the above queries are working then you already have everything you need.
You must combine from the 1st group, the first 2 to make the prior query, then
from the 2nd group to make the cur query
and finally join the prior query to the cur query:

with 
c as(
select
  employee,
  sum(salesPrice) as priorSpecifics
from schema1.orders g
    inner join dateSchema.dates d
    on g.dateField = d.dateField
where  d.newDate between '2018-01-01' and '2018-01-31'
and g.details = 'CategoryOne'
group by employee
),

d as(
select
  employee,
  sum(salesPrice) as priorTotals
from schema1.orders g
    inner join dateSchema.dates d
    on g.dateField = d.dateField
where  d.newDate between '2018-01-01' and '2018-01-31'
group by employee
),

prior as (
Select  ifnull(c.employee,d.employee) as employee
      ,c.priorSpecifics
      ,d.priorTotals
      ,cast(Round((DEC(c.priorSpecifics,12,2)/DEC(d.priorTotals,12,2)) * 100,2) as decimal(12,2)) as priorPercent
  from c full join d ON (c.employee = d.employee)
),

e as(
select
  employee,
  sum(salesPrice) as currentSpecifics
from schema1.orders g
    inner join dateSchema.dates d
    on g.dateField = d.dateField
where  d.newDate between '2019-01-01' and '2019-01-31'
and g.details = 'CategoryOne'
group by employee
),

f as(
select
  employee,
  sum(salesPrice) as currentTotals
from schema1.orders g
    inner join dateSchema.dates d
    on g.dateField = d.dateField
where  d.newDate between '2019-01-01' and '2019-01-31'
group by employee
),

cur as (
Select  ifnull(e.employee,f.employee) as employee
      ,e.currentSpecifics
      ,f.currentTotals
      ,cast(Round((DEC(e.currentSpecifics,12,2)/DEC(f.currentTotals,12,2)) * 100,2) as decimal(12,2)) as currentPercent
  from e full join f ON (e.employee = f.employee)
)

Select  ifnull(p.employee, c.employee) as employee
      ,p.priorSpecifics
      ,p.priorTotals
      ,p.priorPercent
      ,c.currentSpecifics
      ,c.currentTotals
      ,c.currentPercent
from prior p full join cur c ON (c.employee = c.employee);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I think you can do everything you want with conditional aggregation:

select employee,
       sum(case when d.newDate between '2018-01-01' and '2018-01-31' and g.details = 'CategoryOne' then salesPrice end) as priorSpecifics,
       sum(case when d.newDate between '2019-01-01' and '2019-01-31' and g.details = 'CategoryOne' then salesPrice end) as currentSpecifics,
       sum(case when d.newDate between '2018-01-01' and '2018-01-31' then salesPrice end) as priorTotals,
       sum(case when d.newDate between '2019-01-01' and '2019-01-31' then salesPrice end) as currentTotals
from schema1.orders g inner join
     dateSchema.dates d
     on g.dateField = d.dateField
group by employee;

I'll let you fill in with the additional calculations.

Upvotes: 1

Related Questions