Help on this Cross apply related query

Question

Help on this Cross apply related query

Viewing 5 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

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.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

buddhika45 SSC Enthusiast Points: 155 More actions · Answer 2

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

LutzM SSC Guru Points: 107049 More actions · Answer 3

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...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

WayneS SSC Guru Points: 95461 More actions · Answer 4

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

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

buddhika45 SSC Enthusiast Points: 155 More actions · Answer 5

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