quote:

---

Originally posted by hardikspider123

With cte as
(Select mrmatter, 'Timekeeper: ' + tkinit + ' ' + (tklast + ', ' + tkfirst) as [Exception Rates Tkpr/Title/Codes],
mreffdate as Effectivedate, mrenddate as Enddate, mrmaximum as [Max], mrrate as Specificrate, mrdevper as [%Dev], mrdevdol as AmtDev,
mrrtnum as Ratecode, ROW_NUMBER() OVER(PARTITION BY mrmatter, tkinit ORDER BY tklast, tkfirst ASC) AS RowNumber, 1 as Num
from mexrate join timekeep on mexrate.mrtk = timekeep.tkinit
where mrtk is not null and mrtk not like '!' --and mreffdate <= getdate()
and mrmatter in (select mmatter from matter where mrtcode !=1 and ltrim(rtrim(mstatus)) not like'CL'
and mclient in (@client))


Union

Select mrmatter, 'Title: ' + mrtitle as [Exception Rates Tkpr/Title/Codes], mreffdate as Effectivedate, mrenddate as Enddate,
mrmaximum as [Max], mrrate as Specificrate, mrdevper as [%Dev], mrdevdol as AmtDev, mrrtnum as Ratecode,
ROW_NUMBER() OVER(PARTITION BY mrmatter ORDER BY mreffdate DESC) AS RowNumber, 2 as Num
from mexrate
where mrtitle like 'ALL' --and mreffdate <= getdate()
and mrmatter in (select mmatter from matter where mrtcode !=1 and ltrim(rtrim(mstatus)) not like'CL'
and mclient in (@client))

Union

Select mrmatter, 'Title: ' + mrtitle as [Exception Rates Tkpr/Title/Codes], mreffdate as Effectivedate, mrenddate as Enddate,
mrmaximum as [Max], mrrate as Specificrate, mrdevper as [%Dev], mrdevdol as AmtDev, mrrtnum as Ratecode,
ROW_NUMBER() OVER(PARTITION BY mrmatter, mrtitle ORDER BY mreffdate DESC) AS RowNumber, 3 as Num
from mexrate
where mrtitle not like '!' and mrtitle not like 'ALL' --and mreffdate <= getdate()
and mrmatter in (select mmatter from matter where mrtcode !=1 and ltrim(rtrim(mstatus)) not like'CL'
and mclient in (@client))

Union

Select mrmatter, 'COSTCODE: ' + mrccode+ ' ' +codesc1 as [Exception Rates Tkpr/Title/Codes], mreffdate as Effectivedate, mrenddate as Enddate,
mrmaximum as [Max], mrrate as Specificrate, mrdevper as [%Dev], mrdevdol as AmtDev, mrrtnum as Ratecode,
ROW_NUMBER() OVER(PARTITION BY mrmatter, cocode ORDER BY mreffdate DESC) AS RowNumber, 4 as Num
from mexrate join costcode on mexrate.mrccode = costcode.cocode
where mrmatter in (select mmatter from matter where mrtcode !=1 and ltrim(rtrim(mstatus)) not like'CL' --and mreffdate <= getdate()
and mclient in (@client))
),

cte2 as (
select crclient, 'Timekeeper: ' + tkinit + ' ' + (tklast + ', ' + tkfirst) as [Exception Rates Tkpr/Title/Codes],
creffdate as Effectivedate, crenddate as Enddate, crmaximum as [Max], crrate as Specificrate, crdevper as [%Dev], crdevdol as AmtDev,
crrtnum as Ratecode, ROW_NUMBER() OVER(PARTITION BY crclient, tkinit ORDER BY tklast, tkfirst ASC) AS RowNumber, 1 as Num
from cexrate join timekeep on cexrate.crtk = timekeep.tkinit
where crclient in (select clnum from client where ltrim(rtrim(clstatus)) like 'C'
and clnum in (@client))
and crtk is not null and crtk not like '!' --and creffdate <= getdate()


Union

select crclient, 'Title: ' + crtitle as [Exception Rates Tkpr/Title/Codes], creffdate as Effectivedate, crenddate as Enddate,
crmaximum as [Max], crrate as Specificrate, crdevper as [%Dev], crdevdol as AmtDev, crrtnum as Ratecode,
ROW_NUMBER() OVER(PARTITION BY crclient, crtitle ORDER BY creffdate DESC) AS RowNumber, 2 as Num
from cexrate
where crclient in (select clnum from client where ltrim(rtrim(clstatus)) = 'C'
and clnum in (@client)) and crtitle like 'ALL'

Union

select crclient, 'Title: ' + crtitle as [Exception Rates Tkpr/Title/Codes], creffdate as Effectivedate, crenddate as Enddate,
crmaximum as [Max], crrate as Specificrate, crdevper as [%Dev], crdevdol as AmtDev, crrtnum as Ratecode,
ROW_NUMBER() OVER(PARTITION BY crclient, crtitle ORDER BY creffdate DESC) AS RowNumber, 3 as Num
from cexrate
where crclient in (select clnum from client where ltrim(rtrim(clstatus)) like'C'
and clnum in (@client))
and crtitle not like '!' and crtitle not like 'ALL' --and creffdate <= getdate()

Union

select crclient, 'COSTCODE: ' + crccode +' ' + codesc1 as [Exception Rates Tkpr/Title/Codes], creffdate as Effectivedate, crenddate as Enddate,
crmaximum as [Max], crrate as Specificrate, crdevper as [%Dev], crdevdol as AmtDev, crrtnum as Ratecode,
ROW_NUMBER() OVER(PARTITION BY crclient, cocode ORDER BY creffdate DESC) AS RowNumber, 4 as Num
from cexrate join costcode on cexrate.crccode = costcode.cocode
where crclient in (select clnum from client where ltrim(rtrim(clstatus)) like'C'
and clnum in (@client)) --and creffdate <= getdate()

)

