Altering View with stored procedure and parameters

Transact-SQL
1

Good Morning. Glad I found this forum.
I have the following problem. I have a view that applies attributes to fields via case statements. That view is then used as a source of a select query inside a stored procedure to run modeling calculations and display data in database connected excel workbook. Problem is that two attributes in this view are dynamic and should be parameterized

case when fpe<='2016-03-31' then 'Ongoing'
when fpe is null then 'Ongoing'
when fpe='' then 'Ongoing'
else 'New'
end as Calc_FPE_New_Ongoing_grp,

case
when cast (MonthDisplayKey+'-01' as date)<='2016-06-30' then 'Current'
else 'Future'
End
as Calc_current_future_quarter,

What I am attempting to do is to alter the view with stored procedure and pass date parameters for fpe and monthdisplay key so user as a step one can rebuild the view and as a step 2 run calculation against the updated view. What is the way to accomplish this? Thank you for your help.

CREATE view [DW].[PVPA_PULL_VW]
as

select
a.SnapshotName,
a.cost_unit,
a.New_Study_Category,
a.MRL_Short_Franchise,
a.Calc_TA_Area_Grp,
a.activity_unique_id,
a.MK_or_V,
a.study_filing_status,
a.Calc_Filing_Status_Grp,
a.prioritization_category,
a.calc_Prioritization_grp,
a.FPE,
a.Calc_FPE_New_Ongoing_grp,
a.Execution_Project_Name,
a.Execution_Project_Sequence,
a.calc_execution_project_sequence_grp,
a.Current_Clinical_Phase,
a.Study_Phase,
a.MonthDisplayKey as Forecast_Month,
a.Calc_current_future_quarter,
a.GrossAmount,
a.Calc_TA_Area_Grp+a.Calc_Filing_Status_Grp+a.calc_Prioritization_grp+a.Calc_FPE_New_Ongoing_grp+a.Calc_current_future_quarter+a.calc_execution_project_sequence_grp as FK_Rules_Unique_Id,
a.SnapshotName+a.activity_unique_id as FK_PTRS_Unique_ID

from

