Get New Month - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Get New Month

Author

Topic

FLGirl
Starting Member

3 Posts

Posted - 2005-10-19 : 00:33:00

I have a table which has the following data
Year | Month | Number
------------------
5 | 8 | 0001
5 | 8 | 0002
5 | 9 | 0001
5 | 9 | 0002
5 | 9 | 0003
5 | 9 | 0004

When the month of October is the current month, I need to insert the new row and restart the count for the Number column to "1".
5 | 10 | 0001

How can I do this, below is what I started?

-- Get the Max Month for the curent year sent to sp
SELECT MAX(month) AS checkMonth
INTO #tsalesnumber
FROM tblsalesnumber
WHERE year = 5

IF (SELECT checkMonth
FROM #tsalesnumber
WHERE checkMonth = DATEPART(mm,getdate())) = 1
PRINT 'New Month'
-- Insert a New increment for the month starting with 1

ELSE
PRINT 'Same Month'
-- The New Sales Number
SELECT MAX(CAST(REPLACE(STR(number,4),' ','0') AS char(4))) AS newNum
FROM tblsalesnumber
WHERE year = 5
AND month = 10

SELECT DATEPART(mm,getdate()) AS NewMonth,
checkMonth
FROM #tblsalesnumber

DROP TABLE #tblsalesnumber

Thank you!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-19 : 01:12:30

Where do you want to show this data?
If it is Reports then make use of RecordNumber feature and reset it to every month

Otherwise try this

Select [year],[Month],(select count(*) from yourTable where [Month]=[T.Month]) as count
from yourTable T

Madhivanan

Failing to plan is Planning to fail

FLGirl
Starting Member

3 Posts

Posted - 2005-10-19 : 01:28:39

I just need to create a new sales number i.e 05100001.

I need to do the following steps:
1. Get the current month
2. Match it with the MAX number month
3. If the Max number month is less than the current month, NEW MONTH
4. With the new month insert a new record with the New month, and number starting all over at 0001

I hope this helps.

Thank you!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-19 : 01:41:07

Did you run the query I suggested?

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-10-19 : 01:49:50

Hope this work for you. .

Select IsNull(Max(Number),0)+1 From Tbl Where Month(GetDate()) = (Select Max(Month) From Tbl) And Year = 5

Complicated things can be done by simple thinking

FLGirl
Starting Member

3 Posts

Posted - 2005-10-21 : 01:44:55

Thank you so much both of you. They worked out jest perfectly.

You are wonderful!