quote:

---

Originally posted by tduggan

If your data already was corrected with this new format, then this would work for future INSERTs:

INSERT INTO Table1 (Column1)
SELECT CONVERT(VARCHAR(2), DATEPART(m, GETDATE())) + '-' + SUBSTRING(CONVERT(VARCHAR(4), DATEPART(yy, GETDATE())), 3, 2) + '-' + CONVERT(VARCHAR(3), COUNT(Column1) + 1)
FROM Table1
GROUP BY SUBSTRING(Column1, 1, 5)

As far as fixing your data for this new format, I can only figure it out with a loop. Hopefully someone else can figure out a set-based method for you. Or is the data already "fixed"?

Tara

---

quote:

---

Originally posted by jsmith8858

I would ask: what is the significance of the numbering? Why are you storing this in the table? what are you using it for? What is the primary key of this table, and what kind of data is stored in it?

- Jeff

---

DECLARE @num INT
DECLARE @WhichMonth INT
DECLARE @WhichYear INT
DECLARE @WhichOne VARCHAR(50)

SET @num = 1

WHILE (SELECT COUNT(*) FROM Table1 WHERE ColumnA NOT LIKE '%-%') > 0
BEGIN

	SELECT @WhichOne = ColumnA 
	FROM Table1 
	WHERE ColumnA NOT LIKE '%-%' AND ColumnB = (SELECT MIN(ColumnB) FROM Table1 WHERE ColumnA NOT LIKE '%-%')

	SELECT @WhichMonth = DATEPART(m, ColumnB), @WhichYear = DATEPART(yy, ColumnB)
	FROM Table1
	WHERE ColumnA = @WhichOne

	UPDATE Table1
	SET ColumnA = CASE 
			WHEN DATEPART(m, ColumnB) < 10 THEN '0' + CONVERT(VARCHAR(2), DATEPART(m, ColumnB)) 
			ELSE CONVERT(VARCHAR(2), DATEPART(m, ColumnB)) 
		      END 
		      + '-' + SUBSTRING(CONVERT(VARCHAR(4), DATEPART(yy, ColumnB)), 3, 2) + '-' + 
		      CASE
			WHEN @num < 10 THEN '00'+ CONVERT(VARCHAR(3), @num)
			WHEN @num < 100 THEN '0'+ CONVERT(VARCHAR(3), @num)
			ELSE CONVERT(VARCHAR(3), @num)
		      END
	WHERE ColumnA = @WhichOne

	IF @WhichMonth = (SELECT DATEPART(m, MIN(ColumnB)) FROM Table1 WHERE ColumnA NOT LIKE '%-%')
		AND @WhichYear = (SELECT DATEPART(yy, MIN(ColumnB)) FROM Table1 WHERE ColumnA NOT LIKE '%-%')
		SET @num = @num + 1
	ELSE
		SET @num = 1

END

update a
set orderid = cast(datepart(mm, a.orderdate) as varchar) +  '-' + 
        substring(cast(datepart(yy, a.orderdate) as varchar),3,2) + '-' + 
	cast(((select count(*) from #orders b
		where 
		datepart (yy, b.orderdate) = datepart(yy, a.orderdate)
		and datepart (m, b.orderdate) = datepart(m, a.orderdate)
		and b.orderdate < a.orderdate) + 1) as varchar)
from #orders a

quote:

---

Originally posted by Oliver Harris

I agree it would definately be worth it. If you guys don't mind helping a newbie I'd love to learn. I'm afraid you already lost me on some of the terminology though. What are DDL and DML statements?

---

quote:

---

Originally posted by mohdowais

Oliver, right click on the table in Enterprise Manager, and choose "All tasks -> Generate SQL Script" to generate the DDL for the table. It's a good way to learn how an existing table translates into DDL and how you can create tables using Query Analyzer. Unfortunately, there is no way to generate DML (for the sample data) statements from EM, but there are a few third party tools that could do the trick.

Owais

We make a living out of what we get, but we make a life out of what we give.

---