Pull a record line based on a Minumum value

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

General SQL Server Forums

New to SQL Server Programming

Pull a record line based on a Minumum value

Author

Topic

jaimealvarez
Starting Member

31 Posts

Posted - 2012-07-25 : 16:47:53

Hi,
I have this sql

WITH C1 AS(
SELECT
c.crclient
, c.sort
, c.name
, CASE
WHEN (c.Tkeeper = 'Y' and c.crmaximum = 'Y' and c.crrate IS NOT NULL) then c.MaxRate
WHEN (c.Tkeeper = 'Y' and c.crmaximum = 'N' and c.crrate IS NOT NULL) then c.crrate
end AS 'BillingRate1'
FROM C
WHERE c.RecKey = 'Y'

)

SELECT
c1.crclient
, tkinit AS 'Name'
, c1.sort
, timekeep.tkstrate AS 'standard Rate'
, CASE
WHEN (c1.billingrate1 IS NOT NULL) then c1.billingrate1
else timekeep.tkstrate
end AS 'Billing Rate'
FROM son_db.dbo.timekeep timekeep LEFT OUTER JOIN C1 ON (C1.name = timekeep.tkinit)

C1 is bringing values like this

CrClient Sort Name BillingRate1
A 1 Smith 20
A 2 Smith 22
A 2 Joseph 30
B 3 Smith 20
B 3 Joseph 40

How can I do it, so that in that the next part just pulls the min(sort) by client and Name, so that it looks like this:

CrClient Sort Name BillingRate1
A 1 Smith 20
A 2 Joseph 30
B 3 Smith 20
B 3 Joseph 40

Any help would be appreciated. Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-25 : 16:51:38

[code]
WITH C1 AS(
SELECT
c.crclient
, c.sort
, c.name
, CASE
WHEN (c.Tkeeper = 'Y' and c.crmaximum = 'Y' and c.crrate IS NOT NULL) then c.MaxRate
WHEN (c.Tkeeper = 'Y' and c.crmaximum = 'N' and c.crrate IS NOT NULL) then c.crrate
end AS 'BillingRate1'
FROM C
WHERE c.RecKey = 'Y'

)

SELECT crclient,[Name],sort,[Billing Rate]
FROM
(
SELECT ROW_NUMBER OVER (PARTITION BY c1.crclient,tkinit ORDER BY c1.sort) AS Seq
,c1.crclient
, tkinit AS 'Name'
, c1.sort
, timekeep.tkstrate AS 'standard Rate'
, CASE
WHEN (c1.billingrate1 IS NOT NULL) then c1.billingrate1
else timekeep.tkstrate
end AS 'Billing Rate'
FROM son_db.dbo.timekeep timekeep LEFT OUTER JOIN C1 ON (C1.name = timekeep.tkinit)
)t
WHERE Seq=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources