Dynamics SL aged AR query

I need to develop a sql query that returns invoices that are more than 90 days overdue. I looked at the aged ar report but the query that it uses is a little confusing.  I also need to be able to export this data to excel so just exporting the aged AR report doesnt work well.  Can someone give me a query to return these invoice that can be used in SQL Server?

thanks
LVL 1
jlinggAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

James GlaubigerCo-FounderCommented:
Please post a sample output of the tables that the current report uses.  We can then build a query.  

1. Export a subset of data including column names to excel, use one sheet for each SQL table that we need to pull data from.

I can then build you a query.

0
DarkAztecCommented:
Hi, this is the SQL Query:

select refnbr,custid,* from ardoc where doctype='in' and duedate >= 01/01/2008

You must change the end date, in this sample this query will print all invoices that has a due date greater or equal than 01 january 2008.

0
Philippe BoulosSenior ConsultantCommented:
Since you need to be able to export this to excel, have you considered using sql reporting services?  One of the output formats is Excel.  Another option is to have the query used by SL transfered either into a procedure or a function.  I took the ps aged ar query and converted it into a sql function.  I took the standard aged ar and moved it into a stored procedure.  Both work great.  
fn08611.sql.txt
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Dynamics

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.