sql server - SQL Query - Multiple Table Join With Grouping Functions that Keep Branch Structure -

i have exhausted search solution , post question see if solution exists.

i need write report show debits , credits per branch. report needs show if branches have had no dbs or crs.

for simplicity have scaled down tables try highlight issue.

my first table holds branch data

branchno    branchname 1           main 2           mgorogoro 3           arusha 

my second table holds debit transactions

txid    narrative   amount  date        branchno 1       test 1      50.00   2014/11/26  1 2       test 2      20.00   2014/11/27  3 

i've written sql statement gives me results need:-

declare @get_dates cursor; declare @date varchar(10);  declare @tbl table                 (                     debitoutcount int,                      branchcode varchar(250),                     txdate varchar(10)                 ) --declare @tbl table(idx1 varchar(50), idx8 varchar(50), idx3 varchar(50))  set @get_dates = cursor      select debits_out.date debits_out group debits_out.date order debits_out.date  open @get_dates;  fetch next @get_dates @date;  while (@@fetch_status = 0)      begin          --insert @tbl select idx1, idx8, idx3 sessiondailycontrol idx1 = @sessionid         insert @tbl              select              (select isnull(sum(db_out.amount), 0) debits_out db_out b.branchno = db_out.branchno , db_out.date = @date) debitoutvalue,             cast(b.branchno varchar(10)) + ' ' + b.branchname [branch names],             @date             exbranches b          fetch next @get_dates @date     end   close @get_dates  deallocate @get_dates  select * @tbl 

the result in format need:-

debitoutcount   branchcode  txdate 50              1 main      2014/11/26 0               2 mgorogoro 2014/11/26 0               3 arusha    2014/11/26 0               1 main      2014/11/27 0               2 mgorogoro 2014/11/27 20              3 arusha    2014/11/27 

however, report tools , views cannot work above. have tried left joins - problem result set not keep branch numbers dates there 0 transactions. example:-

select      isnull(sum(b.amount), 0),     cast(a.branchno varchar(10)) + ' ' + a.branchname [branch names],     b.date  exbranches     left join debits_out b on a.branchno = b.branchno group b.date, a.branchno, a.branchname  order b.date, a.branchno, a.branchname 

returns:-

db_out  branch names    date 0.00    2 mgorogoro     null 50.00   1 main          2014/11/26 20.00   3 arusha        2014/11/27 

in join combinations try, cannot branches show branches each date in debits table.

is there fundamental concept have missed? need have query can run in view returns same data cursor statement. possible?

the idea generate possible combinations of branches , dates first:

create table exbranches(     branchno int,     branchname varchar(20) ) create table debits_out(     txid int,     narrative varchar(20),     amount decimal (6,2),     [date] date,     branchno int ) insert exbranches values (1, 'main'), (2, 'mgorogoro'), (3, 'arusha') insert debits_out values (1, 'test 1', 50.00, '20141126', 1), (2, 'test 2', 20.00, '20141127', 3);  branchdate as(     select         b.branchno,         b.branchname,         d.date     exbranches b     cross join (         select distinct [date] debits_out     )d )     select         isnull(debitoutcount,0),         cast(b.branchno varchar(10)) + ' ' + b.branchname branchname,         b.date     branchdate b     left join (         select             branchno,             [date],             sum(amount) debitoutcount         debits_out         group             branchno, [date]     )d          on d.branchno = b.branchno         , d.date = b.date order b.date, b.branchno asc  drop table exbranches drop table debits_out