select distinct matter.mmatter as Number, (coalesce(mdesc1, '') + coalesce(mdesc2, '') + coalesce(mdesc3, '')) as Name,
(SELECT * FROM (SELECT(SELECT mddesc+' ' AS [text()] FROM mattdesc WHERE mattdesc.mmatter = matter.mmatter
ORDER BY mdline FOR XML PATH('') ) AS mddesc ) mddesc ) as NameNarrative,
mrtcode as Rate, mstatus as status, mcurrency as Currency, mbillaty as BANumber,
(tkfirst+' ,'+ tklast) as BAName, [Exception Rates Tkpr/Title/Codes], Effectivedate, Enddate, [Max],
Specificrate, [%Dev], AmtDev, Ratecode, Num, RowNumber
From matter left join cte on matter.mmatter = cte.mrmatter and cte.RowNumber = 1
left join timekeep on timekeep.tkinit = matter.mbillaty
where mrtcode !=1 and ltrim(rtrim(mstatus)) not like'CL'
and mclient in (@client)


Union

select distinct client.clnum as Number, clname1 as Name,
(SELECT * FROM (SELECT(SELECT cddesc+' ' AS [text()] FROM clidesc WHERE clidesc.clnum = client.clnum
ORDER BY cdline FOR XML PATH('') ) AS cddesc ) cddesc ) as NameNarrative,
'' as Rate, clstatus as Status, '' as Currency, '' as BANumber, '' as BAName, [Exception Rates Tkpr/Title/Codes],
Effectivedate, Enddate, [Max], Specificrate, [%Dev], AmtDev, Ratecode, Num, RowNumber
From client left join cte2 on client.clnum = cte2.crclient and cte2.RowNumber = 1
join matter on matter.mclient = client.clnum and mrtcode !=1 and mstatus not like 'CL'
where ltrim(rtrim(clstatus)) like 'C' and clnum in (@client)

Union

select distinct client.clnum as Number, clname1 as Name,
(SELECT * FROM (SELECT(SELECT cddesc+' ' AS [text()] FROM clidesc WHERE clidesc.clnum = client.clnum
ORDER BY cdline FOR XML PATH('') ) AS cddesc ) cddesc ) as NameNarrative,
'' as Rate, clstatus as Status, '' as Currency, '' as BANumber, '' as BAName, [Exception Rates Tkpr/Title/Codes],
Effectivedate, Enddate, [Max], Specificrate, [%Dev], AmtDev, Ratecode, Num, RowNumber
From client left join cte2 on client.clnum = cte2.crclient and cte2.RowNumber = 1
where ltrim(rtrim(clstatus)) like 'C' and clnum in (@client)
and clnum not in (select mclient from matter)


order by Number,NameNarrative, Num, RowNumber

need tkfirst and tklast order by.please let me know if you can help me. Thanks

---

