Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 where condition

Author  Topic 

hardikspider123
Starting Member

12 Posts

Posted - 2014-10-24 : 15:17:43
hi

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-24 : 15:24:28
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


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

hardikspider123
Starting Member

12 Posts

Posted - 2014-10-24 : 15:29:25
Please give me youe rmaild id so I can explain better.
Go to Top of Page

hardikspider123
Starting Member

12 Posts

Posted - 2014-10-24 : 15:32:55
hi
Go to Top of Page

hardikspider123
Starting Member

12 Posts

Posted - 2014-10-24 : 15:51:25
thanks
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-24 : 15:57:32
quote:
Originally posted by hardikspider123

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



Just explain here in this thread.

quote:
Originally posted by hardikspider123

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


order by Number,NameNarrative, Num, RowNumber, [Exception Rates Tkpr/Title/Codes]

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

hardikspider123
Starting Member

12 Posts

Posted - 2014-10-24 : 16:01:51
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
Go to Top of Page

hardikspider123
Starting Member

12 Posts

Posted - 2014-10-24 : 16:04:08
Thanks
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-24 : 16:22:33
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


order by tkfirst, tklast, tkid

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -