Jump to content

Hola to all! Hey, what language is this?


MrDuke

Recommended Posts

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

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

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

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

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

  • 3 weeks later...

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...