With cte as 
(Select mrmatter, 'Timekeeper: ' + tkinit + ' ' + (tklast + ', ' + tkfirst) as [Exception Rates Tkpr/Title/Codes], 
mreffdate as Effectivedate, mrenddate as Enddate, mrmaximum as [Max], mrrate as Specificrate, mrdevper as [%Dev], mrdevdol as AmtDev, 
mrrtnum as Ratecode, ROW_NUMBER() OVER(PARTITION BY mrmatter, tkinit ORDER BY tklast, tkfirst ASC) AS RowNumber, 1 as Num
from mexrate join timekeep on mexrate.mrtk = timekeep.tkinit
where mrtk is not null and mrtk not like '!' --and mreffdate <= getdate()
and mrmatter in (select mmatter from matter where mrtcode !=1 and ltrim(rtrim(mstatus)) not like'CL' 
and mclient in (@client))


Union

Select mrmatter, 'Title: ' + mrtitle as [Exception Rates Tkpr/Title/Codes], mreffdate as Effectivedate, mrenddate as Enddate,
mrmaximum as [Max], mrrate as Specificrate, mrdevper as [%Dev], mrdevdol as AmtDev, mrrtnum as Ratecode,
ROW_NUMBER() OVER(PARTITION BY mrmatter ORDER BY mreffdate DESC) AS RowNumber, 2 as Num
from mexrate 
where mrtitle like 'ALL' --and mreffdate <= getdate()
and mrmatter in (select mmatter from matter where mrtcode !=1 and ltrim(rtrim(mstatus)) not like'CL' 
and mclient in (@client))

Union

Select mrmatter, 'Title: ' + mrtitle as [Exception Rates Tkpr/Title/Codes], mreffdate as Effectivedate, mrenddate as Enddate, 
mrmaximum as [Max], mrrate as Specificrate, mrdevper as [%Dev], mrdevdol as AmtDev, mrrtnum as Ratecode, 
ROW_NUMBER() OVER(PARTITION BY mrmatter, mrtitle ORDER BY mreffdate DESC) AS RowNumber, 3 as Num
from mexrate 
where mrtitle not like '!' and mrtitle not like 'ALL' --and mreffdate <= getdate()
and mrmatter in (select mmatter from matter where mrtcode !=1 and ltrim(rtrim(mstatus)) not like'CL' 
and mclient in (@client))

Union

Select mrmatter, 'COSTCODE: ' + mrccode+ ' ' +codesc1 as [Exception Rates Tkpr/Title/Codes], mreffdate as Effectivedate, mrenddate as Enddate, 
mrmaximum as [Max], mrrate as Specificrate, mrdevper as [%Dev], mrdevdol as AmtDev, mrrtnum as Ratecode, 
ROW_NUMBER() OVER(PARTITION BY mrmatter, cocode ORDER BY mreffdate DESC) AS RowNumber, 4 as Num
from mexrate join costcode on mexrate.mrccode = costcode.cocode 
where mrmatter in (select mmatter from matter where mrtcode !=1 and ltrim(rtrim(mstatus)) not like'CL' --and mreffdate <= getdate()
and mclient in (@client))
),

