PERCENTAGE TO COUNT IFS LOBBY

Rpauley wrote:

Good Morning,

 i am trying to see if it is possible to get a column that would tell me my total percent to count for a month, example would be i have 4500 records and need to count 10% for this month the result in the column would give me 450. Below is screen shot of my SQL Data. Thank you for any help

 

 

You want to show 10% of on hand qty? Just do QTY_ONHAND*.10 as one of your columns 

It would be 10% of total records not the Qty on hand

Not sure if this will help to figure out how to get 10 %, i have the below column that tells me records counted and records to be counted, i would need 10% of records to be counted

(CASE WHEN LAST_COUNT_DATE <trunc(to_date( '#START_OF_THIS_YEAR#', 'YYYY-MM-DD-HH24:MI:SS' )) THEN 'Records Counted' ELSE 'Records To Be Counted' END)

You could try something like this then. 

select sum(case WHEN LAST_COUNT_DATE <trunc(to_date(Context_Substitution_Var_API.Get_Client_Value__('START_OF_THIS_YEAR'), 'YYYY-MM-DD-HH24:MI:SS' )) THEN 0 ELSE 1 END)*.1
from inventory_part_in_stock

@anmise  Thank you i had to do some playing with it i kept getting a error message i got it to work using the below, Do you know how i could get it to round up and only show whole numbers. Thank you

(CASE WHEN LAST_COUNT_DATE >trunc(to_date( '#START_OF_THIS_YEAR#', 'YYYY-MM-DD-HH24:MI:SS' )) THEN '0' ELSE '1' END) *.10

Rpauley wrote:

@anmise  Thank you i had to do some playing with it i kept getting a error message i got it to work using the below, Do you know how i could get it to round up and only show whole numbers. Thank you

 

(CASE WHEN LAST_COUNT_DATE >trunc(to_date( '#START_OF_THIS_YEAR#', 'YYYY-MM-DD-HH24:MI:SS' )) THEN '0' ELSE '1' END) *.10

 

 

Try using CEIL() to round up to nearest integer. 

i get a error message unless i am writing it wrong, Thank you for your help

(CASE WHEN LAST_COUNT_DATE >trunc(to_date( '#START_OF_THIS_YEAR#', 'YYYY-MM-DD-HH24:MI:SS' )) THEN '0' ELSE '1' END) CEIL(*.10)

Try

select part_no, ceil(sum(case WHEN LAST_COUNT_DATE <trunc(to_date(Context_Substitution_Var_API.Get_Client_Value__('START_OF_THIS_YEAR'), 'YYYY-MM-DD-HH24:MI:SS' )) THEN 0 ELSE 1 END)*.1)
from inventory_part_in_stock
group by part_no