btw this will only allow 99 occurrances

Use a trigger like this

CREATE TRIGGER trg_test ON [Test] FOR INSERT AS

UPDATE t

SET t.eng_ref=t.eng_ref+RIGHT('0'+CAST(t.test_id as varchar),2)

FROM [Test] t

JOIN inserted i ON i.test_id=t.test_id

Gopal Singh (5/26/2011)

---

Hello ,

I have create table Test table with primary key id test_Id and auto increment when inserting a record in a table, then i add another field with...

LEFT(RefNo,PATINDEX('%[0-9]%',RefNo)-1)+

CAST(CAST(SUBSTRING(RefNo,PATINDEX('%[0-9]%',RefNo),255) as int)+1 as varchar)

SELECT a.NUMBER, a.[TYPE], a.DATESTAMP, b.ASSIGNMENT

FROM (SELECT ROW_NUMBER() OVER (PARTITION BY act.NUMBER ORDER BY act.THENUMBER ASC) AS [RowNum],

act.THENUMBER, act.NUMBER, act.[TYPE], act.DATESTAMP

FROM #tbl_ACTIVITY act WHERE act.[TYPE] <> 'Notification') a

JOIN...

calvo (5/19/2011)

---

I like the patindex idea, I'll have to keep that one in the ol' toolbox.

Here's what I came up with

declare @x varchar(50) = 'Jane Smith 05 18 2011 mamm.pdf'

select...

Providing that the month is the first 2 digits and the date is 10 chars long then this should work

ISDATE(REPLACE(SUBSTRING([column],PATINDEX('%[0-9][0-9]%',@x),10),' ','/'))

and

CAST(REPLACE(SUBSTRING([column],PATINDEX('%[0-9][0-9]%',@x),10),' ','/') as datetime)

WITH cte (PeriodID,StaffInitials)

AS (

SELECT p.PeriodID,s.StaffInitials

FROM (SELECT DISTINCT t.DayId,t.PeriodID FROM tTimeTable t WHERE t.DayID = 1) p

CROSS JOIN tStaffTimeTableInfo s

WHERE NOT EXISTS(SELECT * FROM tTimeTable t...

I think this will work but there must be a better way, just cannot think of it at the moment.

WITH p (DayId,PeriodID)

AS (SELECT DISTINCT t.DayId,t.PeriodID

FROM tTimeTable t

WHERE...

p.s. I normally run the SELECT without the INSERT and analyse the output for duplicates and start backwards from there.

What Ninja's stated is most likely the cause.

Most of PK violations I come across are due to...

:w00t: :hehe: :crazy:

Or without the cte

SELECT

CAST(LEFT([Data Team],2) as int)+CAST(LEFT([CRM],2) as int)+CAST(LEFT([IM Team],2) as int)

+(CAST(RIGHT([Data Team],2) as int)+CAST(RIGHT([CRM],2) as int)+CAST(RIGHT([IM Team],2) as int))/60 AS [HH],

(CAST(RIGHT([Data Team],2) as int)+CAST(RIGHT([CRM],2) as int)+CAST(RIGHT([IM...

pee - the dog last night and none on the roofs here. A lot in the grass.

This will give you hours and minutes which you can then format into HH:MM or how you wish.

WITH cte (HH,MM)

AS (SELECT

CAST(LEFT([Data Team],2) as int)+CAST(LEFT([CRM],2) as int)+CAST(LEFT([IM Team],2) as...