June 15, 2010 at 11:57 am
Given below is the coding and the results of the query. The basic function of the query is to find out if an invoice has been paid for or not. Sometimes partial credits are offered to invoices and thus creating duplicate invoice lines (eg-invoice_no 2986089). This is fine since I need to see how much of the invoice was paid in cash and how much credit was applied to it.
Here's the isssue - When the sum of the 'Applied_Amt' column for a particular invoice equals the 'total_invoice_amt' the 'pd_balance' column should return the value as '0'. If the invoice is not fully applied for by means of cash or credits, only then the 'pd_balance' column should show me the deficit. Eg- on invoice_no 2986089 you can see there's cash payment for 0.03 and then two credit adjustments which in all sums up to 129.06 which is exactly the invoice_amt. In this case the pd_balance should be '0' but based on my current coding it's not. If you could suggest a way to correct this I would greatly appreciate it. So sorry for the long description.
Coding
select distinct
r.gp_custnmbr,
I.billto_cust_no,
p.site_no,
I.Invoice_no,
I.inv_date,
p.trantype_id,
I.total_invoice_amt,
pd.trantype_id,
pd.Applied_AMT,
pd.reversed,
a.pd_balance
from masdb..invoice_header I
left join masview..invoiceview p on p.invoice_no=I.invoice_no and p.cust_no=i.billto_cust_no
left join masdb..payment_header t on t.cust_no=i.billto_cust_no
left join (select distinct cust_no,orig_payctrl_no,invoice_no, trantype_id, sum(applied_amt)Applied_AMT, reversed
from masview..paymentdetail where reversed ='no'ANDtran_date< '4/21/2010' and applied_amt<>'0' group by cust_no,invoice_no,trantype_id,reversed,orig_payctrl_no) pd on pd.invoice_no=I.invoice_no and pd.cust_no=I.billto_cust_no
left join masview..customersiteview r on r.cust_no=i.billto_cust_no
left join (select ref_payctrl_no,count(aroffset_gl)#_of_GLs from masdb..payment_header group by ref_payctrl_no)tt on tt.ref_payctrl_no=pd.orig_payctrl_no
cross apply(select case
when pd.applied_AMT is null then '0' else pd.Applied_amt
end as AMT) as c
cross apply
(select
case
when I.inv_date<'12/1/2009'and (pd.trantype_id is NULL) and p.trantype_id in ('Recur','manual') and I.total_invoice_amt<'400'
then I.total_invoice_amt+ amt
when P.inv_date>='12/1/2009' and (((pd.trantype_id is NULL) and (pd.Applied_AMT is null)) or ((pd.trantype_id is not NULL) and (pd.Applied_AMT <='0'))) and p.trantype_id in ('Recur','manual')and I.total_invoice_amt<'400'
then I.total_invoice_amt+ amt
else '0'
end as PD_balance) as a
where
I.inv_date <= '4/21/2010' and
I.billto_cust_no in ('2599660')
order by r.gp_custnmbr,p.site_no, I.invoice_no
Results are in the attached file. Thank you so much for you help in advance
June 15, 2010 at 1:37 pm
Please post table def, sample data and expected result based on the sample data as described in the first link in my signature. There are some people around (including me) who prefer testing the suggestions before posting.
June 15, 2010 at 4:04 pm
Thanks for taking a look at it. I attached sample data from all the tables and a view of the expected results. Hopefully this will help you understand the problem and help me out. Please let me know if the issue isn't clear enough. Thanks again
June 15, 2010 at 4:11 pm
It would be much more helpful if you could post your data in a ready to use format as described in the link I pointed you at.
There are some people around that won't be able to help you simply because you attached data in a format the don't have the software to open it...
June 15, 2010 at 5:18 pm
lmu92 (6/15/2010)
It would be much more helpful if you could post your data in a ready to use format as described in the link I pointed you at.There are some people around that won't be able to help you simply because you attached data in a format the don't have the software to open it...
buddhika45, I have to agree with Lutz here. Note that you posted this over 5 hours ago, and you haven't gotten any responses to your problem. Why is that? Because the people that help out here are all un-paid volunteers, and with their full-time job, they don't have the time to set up a test environment to work your problem - if they can't cut-and-paste code to set up the environment, they just move on. They go to posts where people do provide this information - which shows that the posters are considerate enough to not waste their time. So please HELP US HELP YOU. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 16, 2010 at 11:30 am
Sorry for not including table defs, I was going to update the post with table codes as well but then I figured out a solution to my problem. Thank you for offering to help guys, next time I'll make sure everything is provided.:-)
just in case if anyone is intersted, I'm posting the corrected code.
select distinct
r.gp_custnmbr,
I.billto_cust_no,
p.site_no,
I.Invoice_no,
I.inv_date,
p.trantype_id,
I.total_invoice_amt,
pd.trantype_id,
pd.Applied_AMT,
pd.reversed,
a.pd_balance
from masdb..invoice_header I
left join masview..invoiceview p on p.invoice_no=I.invoice_no and p.cust_no=i.billto_cust_no
left join masdb..payment_header t on t.cust_no=i.billto_cust_no
left join (select distinct cust_no,orig_payctrl_no,invoice_no, trantype_id, sum(applied_amt)Applied_AMT, reversed
from masview..paymentdetail where reversed ='no'ANDtran_date<'4/30/2010'and applied_amt<>'0' group by cust_no,invoice_no,trantype_id,reversed,orig_payctrl_no) pd on pd.invoice_no=I.invoice_no and pd.cust_no=I.billto_cust_no
left join masview..customersiteview r on r.cust_no=i.billto_cust_no
left join (select ref_payctrl_no,count(aroffset_gl)#_of_GLs from masdb..payment_header group by ref_payctrl_no)tt on tt.ref_payctrl_no=pd.orig_payctrl_no
left join (select distinct invoice_no,sum(applied_amt)Applied_AMT, reversed
from masview..paymentdetail where reversed ='no'ANDtran_date< '4/30/2010'and applied_amt<'0' group by invoice_no,reversed) cd on cd.invoice_no=I.invoice_no
cross apply(select case
when pd.applied_AMT is null then '0' else pd.Applied_amt
end as AMT) as c
cross apply
(select
case
when I.inv_date<'12/1/2009'and (pd.trantype_id is NULL)and I.total_invoice_amt<>'25' and p.trantype_id in ('Recur','manual') and I.total_invoice_amt<'400'
then I.total_invoice_amt+ amt
when P.inv_date>='12/1/2009' and (((pd.trantype_id is NULL) and (pd.Applied_AMT is null)) or ((pd.trantype_id is not NULL) and (pd.Applied_AMT ='0'))) and I.total_invoice_amt<>'25' and p.trantype_id in ('Recur','manual')
then I.total_invoice_amt+ amt
when P.inv_date>='12/1/2009' and (((pd.trantype_id is NULL) and (pd.Applied_AMT is null)) or ((pd.trantype_id is not NULL) and (pd.Applied_AMT <'0'))) and I.total_invoice_amt<>'25' and p.trantype_id in ('Recur','manual')and ((I.total_invoice_amt+cd.applied_amt)<>'0')
then I.total_invoice_amt+ amt
else '0'
end as PD_balance) as a
where
I.inv_date < '4/21/2010' and
I.billto_cust_no in ('2599660')
order by r.gp_custnmbr,p.site_no, I.invoice_no
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply