I have a need were I need to look at a historical table at 4 different points in time and based on a few other filters. The only difference is the points in time. I have the 4 tables and I have the data grouped by Machine Type and Models. I need to stitch them back together. Example below:
Table Y1
Model Type Qty
======= ====== =====
2074 001 18
2096 360 205
DSC Base 22
Table Y2
Model Type Qty
======= ====== =====
2074 001 33
2096 360 156
DSC Base 29
3678 345 16
Table Y3
Model Type Qty
======= ====== =====
2074 001 28
2096 360 295
DSC Base 36
3678 345 25
Table Y4
Model Type Qty
======= ====== =====
2074 001 19
2096 360 200
DSC Base 20
3678 345 18
Stitched Table
Model Type Y1 Y2 Y3 Y4
====== ====== ==== ==== ===== ======
2074 001 18 33 28 29
2096 360 205 156 295 200
DSC Base 22 28 36 20
3678 345 0 16 25 18
Any help would be appreciated!
Since this is FM, I am assuming you want to create a query subject - so somewhat depending on the database in use, you could probably use SQL something like:
with u as (
select model, type, qty as y1, 0 as y2, 0 as y3, 0 as y4
union
select model, type, 0 as y1, qty as y2, 0 as y3, 0 as y4
union
select model, type, 0 as y1, 0 as y2, qty as y3, 0 as y4
union
select model, type, 0 as y1, 0 as y2, 0 as y3, qty as y4
)
select model, type, sum(y1), sum(y2), sum(y3), sum(y4)
group by model, type
Technically speaking, you would likely not need the "as" portion on any but the first select and the rest would be ignored; only order is important on subsequent selects. I included them thinking it might clarify what is intended.