SQLize Online / PHPize Online  /  SQLtest Online

CREATE PROCEDURE [cpgbi].[sp_BI_STACKLINE_SSD_MSS] As BEGIN Set NoCount On BEGIN TRY DECLARE @CurDate DATETIME SET @CurDate = GETDATE() DECLARE @Month_Start INT DECLARE @Quarter_Month_Start INT DECLARE @Quarter_Year_Start INT SELECT @Quarter_Month_Start = 10 /*CASE WHEN MONTH(@CurDate) <=3 THEN 7 WHEN MONTH(@CurDate) >=4 AND MONTH(@CurDate) <=6 THEN 10 WHEN MONTH(@CurDate) >=7 AND MONTH(@CurDate) <=9 THEN 1 WHEN MONTH(@CurDate) >=10 AND MONTH(@CurDate) <=12 THEN 4 END */--commenting as we need data from 2022 Q4 SELECT @Quarter_Year_Start = 2022/*CASE WHEN MONTH(@CurDate) <=6 THEN YEAR(@CurDate)-1 ELSE YEAR(@CurDate) END */ --commenting as we need data from 2022 Q4 /*SELECT @Month_Start = CASE WHEN MONTH(@CurDate) <=3 THEN 1 WHEN MONTH(@CurDate) >=4 AND MONTH(@CurDate) <=6 THEN 4 WHEN MONTH(@CurDate) >=7 AND MONTH(@CurDate) <=9 THEN 7 WHEN MONTH(@CurDate) >=10 AND MONTH(@CurDate) <=12 THEN 10 END */ SET @Month_Start = 10 SELECT [RETAILER_SKU] ,SUM(VOL) AS Volume , SUM(ESTMTE_SALE) AS [Estimated Sales] , CASE When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) = 'CRUCIAL' AND UPPER(LEFT([MODL_NUM], 2)) = 'BL' Then 'Ballistix' When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) LIKE 'SAMSUNG%' Then 'Samsung' When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) LIKE 'SANDISK%' Then 'Sandisk' When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) LIKE 'KINGSTON%' Then 'Kingston' When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) LIKE 'WESTERNDIGITAL%' Then 'Western Digital' When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) LIKE 'SEAGATE%' Then 'Seagate' When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) LIKE 'INLAND%' Then 'Inland' When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) LIKE 'SPSILICONPOWER%' Then 'SP Silicon Power' WHEN (UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) LIKE '%CRUCIAL%' OR UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) LIKE '%MICRON%') THEN 'Crucial' When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) LIKE 'PNY%' Then 'PNY' When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) LIKE 'FANXIANG%' Then 'Fanxiang' When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) LIKE 'LEXAR%' Then 'Lexar' When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) LIKE 'LEVEN%' Then 'Leven' When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) LIKE 'PATRIOT MEMORY%' Then 'Patriot Memory' When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) LIKE 'SYNOLOGY%' Then 'Synology' When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) LIKE 'TIMETEC%' Then 'Timetec' When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) LIKE 'CORSAIR%' Then 'Corsair' When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ',''),' ','')) LIKE 'SKHYNIX%' Then 'SK Hynix' When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ',''),' ','')) LIKE 'TEAM%' Then 'TeamGroup' When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ',''),' ','')) LIKE 'WDBLACK%' Then 'WD Black' When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) NOT LIKE 'HPE%' AND UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) LIKE 'HP%'Then 'HP' When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ',''),' ','')) LIKE 'SABRENT%' Then 'Sabrent' Else UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) END AS [Brand Name] , SUB_CAT_NM AS [Subcategory Name] /** Commented on 02/15 ***/ -- , Case When WK_ID =202053 then 202101 -- When WK_ID >=202101 and WK_ID<=202151 then WK_ID+1--added 1/20/2022 ---- When WK_ID >=202101 then WK_ID+1 -- ELSE WK_ID -- End as [Week ID] /*** end comment **/ , Case When WK_ID =202053 then 202101 --When WK_ID >=202102 then WK_ID+1 When WK_ID >=202101 and WK_ID<=202151 then WK_ID+1 When WK_ID =202152 then 202201 When WK_ID >=202201 and WK_ID<=202251 then WK_ID+1 When WK_ID = 202252 then 202301 When WK_ID >=202301 and WK_ID<=202351 then WK_ID+1 When WK_ID = 202352 then 202401 When WK_ID >=202401 and WK_ID<=202452 then WK_ID+1 When WK_ID = 202453 then 202501 ELSE WK_ID End as [Week ID] , RGN_CD AS [Region Code] ,TITL AS [Description]--added 3/2/2022 ,ISNULL(MODL_NUM,'') AS MODL_NUM Into #tb_stk FROM cpgbi.BI_STACKLINE WHERE SUB_CAT_NM IN( 'Internal Solid State Drives','External Solid State Drives') --AND MODL_NUM IS NOT NULL AND RGN_CD IN( 'US','FR','IT','UK','ES','DE') GROUP BY [RETAILER_SKU], CASE When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) = 'CRUCIAL' AND UPPER(LEFT([MODL_NUM], 2)) = 'BL' Then 'Ballistix' When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) LIKE 'SAMSUNG%' Then 'Samsung' When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) LIKE 'SANDISK%' Then 'Sandisk' When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) LIKE 'KINGSTON%' Then 'Kingston' When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) LIKE 'WESTERNDIGITAL%' Then 'Western Digital' When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) LIKE 'SEAGATE%' Then 'Seagate' When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) LIKE 'INLAND%' Then 'Inland' When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) LIKE 'SPSILICONPOWER%' Then 'SP Silicon Power' WHEN (UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) LIKE '%CRUCIAL%' OR UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) LIKE '%MICRON%') THEN 'Crucial' When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) LIKE 'PNY%' Then 'PNY' When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) LIKE 'FANXIANG%' Then 'Fanxiang' When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) LIKE 'LEXAR%' Then 'Lexar' When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) LIKE 'LEVEN%' Then 'Leven' When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) LIKE 'PATRIOT MEMORY%' Then 'Patriot Memory' When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) LIKE 'SYNOLOGY%' Then 'Synology' When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) LIKE 'TIMETEC%' Then 'Timetec' When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) LIKE 'CORSAIR%' Then 'Corsair' When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ',''),' ','')) LIKE 'SKHYNIX%' Then 'SK Hynix' When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ',''),' ','')) LIKE 'TEAM%' Then 'TeamGroup' When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ',''),' ','')) LIKE 'WDBLACK%' Then 'WD Black' When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) NOT LIKE 'HPE%' AND UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) LIKE 'HP%'Then 'HP' When UPPER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ',''),' ','')) LIKE 'SABRENT%' Then 'Sabrent' Else UPPER(REPLACE(REPLACE(REPLACE(REPLACE(BRND_NM,'/',''),'_',''),'-',''),' ','')) END , SUB_CAT_NM , WK_ID , RGN_CD ,TITL , MODL_NUM DELETE FROM #tb_stk WHERE (MODL_NUM LIKE '%P1%' OR MODL_NUM LIKE '%P2%') AND (UPPER([Brand Name]) LIKE '%CRUCIAL%' OR UPPER([Brand Name]) LIKE '%MICRON%' ) UPDATE ts SET MODL_NUM = CASE WHEN ts.MODL_NUM='' OR ts.MODL_NUM IS NULL THEN res.PART_NO ELSE ts.MODL_NUM END FROM #tb_stk ts INNER JOIN (select * from [cpgbi].[BI_KEEPA_FEED_DATA] where MANUFACTURER = 'CRUCIAL' ) res ON ts.RETAILER_SKU =res.[ASIN] AND ts.[Region Code] =res.COUNTRY select CAT_NM cat_nm ,SUB_CAT_NM sub_cat_nm ,SWIM_LANE_DESIGNATION swim_lane_designation ,SUB_SWIMLANE sub_swinlane ,TECHNOLOGY technology ,HEATSINK heatsink ,FORM_FACTOR formfactor ,CAPACITY capacity ,RETAILER_SKU Retailer_sku into #tb_asin FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY RETAILER_SKU ORDER BY TITL) AS RNK ,CAT_NM ,SUB_CAT_NM ,SWIM_LANE_DESIGNATION ,SUB_SWIMLANE ,TECHNOLOGY ,HEATSINK ,FORM_FACTOR , CASE WHEN TECHNOLOGY= 'SATA' AND UPPER(CAPACITY) IN ('250GB','500GB','1TB','2TB','4TB','8TB') THEN CAPACITY WHEN TECHNOLOGY IN ('Gen3','Gen4','Gen5') AND UPPER(CAPACITY) IN ('500GB','1TB','2TB','4TB') THEN CAPACITY ELSE '' END AS CAPACITY ,RETAILER_SKU FROM cpgbi.BI_SSD_ASIN_MASTER ) a where a.RNK = 1 SELECT distinct [ww] ,[ww_no] ,[ww_name] ,[year_begin] ,[quarter_begin] ,[month_begin] ,[week_begin] ,DATEADD(dd,-4 ,[week_begin]) AS [week_begin_cal] ,CAST(CAST(DATEADD(week, DATEDIFF(day, 0,[week_begin])/7,0)AS DATE)AS VARCHAR(10)) AS [DATEWEEK] Into #tb_dt FROM [CPG].[ref].[vDates_Xref] Group by [ww] ,[ww_no] ,[ww_name] ,[year_begin] ,[quarter_begin] ,[month_begin] ,[week_begin] select [RetailerSKU] ,[productClass1] , CASE WHEN LTRIM(RTRIM(REPLACE(UPPER([productClass2]),'CLASS',''))) IN ('500GB','1TB','2TB','4TB') THEN LTRIM(RTRIM(REPLACE(UPPER([productClass2]),'CLASS',''))) ELSE '' END [productClass2] ,[productClass3] ,[productClass4] ,[ProductFamily] ,[MFGName] ,[BrandName] into #tb_pssd from [CPG].[cpgbi].[BI_STACKLINE_PORTABLE_SSD_DETAILS] /*WHERE lower(CorporationName) = 'amazon' AND ProductType = 'External SSD'*/ SELECT [RETAILERSKU] ,[REPORTINGENTITY] ,[CATEGORY] ,[WHYEXCLUDED] into #tb_junk FROM [CPG].[cpgbi].[BI_STACKLINE_JUNK_ASIN] SELECT [Volume] , CASE WHEN [Region Code] IN( 'ES','FR','DE','IT') THEN ABS([Estimated Sales])*1.20 WHEN [Region Code] = 'UK' THEN ABS([Estimated Sales])*1.40 ELSE ABS([Estimated Sales]) END AS [Estimated Sales] ,[Brand Name] ,[Subcategory Name] ,[Week ID] ,CASE WHEN [Region Code] IN ('US') THEN 'US' ELSE 'EU' END AS [Region Code] --,[Description] ,[ww] ,[ww_no] ,[ww_name] ,[year_begin] ,[quarter_begin] ,[month_begin] ,[week_begin] -- ,[week_begin_cal] , ISNULL(MODL_NUM , '') AS MODL_NUM ,[productClass1] ,[productClass2] ,[productClass3] ,[productClass4] ,[ProductFamily] -- ,[MFGName] ,[WHYEXCLUDED] ,cat_nm ,sub_cat_nm ,swim_lane_designation ,sub_swinlane , ISNULL(technology,'Other SSD') AS technology , CASE WHEN technology = 'SATA' THEN technology+' ' + ISNULL(capacity,'') + ' '+ISNULL(sub_swinlane,'') WHEN technology IN( 'Gen3','Gen4','Gen5') THEN REPLACE(ISNULL(swim_lane_designation,''),'PCIe','NVMe') +' '+technology+' ' + CASE WHEN UPPER( heatsink) IN('N', 'NO' ,'NEVER') THEN 'no HS ' WHEN UPPER( heatsink) IN ('Y', 'YES' ) THEN 'with HS ' ELSE '' END + ISNULL(capacity,'') + ' '+ +ISNULL(sub_swinlane,'') ELSE ISNULL(technology,'Other SSD') END AS segment -- ,heatsink , formfactor , capacity , dt.[week_begin_cal] AS [week_begin_cal] , SUBSTRING(datename(month,dt.[week_begin_cal]),1,3)+' '+ cast(datepart(year,dt.[week_begin_cal]) as char(4)) AS YEARMON , 'Q'+DATENAME(QUARTER,dt.[week_begin_cal])+' '+ cast(datepart(year,dt.[week_begin_cal]) as char(4)) AS YEARQTR , dt.DATEWEEK , CAST('' AS VARCHAR(50)) as segment_new into #final_results FROM #tb_stk stk left join #tb_dt dt on stk.[Week ID] = dt.[ww] left join #tb_pssd pd on stk.[RETAILER_SKU]=pd.[RetailerSKU] left join #tb_junk jk on stk.[RETAILER_SKU]=jk.RETAILERSKU left join #tb_asin am on stk.[RETAILER_SKU] = am.Retailer_sku WHERE --stk.MODL_NUM IS NOT NULL [Subcategory Name] = 'Internal Solid State Drives' AND ((datepart(year,dt.[week_begin_cal]) >= @Quarter_Year_Start AND datepart(month,dt.[week_begin_cal]) >= @Quarter_Month_Start) OR (datepart(year,dt.[week_begin_cal]) > @Quarter_Year_Start AND datepart(month,dt.[week_begin_cal]) >=1)) --AND datepart(year,dt.[week_begin_cal]) <= YEAR(GETDATE()) --and technology IS NOT NULL -- select * from #final_results INSERT INTO #final_results ([Volume],[Estimated Sales],[Brand Name] ,[Subcategory Name] ,[Region Code] , MODL_NUM, [productClass4],[WHYEXCLUDED], technology,segment,capacity,[week_begin_cal],YEARMON, YEARQTR,DATEWEEK,segment_new) SELECT [Volume] , CASE WHEN [Region Code] IN( 'ES','FR','DE','IT') THEN ABS([Estimated Sales])*1.20 WHEN [Region Code] = 'UK' THEN ABS([Estimated Sales])*1.40 ELSE ABS([Estimated Sales]) END AS [Estimated Sales] ,[Brand Name] ,[Subcategory Name] -- ,[Week ID] ,CASE WHEN [Region Code] IN ('US') THEN 'US' ELSE 'EU' END AS [Region Code] --,[Description] /* ,[ww] ,[ww_no] ,[ww_name] ,[year_begin] ,[quarter_begin] ,[month_begin] ,[week_begin] -- ,[week_begin_cal]*/ , ISNULL(MODL_NUM , '') AS MODL_NUM --,[productClass1] --,[productClass2] --,[productClass3] ,[productClass4] , [WHYEXCLUDED] --,[ProductFamily] , CASE WHEN [productClass4] IS NOT NULL AND ISNUMERIC(REPLACE(UPPER([productClass4]),'MB/S CLASS','')) = 1 AND REPLACE(UPPER([productClass4]),'MB/S CLASS','')< 1000 THEN '<1GB/s pSSD' WHEN [productClass4] IS NOT NULL AND ISNUMERIC(REPLACE(UPPER([productClass4]),'MB/S CLASS','')) = 1 AND REPLACE(UPPER([productClass4]),'MB/S CLASS','')>= 1000 AND REPLACE(UPPER([productClass4]),'MB/S CLASS','')< 2000 THEN '1GB/s pSSD' WHEN [productClass4] IS NOT NULL AND ISNUMERIC(REPLACE(UPPER([productClass4]),'MB/S CLASS','')) = 1 AND REPLACE(UPPER([productClass4]),'MB/S CLASS','')>= 2000 AND REPLACE(UPPER([productClass4]),'MB/S CLASS','')< 3000 THEN '2GB/s pSSD' ELSE 'pSSD' END AS technology , CASE WHEN [productClass4] IS NOT NULL AND ISNUMERIC(REPLACE(UPPER([productClass4]),'MB/S CLASS','')) = 1 AND REPLACE(UPPER([productClass4]),'MB/S CLASS','')< 1000 AND [productClass2] <>'' THEN 'pSSD <1GB/s '+ [productClass2] WHEN [productClass4] IS NOT NULL AND ISNUMERIC(REPLACE(UPPER([productClass4]),'MB/S CLASS','')) = 1 AND REPLACE(UPPER([productClass4]),'MB/S CLASS','')>= 1000 AND REPLACE(UPPER([productClass4]),'MB/S CLASS','')< 2000 AND [productClass2] <>'' THEN 'pSSD 1GB/s ' + [productClass2] WHEN [productClass4] IS NOT NULL AND ISNUMERIC(REPLACE(UPPER([productClass4]),'MB/S CLASS','')) = 1 AND REPLACE(UPPER([productClass4]),'MB/S CLASS','')>= 2000 AND REPLACE(UPPER([productClass4]),'MB/S CLASS','')< 3000 AND [productClass2] <>'' THEN 'pSSD 2GB/s ' + [productClass2] WHEN [productClass2] ='' AND [productClass4] IS NOT NULL AND ISNUMERIC(REPLACE(UPPER([productClass4]),'MB/S CLASS','')) = 1 AND REPLACE(UPPER([productClass4]),'MB/S CLASS','')>= 0 AND REPLACE(UPPER([productClass4]),'MB/S CLASS','')< 3000 THEN '' ELSE 'Other pSSD' END AS segment -- ,heatsink , [productClass2] AS capacity , dt.[week_begin_cal] AS [week_begin_cal] , SUBSTRING(datename(month,dt.[week_begin_cal]),1,3)+' '+ cast(datepart(year,dt.[week_begin_cal]) as char(4)) AS YEARMON , 'Q'+DATENAME(QUARTER,dt.[week_begin_cal])+' '+ cast(datepart(year,dt.[week_begin_cal]) as char(4)) AS YEARQTR , dt.DATEWEEK , '' AS segment_new FROM #tb_stk stk left join #tb_dt dt on stk.[Week ID] = dt.[ww] left join #tb_pssd pd on stk.[RETAILER_SKU]=pd.[RetailerSKU] left join #tb_junk jk on stk.[RETAILER_SKU]=jk.RETAILERSKU -- left join #tb_asin am --on stk.[RETAILER_SKU] = am.Retailer_sku WHERE --stk.MODL_NUM IS NOT NULL [Subcategory Name] = 'External Solid State Drives' AND ((datepart(year,dt.[week_begin_cal]) >= @Quarter_Year_Start AND datepart(month,dt.[week_begin_cal]) >= @Quarter_Month_Start) OR (datepart(year,dt.[week_begin_cal]) > @Quarter_Year_Start AND datepart(month,dt.[week_begin_cal]) >=1)) AND [WHYEXCLUDED] IS NULL UPDATE #final_results SET segment_new = CASE WHEN technology = 'Gen3' AND UPPER(segment) LIKE '%MAINSTREAM%' THEN 'NVMe Gen3 Mainstream' WHEN technology IN('Gen3','Gen4','Gen5') AND UPPER(segment) LIKE '%WITH HS%' AND UPPER(segment) LIKE '%VALUE%' THEN '' WHEN technology IN ('Gen3','Gen4','Gen5') AND (UPPER(segment) LIKE '%VALUE%' OR UPPER(segment) LIKE '%PERFORMANCE%') AND UPPER(segment) LIKE '%HS%' AND capacity <>'' THEN segment WHEN technology ='SATA' AND (UPPER(segment) LIKE '%VALUE%' OR UPPER(segment) LIKE '%PERFORMANCE%') AND capacity <>'' THEN segment WHEN technology IN ( '1GB/s pSSD','2GB/s pSSD') AND segment like '%500GB%' THEN '' WHEN technology LIKE '%pSSD%' THEN segment ELSE '' END UPDATE fr SET MODL_NUM = li.MODL_NUM_TO_SHOW FROM #final_results fr INNER JOIN cpgbi.BI_SSD_MODL_NUM_LIST li ON li.MODL_NUM_TO_REPLACE = fr.MODL_NUM --WHERE fr.[Region Code] ='EU' ----------------------------------------------------------------------------------------------------------------- SELECT technology AS Cat , segment_new AS segment , YEARQTR /*, CASE WHEN UPPER([Brand Name]) LIKE '%CRUCIAL%' THEN MODL_NUM WHEN UPPER([Brand Name]) LIKE '%MICRON%' THEN MODL_NUM ELSE '' END AS MODL_NUM*/ , [Region Code] , SUM([Estimated Sales]) AS TOTAL_REV , [Subcategory Name] --, SUM(CASE WHEN UPPER([Brand Name]) = 'CRUCIAL' THEN ABS([Estimated Sales]) -- ELSE 0 -- END) AS CRUCIAL_REV into #tot_revnue FROM #final_results WHERE (([Subcategory Name] ='Internal Solid State Drives' AND [Brand Name] IN (SELECT BRND_NM FROM cpgbi.BI_STACKLINE_SSD_BRAND)) OR ([Subcategory Name] ='External Solid State Drives')) GROUP BY technology , segment_new , YEARQTR /* , CASE WHEN UPPER([Brand Name]) LIKE '%CRUCIAL%' THEN MODL_NUM WHEN UPPER([Brand Name]) LIKE '%MICRON%' THEN MODL_NUM ELSE '' END*/ , [Region Code] , [Subcategory Name] --SELECT * FROM #BRND_DET SELECT technology AS Cat , segment_new AS segment , SUM([Estimated Sales]) AS CRUCIAL_REV --, SUM(CASE WHEN UPPER([Brand Name]) = 'CRUCIAL' THEN ABS([Estimated Sales]) -- ELSE 0 -- END) AS CRUCIAL_REV -- , MODL_NUM , YEARQTR , [Region Code] , [Subcategory Name] , CASE WHEN segment_new = '' THEN '' WHEN technology = 'SATA' AND (MODL_NUM LIKE '%BX500%' OR MODL_NUM LIKE '%MX500%') AND MODL_NUM LIKE '%1' AND MODL_NUM NOT LIKE '%960BX500%' THEN MODL_NUM WHEN technology = 'Gen3' AND UPPER(segment) LIKE '%MAINSTREAM%' THEN '' WHEN technology IN('Gen3','Gen4','Gen5') AND UPPER(segment) LIKE '%WITH HS%' AND UPPER(segment) LIKE '%VALUE%' THEN '' WHEN technology IN ('Gen3','Gen4','Gen5') AND (MODL_NUM LIKE '%P3%' OR MODL_NUM LIKE '%P5%' OR MODL_NUM LIKE '%T700%' OR MODL_NUM LIKE '%T500%') THEN MODL_NUM WHEN technology LIKE '%pSSD%' AND segment<>'Other pSSD' AND segment_new<>'' AND (MODL_NUM LIKE '%X6%' OR MODL_NUM LIKE '%X8%' OR MODL_NUM LIKE '%X9%' OR MODL_NUM LIKE '%X10%') THEN MODL_NUM ELSE '' END AS MODL_NUM into #crucial_revnue FROM #final_results WHERE (UPPER([Brand Name]) LIKE '%CRUCIAL%' OR UPPER([Brand Name]) LIKE '%MICRON%') --AND ISNULL(MODL_NUM,'')<>'' GROUP BY technology , segment_new --, MODL_NUM , YEARQTR , [Region Code] , [Subcategory Name] , CASE WHEN segment_new = '' THEN '' WHEN technology = 'SATA' AND (MODL_NUM LIKE '%BX500%' OR MODL_NUM LIKE '%MX500%') AND MODL_NUM LIKE '%1' AND MODL_NUM NOT LIKE '%960BX500%' THEN MODL_NUM WHEN technology = 'Gen3' AND UPPER(segment) LIKE '%MAINSTREAM%' THEN '' WHEN technology IN('Gen3','Gen4','Gen5') AND UPPER(segment) LIKE '%WITH HS%' AND UPPER(segment) LIKE '%VALUE%' THEN '' WHEN technology IN ('Gen3','Gen4','Gen5') AND (MODL_NUM LIKE '%P3%' OR MODL_NUM LIKE '%P5%' OR MODL_NUM LIKE '%T700%' OR MODL_NUM LIKE '%T500%') THEN MODL_NUM WHEN technology LIKE '%pSSD%' AND segment<>'Other pSSD' AND segment_new<>'' AND (MODL_NUM LIKE '%X6%' OR MODL_NUM LIKE '%X8%' OR MODL_NUM LIKE '%X9%' OR MODL_NUM LIKE '%X10%') THEN MODL_NUM ELSE '' END --------------------------------------------------------------------------------------------------- SELECT technology AS Cat , segment_new AS segment , YEARMON /* , CASE WHEN UPPER([Brand Name]) LIKE '%CRUCIAL%' THEN MODL_NUM WHEN UPPER([Brand Name]) LIKE '%MICRON%' THEN MODL_NUM ELSE '' END AS MODL_NUM*/ , [Region Code] , SUM([Estimated Sales]) AS TOTAL_REV , [Subcategory Name] --, SUM(CASE WHEN UPPER([Brand Name]) = 'CRUCIAL' THEN ABS([Estimated Sales]) -- ELSE 0 -- END) AS CRUCIAL_REV into #tot_revnue1 FROM #final_results WHERE /*datepart(year,[week_begin_cal]) = YEAR(@CurDate) AND datepart(month,[week_begin_cal]) >= @Month_Start*/ --AND (([Subcategory Name] ='Internal Solid State Drives' AND [Brand Name] IN (SELECT BRND_NM FROM cpgbi.BI_STACKLINE_SSD_BRAND)) OR ([Subcategory Name] ='External Solid State Drives')) GROUP BY technology , segment_new , YEARMON --, CASE WHEN UPPER([Brand Name]) LIKE '%CRUCIAL%' THEN MODL_NUM -- WHEN UPPER([Brand Name]) LIKE '%MICRON%' THEN MODL_NUM --ELSE '' END , [Region Code] , [Subcategory Name] SELECT technology AS Cat , segment_new AS segment , SUM([Estimated Sales]) AS CRUCIAL_REV --, SUM(CASE WHEN UPPER([Brand Name]) = 'CRUCIAL' THEN ABS([Estimated Sales]) -- ELSE 0 -- END) AS CRUCIAL_REV --, MODL_NUM , YEARMON , [Region Code] , [Subcategory Name] , CASE WHEN segment_new = '' THEN '' WHEN technology = 'SATA' AND (MODL_NUM LIKE '%BX500%' OR MODL_NUM LIKE '%MX500%') AND MODL_NUM LIKE '%1' AND MODL_NUM NOT LIKE '%960BX500%' THEN MODL_NUM WHEN technology = 'Gen3' AND UPPER(segment) LIKE '%MAINSTREAM%' THEN '' WHEN technology IN('Gen3','Gen4','Gen5') AND UPPER(segment) LIKE '%WITH HS%' AND UPPER(segment) LIKE '%VALUE%' THEN '' WHEN technology IN ('Gen3','Gen4','Gen5') AND (MODL_NUM LIKE '%P3%' OR MODL_NUM LIKE '%P5%' OR MODL_NUM LIKE '%T700%' OR MODL_NUM LIKE '%T500%') THEN MODL_NUM WHEN technology LIKE '%pSSD%' AND segment<>'Other pSSD' AND segment_new<>'' AND (MODL_NUM LIKE '%X6%' OR MODL_NUM LIKE '%X8%' OR MODL_NUM LIKE '%X9%' OR MODL_NUM LIKE '%X10%') THEN MODL_NUM ELSE '' END AS MODL_NUM into #crucial_revnue1 FROM #final_results WHERE /*datepart(year,[week_begin_cal]) = YEAR(@CurDate) AND datepart(month,[week_begin_cal]) >= @Month_Start*/ --AND (UPPER([Brand Name]) LIKE '%CRUCIAL%' OR UPPER([Brand Name]) LIKE '%MICRON%' ) --AND ISNULL(MODL_NUM,'')<>'' GROUP BY technology , segment_new -- , MODL_NUM , YEARMON , [Region Code] , [Subcategory Name] , CASE WHEN segment_new = '' THEN '' WHEN technology = 'SATA' AND (MODL_NUM LIKE '%BX500%' OR MODL_NUM LIKE '%MX500%') AND MODL_NUM LIKE '%1' AND MODL_NUM NOT LIKE '%960BX500%' THEN MODL_NUM WHEN technology = 'Gen3' AND UPPER(segment) LIKE '%MAINSTREAM%' THEN '' WHEN technology IN('Gen3','Gen4','Gen5') AND UPPER(segment) LIKE '%WITH HS%' AND UPPER(segment) LIKE '%VALUE%' THEN '' WHEN technology IN ('Gen3','Gen4','Gen5') AND (MODL_NUM LIKE '%P3%' OR MODL_NUM LIKE '%P5%' OR MODL_NUM LIKE '%T700%' OR MODL_NUM LIKE '%T500%') THEN MODL_NUM WHEN technology LIKE '%pSSD%' AND segment<>'Other pSSD' AND segment_new<>'' AND (MODL_NUM LIKE '%X6%' OR MODL_NUM LIKE '%X8%' OR MODL_NUM LIKE '%X9%' OR MODL_NUM LIKE '%X10%') THEN MODL_NUM ELSE '' END -------------------------------------------------------------------------------------------------------------------- SELECT technology AS Cat , segment_new AS segment , DATEWEEK /*, CASE WHEN UPPER([Brand Name]) LIKE '%CRUCIAL%' THEN MODL_NUM WHEN UPPER([Brand Name]) LIKE '%MICRON%' THEN MODL_NUM ELSE '' END AS MODL_NUM*/ , [Region Code] , SUM([Estimated Sales]) AS TOTAL_REV , [Subcategory Name] --, SUM(CASE WHEN UPPER([Brand Name]) = 'CRUCIAL' THEN ABS([Estimated Sales]) -- ELSE 0 -- END) AS CRUCIAL_REV into #tot_revnue2 FROM #final_results WHERE (([Subcategory Name] ='Internal Solid State Drives' AND [Brand Name] IN (SELECT BRND_NM FROM cpgbi.BI_STACKLINE_SSD_BRAND)) OR ([Subcategory Name] ='External Solid State Drives')) GROUP BY technology , segment_new , DATEWEEK /* , CASE WHEN UPPER([Brand Name]) LIKE '%CRUCIAL%' THEN MODL_NUM WHEN UPPER([Brand Name]) LIKE '%MICRON%' THEN MODL_NUM ELSE '' END*/ , [Region Code] , [Subcategory Name] --SELECT * FROM #BRND_DET SELECT technology AS Cat , segment_new AS segment , SUM([Estimated Sales]) AS CRUCIAL_REV --, SUM(CASE WHEN UPPER([Brand Name]) = 'CRUCIAL' THEN ABS([Estimated Sales]) -- ELSE 0 -- END) AS CRUCIAL_REV -- , MODL_NUM , DATEWEEK , [Region Code] , [Subcategory Name] , CASE WHEN segment_new = '' THEN '' WHEN technology = 'SATA' AND (MODL_NUM LIKE '%BX500%' OR MODL_NUM LIKE '%MX500%') AND MODL_NUM LIKE '%1' AND MODL_NUM NOT LIKE '%960BX500%' THEN MODL_NUM WHEN technology = 'Gen3' AND UPPER(segment) LIKE '%MAINSTREAM%' THEN '' WHEN technology IN('Gen3','Gen4','Gen5') AND UPPER(segment) LIKE '%WITH HS%' AND UPPER(segment) LIKE '%VALUE%' THEN '' WHEN technology IN ('Gen3','Gen4','Gen5') AND (MODL_NUM LIKE '%P3%' OR MODL_NUM LIKE '%P5%' OR MODL_NUM LIKE '%T700%' OR MODL_NUM LIKE '%T500%') THEN MODL_NUM WHEN technology LIKE '%pSSD%' AND segment<>'Other pSSD' AND segment_new<>'' AND (MODL_NUM LIKE '%X6%' OR MODL_NUM LIKE '%X8%' OR MODL_NUM LIKE '%X9%' OR MODL_NUM LIKE '%X10%') THEN MODL_NUM ELSE '' END AS MODL_NUM into #crucial_revnue2 FROM #final_results WHERE (UPPER([Brand Name]) LIKE '%CRUCIAL%' OR UPPER([Brand Name]) LIKE '%MICRON%') -- AND ISNULL(MODL_NUM,'')<>'' GROUP BY technology , segment_new --, MODL_NUM , DATEWEEK , [Region Code] , [Subcategory Name] , CASE WHEN segment_new = '' THEN '' WHEN technology = 'SATA' AND (MODL_NUM LIKE '%BX500%' OR MODL_NUM LIKE '%MX500%') AND MODL_NUM LIKE '%1' AND MODL_NUM NOT LIKE '%960BX500%' THEN MODL_NUM WHEN technology = 'Gen3' AND UPPER(segment) LIKE '%MAINSTREAM%' THEN '' WHEN technology IN('Gen3','Gen4','Gen5') AND UPPER(segment) LIKE '%WITH HS%' AND UPPER(segment) LIKE '%VALUE%' THEN '' WHEN technology IN ('Gen3','Gen4','Gen5') AND (MODL_NUM LIKE '%P3%' OR MODL_NUM LIKE '%P5%' OR MODL_NUM LIKE '%T700%' OR MODL_NUM LIKE '%T500%') THEN MODL_NUM WHEN technology LIKE '%pSSD%' AND segment<>'Other pSSD' AND segment_new<>'' AND (MODL_NUM LIKE '%X6%' OR MODL_NUM LIKE '%X8%' OR MODL_NUM LIKE '%X9%' OR MODL_NUM LIKE '%X10%') THEN MODL_NUM ELSE '' END ------------------------------------------------------------------------------------------------------------- -- drop table #FINAL_SSD_RESULTS SELECT * INTO #FINAL_SSD_RESULTS FROM ( SELECT ISNULL(tr.Cat,cr.Cat) AS Cat , ISNULL(tr.segment,cr.segment) AS segment -- , ttr.TOTAL_REV_CAT , ISNULL(cr.MODL_NUM,'') AS MODL_NUM , tr.TOTAL_REV , ISNULL(tr.[Region Code] , cr.[Region Code]) AS RGN_CD , ISNULL(cr.CRUCIAL_REV,0) AS CRUCIAL_REV , ISNULL(tr.YEARQTR , cr.YEARQTR) AS YEARMON , ISNULL(tr.[Subcategory Name], cr.[Subcategory Name]) AS [Subcategory Name] , 1 AS RESULT_TYPE FROM #tot_revnue tr -- INNER JOIN #tot_revnue_technology ttr --ON tr.Cat = ttr.Cat --AND tr.YEARMON = ttr.YEARMON --AND tr.[Region Code] = ttr.[Region Code] FULL OUTER JOIN #crucial_revnue cr ON tr.Cat = cr.Cat AND tr.segment = cr.segment AND tr.[Region Code] = cr.[Region Code] AND tr.YEARQTR = cr.YEARQTR --AND tr.MODL_NUM = cr.MODL_NUM AND tr.[Subcategory Name] = cr.[Subcategory Name] WHERE tr.Cat IS NOT NULL AND tr.segment IS NOT NULL AND tr.[Region Code] IS NOT NULL --AND tr.Capacity IS NOT NULL --AND tr.Capacity!='Unknown' AND tr.YEARQTR IS NOT NULL UNION ALL SELECT ISNULL(tr.Cat,cr.Cat) AS Cat , ISNULL(tr.segment,cr.segment) AS segment -- , ttr.TOTAL_REV_CAT , ISNULL(cr.MODL_NUM,'') AS MODL_NUM , tr.TOTAL_REV , ISNULL(tr.[Region Code] , cr.[Region Code]) AS RGN_CD , ISNULL(cr.CRUCIAL_REV,0) AS CRUCIAL_REV , ISNULL(tr.YEARMON , cr.YEARMON) AS YEARMON , ISNULL(tr.[Subcategory Name], cr.[Subcategory Name]) AS [Subcategory Name] , 2 AS RESULT_TYPE FROM #tot_revnue1 tr -- INNER JOIN #tot_revnue_technology ttr --ON tr.Cat = ttr.Cat --AND tr.YEARMON = ttr.YEARMON --AND tr.[Region Code] = ttr.[Region Code] FULL OUTER JOIN #crucial_revnue1 cr ON tr.Cat = cr.Cat AND tr.segment = cr.segment AND tr.[Region Code] = cr.[Region Code] AND tr.YEARMON = cr.YEARMON -- AND tr.MODL_NUM = cr.MODL_NUM AND tr.[Subcategory Name] = cr.[Subcategory Name] WHERE tr.Cat IS NOT NULL AND tr.segment IS NOT NULL AND tr.[Region Code] IS NOT NULL --AND tr.Capacity IS NOT NULL --AND tr.Capacity!='Unknown' AND tr.YEARMON IS NOT NULL UNION ALL SELECT ISNULL(tr.Cat,cr.Cat) AS Cat , ISNULL(tr.segment,cr.segment) AS segment -- , ttr.TOTAL_REV_CAT , ISNULL(cr.MODL_NUM,'') AS MODL_NUM , tr.TOTAL_REV , ISNULL(tr.[Region Code] , cr.[Region Code]) AS RGN_CD , ISNULL(cr.CRUCIAL_REV,0) AS CRUCIAL_REV , ISNULL(tr.DATEWEEK , cr.DATEWEEK) AS YEARMON , ISNULL(tr.[Subcategory Name], cr.[Subcategory Name]) AS [Subcategory Name] , 3 AS RESULT_TYPE FROM #tot_revnue2 tr -- INNER JOIN #tot_revnue_technology ttr --ON tr.Cat = ttr.Cat --AND tr.YEARMON = ttr.YEARMON --AND tr.[Region Code] = ttr.[Region Code] FULL OUTER JOIN #crucial_revnue2 cr ON tr.Cat = cr.Cat AND tr.segment = cr.segment AND tr.[Region Code] = cr.[Region Code] AND tr.DATEWEEK = cr.DATEWEEK -- AND tr.MODL_NUM = cr.MODL_NUM AND tr.[Subcategory Name] = cr.[Subcategory Name] WHERE tr.Cat IS NOT NULL AND tr.segment IS NOT NULL AND tr.[Region Code] IS NOT NULL --AND tr.Capacity IS NOT NULL --AND tr.Capacity!='Unknown' AND tr.DATEWEEK IS NOT NULL ) AS RES /* SELECT technology AS Cat , segment_new AS segment , CASE WHEN technology = 'SATA' AND (MODL_NUM LIKE '%BX500%' OR MODL_NUM LIKE '%MX500%') AND MODL_NUM LIKE '%1' AND MODL_NUM NOT LIKE '%960BX500%' THEN MODL_NUM WHEN technology = 'Gen3' AND UPPER(segment) LIKE '%MAINSTREAM%' THEN '' WHEN technology IN('Gen3','Gen4','Gen5') AND UPPER(segment) LIKE '%WITH HS%' AND UPPER(segment) LIKE '%VALUE%' THEN '' WHEN technology IN ('Gen3','Gen4','Gen5') AND (MODL_NUM LIKE '%P3%' OR MODL_NUM LIKE '%P5%' OR MODL_NUM LIKE '%T700%') THEN MODL_NUM WHEN technology LIKE '%pSSD%' AND segment<>'Other pSSD' AND (MODL_NUM LIKE '%X6%' OR MODL_NUM LIKE '%X8%' OR MODL_NUM LIKE '%X9%' OR MODL_NUM LIKE '%X10%') THEN MODL_NUM ELSE '' END AS MODL_NUM , YEARQTR AS YEARMON /* , CASE WHEN UPPER([Brand Name]) LIKE '%CRUCIAL%' THEN MODL_NUM WHEN UPPER([Brand Name]) LIKE '%MICRON%' THEN MODL_NUM ELSE '' END AS MODL_NUM*/ , [Region Code] , [Subcategory Name] --, SUM(CASE WHEN UPPER([Brand Name]) = 'CRUCIAL' THEN ABS([Estimated Sales]) -- ELSE 0 -- END) AS CRUCIAL_REV INTO #MODL_NUM_DATA FROM #final_results WHERE (UPPER([Brand Name]) LIKE '%CRUCIAL%' OR UPPER([Brand Name]) LIKE '%MICRON%' ) INSERT INTO #MODL_NUM_DATA SELECT technology AS Cat , segment_new as segment , CASE WHEN technology = 'SATA' AND (MODL_NUM LIKE '%BX500%' OR MODL_NUM LIKE '%MX500%') AND MODL_NUM LIKE '%1' AND MODL_NUM NOT LIKE '%960BX500%' THEN MODL_NUM WHEN technology = 'Gen3' AND UPPER(segment) LIKE '%MAINSTREAM%' THEN '' WHEN technology = 'Gen4' AND UPPER(segment) LIKE '%WITH HS%' AND UPPER(segment) LIKE '%VALUE%' THEN '' WHEN technology IN ('Gen3','Gen4','Gen5') AND (MODL_NUM LIKE '%P3%' OR MODL_NUM LIKE '%P5%' OR MODL_NUM LIKE '%T700%') THEN MODL_NUM WHEN technology LIKE '%pSSD%' AND segment<>'Other pSSD' AND (MODL_NUM LIKE '%X6%' OR MODL_NUM LIKE '%X8%' OR MODL_NUM LIKE '%X9%' OR MODL_NUM LIKE '%X10%') THEN MODL_NUM ELSE '' END AS MODL_NUM , YEARMON /* , CASE WHEN UPPER([Brand Name]) LIKE '%CRUCIAL%' THEN MODL_NUM WHEN UPPER([Brand Name]) LIKE '%MICRON%' THEN MODL_NUM ELSE '' END AS MODL_NUM*/ , [Region Code] , [Subcategory Name] --, SUM(CASE WHEN UPPER([Brand Name]) = 'CRUCIAL' THEN ABS([Estimated Sales]) -- ELSE 0 -- END) AS CRUCIAL_REV FROM #final_results WHERE /* datepart(year,[week_begin_cal]) = YEAR(@CurDate) AND datepart(month,[week_begin_cal]) >= @Month_Start*/ (UPPER([Brand Name]) LIKE '%CRUCIAL%' OR UPPER([Brand Name]) LIKE '%MICRON%' ) INSERT INTO #MODL_NUM_DATA SELECT technology AS Cat , segment_new , CASE WHEN technology = 'SATA' AND (MODL_NUM LIKE '%BX500%' OR MODL_NUM LIKE '%MX500%') AND MODL_NUM LIKE '%1' AND MODL_NUM NOT LIKE '%960BX500%' THEN MODL_NUM WHEN technology = 'Gen3' AND UPPER(segment) LIKE '%MAINSTREAM%' THEN '' WHEN technology = 'Gen4' AND UPPER(segment) LIKE '%WITH HS%' AND UPPER(segment) LIKE '%VALUE%' THEN '' WHEN technology IN ('Gen3','Gen4','Gen5') AND (MODL_NUM LIKE '%P3%' OR MODL_NUM LIKE '%P5%' OR MODL_NUM LIKE '%T700%') THEN MODL_NUM WHEN technology LIKE '%pSSD%' AND segment<>'Other pSSD' AND (MODL_NUM LIKE '%X6%' OR MODL_NUM LIKE '%X8%' OR MODL_NUM LIKE '%X9%' OR MODL_NUM LIKE '%X10%') THEN MODL_NUM ELSE '' END AS MODL_NUM , DATEWEEK AS YEARMON /* , CASE WHEN UPPER([Brand Name]) LIKE '%CRUCIAL%' THEN MODL_NUM WHEN UPPER([Brand Name]) LIKE '%MICRON%' THEN MODL_NUM ELSE '' END AS MODL_NUM*/ , [Region Code] , [Subcategory Name] --, SUM(CASE WHEN UPPER([Brand Name]) = 'CRUCIAL' THEN ABS([Estimated Sales]) -- ELSE 0 -- END) AS CRUCIAL_REV FROM #final_results WHERE /* datepart(year,[week_begin_cal]) = YEAR(@CurDate) AND datepart(month,[week_begin_cal]) >= @Month_Start*/ (UPPER([Brand Name]) LIKE '%CRUCIAL%' OR UPPER([Brand Name]) LIKE '%MICRON%' ) UPDATE FSD SET MODL_NUM = RES.MODL_NUM FROM #FINAL_SSD_RESULTS FSD INNER JOIN ( SELECT * FROM #MODL_NUM_DATA RES WHERE MODL_NUM<>'' AND UPPER([Subcategory Name]) LIKE '%INTERNAL%' ) AS RES ON FSD.Cat = RES.Cat AND FSD.segment = RES.segment AND FSD.RGN_CD = RES.[Region Code] AND FSD.YEARMON = RES.YEARMON AND FSD.[Subcategory Name] = RES.[Subcategory Name] WHERE FSD.segment<>''*/ --drop table #newres_SSD SELECT DISTINCT Cat , '' AS segment , RGN_CD , YEARMON , RESULT_TYPE ,'Internal Solid State Drives' AS [Subcategory Name] into #newres_SSD FROM #FINAL_SSD_RESULTS WHERE Cat IN ('SATA','Gen3','Gen4','Gen5') -- SELECT * FROM #newres_SSD --select * from #FINAL_SSD_RESULTS --select * from #FINAL_SSD_RESULTS --WHERE Cat = 'Gen5' INSERT INTO #FINAL_SSD_RESULTS (Cat, segment,MODL_NUM,[Subcategory Name],RGN_CD,YEARMON,RESULT_TYPE, CRUCIAL_REV, TOTAL_REV) SELECT fdr.Cat , '' ,'' ,'Internal Solid State Drives' , fdr.RGN_CD , fdr.YEARMON , fdr.RESULT_TYPE , 0 , 0 FROM #newres_SSD fdr LEFT JOIN #FINAL_SSD_RESULTS fs ON fdr.Cat = fs.Cat AND fdr.segment = fs.segment AND fdr.RGN_CD = fs.RGN_CD AND fdr.RESULT_TYPE = fs.RESULT_TYPE AND fdr.YEARMON = fs.YEARMON AND fdr.[Subcategory Name] = fs.[Subcategory Name] WHERE fs.segment IS NULL UPDATE fs SET CRUCIAL_REV = RES.CRUCIAL_REV FROM #FINAL_SSD_RESULTS fs INNER JOIN ( SELECT Cat --, segment --, MODULE_DENSITY AS Capacity , [Region Code] AS RGN_CD , YEARQTR , [Subcategory Name] , SUM( ISNULL(CRUCIAL_REV,0)) AS CRUCIAL_REV FROM #crucial_revnue WHERE segment<>'' GROUP BY Cat --, segment --, MODULE_DENSITY , [Region Code] , YEARQTR , [Subcategory Name] ) AS RES ON fs.Cat = RES.Cat --AND fs.segment = RES.segment AND fs.RGN_CD = RES.RGN_CD AND fs.YEARMON = RES.YEARQTR AND fs.[Subcategory Name] = RES.[Subcategory Name] WHERE fs.segment = '' AND fs.RESULT_TYPE = 1 UPDATE fs SET CRUCIAL_REV = RES.CRUCIAL_REV FROM #FINAL_SSD_RESULTS fs INNER JOIN ( SELECT Cat --, segment --, MODULE_DENSITY AS Capacity , [Region Code] AS RGN_CD , YEARMON , [Subcategory Name] , SUM( ISNULL(CRUCIAL_REV,0)) AS CRUCIAL_REV FROM #crucial_revnue1 WHERE segment<>'' GROUP BY Cat --, segment --, MODULE_DENSITY , [Region Code] , YEARMON , [Subcategory Name] ) AS RES ON fs.Cat = RES.Cat --AND fs.segment = RES.segment AND fs.RGN_CD = RES.RGN_CD AND fs.YEARMON = RES.YEARMON AND fs.[Subcategory Name] = RES.[Subcategory Name] WHERE fs.segment = '' AND fs.RESULT_TYPE = 2 UPDATE fs SET CRUCIAL_REV = RES.CRUCIAL_REV FROM #FINAL_SSD_RESULTS fs INNER JOIN ( SELECT Cat --, segment --, MODULE_DENSITY AS Capacity , [Region Code] AS RGN_CD , DATEWEEK , [Subcategory Name] , SUM( ISNULL(CRUCIAL_REV,0)) AS CRUCIAL_REV FROM #crucial_revnue2 WHERE segment<>'' GROUP BY Cat --, segment --, MODULE_DENSITY , [Region Code] , DATEWEEK , [Subcategory Name] ) AS RES ON fs.Cat = RES.Cat --AND fs.segment = RES.segment AND fs.RGN_CD = RES.RGN_CD AND fs.YEARMON = RES.DATEWEEK AND fs.[Subcategory Name] = RES.[Subcategory Name] WHERE fs.segment = '' AND fs.RESULT_TYPE = 3 ------------------------------------------------------------------------------------------------------- UPDATE fs SET TOTAL_REV = RES.TOTAL_REV FROM #FINAL_SSD_RESULTS fs INNER JOIN ( SELECT Cat -- , segment --, MODULE_DENSITY AS Capacity , [Region Code] AS RGN_CD , YEARQTR , SUM( ISNULL(TOTAL_REV,0)) AS TOTAL_REV , [Subcategory Name] FROM #tot_revnue GROUP BY Cat -- , segment --, MODULE_DENSITY , [Region Code] , YEARQTR , [Subcategory Name] ) AS RES ON fs.Cat = RES.Cat -- AND fs.segment = RES.segment AND fs.RGN_CD = RES.RGN_CD AND fs.YEARMON = RES.YEARQTR AND fs.[Subcategory Name] = RES.[Subcategory Name] WHERE fs.segment='' AND fs.RESULT_TYPE = 1 UPDATE fs SET TOTAL_REV = RES.TOTAL_REV FROM #FINAL_SSD_RESULTS fs INNER JOIN ( SELECT Cat -- , segment --, MODULE_DENSITY AS Capacity , [Region Code] AS RGN_CD , YEARMON , SUM( ISNULL(TOTAL_REV,0)) AS TOTAL_REV , [Subcategory Name] FROM #tot_revnue1 GROUP BY Cat -- , segment --, MODULE_DENSITY , [Region Code] , YEARMON , [Subcategory Name] ) AS RES ON fs.Cat = RES.Cat -- AND fs.segment = RES.segment AND fs.RGN_CD = RES.RGN_CD AND fs.YEARMON = RES.YEARMON AND fs.[Subcategory Name] = RES.[Subcategory Name] WHERE fs.segment='' AND fs.RESULT_TYPE = 2 UPDATE fs SET TOTAL_REV = RES.TOTAL_REV FROM #FINAL_SSD_RESULTS fs INNER JOIN ( SELECT Cat -- , segment --, MODULE_DENSITY AS Capacity , [Region Code] AS RGN_CD , DATEWEEK , SUM( ISNULL(TOTAL_REV,0)) AS TOTAL_REV , [Subcategory Name] FROM #tot_revnue2 GROUP BY Cat -- , segment --, MODULE_DENSITY , [Region Code] , DATEWEEK , [Subcategory Name] ) AS RES ON fs.Cat = RES.Cat -- AND fs.segment = RES.segment AND fs.RGN_CD = RES.RGN_CD AND fs.YEARMON = RES.DATEWEEK AND fs.[Subcategory Name] = RES.[Subcategory Name] WHERE fs.segment='' AND fs.RESULT_TYPE = 3 ------------------------------------------------------------------------------------------------------------- SELECT Cat, segment,TOTAL_REV,RGN_CD,YEARMON, [Subcategory Name],RESULT_TYPE,count(*) AS REC_CNT INTO #TEMP_RES_LATEST FROM #FINAL_SSD_RESULTS WHERE segment IS NOT NULL AND segment<>'' GROUP BY Cat, segment,TOTAL_REV,RGN_CD,YEARMON, [Subcategory Name],RESULT_TYPE HAVING COUNT(*) > 1 DELETE fsr FROM #FINAL_SSD_RESULTS fsr INNER JOIN #TEMP_RES_LATEST trl ON fsr.Cat = trl.Cat AND fsr.segment = trl.segment AND fsr.TOTAL_REV = trl.TOTAL_REV AND fsr.RGN_CD = trl.RGN_CD AND fsr.YEARMON = trl.YEARMON AND fsr.[Subcategory Name] = trl.[Subcategory Name] AND fsr.RESULT_TYPE = trl.RESULT_TYPE WHERE fsr.MODL_NUM = '' SELECT * FROM #FINAL_SSD_RESULTS END TRY BEGIN CATCH DECLARE @Message varchar(MAX) = ERROR_MESSAGE(), @Severity int = ERROR_SEVERITY(), @State smallint = ERROR_STATE(); Throw 500001, @Message , @State END CATCH END GO