(select snapshotName,
cost_unit,
New_Study_Category,
MRL_Short_Franchise,

  --========Therapeutic Area Group
  --MK-3475 is PD1
  -- Licensed Compound Check is Cubist 
  --The rest follow MRL Short Franchise

  case
  when MK_or_V='MK-3475' then 'PD1'
  when Licensed_Compound_Check='Cubist' then MRL_Short_Franchise+' (Cubist)'
  when MRL_Short_Franchise not in ('CV','D&E','ID','N&O','Onc','R&I','Vac') then 'Other'
  else MRL_Short_Franchise
  end as Calc_TA_Area_Grp,
  activity_unique_id,
  MK_or_V,
  study_filing_status,

  --======Study Filing Status Group.=======  
  --Authorized, NA,Cancelled,Cancelled- work ongoing=Authorized
  --Projected, On Hold=Projected
  --Rest NA
  case 
  when study_filing_status='Authorized' then 'Authorized'
  when study_filing_status='NA' then 'Authorized'
  when study_filing_status='Cancelled' then 'Authorized'
  when study_filing_status='Cancelled - work ongoing' then 'Authorized'
  --when study_filing_status='On Hold' then 'Authorized'
  when study_filing_status='Projected' then 'Projected'
  when study_filing_status='On Hold' then 'Projected'
  else 'NA'
  end  as Calc_Filing_Status_Grp,

  prioritization_category,
  
  
  case 
  when MK_or_V<>'MK-3475' and  Prioritization_Category='Continue' then 'Medium/Continue'
  when MK_or_V<>'MK-3475' and  Prioritization_Category='High' then 'Medium/Continue'
  when MK_or_V<>'MK-3475' and  Prioritization_Category='Medium' then 'Medium/Continue'
  when MK_or_V<>'MK-3475' and  Prioritization_Category='Unrated' then 'Medium/Continue'
  when MK_or_V<>'MK-3475' and  Prioritization_Category='BLOCK' then 'Medium/Continue'
  when MK_or_V<>'MK-3475' and  Prioritization_Category='Low' then 'Low/Gated'
  when MK_or_V<>'MK-3475' and  Prioritization_Category='Gated' then 'Low/Gated'
  when MK_or_V='MK-3475'  and	 Prioritization_Category='High' then 'High'
  when MK_or_V='MK-3475' and  Prioritization_Category='Continue' then 'Medium/Continue'
  when MK_or_V='MK-3475' and  Prioritization_Category='Medium' then 'Medium/Continue'
  when MK_or_V='MK-3475' and  Prioritization_Category='Unrated' then 'Medium/Continue'
  when MK_or_V='MK-3475' and  Prioritization_Category='BLOCK' then 'Medium/Continue'
  when MK_or_V='MK-3475' and  Prioritization_Category='Low' then 'Low/Gated'
  when MK_or_V='MK-3475' and  Prioritization_Category='Gated' then 'Low/Gated'

  else 'NA'
  end as calc_Prioritization_grp,
  FPE,
  case
  
  ----///////////////////////
  ---MANUAL UPDATE UPDATE FPE TO ONE QUARTER BEHIND FORECAST
  when fpe<='2016-03-31' then 'Ongoing'
  when fpe is null then 'Ongoing'
  when fpe='' then 'Ongoing'
  else  'New'
  end as Calc_FPE_New_Ongoing_grp,
  ----///////////////////////
  ---MANUAL UPDATE UPDATE FPE TO ONE QUARTER BEHIND FORECAST
  Execution_Project_Name,
  Execution_Project_Sequence,
  case
  when Execution_Project_Sequence='Next' then 'Next/Future'
  when Execution_Project_Sequence='Future' then 'Next/Future'
  else 'Current'
  end as calc_execution_project_sequence_grp,
  Current_Clinical_Phase,
  Study_Phase,
  cast(MonthDisplayKey+'-01' as date) as MonthDisplayKey,
  
  
  --Current/Future Quarter
  ------///////////////////////
  ---MANUAL UPDATE UPDATE MONTHDISPLAYKEY TO SET CURRENT FORECAST QUARTER
  case
  when cast (MonthDisplayKey+'-01' as date)<='2016-06-30' then 'Current'
  else 'Future'
  End
  as Calc_current_future_quarter,
  --Current/Future Quarter
  ------///////////////////////
  ---MANUAL UPDATE UPDATE MONTHDISPLAYKEY TO SET CURRENT FORECAST QUARTER
  
  isnull(GrossAmount,0) as GrossAmount

  from  pvpa.dbo.Cost_AsReported_VW
  where
  (cost_unit='Grants'
  and new_study_category='Clin res' AND Activity_Unique_ID NOT LIKE '%Finance%')
  
  OR
  (cost_unit in('Merck Sourced Materials','Competitive Product')) and Activity_Unique_ID not like '%Finance%')as a
2

Replace:

when fpe<='2016-03-31' then 'Ongoing'

with

when fpe<select dateadd(quarter,datediff(quarter,0,current_timestamp),0) then 'Ongoing'

and replace:

when cast (MonthDisplayKey+'-01' as date)<='2016-06-30' then 'Current'

with

when cast (MonthDisplayKey+'-01' as date)<dateadd(quarter,datediff(quarter,0,current_timestamp)+1,0) then 'Current'
3

Bitsmed Thanks a lot. I am attepmting to have a little bit more flexibility and have it as variable user passed parameter so users can specify what is current fpe and quarter to perhaps run different subsequent what if calculations.

4

Create a function that returns a table:

