|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
|
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
|
Click Here
|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
|
| Any good ideas for SQL on how to compare Quarter by Quarter? |
| Author |
Message |
|
|
| Posted : 2007-06-20 00:33:40 |
I was thinking about running a report that would show me all the activity in the last 3 months or the last 12 months, what would be the easiest way to do that?
Like
Last 3 months: xxx.xx
3 months before that xxx.xx
so that I could calculate a percentage and see if their sales are going up or down. any ideas? |
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-06-20 03:06:18 |
Ok, here goes:
sp_report ProfitAndLossPrevYearComp show Text, Label,
Amount_1 as "Jul-Sep", AmountPreviousPeriod_1 as "Apr-Jun",
AmountPreviousPeriodChange_1 as "$ Change", PercentPreviousPeriodChange_1 as "% Change",
Amount_2 as "Oct-Dec", AmountPreviousPeriod_2 as "Jul-Sep",
AmountPreviousPeriodChange_2 as "$ Change", PercentPreviousPeriodChange_2 as "% Change",
Amount_3 as "Jan-Mar", AmountPreviousPeriod_3 as "Oct-Dec",
AmountPreviousPeriodChange_3 as "$ Change", PercentPreviousPeriodChange_3 as "% Change",
Amount_4 as "Apr-Jun", AmountPreviousPeriod_4 as "Jan-Mar",
AmountPreviousPeriodChange_4 as "$ Change", PercentPreviousPeriodChange_4 as "% Change"
parameters DateMacro = 'ThisYear', SummarizeColumnsBy = 'Quarter', DisplayReport='Yes'
I've added the DisplayReport='Yes' parameter so you can sort out the labels for your financial year. In Australia the financial year starts July 1 and ends June 30. |
|
|
|
|
|
| Posted : 2007-06-20 03:28:20 |
Wow, that certainly has me sold with purchasing the software, that is amazing, I was unaware you had custom queries like that but that is not what I am looking for. I meant more like "Select * from Invoices Where customerreflistID='dhdhdhdhdhd-12823828' and txndate=>2006-05-01 and txndate=<2006-08-01"
So that I would then have all the orcers they placed between may and august.
As for your sp_report ProfitAndLossPrevYearComp do you have a section where I could read about your predefined reports? |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-06-20 04:12:43 |
Well the last one was about the company, but we can do the same thing for customers:
sp_report SalesByCustomerSummary show Label,
Amount_1 as "Jul-Sep", AmountPreviousPeriod_1 as "Apr-Jun",
AmountPreviousPeriodChange_1 as "$ Change", PercentPreviousPeriodChange_1 as "% Change",
Amount_2 as "Oct-Dec", AmountPreviousPeriod_2 as "Jul-Sep",
AmountPreviousPeriodChange_2 as "$ Change", PercentPreviousPeriodChange_2 as "% Change",
Amount_3 as "Jan-Mar", AmountPreviousPeriod_3 as "Oct-Dec",
AmountPreviousPeriodChange_3 as "$ Change", PercentPreviousPeriodChange_3 as "% Change",
Amount_4 as "Apr-Jun", AmountPreviousPeriod_4 as "Jan-Mar",
AmountPreviousPeriodChange_4 as "$ Change", PercentPreviousPeriodChange_4 as "% Change"
parameters DateMacro = 'ThisYear', SummarizeColumnsBy = 'Quarter'
Again, add the DisplayReport='Yes' parameter if you need sort out the labels for your financial year. In Australia the financial year starts July 1 and ends June 30.
See: How do I use the QuickBooks Reporting Engine with QODBC? I've heard something about sp_report ? for more. |
|
|
|
|
|
| Posted : 2007-06-22 00:50:00 |
| I appreciate the use of Stored procedures but I am much more comfortable with SQL Queries, and I can not get your date functions to work. All I want is how much a customer bought in the last 3 months so I can show it in a webpage, no needs for the fancy %'s that is something I can figure out. |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-06-22 03:36:20 |
|
|
|
|