Good Morning, I am new to SQL and i was given a stored proc, Now i need to add additional SQL to this stored proc however, it seems to be made up of tables and i am unsure how to proceed. the code i need to update
the SQL i need to add is here
SELECT bh.billinginvoiceheaderpk, bh.chargeamount, b.billingchargecodepk, bh.invoicenumber, bh.contractfk, bh.remainingbalanceamount, bh.invoiceduedate,
bh.contract_number
FROM ch_billingchargecode AS b INNER JOIN
ch_billinginvoiceheader AS bh ON b.billingchargecodepk = bh.billingchargecodefk
WHERE (b.billingchargecodeid = 'SREC SALE')
fairly easy but when i look at the stored proc so confused, If i add it at the end i am unable to still query my fields this is the stored proc any advise on how to add or modify would be appreciated thanks
/****** Object: StoredProcedure [dbo].[sp_GetARAgingCP] Script Date: 01/10/2017 11:33:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- exec sp_GetARAgingcp '6,7','N','12/31/2013'
-- select * from ch_artranstype
ALTER procedure [dbo].[sp_GetARAgingCP]
(
@corppk varchar(50),
@open_invoices_only varchar(50),
@asofdate datetime
)
as
--=================================================
create table #corppk_table (corppk int)
declare @input as varchar(1000) = @corppk
declare @current as varchar(50)
if LEN(@input)>0 and RIGHT(@input,1)<>','
set @input=@input+','
while LEN(@input)>1
begin
set @current=LEFT(@input,charindex(',',@input)-1)
insert into #corppk_table (corppk) select @current
--print 'Current:'+@current
set @input = SUBSTRING(@input,charindex(',',@input)+1,LEN(@input)-charindex(',',@input))
--print 'Input:'+@input
end
--select * from #corppk_table
declare @corpfk bigint
declare @checkdate datetime
set @checkdate = GETDATE()
create table #suspensebalances
(
corpfk bigint,
invoicenumber bigint,
nacustomerfk bigint,
-- contractfk bigint,
tosuspense [numeric](13, 2) ,
fromsuspense [numeric](13, 2) ,
suspensebalance [numeric](13, 2)
)
create table #tempnetbalances
(
corpfk bigint,
invoicenumber bigint,
recordtype varchar(20) ,
chargeamount [numeric](13, 2) ,
invoiceamount [numeric](13, 2) ,
taxamount [numeric](13, 2) ,
glpostdate datetime null ,
invoiceduedate datetime null ,
invoiceeffectivedate datetime null,
nacustomerfk bigint,
contractfk bigint,
billingchargecodefk bigint ,
lastpmtdate datetime null)
-- invoice
insert into #tempnetbalances (corpfk,invoicenumber,recordtype,chargeamount,invoiceamount,taxamount,
glpostdate,invoiceduedate,invoiceeffectivedate,nacustomerfk,contractfk,billingchargecodefk)
select
bih.corpfk,
bih.invoicenumber,
'invoice',
isnull(sum(bih.chargeamount),0) ,
isnull(sum(bih.remainingbalanceamount),0) ,
isnull(sum(bih.taxesamount),0) ,
bih.glpostdate,
bih.invoiceduedate,
bih.invoiceeffectivedate,
--ch.nacustomerfk ,
case when bih.onetimechargefk IS null then ch.nacustomerfk else otch.nacustomerfk end as nacustomerfk,
bih.contractfk,
MAX(bih.billingchargecodefk)
from ch_billinginvoiceheader bih
left outer join ch_contracts ch on ch.contractpk = bih.contractfk
left outer join ch_onetimecharges otc on otc.onetimechargepk = bih.onetimechargefk
left join ch_onetimechargesheader otch on otch.onetimechargeheaderpk = otc.onetimechargeheaderfk
join ch_corp cp on cp.corppk = bih.corpfkwhere
cp.corppk in (select corppk from #corppk_table) and bih.glpostdate < = @asofdate
-- cp.corpid in (@corpid)
group by bih.corpfk,bih.invoicenumber,bih.glpostdate,bih.invoiceduedate,bih.invoiceeffectivedate,case when bih.onetimechargefk IS null then ch.nacustomerfk else otch.nacustomerfk end ,
-- nacustomerfk,
bih.contractfk
order by bih.corpfk,bih.invoicenumber desc,bih.glpostdate
-- transactions -----------------------------------------
insert into #tempnetbalances (bih.corpfk,bih.invoicenumber,recordtype,chargeamount,
invoiceamount,taxamount,lastpmtdate,nacustomerfk,contractfk,billingchargecodefk,invoiceduedate,invoiceeffectivedate )
select
bih.corpfk,
abt.invoicenumber,
artt.artranstypeid ,
sum(isnull(-art.amount,0)),
case when artt.artranstypeid <> 'TO SUSPENSE' then sum(isnull(-art.amount,0))else 0 end ,
0 ,
abt.glpostdate,
abt.nacustomerfk,
ch.contractpk,
MAX(bih.billingchargecodefk),
bih.invoiceduedate,
bih.invoiceeffectivedate
from ch_artransdetail art
join ch_billinginvoiceheader bih on bih.billinginvoiceheaderpk = art.billinginvoiceheaderfk
join ch_arbatchdetail abt on abt.arbatchdetailpk = art.arbatchdetailfk
join ch_arbatchheader bh on bh.arbatchheaderpk = abt.arbatchheaderfk
join ch_artranstype artt on artt.artranstypepk = abt.artranstypefk
join ch_corp cp on cp.corppk = bih.corpfk
left join ch_contracts ch on ch.contractpk = bih.contractfk
where
cp.corppk in (select corppk from #corppk_table) and bh.glpostdate < = @asofdate
--cp.corpid in (@corpid)
and (abt.reversal is null or abt.reversal = 'N') and bh.arprocessstatusfk = 2 and artt.artranstypeid <> 'TO SUSPENSE'
group by bih.corpfk,
abt.invoicenumber,
artt.artranstypeid,
abt.nacustomerfk,
abt.glpostdate,
ch.contractpk,
bih.invoiceduedate,
bih.invoiceeffectivedate
--to suspense
insert into #tempnetbalances (bih.corpfk,bih.invoicenumber,recordtype,chargeamount,
invoiceamount,taxamount,lastpmtdate,nacustomerfk,contractfk,billingchargecodefk,invoiceduedate,invoiceeffectivedate )
select
bh.corpfk,
abt.invoicenumber,
artt.artranstypeid ,
sum(isnull(-art.amount,0)),
0,
-- case when artt.artranstypeid in ('TO SUSPENSE','PAY SUSPENSE') then sum(isnull(-art.amount,0))else 0 end ,
0 ,
abt.glpostdate,
abt.nacustomerfk,
null,
null,
null,
null
--bh.transdate,
--bh.transdate
from ch_artransdetail art
join ch_arbatchdetail abt on abt.arbatchdetailpk = art.arbatchdetailfk
join ch_arbatchheader bh on bh.arbatchheaderpk = abt.arbatchheaderfk
join ch_artranstype artt on artt.artranstypepk = abt.artranstypefk
join ch_corp cp on cp.corppk = bh.corpfk
where
cp.corppk in (select corppk from #corppk_table) and bh.glpostdate < = @asofdate
--cp.corpid in (@corpid)
and (abt.reversal is null or abt.reversal = 'N') and bh.arprocessstatusfk = 2 and artt.artranstypeid in ('TO SUSPENSE' ,'PAY SUSPENSE')
group by bh.corpfk,
abt.invoicenumber,
artt.artranstypeid,
abt.nacustomerfk,
abt.glpostdate,
bh.transdate
-- suspense table ----------------------------------------------------------------
insert into #suspensebalances (corpfk,invoicenumber,nacustomerfk,tosuspense,fromsuspense,suspensebalance )
select
bh.corpfk,
0,
abt.nacustomerfk,
case when artt.artranstypeid = 'TO SUSPENSE' then sum(isnull(art.amount,0))else 0 end ,
--case when artt.artranstypeid = 'FROM SUSPENSE' then sum(isnull(art.amount,0))else 0 end ,
0,
0-- case (when artt.artranstypeid = 'TO SUSPENSE' then sum(isnull(art.amount,0))else 0 -
-- when artt.artranstypeid = 'FROM SUSPENSE' then sum(isnull(art.amount,0))else 0) end
from ch_artransdetail art
join ch_arbatchdetail abt on abt.arbatchdetailpk = art.arbatchdetailfk
join ch_arbatchheader bh on bh.arbatchheaderpk = abt.arbatchheaderfk
join ch_artranstype artt on artt.artranstypepk = abt.artranstypefk
join ch_corp cp on cp.corppk = bh.corpfk
where
cp.corppk in (select corppk from #corppk_table) and bh.glpostdate < = @asofdate
--cp.corpid in (@corpid)
and (abt.reversal is null or abt.reversal = 'N')
and bh.glpostdate is not null and artt.artranstypeid in ( 'TO SUSPENSE' )
group by bh.corpfk,abt.invoicenumber,abt.nacustomerfk,artt.artranstypeid
-- select cnam.commonname, tp.* from #suspensebalances tp
-- join ch_names_customer namc on namc.nacustomerpk = tp.nacustomerfk
--join ch_names cnam on cnam.napk = namc.nafk
---- select * from #tempnetbalances where recordtype = 'PAY SUSPENSE'
select
cp.corpid,
max(cnam.namecode)namecode,
max(cnam.companyname)companyname,
max(cnam.primaryphone)phone,
isnull(ch.contractid,'') contract_id,
isnull(max(bc.billingchargecodeid),'')invoicetype,
bih.invoicenumber,
--bih.recordtype,
MAX(bih.invoiceduedate)invoicedate,
MAX(bih.invoiceduedate)effectivedate,
isnull(SUM(case when recordtype = 'invoice' then bih.invoiceamount end),0) as invoiceamount,
isnull(SUM(case when recordtype = 'PAYMENT' then bih.invoiceamount end),0) as payments,
isnull(SUM(case when recordtype = 'credit' then bih.invoiceamount end),0) as credits,
isnull(SUM(case when recordtype = 'TO SUSPENSE' then bih.chargeamount end),0) as tosuspense,
--max(tmpsus.tosuspense)tosuspense,
isnull(SUM(case when recordtype = 'FROM SUSPENSE' then bih.invoiceamount end),0) as fromsuspense,
isnull(SUM(case when recordtype = 'PAY SUSPENSE' then bih.invoiceamount end),0) as paysuspense,
--isnull(SUM(case when recordtype not in ( 'invoice','Credits','PAYMENT''To suspense','PAY SUSPENSE') then bih.invoiceamount end),0) as other,
isnull(SUM(taxamount),0 )Taxes,
isnull(SUM(invoiceamount),0 )Rem_Due,
case when isnull(SUM(invoiceamount),0 ) = 0 then 0 else (DATEDIFF(day, bih.invoiceduedate ,@asofdate))end daysdelinquent,
isnull(sum(case when DATEDIFF(day, bih.invoiceduedate,@asofdate) < 0 then isnull((invoiceamount),0 ) end),0)Future,
isnull(sum(case when DATEDIFF(day, bih.invoiceduedate,@asofdate) >= 0 and DATEDIFF(day, bih.invoiceduedate,@asofdate)<= 30 then isnull((invoiceamount),0 ) end),0)[0-30],
isnull(sum(case when DATEDIFF(day, bih.invoiceduedate,@asofdate) >= 31 and DATEDIFF(day, bih.invoiceduedate,@asofdate)<= 60 then isnull((invoiceamount),0 ) end),0)[31-60],
isnull(sum(case when DATEDIFF(day, bih.invoiceduedate,@asofdate) >= 61 and DATEDIFF(day, bih.invoiceduedate,@asofdate)<= 90 then isnull((invoiceamount),0 ) end),0)[61-90],
isnull(sum(case when DATEDIFF(day, bih.invoiceduedate,@asofdate) > 90 then isnull((invoiceamount),0 ) end),0)[90+],
MAX(lastpmtdate)lastpmtdate,
@asofdate asofdate,
conam.companyname as company,
fac.facilityname,
ct.contactname
from #tempnetbalances bih
join ch_corp cp on cp.corppk = bih.corpfk
left join ch_names_customer namc on namc.nacustomerpk = bih.nacustomerfk
left join ch_names cnam on cnam.napk = namc.nafk
left join ch_contracts ch on ch.contractpk = bih.contractfk
left join ch_names_company namco on namco.nacompanypk =ch.nacompanyfk
left join ch_names conam on conam.napk = namco.nafk
left join ch_facility_information fac on fac.contractfk = ch.contractpk
left join ch_names_contacts ct on ct.contactpk = cnam.contactfk
left join ch_billingchargecode bc on bc.billingchargecodepk = bih.billingchargecodefk
--left join #suspensebalances tmpsus on tmpsus.corpfk = bih.corpfk and tmpsus.invoicenumber = bih.invoicenumber
group by
cp.corpid,
cnam.companyname,
conam.companyname,
fac.facilityname,
ct.contactname,
bih.invoiceeffectivedate,
bih.invoiceduedate,
bih.invoicenumber,
ch.contractid,
(DATEDIFF(day, bih.invoiceduedate ,@asofdate))
--bih.recordtype
-- check to show 0 invoices or not
HAVING (
@open_invoices_only = 'Y'
AND
SUM(invoiceamount) <> 0)
OR
(
@open_invoices_only = 'N'
--AND
-- SUM(invoiceamount)<> 0
)
order by
cp.corpid,
cnam.companyname,
bih.invoiceeffectivedate desc,
bih.invoicenumber ,
bih.invoiceduedate desc
--ch.contractid,
--bih.recordtype
drop table #tempnetbalances
return 0