sql - In-memory table multiple INNER JOIN -

i trying update report in ssrs. query build colleague using stored procedure, whom has left project , can't contact anymore. used in-memory table batches in order add aggregations.

the columns outputs are:

• batchid
• transformdate
• sourcesystem
• companytype
• max(b.footerlines) footertotallines
• max(src.totallines) sourcetotallines
• max(balancinglinestable.balancinglines) balancinglines
• sum(stg.totallines) stagingtotallines
• (cast(stg.company int) * 1000000) + b.batchid companycode

however need add column called balancing lines. developed query , works:

select count(0) "balancing lines" [source].[staging].[transactionline] t inner join  [source].[staging].headerline h on t.headerid = h.headerid h.batchid  = 1234* , h.sourcesysteminstance = 'abc' , accountnumber = '98765' *1234 should variable -> b.batchid , soursesysteminstance 'abc'->     b.sourcesystem  

but, stored procedure written collegue still foreign me. know how 'add' working query working stored procedure?

for clarity table used:

• @batches b
• headerline h
• footerline fl
• 'inner join table' balancinglinestable
• 'inner join table' src
• 'left join table' stg

the stored procedure:

use [source]  alter procedure  [dbo].[usp_getsomecounts] ( @startat datetime ) begin  set nocount on;  -- set default start date if none specified  select @startat = isnull(@startat, dateadd(dd, -7, getdate()))  -- use in-memory table batches specified period  declare @batches table(batchid int, transformdate datetime, headerid nvarchar(36), companytype nvarchar(30), sourcesystem nvarchar(4), footerlines int)  -- fill in-memory table batches insert @batches (      batchid      ,transformdate     ,headerid     ,companytype     ,sourcesystem     ,footerlines )     select          h.batchid         ,h.transformdate         ,h.headerid         ,companytypeid         ,h.sourcesysteminstance         ,isnull(fl.totaltransactionlines, 0)             staging.headerline h (nolock)     inner join         staging.footerline fl (nolock)     on         h.headerid = fl.headerid             h.batchdate >= @startat     order         h.batchid  /*  using in-memory table 'batches', count valid transactions in both source , staging     databases. include footer totals */ select       b.batchid     , b.transformdate     , b.sourcesystem     , b.companytype     , max(b.footerlines) footertotallines     , max(src.totallines) sourcetotallines     , max(balancinglinestable.balancinglines) balancinglines     , sum(stg.totallines) stagingtotallines     , (cast(stg.company int) * 1000000) + b.batchid companycode     @batches b  inner join (     select          b.batchid         ,b.headerid         ,count(0) balancinglines           @batches b  -- 'inner join table' balancinglinestable added     inner join         staging.transactionline t (nolock)         on b.headerid = t.headerid     inner join         staging.headerline h (nolock)         on t.headerid = h.headerid       h.batchid = b.batchid , h.sourcesysteminstance = b.sourcesystem , accountnumber = 399990     group          b.batchid         ,b.headerid      ) balancinglinestable on b.batchid = balancinglinestable.batchid  inner join (     select          b.batchid         ,b.headerid         ,count(0) totallines             @batches b     inner join         staging.transactionline t (nolock)     on         b.headerid = t.headerid             t.linestatus = 'n'     group          b.batchid         ,b.headerid ) src on b.batchid = src.batchid  left join (     select         b.batchid         ,b.headerid         ,mt.company         ,count(0) totallines             @batches b     inner join         [production].[dbo].[mutated_transactions] mt (nolock)     on         b.headerid = mt.headerid             mt.rowhaserror = 'n'     group          b.batchid         ,b.headerid         ,mt.company) stg on src.batchid = stg.batchid , src.headerid = stg.headerid  group      b.batchid     ,b.transformdate     ,b.sourcesystem     ,b.companytype     ,stg.company  order      b.transformdate desc     ,b.batchid 

end

wow.. after changing accoutnumber = 98765 '98765' got rid of error. resulting output still empty. appears forgot add [balancinglines] in ssrs column. yeah.. know..