asked on

SQL Select Higher Value

So this may be hard to word -

I have a table that I query and due to the way that the software is setup it gives me results that are duplicated on hours and minutes...

What I am trying to do is if it shows me that it has "duplicates in essence" I need the one with the HIGHER Billable rate per user....

See data below - I can answer any questions needed

Project	Hours	Minutes	Team Member	Responsibility	Billable Rate	Billable Ammount	ProjID
18180 OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	2	0	Corey 	Designer	85.00	170.00	2049
18180 OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	2	0	Corey 	Project Manager	170.00	340.00	2049
18180 OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	5	0	Corey 	Designer	85.00	425.00	2049
18180 OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	5	0	Corey 	Project Manager	170.00	850.00	2049
18180 OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	10	0	Corey 	Designer	85.00	850.00	2049
18180 OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	10	0	Corey 	Project Manager	170.00	1700.00	2049
18180 OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	16	0	Corey 	Designer	85.00	1360.00	2049
18180 OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	16	0	Corey 	Project Manager	170.00	2720.00	2049
18180 OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	0	5	Jag 	Designer	85.00	7.083	2049
18180 OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	5	0	Jag 	Designer	85.00	425.00	2049

Open in new window

SELECT proj_id,Project,Hours, Minutes, userid as [Team Member], Expr1 as Responsibility, rate AS [Billable Rate], rate * Hours + Minutes * (rate / 60) AS [Billable Ammount]
FROM dbo.OCI_QB_TEST WHERE project like('18180%') and CONVERT(VARCHAR(5),timespent,108) <> '00:00' and task <> '16'
union all
select '', '','', '', '', 'Total','' , sum(rate * Hours + Minutes * (rate / 60) ) from dbo.OCI_QB_TEST

WHERE project like('18180%') and CONVERT(VARCHAR(5),timespent,108) <> '00:00' and task <> '16'

Sharath S

try this.

SELECT proj_id,Project,Hours, Minutes, [Team Member], Responsibility, [Billable Rate],[Billable Ammount]
  FROM ( 
SELECT        proj_id,Project,Hours, Minutes, userid as [Team Member], Expr1 as Responsibility, rate AS [Billable Rate], rate * Hours + Minutes * (rate / 60) AS [Billable Ammount] ,
ROW_NUMBER() OVER (PARTITION BY userid ORDER BY rate DESC) rn
FROM            dbo.OCI_QB_TEST WHERE         project like('18180%') and CONVERT(VARCHAR(5),timespent,108) <> '00:00' and task <> '16') t1 WHERE rn = 1
 union all
  select  '',  '','', '', '', 'Total',''  , sum(rate * Hours + Minutes * (rate / 60) ) from dbo.OCI_QB_TEST

   WHERE          project like('18180%') and CONVERT(VARCHAR(5),timespent,108) <> '00:00' and task <> '16'

Open in new window

corey gashlin

ASKER

I tried that and it did not work well it limits down but I need it to shop me all the entries that are higher -

 select 
 *
  from OCI_JOB_COST_2019 where project like('18180%') and [billable rate] = 
  (select max([Billable Rate]) from OCI_JOB_COST_2019 as f where f.[Team Member] = OCI_JOB_COST_2019.[Team Member]) order by [Team Member]

Open in new window

Is what I tried after putting

SELECT        proj_id,Project,Hours, Minutes, userid as [Team Member], Expr1 as Responsibility, rate AS [Billable Rate], rate * Hours + Minutes * (rate / 60) AS [Billable Ammount]
FROM            dbo.OCI_QB_TEST WHERE         project like('18180%') and CONVERT(VARCHAR(5),timespent,108) <> '00:00' and task <> '16' 
 union all
  select  '',  '','', '', '', 'Total',''  , sum(rate * Hours + Minutes * (rate / 60) ) from dbo.OCI_QB_TEST

   WHERE          project like('18180%') and CONVERT(VARCHAR(5),timespent,108) <> '00:00' and task <> '16' order by [team member]

Open in new window

into a view

it spits the correct data out on some queries but not for all projects that i request -

I

Sharath S

For the sample you provided, can you provide the expected result?

corey gashlin

ASKER

I am looking to have these results -

Project	Hours	Minutes	Team Member	Responsibility	Billable Rate	Billable Ammount
18180 OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	2	0	Corey	Designer	85	170
18180 OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	2	0	Corey	Project Manager	170	340
18180 OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	5	0	Corey	Designer	85	425
18180 OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	5	0	Corey	Project Manager	170	850
18180 OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	10	0	Corey	Designer	85	850
18180 OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	10	0	Corey	Project Manager	170	1700
18180 OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	16	0	Corey	Designer	85	1360
18180 OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	16	0	Corey	Project Manager	170	2720
18180 OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	0	5	Jag	Designer	85	7.083
18180 OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	5	0	Jag 	Designer	85	425

Open in new window

Turned into

