Year, Month, Count on an Invoices Table

[DaBrain]

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

[Plamen Ratchev]

Re: Year, Month, Count on an Invoices Table

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

[--CELKO--]

Re: Year, Month, Count on an Invoices Table

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.