1 2 3 4 5 6 7 8 9 10 11 12
| SELECT
DATEADD(month,2,tbl_ms_set_steps.MS_EXP_CLOSE_DATE) AS HELLO, tbl_ms_set_steps.active_inactive, tbl_addreses.ADDR_COUNTRY_CODE AS country, tbl_ms_set_steps.MS_RFI_EXT_DL AS extDeadline, tbl_ms_set_steps.MS_SUBJ_PROB_IRIS AS Probability, tbl_MAIN.MAIN_AM AS AM, tbl_MAIN.MAIN_NAME+' '+MS_CASE_NBR+' / '+MS_SR_IRIS+' / '+ms_description+' / Psr: '+ltrim(str(100*MS_SUBJ_PROB_IRIS))+' / Piris: '+ltrim(str(100*MS_SUBJ_PROB_IRIS)) AS CASE1, 50*tbl_ms_set_steps.MS_LICENCE/100 AS LIC_CHF, 50*tbl_ms_set_steps.MS_LICENCE*MS_SUBJ_PROB_IRIS/100 AS PrLIC_CHF, 50*tbl_ms_set_steps.MS_SERVICE/100 AS SRV_CHF, 50*tbl_ms_set_steps.MS_SERVICE*MS_SUBJ_PROB_IRIS/100 AS Pr_SRV_CHF, tbl_ms_set_steps.est_time AS SRV_Time, tbl_ms_set_steps.est_time*MS_SUBJ_PROB_IRIS AS PrSRV_Time, tbl_ms_set_steps.spec_dev_days AS Dev_Time, tbl_ms_set_steps.spec_dev_days*MS_SUBJ_PROB_IRIS AS PrDev_Time, tbl_ms_set_steps.spec_dev_CHF AS Dev_CHF, tbl_ms_set_steps.spec_dev_CHF*MS_SUBJ_PROB_IRIS AS PrDev_CHF, tbl_ms_set_steps.MS_LICENCE+tbl_ms_set_steps.MS_SERVICE+spec_dev_CHF AS totalVolume_CHF, (tbl_ms_set_steps.MS_LICENCE+tbl_ms_set_steps.MS_SERVICE+spec_dev_CHF)*MS_SUBJ_PROB_IRIS/100 AS Pr_totalVolume_CHF, tbl_ms_set_steps.MS_EXP_CLOSE_DATE, tbl_local_lic_types_source.LIC_TYPE_NAME, tbl_phase.Phase, tbl_budget.Budget, tbl_ms_set_steps.MS_SR_IRIS AS SalesResp
FROM
CRM_1.dbo.tbl_addreses tbl_addreses, CRM_1.dbo.tbl_budget tbl_budget, CRM_1.dbo.tbl_local_lic_types_source tbl_local_lic_types_source, CRM_1.dbo.tbl_MAIN tbl_MAIN, CRM_1.dbo.tbl_ms_set_steps tbl_ms_set_steps, CRM_1.dbo.tbl_phase tbl_phase
WHERE
tbl_local_lic_types_source.LIC_TYPE_NAME = 'FIRST' AND tbl_ms_set_steps.MS_PARENT = tbl_MAIN.MAIN_ID_LABEL AND ((tbl_ms_set_steps.MS_SIGNED Is Null) AND (tbl_ms_set_steps.MS_LOST_DATE Is Null) AND (tbl_MAIN.MAIN_NAME<>'MUSTER BANK') AND (tbl_local_lic_types_source.LIC_TYPE_ID=ms_type) AND (tbl_phase.id=idphase) AND (tbl_budget.id=idbudget) AND (tbl_MAIN.MAIN_ID=addr_parent_id) AND (tbl_ms_set_steps.active_inactive=1))
UNION ALL
SELECT
DATEADD(month,3,tbl_ms_set_steps.MS_EXP_CLOSE_DATE) AS HELLO, tbl_ms_set_steps.active_inactive, tbl_addreses.ADDR_COUNTRY_CODE AS country, tbl_ms_set_steps.MS_RFI_EXT_DL AS extDeadline, tbl_ms_set_steps.MS_SUBJ_PROB_IRIS AS Probability, tbl_MAIN.MAIN_AM AS AM, tbl_MAIN.MAIN_NAME+' '+MS_CASE_NBR+' / '+MS_SR_IRIS+' / '+ms_description+' / Psr: '+ltrim(str(100*MS_SUBJ_PROB_IRIS))+' / Piris: '+ltrim(str(100*MS_SUBJ_PROB_IRIS)) AS CASE1, 30*tbl_ms_set_steps.MS_LICENCE/100 AS LIC_CHF, 30*tbl_ms_set_steps.MS_LICENCE*MS_SUBJ_PROB_IRIS/100 AS PrLIC_CHF, 30*tbl_ms_set_steps.MS_SERVICE/100 AS SRV_CHF, 30*tbl_ms_set_steps.MS_SERVICE*MS_SUBJ_PROB_IRIS/100 AS Pr_SRV_CHF, tbl_ms_set_steps.est_time AS SRV_Time, tbl_ms_set_steps.est_time*MS_SUBJ_PROB_IRIS AS PrSRV_Time, tbl_ms_set_steps.spec_dev_days AS Dev_Time, tbl_ms_set_steps.spec_dev_days*MS_SUBJ_PROB_IRIS AS PrDev_Time, tbl_ms_set_steps.spec_dev_CHF AS Dev_CHF, tbl_ms_set_steps.spec_dev_CHF*MS_SUBJ_PROB_IRIS AS PrDev_CHF, tbl_ms_set_steps.MS_LICENCE+tbl_ms_set_steps.MS_SERVICE+spec_dev_CHF AS totalVolume_CHF, (tbl_ms_set_steps.MS_LICENCE+tbl_ms_set_steps.MS_SERVICE+spec_dev_CHF)*MS_SUBJ_PROB_IRIS/100 AS Pr_totalVolume_CHF, tbl_ms_set_steps.MS_EXP_CLOSE_DATE, tbl_local_lic_types_source.LIC_TYPE_NAME, tbl_phase.Phase, tbl_budget.Budget, tbl_ms_set_steps.MS_SR_IRIS AS SalesResp
FROM CRM_1.dbo.tbl_addreses tbl_addreses, CRM_1.dbo.tbl_budget tbl_budget, CRM_1.dbo.tbl_local_lic_types_source tbl_local_lic_types_source, CRM_1.dbo.tbl_MAIN tbl_MAIN, CRM_1.dbo.tbl_ms_set_steps tbl_ms_set_steps, CRM_1.dbo.tbl_phase tbl_phase
WHERE
tbl_local_lic_types_source.LIC_TYPE_NAME = 'FIRST' AND tbl_ms_set_steps.MS_PARENT = tbl_MAIN.MAIN_ID_LABEL AND ((tbl_ms_set_steps.MS_SIGNED Is Null) AND (tbl_ms_set_steps.MS_LOST_DATE Is Null) AND (tbl_MAIN.MAIN_NAME<>'MUSTER BANK') AND (tbl_local_lic_types_source.LIC_TYPE_ID=ms_type) AND (tbl_phase.id=idphase) AND (tbl_budget.id=idbudget) AND (tbl_MAIN.MAIN_ID=addr_parent_id) AND (tbl_ms_set_steps.active_inactive=1)) |
Partager