Solved

[ LOBBY - DATA SOURCE DESIGNER ] - SQL request

  • 24 October 2022
  • 1 reply
  • 223 views
Eric FOULART
Rank
Userlevel 5
Badge +7

Hi All

I need help about a request i would like to do in the view : PROJECT_TRANSACTION

In this view, i have selected the items below

  • Internal_quantity
  • Company_ID

I have some parameters :

  • $COMPANY_ID$ → value authorized : 622 or 6%
  • $YEAR$  → value authorized : 2022
  • $MONTH$  → value authorized : 01 or 02, …
  • $EMPLOYEE$  → value authorized : ERFOFR or %

I would like to have this result  

  • IQ_TOTAL → Sum(INTERNAL_QUANTITY)
  • IQ_TOTALINV → Sum(INTERNAL_QUANTITY) only for INVOICABILITY =’Facturable’
  • IQ_TOTALNOTINV → Sum(INTERNAL_QUANTITY)  only for INVOICABILITY =’Non Facturable’ 

For Column IQ_TOTALINV , i tried this below with $COMPANY_ID$ = 6% and $YEAR$ =2022 and  $MONTH$ =01 :

(select sum(INTERNAL_QUANTITY) from PROJECT_TRANSACTION where INVOICABILITY='Non facturable' and COMPANY_ID LIKE '$COMPANY$' and (to_char(ACCOUNT_DATE,'yyyy') = '$YEAR$' and to_char(ACCOUNT_DATE,'mm')='$MONTH$'))

But it is wrong, because i have the same value for every company … 

if you have any idea …

 

Thank for your help

 

/eric

icon

Best answer by Eric FOULART 25 October 2022, 04:33

View original
Eric FOULART - What's else ?

1 reply

Eric FOULART
Rank
Userlevel 5
Badge +7

Hi All

I founded the solution myself :)

I just create 3 columns with the right function (this is another example about the same subject i would like to have in another datasource  

  • sum(case when WAGE_GRP_DB='O' then WAGE_HOURS else 0 end)
  • sum(case when WAGE_GRP_DB='N' then WAGE_HOURS else 0 end)
  • sum(case when WAGE_GRP_DB='F' then WAGE_HOURS else 0 end)

Thank

Eric FOULART - What's else ?

Reply


Terms & ConditionsCookie settings