You could join to a table of ranges

create table Ranges(RangeStart int, RangeEnd int, RangeDesc varchar(20))

insert into Ranges(RangeStart , RangeEnd , RangeDesc )

select 0,30,'<31' union all

select 31,60,'31-60' union all

select 61,90,'61-90'

select r.RangeDesc,count(*)

from...

SELECT N

FROM

(

SELECT N

from Tally

where N < 11

UNION ALL

SELECT NULL

) X

ORDER BY CASE WHEN N IS NULL THEN 1 ELSE 0 END,N

See if this helps

select t.DateRecorded, t.ModifiedBy, t.RankTypeID,

d.value('@LastModifiedBy','varchar(50)') as Deleted_LastModifiedBy,

d.value('@DateLastModified','datetime') as Deleted_DateLastModified,

...

Just join to another table as normal

select t.DateRecorded, t.ModifiedBy, r.RankDescription,

d.value('@LastModifiedBy','varchar(50)') as Deleted_LastModifiedBy,

d.value('@DateLastModified','datetime') as Deleted_DateLastModified,

...

select DateRecorded, ModifiedBy, RankTypeID,

d.value('@LastModifiedBy','varchar(50)') as Deleted_LastModifiedBy,

d.value('@DateLastModified','datetime') as Deleted_DateLastModified,

d.value('@Description','varchar(100)') as...

You could add

cross join (select 1 union all select 2) X(Y)

into your query

Maybe this?

WITH CTE AS (

SELECT ID,Val AS RootVal

FROM MyTable

WHERE PARENTID=0

UNION ALL

SELECT t.ID,c.RootVal

FROM CTE c

INNER JOIN MyTable t ON t.PARENTID=c.ID)

UPDATE t

SET Val=c.RootVal

FROM MyTable t

INNER JOIN CTE c ON c.ID=t.ID

WHERE...

Remove 'Category' from your GROUP BY clause

Here's another approach generating the full XML once

CREATE FUNCTION dbo.GetSubTree(@ID INT,@Path VARCHAR(MAX))

RETURNS XML

BEGIN RETURN

(SELECT ID AS "ID",

[Name] AS "Name",

...

WITH CTE AS (

SELECT ID,NAME AS DEPT

FROM TABLE1

WHERE PARENTID=-1

UNION ALL

SELECT t.ID,c.DEPT

FROM CTE c

INNER JOIN TABLE1 t ON t.PARENTID=c.ID)

SELECT t.DATE,c.DEPT,t.QTY

FROM CTE c

INNER JOIN TABLE2 t ON t.ID=c.ID

ORDER BY t.DATE,t.QTY

I can't see exactly what you're trying to achieve but see if this helps

CREATE FUNCTION dbo.GetSubTree(@ID int)

RETURNS XML

BEGIN RETURN

(SELECT ID AS "ID",

...

with CTE(id , parentid ,[text], fullpath)

as (

select id , parentid ,[text], cast([text] as varchar(max))

from t3

where parentid is null

union all

select p.id , p.parentid ,p.[text], c.fullpath+'/'+cast(p.[text] as varchar(max))

from CTE c

inner join t3...

Something like this?

update mytable

set Ct1='PT'

where Ct1<>'PT'

and exists (

select t.SNo,t.CCode,t.Pt1

from mytable t

where t.SNo=mytable.SNo

and t.CCode=mytable.CCode

and t.Pt1=mytable.Pt1

group by t.SNo,t.CCode,t.Pt1

having count(*)>1 and count(distinct t.Ct1)>1

)

Try changing

as AutoInventory WHERE id BETWEEN ' + cast(@startrow as nvarchar(1)) + '

AND (' + cast(@startrow as nvarchar(1)) + ' + ' + cast(@maxrows as nvarchar(2)) +...

SELECT x.node.value('count(for $a in .. return $a/../*[. << $a])','int') Position,

x.node.value('@T', 'int') Table_ID

,x.node.value('@FN', 'nvarchar(255)') Field_Name

...