Member Avatar for 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!

  • 2 Contributors
  • 2 Replies
  • 124 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by konczuras
Member Avatar for 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.

Member Avatar for 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!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.