Month() problem

[konczuras]

Hello,
I've the following problem:

I need to insert to a table the following customer data:
Year No., Month No., CustomerName, CustomerID... The problem is with the Month No. (MonthNo is the table column name). I'm using the following command, in a stored procedure, after the insertion.

SET @MonthNo = (SELECT COUNT (*) FROM Customers, Tools WHERE Customers.CustomerID = Tools.CustomerID AND MONTH(Tools.DeployDate) = MONTH(GETDATE()))

But it doesn't work perfectly. When it comes to the next month, it inserts zeros. How could I work this out properly?

Thank you in advance!

[DB_err]

I am not aure exactly what you mean.

Do you want to have some kind of a loop to go through a series of months, i.e. Jan through Dec and compares those months with the DeployDate field?

Right now you are always comparing the DeployDate field with the current system date. You may want to for example use the dateadd function.

DATEADD(month, NumberOfMonthstoAdd, getdate())

Sorry I haven't had a chance to test this code.

[konczuras]

Thank you for your reply!

I'm trying to achieve a monthly number to every customer. A little sample:

YearNo MonthNo CustomerName
--JANUARY
1 1 XY
2 2 ZZ
--FEBRUARY
3 1 AB
4 2 CD

And I want to automate the process. So when the system date month turns to eg. February, the program would restart counting from 1.

Hope I managed to made myself clear, any suggestions?

Thanks in advance!