cte2 as (
select crclient, 'Timekeeper: ' + tkinit + ' ' + (tklast + ', ' + tkfirst) as [Exception Rates Tkpr/Title/Codes], 
creffdate as Effectivedate, crenddate as Enddate, crmaximum as [Max], crrate as Specificrate, crdevper as [%Dev], crdevdol as AmtDev, 
crrtnum as Ratecode, ROW_NUMBER() OVER(PARTITION BY crclient, tkinit ORDER BY tklast, tkfirst ASC) AS RowNumber, 1 as Num
from cexrate join timekeep on cexrate.crtk = timekeep.tkinit
where crclient in (select clnum from client where ltrim(rtrim(clstatus)) like 'C' 
and clnum in (@client))
and crtk is not null and crtk not like '!' --and creffdate <= getdate()


Union

select crclient, 'Title: ' + crtitle as [Exception Rates Tkpr/Title/Codes], creffdate as Effectivedate, crenddate as Enddate, 
crmaximum as [Max], crrate as Specificrate, crdevper as [%Dev], crdevdol as AmtDev, crrtnum as Ratecode, 
ROW_NUMBER() OVER(PARTITION BY crclient, crtitle ORDER BY creffdate DESC) AS RowNumber, 2 as Num
from cexrate 
where crclient in (select clnum from client where ltrim(rtrim(clstatus)) = 'C' 
and clnum in (@client)) and crtitle like 'ALL' 

Union

select crclient, 'Title: ' + crtitle as [Exception Rates Tkpr/Title/Codes], creffdate as Effectivedate, crenddate as Enddate, 
crmaximum as [Max], crrate as Specificrate, crdevper as [%Dev], crdevdol as AmtDev, crrtnum as Ratecode, 
ROW_NUMBER() OVER(PARTITION BY crclient, crtitle ORDER BY creffdate DESC) AS RowNumber, 3 as Num
from cexrate 
where crclient in (select clnum from client where ltrim(rtrim(clstatus)) like'C' 
and clnum in (@client))
and crtitle not like '!' and crtitle not like 'ALL' --and creffdate <= getdate()

Union

select crclient, 'COSTCODE: ' + crccode +' ' + codesc1 as [Exception Rates Tkpr/Title/Codes], creffdate as Effectivedate, crenddate as Enddate,
crmaximum as [Max], crrate as Specificrate, crdevper as [%Dev], crdevdol as AmtDev, crrtnum as Ratecode,
ROW_NUMBER() OVER(PARTITION BY crclient, cocode ORDER BY creffdate DESC) AS RowNumber, 4 as Num
from cexrate join costcode on cexrate.crccode = costcode.cocode 
where crclient in (select clnum from client where ltrim(rtrim(clstatus)) like'C' 
and clnum in (@client)) --and creffdate <= getdate()

) 
select * 
from 
(
	select distinct matter.mmatter as Number, (coalesce(mdesc1, '') + coalesce(mdesc2, '') + coalesce(mdesc3, '')) as Name, 
	(SELECT * FROM (SELECT(SELECT mddesc+' ' AS [text()] FROM mattdesc WHERE mattdesc.mmatter = matter.mmatter
	ORDER BY mdline FOR XML PATH('') ) AS mddesc ) mddesc ) as NameNarrative, 
	mrtcode as Rate, mstatus as status, mcurrency as Currency, mbillaty as BANumber, 
	(tkfirst+' ,'+ tklast) as BAName, [Exception Rates Tkpr/Title/Codes], Effectivedate, Enddate, [Max], 
	Specificrate, [%Dev], AmtDev, Ratecode, Num, RowNumber
	From matter left join cte on matter.mmatter = cte.mrmatter and cte.RowNumber = 1
	left join timekeep on timekeep.tkinit = matter.mbillaty
	where mrtcode !=1 and ltrim(rtrim(mstatus)) not like'CL' 
	and mclient in (@client)


	Union

	select distinct client.clnum as Number, clname1 as Name, 
	(SELECT * FROM (SELECT(SELECT cddesc+' ' AS [text()] FROM clidesc WHERE clidesc.clnum = client.clnum
	ORDER BY cdline FOR XML PATH('') ) AS cddesc ) cddesc ) as NameNarrative, 
	'' as Rate, clstatus as Status, '' as Currency, '' as BANumber, '' as BAName, [Exception Rates Tkpr/Title/Codes], 
	Effectivedate, Enddate, [Max], Specificrate, [%Dev], AmtDev, Ratecode, Num, RowNumber
	From client left join cte2 on client.clnum = cte2.crclient and cte2.RowNumber = 1
	join matter on matter.mclient = client.clnum and mrtcode !=1 and mstatus not like 'CL'
	where ltrim(rtrim(clstatus)) like 'C' and clnum in (@client)

	Union

	select distinct client.clnum as Number, clname1 as Name, 
	(SELECT * FROM (SELECT(SELECT cddesc+' ' AS [text()] FROM clidesc WHERE clidesc.clnum = client.clnum
	ORDER BY cdline FOR XML PATH('') ) AS cddesc ) cddesc ) as NameNarrative, 
	'' as Rate, clstatus as Status, '' as Currency, '' as BANumber, '' as BAName, [Exception Rates Tkpr/Title/Codes], 
	Effectivedate, Enddate, [Max], Specificrate, [%Dev], AmtDev, Ratecode, Num, RowNumber
	From client left join cte2 on client.clnum = cte2.crclient and cte2.RowNumber = 1
	where ltrim(rtrim(clstatus)) like 'C' and clnum in (@client)
	and clnum not in (select mclient from matter)
) t
order by Number,NameNarrative, Num, RowNumber

quote:

---

Originally posted by hardikspider123

Please give me youe rmaild id so I can explain better.

---

quote:

---

Originally posted by hardikspider123

I am saying that I need (tklast + ', ' + tkfirst) as [Exception Rates Tkpr/Title/Codes], sorted in my output

---

quote:

---

Originally posted by hardikspider123

Timekeeper: 38700 Zack, Richard J.
Timekeeper: 62979 de Vries, Andrea M.


I am getting above data I need it sorted by lastname and firstname in my query

---

quote:

---

Originally posted by hardikspider123

I am still not getting correct result. If you will check my query I have field named as [Exception Rates Tkpr/Title/Codes]
in that I have joined 3 fields tkid tklast tkfirst when it display all result the result should be by lastname firstname tkid it should be in this order

---