MrDuke March 12, 2011 Share MrDuke Member March 12, 2011 It counts the number of records approved by a user. I want to modify it so if the count is less than 150, the count is increased 50%. If the count is 151-200, increase it 30%. If the count is 201-250 increase it 15%. Thanks! Declare @StartDate datetime, @EndDate datetime Set @StartDate = '' Set @EndDate = '' select f.review_user_id, count(f.review_user_id) as 'Count', (u.fname + ' ' + u.lname) as 'User', 'Type'= case when f.wf_status_cn = 630 then 'Failed' else 'Passed' end, @StartDate as 'Start', @EndDate as 'End' from cprx_workflow f left join csuser u on f.review_user_id = u.user_id where wf_status_cn in (630,920) and review_date >= @StartDate and review_date <= @EndDate + 1 group by f.review_user_id, u.fname, u.lname, f.wf_status_cn Link to comment Share on other sites More sharing options...
Flitterkill March 12, 2011 Share Flitterkill GC Board Member March 12, 2011 Looks like a SQL db query to my eyes, most of it at least. Link to comment Share on other sites More sharing options...
yErMoTH3r March 13, 2011 Share yErMoTH3r GC Founder March 13, 2011 Declare @StartDate datetime, @EndDate datetime Set @StartDate = '' Set @EndDate = '' select f.review_user_id, (case when count(f.review_user_id) <150 then .5 when count(f.review_user_id) >=150 and count(f.review_user_id) <=200 then .3 when count(f.review_user_id) >=201 and count(f.review_user_id) <=250 then .15 else '' end) as' dukepct', count(f.review_user_id) as 'Count', (u.fname + ' ' + u.lname) as 'User', 'Type'= case when f.wf_status_cn = 630 then 'Failed' else 'Passed' end, @StartDate as 'Start', @EndDate as 'End' from cprx_workflow f left join csuser u on f.review_user_id = u.user_id where wf_status_cn in (630,920) and review_date >= @StartDate and review_date <= @EndDate + 1 group by f.review_user_id, u.fname, u.lname, f.wf_status_cn,(case when count(f.review_user_id) <150 then .5 when count(f.review_user_id) >=150 and count(f.review_user_id) <=200 then .3 when count(f.review_user_id) >=201 and count(f.review_user_id) <=250 then .15 else end) Link to comment Share on other sites More sharing options...
yErMoTH3r March 13, 2011 Share yErMoTH3r GC Founder March 13, 2011 or if u meant literally: Declare @StartDate datetime, @EndDate datetime Set @StartDate = '' Set @EndDate = '' select f.review_user_id, (case when count(f.review_user_id) <150 then 1.5*count(f.review_user_id) when count(f.review_user_id) >=150 and count(f.review_user_id) <=200 then 1.3*count(f.review_user_id) when count(f.review_user_id) >=201 and count(f.review_user_id) <=250 then 1.15*count(f.review_user_id) else count(f.review_user_id) end) as 'Count', (u.fname + ' ' + u.lname) as 'User', 'Type'= case when f.wf_status_cn = 630 then 'Failed' else 'Passed' end, @StartDate as 'Start', @EndDate as 'End' from cprx_workflow f left join csuser u on f.review_user_id = u.user_id where wf_status_cn in (630,920) and review_date >= @StartDate and review_date <= @EndDate + 1 group by f.review_user_id, u.fname, u.lname, f.wf_status_cn and this MAY be a sybase stored procedure (or mssql server - they are very close in syntax) Link to comment Share on other sites More sharing options...
MrDuke March 14, 2011 Author Share MrDuke Member March 14, 2011 Thanks M0m! If I said the code is Delphi, would that make sense (and would it change the syntax?)? Link to comment Share on other sites More sharing options...
amertrash March 21, 2011 Share amertrash Member March 21, 2011 It's just straight up SQL, not Delphi(which I'd assume would look like Pascal/TurboPascal, but I've never seen code for it myself). Looks like MS SQL syntax, never seen/played with Sybase myself. Link to comment Share on other sites More sharing options...
MrDuke March 21, 2011 Author Share MrDuke Member March 21, 2011 ok, last question. promise. if I wanted to round those numbers or change decimal places to 0, how/where would I incorporate that? The numbers I'm getting show decimals and I don't want those. Sorry to ask. I do drugs, not coding. (let the jokes begin). Link to comment Share on other sites More sharing options...
Flitterkill March 22, 2011 Share Flitterkill GC Board Member March 22, 2011 Sorry to ask. I do drugs, not coding. (let the jokes begin). I so want to change your avatar to Dr. Rockso now... Link to comment Share on other sites More sharing options...
Unclean March 22, 2011 Share Unclean Member March 22, 2011 I think you're looking for: round(your_number, 0) But that keeps significant digits. If you don't need those, then you should cast to an int: cast(round(2.9302, 0) as int) Try them out: select round(2.9302, 0) select round(2.0302, 0) select cast(round(2.0302, 0) as int) select cast(round(2.9302, 0) as int) Link to comment Share on other sites More sharing options...
MrDuke April 9, 2011 Author Share MrDuke Member April 9, 2011 It worked perfectly- thanks to all for contributing to my delinquency!! I can now do 50% less work and still report out a decent number. Link to comment Share on other sites More sharing options...
yErMoTH3r April 12, 2011 Share yErMoTH3r GC Founder April 12, 2011 woohoo! Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now