Hallo,
Ik wil een aantal query's middels een UNION ALL weergeven. Zie onderstaande code.
Het resultaat is niet wat ik had gehoopt. In MSSQL worden getallen velden in een UNION ALL opgeteld. Kan ik dat in interbase ook bewerkstelligen? Nu worden alle regels met een unieke waarde weergegeven, ik zou deze graag opgeteld zien. Dus alle waardes van november en alle waardes van december opgeteld.
Code:
SELECT cl.client_number,
cl.client_name,
cl.client_additionalname,
inv.invoice_year,
sum(inve.inventry_declaredeuro) as november,
0.00 as december
FROM invoiceentry inve
inner join invoice inv on inve.inventry_invoice_id1 = inv.invoice_id1 and inve.inventry_invoice_id2 = inv.invoice_id2 and inve.inventry_invoice_id3 = inv.invoice_id3
inner join timesheetentry tse on inve.inventry_tsentry_id1 = tse.tsentry_id1 and inve.inventry_tsentry_id2 = tse.tsentry_id2 and inve.inventry_tsentry_id3 = tse.tsentry_id3
inner join clienttimesheet cts on tse.tsentry_cts_id1 = cts.cts_id1 and tse.tsentry_cts_id2 = cts.cts_id2 and tse.tsentry_cts_id3 = cts.cts_id3
inner join officedeclarer od on cts.cts_offdeclarer_id1 = od.offdeclarer_id1 and cts.cts_offdeclarer_id2 = od.offdeclarer_id2 and cts.cts_offdeclarer_id3 = od.offdeclarer_id3
inner join users u on od.offdeclarer_user_id1 = u.user_id1 and od.offdeclarer_user_id1 = u.user_id1 and od.offdeclarer_user_id2 = u.user_id2 and od.offdeclarer_user_id3 = u.user_id3
inner join client cl on cts.cts_client_id1 = cl.client_id1 and cts.cts_client_id2 = cl.client_id2 and cts.cts_client_id3 = cl.client_id3
inner join activity act on tse.tsentry_activity_id1 = act.act_id1 and tse.tsentry_activity_id2 = act.act_id2 and tse.tsentry_activity_id3 = act.act_id3
inner join activitysubgroup actsgrp on act.act_activitysubgroup_id1 = actsgrp.actsubgroup_id1 and act.act_activitysubgroup_id2 = actsgrp.actsubgroup_id2 and act.act_activitysubgroup_id3 = actsgrp.actsubgroup_id3
inner join activitygroup actgrp on actsgrp.actsubgroup_actgroup_id1 = actgrp.actgroup_id1 and actsgrp.actsubgroup_actgroup_id2 = actgrp.actgroup_id2 and actsgrp.actsubgroup_actgroup_id3 = actgrp.actgroup_id3
WHERE
cl.client_number = '0370410' and
inv.invoice_year = 2016 and
inv.invoice_faperiodenr = 12
GROUP BY cl.client_number, cl.client_name, cl.client_additionalname,
inv.invoice_year, inv.invoice_faperiodenr,
act.act_code, act.act_description, tse.tsentry_fiscalyear,
actsgrp.actsubgroup_description, actgrp.actgroup_description
UNION ALL
SELECT cl.client_number,
cl.client_name,
cl.client_additionalname,
inv.invoice_year,
0.00 as november,
sum(inve.inventry_declaredeuro) as december
FROM invoiceentry inve
inner join invoice inv on inve.inventry_invoice_id1 = inv.invoice_id1 and inve.inventry_invoice_id2 = inv.invoice_id2 and inve.inventry_invoice_id3 = inv.invoice_id3
inner join timesheetentry tse on inve.inventry_tsentry_id1 = tse.tsentry_id1 and inve.inventry_tsentry_id2 = tse.tsentry_id2 and inve.inventry_tsentry_id3 = tse.tsentry_id3
inner join clienttimesheet cts on tse.tsentry_cts_id1 = cts.cts_id1 and tse.tsentry_cts_id2 = cts.cts_id2 and tse.tsentry_cts_id3 = cts.cts_id3
inner join officedeclarer od on cts.cts_offdeclarer_id1 = od.offdeclarer_id1 and cts.cts_offdeclarer_id2 = od.offdeclarer_id2 and cts.cts_offdeclarer_id3 = od.offdeclarer_id3
inner join users u on od.offdeclarer_user_id1 = u.user_id1 and od.offdeclarer_user_id1 = u.user_id1 and od.offdeclarer_user_id2 = u.user_id2 and od.offdeclarer_user_id3 = u.user_id3
inner join client cl on cts.cts_client_id1 = cl.client_id1 and cts.cts_client_id2 = cl.client_id2 and cts.cts_client_id3 = cl.client_id3
inner join activity act on tse.tsentry_activity_id1 = act.act_id1 and tse.tsentry_activity_id2 = act.act_id2 and tse.tsentry_activity_id3 = act.act_id3
inner join activitysubgroup actsgrp on act.act_activitysubgroup_id1 = actsgrp.actsubgroup_id1 and act.act_activitysubgroup_id2 = actsgrp.actsubgroup_id2 and act.act_activitysubgroup_id3 = actsgrp.actsubgroup_id3
inner join activitygroup actgrp on actsgrp.actsubgroup_actgroup_id1 = actgrp.actgroup_id1 and actsgrp.actsubgroup_actgroup_id2 = actgrp.actgroup_id2 and actsgrp.actsubgroup_actgroup_id3 = actgrp.actgroup_id3
WHERE
cl.client_number = '0370410' and
inv.invoice_year = 2016-1 and
inv.invoice_faperiodenr = 1
GROUP BY cl.client_number, cl.client_name, cl.client_additionalname,
inv.invoice_year, inv.invoice_faperiodenr,
act.act_code, act.act_description, tse.tsentry_fiscalyear,
actsgrp.actsubgroup_description, actgrp.actgroup_description
Resultaat:
CLIENT_NUMBER |
CLIENT_NAME |
CLIENT_ADDITIONALNAME |
INVOICE_YEAR |
NOVEMBER |
DECEMBER |
0370410 |
test |
|
2.016 |
158,500 |
0,000 |
0370410 |
test |
|
2.016 |
108,000 |
0,000 |
0370410 |
test |
|
2.016 |
72,000 |
0,000 |
0370410 |
test |
|
2.016 |
1071,500 |
0,000 |
0370410 |
test |
|
2.016 |
23,500 |
0,000 |
0370410 |
test |
|
2.016 |
443,750 |
0,000 |
0370410 |
test |
|
2.016 |
36,000 |
0,000 |
0370410 |
test |
|
2.016 |
0,000 |
105,000 |
0370410 |
test |
|
2.016 |
0.000 |
292,500 |
Verwacht resultaat:
CLIENT_NUMBER |
CLIENT_NAME |
CLIENT_ADDITIONALNAME |
INVOICE_YEAR |
NOVEMBER |
DECEMBER |
0370410 |
test |
|
2.016 |
1913,250 |
397,500 |
Bookmarks