create function dw.pvpa_pull_udf(@q1 date,@q2 date)
returns table
     as return(select SnapshotName
                     ,cost_unit
                     ,New_Study_Category
                     ,MRL_Short_Franchise
                     ,Calc_TA_Area_Grp
                     ,activity_unique_id
                     ,MK_or_V
                     ,study_filing_status
                     ,Calc_Filing_Status_Grp
                     ,prioritization_category
                     ,calc_Prioritization_grp
                     ,FPE
                     ,Calc_FPE_New_Ongoing_grp
                     ,Execution_Project_Name
                     ,Execution_Project_Sequence
                     ,calc_execution_project_sequence_grp
                     ,Current_Clinical_Phase
                     ,Study_Phase
                     ,MonthDisplayKey as Forecast_Month
                     ,Calc_current_future_quarter
                     ,GrossAmount
                     ,Calc_TA_Area_Grp
                     +Calc_Filing_Status_Grp
                     +calc_Prioritization_grp
                     +Calc_FPE_New_Ongoing_grp
                     +Calc_current_future_quarter
                     +calc_execution_project_sequence_grp
                      as FK_Rules_Unique_Id
                     ,SnapshotName
                     +activity_unique_id
                      as FK_PTRS_Unique_ID
                 from (select snapshotName
                             ,cost_unit
                             ,New_Study_Category
                             ,MRL_Short_Franchise
                             /*========Therapeutic Area Group
                              *MK-3475 is PD1
                              * Licensed Compound Check is Cubist 
                              *The rest follow MRL Short Franchise
                              */
                             ,case
                                 when MK_or_V='MK-3475'                then 'PD1'
                                 when Licensed_Compound_Check='Cubist' then MRL_Short_Franchise+' (Cubist)'
                                 when MRL_Short_Franchise not in ('CV'
                                                                 ,'D&E'
                                                                 ,'ID'
                                                                 ,'N&O'
                                                                 ,'Onc'
                                                                 ,'R&I'
                                                                 ,'Vac'
                                                                 )      then 'Other'
                                 else                                   MRL_Short_Franchise
                              end as Calc_TA_Area_Grp
                             ,activity_unique_id
                             ,MK_or_V
                             ,study_filing_status
                             /*======Study Filing Status Group.=======  
                              *Authorized, NA,Cancelled,Cancelled- work ongoing=Authorized
                              *Projected, On Hold=Projected
                              *Rest NA
                              */
                             ,case study_filing_status
                                 when 'Authorized'
                                   or 'NA'
                                   or 'Cancelled'
                                   or 'Cancelled - work ongoing' then 'Authorized'
                                 when 'Projected'
                                   or 'On Hold'                  then 'Projected'
                                 else                                 'NA'
                              end as Calc_Filing_Status_Grp
                             ,prioritization_category
                             ,case
                                 when Prioritization_Category in ('Continue'
                                                                 ,'Medium'
                                                                 ,'Unrated'
                                                                 ,'BLOCK'
                                                                 )
                                   or (MK_or_V<>'MK-3475'
                                  and  Prioritization_Category='High'
                                      )                                      then 'Medium/Continue'
                                 when Prioritization_Category='High'         then 'High'
                                 when Prioritization_Category in ('Low'
                                                                 ,'Gated'
                                                                 )           then 'Low/Gated'
                                 else                                             'NA'
                              end as calc_Prioritization_grp
                             ,FPE
                             ,case
                                 /* ///////////////////////
                                  *MANUAL UPDATE UPDATE FPE TO ONE QUARTER BEHIND FORECAST
                                  */
                                 when fpe<=@q1
                                   or fpe is null
                                   or fpe=''      then 'Ongoing'
                                 else                  'New'
                              end as Calc_FPE_New_Ongoing_grp
                              /* ///////////////////////
                               *MANUAL UPDATE UPDATE FPE TO ONE QUARTER BEHIND FORECAST
                               */
                             ,Execution_Project_Name
                             ,Execution_Project_Sequence
                             ,case
                                 when Execution_Project_Sequence in ('Next','Future') then 'Next/Future'
                                 else                                                      'Current'
                              end as calc_execution_project_sequence_grp
                             ,Current_Clinical_Phase
                             ,Study_Phase
                             ,cast(MonthDisplayKey+'-01' as date) as MonthDisplayKey
                             /*Current/Future Quarter
                              * ///////////////////////
                              *MANUAL UPDATE UPDATE MONTHDISPLAYKEY TO SET CURRENT FORECAST QUARTER
                              */
                             ,case
                                 when cast (MonthDisplayKey+'-01' as date)<=@q2 then 'Current'
                                 else                                                'Future'
                              end as Calc_current_future_quarter
                             /*Current/Future Quarter
                              * ///////////////////////
                              *MANUAL UPDATE UPDATE MONTHDISPLAYKEY TO SET CURRENT FORECAST QUARTER
                              */
                             ,isnull(GrossAmount,0) as GrossAmount
                         from pvpa.dbo.Cost_AsReported_VW
                        where (cost_unit='Grants'
                          and  new_study_category='Clin res'
                              )
                           or (cost_unit in('Merck Sourced Materials','Competitive Product'))
                          and Activity_Unique_ID not like '%Finance%'
                      ) as a
              )
;

Now you can call the function as a table with parameters:

select *
  from dw.pvpa_pull_udf(cast('2016-03-31' as date)
                       ,cast('2016-06-30' as date)
                       )
;
1 Like
5

Thank you very much this worked perfectly.