i have 3 main tables i'm working with. query returns subset of want
select dt.deposittypeid, dt.deposittypename deposittype, 'total revenues' transactiongrouptype, d.fiscalyeartypeid, sum(d.depositamount) amount deposit d inner join deposittype dt on d.deposittypeid = dt.deposittypeid inner join fiscalyeartype fyt on d.fiscalyeartypeid = fyt.fiscalyeartypeid dt.deposittypeid in(1,2,4) , d.transactionstatustypeid = 3 --must approved , d.isprojecteddeposit = 0 --must not projected deposit group dt.deposittypename, d.fiscalyeartypeid, dt.deposittypeid order dt.deposittypeid, d.fiscalyeartypeid returns subset below
deposittypeid | deposittype | transactiongrouptype | fiscalyeartypeid | amount ------------------------------------------------------------------------------- 1 auction total revenues 1 3434 1 auction total revenues 3 52152 1 auction total revenues 4 12859 1 auction total revenues 5 542863 1 smif interest total revenues 5 524586
now, complete query, need extract deposittypes , fiscalyeartypes not in there , populate amount zero.
deposittypeid | deposittype | transactiongrouptype | fiscalyeartypeid | amount ------------------------------------------------------------------------------- 1 auction total revenues 1 3434 2 reserve sale total revenues 1 0 4 smif interest total revenues 1 0 1 auction total revenues 2 0 2 reserve sale total revenues 2 0 4 smif interest total revenues 2 0 1 auction total revenues 3 52152 2 reserve sale total revenues 3 0 4 smif interest total revenues 3 0 1 auction total revenues 4 12859 2 reserve sale total revenues 4 0 4 smif interest total revenues 4 0 1 auction total revenues 5 542863 2 reserve sale total revenues 5 0 4 smif interest total revenues 5 524586
i got not exists , not in queries life of me cannot need.
i don't think need not exists; should suffice reexamine joins. since want summary of deposits every combination of deposit type , fiscal year type, consider cross join 2 type tables , left outer join deposit table. here's how might look:
-- sample data inferred question: declare @deposit table ( depositid bigint, fiscalyeartypeid bigint, deposittypeid bigint, transactionstatustypeid bigint, isprojecteddeposit bit, depositamount money ); insert @deposit values (1, 1, 1, 3, 0, 3434), (2, 3, 1, 3, 0, 52152), (3, 4, 1, 3, 0, 12859), (4, 5, 1, 3, 0, 542863), (5, 5, 4, 3, 0, 524586), -- edit: added last line test transactionstatustypeid/isprojecteddeposit restrictions. (6, 2, 4, 1, 0, 9000); declare @deposittype table ( deposittypeid bigint, deposittypename varchar(32) ); insert @deposittype values (1, 'auction'), (2, 'reserve sale'), (4, 'smif interest'); declare @fiscalyeartype table (fiscalyeartypeid bigint); insert @fiscalyeartype values (1), (2), (3), (4), (5); -- query: select dt.deposittypeid, dt.deposittypename deposittype, 'total revenues' transactiongrouptype, fyt.fiscalyeartypeid, coalesce(sum(d.depositamount), 0) amount @deposittype dt cross join @fiscalyeartype fyt -- edit: applying deposit checks part of join rather in clause. left join @deposit d on dt.deposittypeid = d.deposittypeid , fyt.fiscalyeartypeid = d.fiscalyeartypeid , d.transactionstatustypeid = 3 , d.isprojecteddeposit = 0 dt.deposittypeid in (1, 2, 4) group dt.deposittypename, fyt.fiscalyeartypeid, dt.deposittypeid order fyt.fiscalyeartypeid, dt.deposittypeid; results:
deposittypeid deposittype transactiongrouptype fiscalyeartypeid amount 1 auction total revenues 1 3434.00 2 reserve sale total revenues 1 0.00 4 smif interest total revenues 1 0.00 1 auction total revenues 2 0.00 2 reserve sale total revenues 2 0.00 4 smif interest total revenues 2 0.00 1 auction total revenues 3 52152.00 2 reserve sale total revenues 3 0.00 4 smif interest total revenues 3 0.00 1 auction total revenues 4 12859.00 2 reserve sale total revenues 4 0.00 4 smif interest total revenues 4 0.00 1 auction total revenues 5 542863.00 2 reserve sale total revenues 5 0.00 4 smif interest total revenues 5 524586.00 (edited correct bug in original version of answer.)
