use tempdb
go
create table #stores
(store_id int,
store_name nvarchar(20))
 
create table #reports
(report_id int,
store_id int,
[daily sales] int,
[daily visits] int,
[daily quessions] int)
 
insert into #stores(store_id,store_name)
values(1,'name a'),(2,'name b'),(3,'name c'),(4,'name d');
 
insert into #reports(report_id,store_id,[daily sales],[daily visits],[daily quessions])
values (1,1,11457,15,47),(2,1,658,9,58),(3,3,547,8,1),(4,2,1247,24,64);


/*first need us 6 declare for showing our result and quering our database*/
declare @store_id int,
@store_name nvarchar(20),
@daily_sales int,
@daily_visits int,
@daily_quessions int,
@message nvarchar(100)
 
 
/*select_id is our first cursor for giving store_id and store_name from our #stores table*/
declare select_id cursor for
  select store_id,store_name from #stores order by 1
  open select_id
  /*insert into variables @store_id,@store_name from result our fist cursor*/
  fetch next from select_id into @store_id,@store_name
  while @@FETCH_STATUS=0
     
    
  begin
   SELECT @message = '----- sum of this store: ' +  
        RTRIM(cast(@store_id as nchar(20)))
 
    PRINT @message
  /*select_sum is our second cursor for giving sum[daily sales],sum([daily visits]),sum([daily quessions]) from our #stores table*/
   declare select_sum cursor for
   select isnull(sum([daily sales]),0),
   isnull(sum([daily visits]),0),
   isnull(sum([daily quessions]),0) from #reports where store_id =@store_id
   group by store_id
      open select_sum
      /*inserting result our second cursor into variables @daily_sales,@daily_visits,@daily_quessions*/
   fetch next from  select_sum into @daily_sales,@daily_visits,@daily_quessions
    while @@FETCH_STATUS=0
    begin
    /*printing our result */
    select @message= RTRIM(cast(@store_id as nchar(20))) + ' /'
     + RTRIM(cast (@store_name  as nchar(10))) +' /'
      + RTRIM(cast (@daily_sales  as nchar(10))) + '/ '
       + RTRIM(cast (@daily_visits  as nchar(10))) + '/ '
        + RTRIM(cast (@daily_quessions  as nchar(10)))
    print @message
  fetch next from select_sum into @daily_sales,@daily_visits,@daily_quessions
    end
    fetch next from select_id into @store_id,@store_name
    close select_sum
    deallocate select_sum
  end
  /*closing and deleting our cursors from cache*/
  close select_id;
  deallocate select_id;