472,981 Members | 1,436 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,981 software developers and data experts.

Year, Month, Count on an Invoices Table

I am trying to make a query on one table, an invoices table, and I
want to see how many orders are in each month

Year Month Count
2006 01 80
2006 02 110
2006 03 208
I cant just do Distinct MonthPart because we have years, so to get
these three columns, is just beyond my SQL skills.
Any suggestions or pointer would be greatly appreciated!!!!

Mar 4 '07 #1
2 7145
It is not very clear what is the format of your table, but assuming you have
table Invoices with column OrderDate (datetime data type), something like
this should give you what you need:

SELECT Year(OrderDate) AS OrderYear,
Month(OrderDate) AS OrderMonth,
Count(*) AS OrdersCount
FROM Invoices
GROUP BY Year(OrderDate), Month(OrderDate)


Plamen Ratchev

Mar 4 '07 #2
SQL is a set-oriented language, not a computational language. Create
a table of reporting periods and use a BETWEEN predicate:

CREATE TABLE ReportPeriods
(period_name CHAR(7) NOT NULL PRIMARY KEY,
CHECK (start_date < end_date));

INSERT INTO ReportPeriods VALUES ('2007-01', '2007-01-01', '2007-01-31

Now adjust the ranges to account for holidays, promotions etc.

Mar 4 '07 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: hartley_aaron | last post by:
Hi, I was wondering if it was possible to build a query that will include a column that will provide a count related records from another table. Although there is a way to achieve this through...
by: Zero.NULL | last post by:
Hi, We are using Month-year tables to keep the history of long transaction of our application. For example: We capture the details of a certain action in table...
by: Fendi Baba | last post by:
The function is called from opencalendar(targetfield). Thanks for any hints on what could be the problem. .............................................................. var...
by: Laguna | last post by:
Hi Gurus, I want to find the expiration date of stock options (3rd Friday of the month) for an any give month and year. I have tried a few tricks with the functions provided by the built-in...
by: Catherine | last post by:
I am trying to create a report that will give me q total count by month to date, and then another by year to date. Example: Discharges By Type Month To Date Year To Date...
by: Burghew | last post by:
Thanks for the previous replies..... What I need is to generate invoices for customers whom we rent out equipment. I will be choosing the date and year say for December 2005 print the invoices...
by: crane.jake | last post by:
Hi, I'm trying to find the following information from the table found bellow. Year_Sales - # of sales that have occurred this year Year_Income - SUM(amount) Month_Sales - # of sales that have...
by: yellowarmy | last post by:
Theres two problems i have. 1) Theres a question i have to answer in a mock is that the company does invoices every month but i need it for to select invoice and then select the month whcih would...
by: uninvitedm | last post by:
Heya I've got a table of invoices, which have dates and customer_id's. What I need to get is the number of occurances for this customer for a 12-month range window. For example, if there's an...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.