Formatting Dates - Feb 05 is actually Jan 05

Johnny R

Hi everyone,

May I ask for some help with the following SQL Statement.

I have Master / Detail DB (Master = Invoices and Detail = Items). I would like to make a report that lists the SUM of all item costs for a particular month and year. For example, if I have 3 invoices for February 2005, that total ??305, 6 invoices for November 2005 that total ??4698 and 5 invoices for January 2006 that total ??3971. I would like my results to be listed similar to ??¦.

February	2005	??305
November	2005	??4698
January	    2006	??3971

My SQL statement is (simplified for clarity) ??¦

SELECT
       MONTH(Invoices.InvoiceDate) AS "Month",
       YEAR(Invoices.InvoiceDate) AS "Year",
       SUM(Items.ItemIncTax) AS ItemIncTax
FROM   Invoices INNER JOIN Items ON Invoices.InvoicePri = Items.ItemToInvioce
GROUP BY MONTH(Invoices.InvoiceDate), YEAR(Invoices.InvoiceDate)
ORDER BY YEAR(Invoices.InvoiceDate), MONTH(Invoices.InvoiceDate);

When I execute the query, I get the following data ??¦

"MonthNumber"	"Year"		ItemIncTax  
2			2005		305     
3			2005		423
4			2005		5976
5			2005		8640
6			2005		361
7			2005		464
8			2005		231
9			2005		2989
10			2005		1577
11			2005		4698
12			2005		1306
1			2006		3971

This is pretty much exactly what I need. However, I would like the ???MonthNumber??? field to display the MonthName. No problem, I???ll just format the TextBox in the report to a Date and enter ???mmmm??? as the Format String. But this is where things get weird.

When I format the results in my report my data now appears like this ??¦.

"MonthNumber"	"Year"		ItemIncTax  
January		2005		305
January		2005		423
January		2005		5976
January		2005		8640
January		2005		361
January		2005		464
January		2005		231
January		2005		2989
January		2005		1577
January		2005		4698
January		2005		1306
December		2006		3971

That is, every month in each year is now displayed as January, unless that month really is January ??? in that case January actually gets displayed as December.

Looking at things a different way, I try to do my formatting in the SQL statement Like this ??¦

SELECT
       FORMAT(MONTH(Invoices.InvoiceDate),"mmmm") AS ???MonthName???,
       YEAR(Invoices.InvoiceDate) AS "Year",
       SUM(Items.ItemIncTax) AS ItemIncTax
FROM   Invoices INNER JOIN Items ON Invoices.InvoicePri = Items.ItemToInvioce
GROUP BY MONTH(Invoices.InvoiceDate), YEAR(Invoices.InvoiceDate)
ORDER BY YEAR(Invoices.InvoiceDate), MONTH(Invoices.InvoiceDate);

However, this makes no difference to my results, February 2005 still = January 2005 and January 2006 still = December 2006.

What have I done wrong ? without any formatting I get the results I want for my aggregate, but my report would be so much clearer if I could use Month Names rather than Month Numbers.

Would someone show me the error of my ways please ?

Many thanks
John.

Using ??¦

Access 2002 Tables
Delphi 7 Pro
Fast Reports 4.4 Pro

Johnny R

Getting a bit closer.


IF I change my SQL statement to the following ...

SELECT
       FORMAT(Invoices.InvoiceDate,'mmmm') AS [Month],
       FORMAT(Invoices.InvoiceDate,'yyyy') AS [Year],
SUM(Items.ItemIncTax) AS ItemIncTax
FROM Invoices INNER JOIN Items ON Invoices.InvoicePri = Items.ItemToInvioce
GROUP BY FORMAT(Invoices.InvoiceDate,'yyyy'), Format(Invoices.InvoiceDate,'mmmm');

I get the results with the correct Month Names ...

April   2005    5976
August  2005    231.
December2005    1306
February2005    305.
July    2005    464.
June    2005    361.
March   2005    423.
May     2005    8640
November2005    4698
October 2005    1577
September2005    298
April   2006    2149
August  2006    1969
December2006    280.

I'm guessing the FORMAT function converts the dates to a 'String' and has ordered things alphabetically.

Johnny R.