471,066 Members | 1,288 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,066 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 6929
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)

HTH,

Plamen Ratchev
http://www.SQLStudio.com

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,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date));

INSERT INTO ReportPeriods VALUES ('2007-01', '2007-01-01', '2007-01-31
23:59:59.99);
etc.

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

Mar 4 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by hartley_aaron | last post: by
8 posts views Thread by Zero.NULL | last post: by
20 posts views Thread by Laguna | last post: by
2 posts views Thread by Catherine | last post: by
reply views Thread by leo001 | last post: by

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.