MrDuke March 12, 2011 Share MrDuke 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 Quote Link to comment Share on other sites More sharing options...
Flitterkill March 12, 2011 Share Flitterkill March 12, 2011 Looks like a SQL db query to my eyes, most of it at least. Quote Link to comment Share on other sites More sharing options...
yErMoTH3r March 13, 2011 Share yErMoTH3r 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) Quote Link to comment Share on other sites More sharing options...
yErMoTH3r March 13, 2011 Share yErMoTH3r 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) Quote Link to comment Share on other sites More sharing options...
MrDuke March 14, 2011 Author Share MrDuke March 14, 2011 Thanks M0m! If I said the code is Delphi, would that make sense (and would it change the syntax?)? Quote Link to comment Share on other sites More sharing options...
amertrash March 21, 2011 Share amertrash 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. Quote Link to comment Share on other sites More sharing options...
MrDuke March 21, 2011 Author Share MrDuke 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). Quote Link to comment Share on other sites More sharing options...
Flitterkill March 22, 2011 Share Flitterkill 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... Quote Link to comment Share on other sites More sharing options...
Unclean March 22, 2011 Share Unclean 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) Quote Link to comment Share on other sites More sharing options...
MrDuke April 9, 2011 Author Share MrDuke 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. Quote Link to comment Share on other sites More sharing options...
yErMoTH3r April 12, 2011 Share yErMoTH3r April 12, 2011 woohoo! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.