Assuming x is an int

select ((x+6)/12)*12

Bob Hovious (5/6/2009)

---

I misunderstood, sorry about that. You are wanting to build the values you originally posted.

A summary query either before or after would...

rockingadmin (5/7/2009)

---

hi Florian Reischl,

i am gettign the same error,

Msg 536, Level 16, State 5, Line 5

Invalid length parameter passed to the SUBSTRING function.

The statement has been terminated.

for '+' every...

Did you try the code I posted?

You're not handling the case where there isn't a '+', try this

update emp_data set firstname=substring(name,1,charindex('+',name+'+')-1)

lastname=substring(name,charindex('+',name+'+'),len(name)) ;

Maybe, perhaps as Bob has suggested using "outer join" and possibly "group by" instead of the sub-query, but you'll need...

Also change

SELECT node.l.value('Ref[1]','NVARCHAR(50)') AS MyRef

to

SELECT node.l.value('n2:Ref[1]','NVARCHAR(50)') AS MyRef

Try this

WITH XMLNAMESPACES (

'http://mywebsite.com/msg/' AS n1, 'http://mywebsite.com/' AS n2

)

SELECT node.l.value('Ref[1]','NVARCHAR(50)') AS MyRef

FROM my_xml_Data CROSS APPLY xml_data_column.nodes('/n1:MyPostAd/n2:Ad/n2:MyPostAd') node(l)

XML is case sensitive so

FROM my_xml_Data CROSS APPLY xml_data_column.nodes('/MyPostAd/Ad/MyPostAd') node(l)

should probably be

FROM my_xml_Data CROSS APPLY xml_data_column.nodes('/MYPOSTAD/AD/MYPOSTAD') node(l)

SELECT p.name,a.activity

FROM activity a

INNER JOIN person p ON p.catid1=a.catid1 AND p.catid2=a.catid2

GROUP BY a.activity,p.name

HAVING COUNT(*)=(SELECT COUNT(*) FROM activity a2 WHERE a2.activity=a.activity)

Another way...

select t1.id, t1.col,d.EventDate,d.EventText

from table1 t1

outer apply (select top 1 t2.EventDate,t2.EventText

from table2 t2

...

DECLARE @NOM VARCHAR(50)

SET @NOM='HENRY20';

WITH CTE AS (

SELECT ID,NOM,ID_PERE

FROM ARBRE

WHERE NOM=@NOM

UNION ALL

SELECT a.ID,a.NOM,a.ID_PERE

FROM CTE c

INNER JOIN ARBRE a ON a.ID=c.ID_PERE)

SELECT ID,NOM

FROM CTE

WHERE ID_PERE IS NULL

Adam Machanic (4/28/2009)

---

We're not done yet -- if we're going to create the "best" method it should do as much as possible in terms of functionality we would normally want...

Use sql:variable

SELECT

x.y.value('declare namespace z="http://dwivedys.blogspot.com"; z:TO[sql:variable("@CNT")][1]', 'nvarchar(50)'), ...

Not really clear what you're asking, but try this

WITH Numbers AS (SELECT number

FROM master.dbo.spt_values

WHERE type='P')

SELECT Appointment_time,Duration,Appt_count,ApptLimit_For_Day,patientRecNum,

PatientName,ApptCreatedDate,

CASE...