These results -

Project	Hours	Minutes	Team Member	Responsibility	Billable Rate	Billable Ammount	ProjID	TimeSpent
18180 OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	5	0	Corey	Project Manager	170	850	2049	00:00.0
18180 OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	16	0	Corey	Project Manager	170	2720	2049	00:00.0
18180 OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	10	0	Corey	Project Manager	170	1700	2049	00:00.0
18180 OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	2	0	Corey	Project Manager	170	340	2049	00:00.0
18180 OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	2	0	Corey	Project Manager	170	340	2049	00:00.0
18180 OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	0	5	Jag	Designer	85	7.083	2049	05:00.0
18180 OCI DYNAMICS CRM PROJECT MANAGEMENT SOFTWARE	5	0	Jag	Designer	85	425	2049	00:00.0

Open in new window

See the initial has Corey as being a Designer and Project Manager - Project Manager has higher billable rate(max) so it should not care about designer and just give me all the records for Corey as Project manager and Jag as a Designer -

Sharath S

try DENSE_RANK

SELECT proj_id,Project,Hours, Minutes, [Team Member], Responsibility, [Billable Rate],[Billable Ammount]
  FROM ( 
SELECT        proj_id,Project,Hours, Minutes, userid as [Team Member], Expr1 as Responsibility, rate AS [Billable Rate], rate * Hours + Minutes * (rate / 60) AS [Billable Ammount] ,
DENSE_RANK() OVER (PARTITION BY userid ORDER BY rate DESC) rn
FROM            dbo.OCI_QB_TEST WHERE         project like('18180%') and CONVERT(VARCHAR(5),timespent,108) <> '00:00' and task <> '16') t1 WHERE rn = 1
 union all
  select  '',  '','', '', '', 'Total',''  , sum(rate * Hours + Minutes * (rate / 60) ) from dbo.OCI_QB_TEST

   WHERE          project like('18180%') and CONVERT(VARCHAR(5),timespent,108) <> '00:00' and task <> '16'

Open in new window

corey gashlin

ASKER

This does seem to produce the results I am looking for... However, When trying to save this as a view so that I can have "project" be a variable instead of a static choice. It does not produce the same results. Any idea why this cant save in a view correctly?

Sharath S

Can you post your view and how are you passing it as variable. I may get an idea and may correct it.

corey gashlin

ASKER

SELECT proj_id as projid,Project, [Team Member], Responsibility, [Billable Rate], sum([billable ammount]) as [Total Ammount]  FROM(SELECT        proj_id, Project, Hours, Minutes, userid as [Team Member], Expr1 as Responsibility, rate AS[Billable Rate], rate * Hours + Minutes * (rate / 60) AS[Billable Ammount],DENSE_RANK() OVER(PARTITION BY userid ORDER BY rate DESC) rn FROM            dbo.OCI_QB_TEST WHERE        projid = @project and  CONVERT(VARCHAR(5), timespent, 108) <> '00:00' and task <> '16') t1 WHERE rn = 1 group by[team member], responsibility, proj_id, Project, [Billable Rate]

Open in new window

the @project is where i want it... I just wanted to leave it blank so it queried all... Then query it from my software.. However when doing that for some reason it does not give me all the results

corey gashlin

ASKER

Also, I am trying to find the OPPPOSITE of this query to do the same thing but instead of it being the higher number. I want the lower number LOL.

I promise Sharath after that I will close this and you get all the points!

corey gashlin

ASKER

I have this to show me lows - However I am trying to group and uynsuccessfully as i was previously -

SELECT proj_id as projid,Project, [Team Member], Responsibility, [Billable Rate], [Billable Rate] * Hours + Minutes * ([Billable Rate] / 60) AS[Billable Ammount]
FROM(SELECT proj_id, Project, Hours, Minutes, userid as [Team Member], Expr1 as Responsibility, rate AS[Billable Rate], rate * Hours + Minutes * (rate / 60) AS[Billable Ammount],DENSE_RANK() OVER(PARTITION BY userid ORDER BY rate asc) rn 
FROM            dbo.OCI_QB_TEST WHERE     projid=2049 and     CONVERT(VARCHAR(5), timespent, 108) <> '00:00' and task <> '16') t1 
WHERE rn = 1 group by[team member], responsibility, proj_id, Project, hours, minutes, [Billable Rate]

Open in new window

This spits out the following and am trying to get a total of billable for the lows- HOwever when I use the sum()_ it shows correct billable rates buy tthe billable ammount is actually based on the higher illable rate.

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

corey gashlin

ASKER

Sorry had emergency and did not get back to this - So the above would work as a view except for some reason it does not return all the results back when queried -

so if i save the above as lets say "PROJECTCOST" and say

select * from projectcost where projid=2049 I only get 2 rows when I am supposed to get 3 rows back.

It is missing one of the employees.

Sharath S

Are you still looking for help. Is the accepted solution working?