SQLize | PHPize | SQLtest

USE [MY_DELFI_SFA] GO /****** Object: StoredProcedure [dbo].[dm_sp_dm_Sales#Analysis#Daily#TEST] Script Date: 18/9/2023 3:06:07 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER proc [dbo].[dm_sp_dm_Sales#Analysis#Daily#TEST] @UserId int, @SFAUserID int, @DistribId int, --@DataType nvarchar(10), --@Month int, --@Year int, --@YearMonth int, -- 202201 @StartDate datetime, -- 2021-02-01 --@EndDate datetime, -- 2022-01-31 @CustCode nvarchar(255), @ShipToCode nvarchar(255), @ZoneID bigint, @RegionID bigint, @StateID int, @AreaID int, @ChainID int, @ChannelID int, @SubChainID int, @SubChannelID int, @GradeID int, --@SalesRoute bigint, @SalesRep bigint, @SKU nvarchar(255), @AgencyID int, @BrandID int, @CategoryID int, @SubBrandID int, @SubCategoryID int, @PackTypeID int, @FunctionID int, @PackSizeID int, @ShadeID int, @MarketingGrpID int, @SubMarketingGrpID int, @ParentSKUCode nvarchar(255), @GrpByLevel1 nvarchar(255), @GrpByLevel2 nvarchar(255), @GrpByLevel3 nvarchar(255) as set nocount on --Declare @DaysOfMonth As Integer = 0 --If @Year <> -1 and @Month <> -1 -- Set @DaysOfMonth = DAY(EOMONTH(Convert(Datetime, Convert(Varchar(10), @Year) + '-' + Convert(Varchar(10), @Month) + '-' + '01'))) IF @GrpByLevel1 <> '' and @GrpByLevel2 <> ''and @GrpByLevel3 <> '' Begin --Declare @GrpByLevel1 As nvarchar(255) = 'Customer'; --Declare @GrpByLevel2 As nvarchar(255) = 'Customer'; --Declare @GrpByLevel3 As nvarchar(255) = 'Customer'; --Declare @StartDate as datetime = Convert(Datetime, '2023-05-02'); Select ROW_NUMBER() OVER ( ORDER BY TableB.[Grouplevel1], TableB.[Grouplevel2], TableB.[Grouplevel3] ) RowNum, TableB.[Grouplevel1], TableB.[Grouplevel2], TableB.[Grouplevel3], '' 'ITEM_CODE', '' 'ITEM_DESCRIPTION', ISNULL(TableB.NET_SALES_AMT,0) AS [SALES_AMT], ISNULL(TableB.NET_SALES_QTY,0) As [SALES_QTY], '' As [UOM], ISNULL(TableB.FOC_AMT,0) AS [FOC_AMT], ISNULL(TableB.FOC_QTY,0) As [FOC_QTY], '' As [UOM1], ISNULL(TableB.RETURN_SALES_AMT,0) AS [RETURN_SALES_AMT], ISNULL(TableB.RETURN_SALES_QTY,0) As [RETURN_SALES_QTY], '' As [UOM2], ISNULL((TableB.NET_SALES_AMT + TableB.RETURN_SALES_AMT),0) AS [NET_AMT], ISNULL((TableB.NET_SALES_QTY + TableB.RETURN_SALES_QTY),0) AS [NET_QTY] From ( Select [Grouplevel1], [Grouplevel2], [Grouplevel3] , ISNULL(TableA.RETURN_SALES_AMT,0) As [RETURN_SALES_AMT] , ISNULL(TableA.RETURN_SALES_QTY,0) As [RETURN_SALES_QTY] , '' AS [UOM] , ISNULL(( Select ISNULL([NET_SALES_AMT], 0) From ( Select SUM([NET_SALES_AMT]) As 'NET_SALES_AMT', ITEM_CODE, ITEM_DESCRIPTION, '' AS [UOM], Case When @GrpByLevel1 = 'Customer' Then [BILL_TO_NAME] When @GrpByLevel1 = 'Ship To' Then [Ship To] When @GrpByLevel1 = 'Bill-To' Then [Zone] When @GrpByLevel1 = 'Region' Then [Region] When @GrpByLevel1 = 'State' Then [State] When @GrpByLevel1 = 'Area' Then [Area] When @GrpByLevel1 = 'Chain' Then [Chain] When @GrpByLevel1 = 'Channel' Then [Channel] When @GrpByLevel1 = 'Sub Chain' Then [Sub Chain] When @GrpByLevel1 = 'Sub Channel' Then [Sub Channel] When @GrpByLevel1 = 'Grade' Then [Grade] --When @GrpByLevel1 = 'Sales Route' Then ST.State_ID When @GrpByLevel1 = 'Sales Rep' Then [Sales Rep] When @GrpByLevel1 = 'SKU' Then [SKU] When @GrpByLevel1 = 'Agency' Then [Agency] When @GrpByLevel1 = 'Brand' Then [Brand] When @GrpByLevel1 = 'Category' Then [Category] When @GrpByLevel1 = 'Sub Brand' Then [Sub Brand] When @GrpByLevel1 = 'Sub Category' Then [Sub Category] When @GrpByLevel1 = 'Type' Then [Type] When @GrpByLevel1 = 'Function' Then [Function] When @GrpByLevel1 = 'Size' Then [Size] When @GrpByLevel1 = 'Shade' Then [Shade] When @GrpByLevel1 = 'Marketing Group' Then [Marketing Group] When @GrpByLevel1 = 'Sub Marketing Group' Then [Sub Marketing Group] --When @GrpByLevel1 = 'Parent SKU Code' Then ST.State_ID End AS [Grouplevel1], Case When @GrpByLevel2 = 'Customer' Then [BILL_TO_NAME] When @GrpByLevel2 = 'Ship To' Then [Ship To] When @GrpByLevel2 = 'Bill-To' Then [Zone] When @GrpByLevel2 = 'Region' Then [Region] When @GrpByLevel2 = 'State' Then [State] When @GrpByLevel2 = 'Area' Then [Area] When @GrpByLevel2 = 'Chain' Then [Chain] When @GrpByLevel2 = 'Channel' Then [Channel] When @GrpByLevel2 = 'Sub Chain' Then [Sub Chain] When @GrpByLevel2 = 'Sub Channel' Then [Sub Channel] When @GrpByLevel2 = 'Grade' Then [Grade] --When @GrpByLevel1 = 'Sales Route' Then ST.State_ID When @GrpByLevel2 = 'Sales Rep' Then [Sales Rep] When @GrpByLevel2 = 'SKU' Then [SKU] When @GrpByLevel2 = 'Agency' Then [Agency] When @GrpByLevel2 = 'Brand' Then [Brand] When @GrpByLevel2 = 'Category' Then [Category] When @GrpByLevel2 = 'Sub Brand' Then [Sub Brand] When @GrpByLevel2 = 'Sub Category' Then [Sub Category] When @GrpByLevel2 = 'Type' Then [Type] When @GrpByLevel2 = 'Function' Then [Function] When @GrpByLevel2 = 'Size' Then [Size] When @GrpByLevel2 = 'Shade' Then [Shade] When @GrpByLevel2 = 'Marketing Group' Then [Marketing Group] When @GrpByLevel2 = 'Sub Marketing Group' Then [Sub Marketing Group] --When @GrpByLevel1 = 'Parent SKU Code' Then ST.State_ID End AS [Grouplevel2], Case When @GrpByLevel3 = 'Customer' Then [BILL_TO_NAME] When @GrpByLevel3 = 'Ship To' Then [Ship To] When @GrpByLevel3 = 'Bill-To' Then [Zone] When @GrpByLevel3 = 'Region' Then [Region] When @GrpByLevel3 = 'State' Then [State] When @GrpByLevel3 = 'Area' Then [Area] When @GrpByLevel3 = 'Chain' Then [Chain] When @GrpByLevel3 = 'Channel' Then [Channel] When @GrpByLevel3 = 'Sub Chain' Then [Sub Chain] When @GrpByLevel3 = 'Sub Channel' Then [Sub Channel] When @GrpByLevel3 = 'Grade' Then [Grade] --When @GrpByLevel1 = 'Sales Route' Then ST.State_ID When @GrpByLevel3 = 'Sales Rep' Then [Sales Rep] When @GrpByLevel3 = 'SKU' Then [SKU] When @GrpByLevel3 = 'Agency' Then [Agency] When @GrpByLevel3 = 'Brand' Then [Brand] When @GrpByLevel3 = 'Category' Then [Category] When @GrpByLevel3 = 'Sub Brand' Then [Sub Brand] When @GrpByLevel3 = 'Sub Category' Then [Sub Category] When @GrpByLevel3 = 'Type' Then [Type] When @GrpByLevel3 = 'Function' Then [Function] When @GrpByLevel3 = 'Size' Then [Size] When @GrpByLevel3 = 'Shade' Then [Shade] When @GrpByLevel3 = 'Marketing Group' Then [Marketing Group] When @GrpByLevel3 = 'Sub Marketing Group' Then [Sub Marketing Group] End AS [Grouplevel3] From ( Select ISNULL(a.Bill_Type_Description,'') 'Billing_Type' ,ISNULL(a.Bill_Type_Description,'') 'Bill_Type_Description' ,ISNULL(ml.[Name],'') 'BILL_TO_NAME' ,ISNULL(a.Sold_to_party,'') 'Bill_To_CustomerCode' ,ISNULL(a.Material,'') 'ITEM_CODE' ,ISNULL(mq.Quest_Desc,'') 'ITEM_DESCRIPTION' ,ISNULL(a.Sales_unit,'') 'UOM' ,ISNULL(a.Ship_to_party,'') 'Ship To' ,ISNULL(Z.[Name],'') 'Zone' ,ISNULL(RE.[Name],'') 'Region' ,ISNULL(ST.[Desc],'') 'State' ,ISNULL(AR.[Name],'') 'Area' ,ISNULL(CC.[Name],'') 'Chain' ,ISNULL(CH.[Name],'') 'Channel' ,ISNULL(subchain.[Name],'') 'Sub Chain' ,ISNULL(subch.[Name],'') 'Sub Channel' ,ISNULL(G.[Name],'') 'Grade' ,ISNULL(a.SALES_PERSON,'') 'Sales Rep' ,ISNULL(mq.Quest_Desc,'') 'SKU' ,ISNULL(mqf1.[Name],'') 'Agency' ,ISNULL(mqf2.[Name],'') 'Brand' ,ISNULL(mqf3.[Name],'') 'Category' ,ISNULL(mqf9.[Name],'') 'Sub Brand' ,ISNULL(mqf8.[Name],'') 'Sub Category' ,ISNULL(mqf4.[Name],'') 'Type' ,ISNULL(mqf5.[Name],'') 'Function' ,ISNULL(mqf6.[Name],'') 'Size' ,ISNULL(mqf7.[Name],'') 'Shade' ,ISNULL(mqf10.[Name],'')'Marketing Group' ,ISNULL(mqf11.[Name],'') 'Sub Marketing Group' ,convert(decimal(18,2) ,a.Price) 'NET_SALES_AMT' ,convert(decimal(18,2) ,a.[Bill_Qty]) 'NET_SALES_QTY' ,a.Billing_date 'INVOICE_DATE' -- YYYY-MM-DD ,DAY(CONVERT(DATETIME, a.Billing_date)) 'DAY' FROM dbo.DM_ORDER_SALES_REPORT a with (nolock) LEFT OUTER JOIN MAIN_QUESTION mq on mq.Quest_Code = a.Material -- SKU LEFT OUTER JOIN MAIN_LOCATION ml on ML.ML_NO = a.Sold_to_party LEFT JOIN Zone Z ON Z.Zone_ID = ml.Zone -- Zone LEFT JOIN Region RE ON RE.ID = ml.Region -- Region LEFT JOIN STATE ST ON ST.State_ID = ml.State -- State LEFT JOIN AREA AR ON AR.ID = ml.Area -- Area LEFT JOIN CHAIN CC ON CC.Chain_ID = ml.Chain -- Chain LEFT JOIN CHANNEL CH ON CH.Channel_ID = ml.Channel -- Channel LEFT JOIN GRADE G ON G.Grade_ID = ml.Grade -- Grade LEFT JOIN SUB_CHAIN subchain ON subchain.SubChain_ID = ml.SubChain_ID -- SubChain LEFT JOIN SUB_CHANNEL subch ON subch.SubChannel_ID = ml.SubChannel_ID -- SubChannel --LEFT OUTER JOIN MAIN_QUESTION mq on mq.Quest_ID = b.Quest_ID -- SKU LEFT JOIN dbo.MAIN_QUESTION_FILTER1 mqf1 ON mq.Filter_1 = mqf1.Code -- Manufacture LEFT JOIN dbo.MAIN_QUESTION_FILTER2 mqf2 ON mq.Filter_2 = mqf2.Code -- Brand LEFT JOIN dbo.MAIN_QUESTION_FILTER3 mqf3 ON mq.Filter_3 = mqf3.Code -- Category LEFT JOIN dbo.MAIN_QUESTION_FILTER4 mqf4 ON mq.Filter_4 = mqf4.Code -- Type LEFT JOIN dbo.MAIN_QUESTION_FILTER5 mqf5 ON mq.Filter_5 = mqf5.Code -- [Function] LEFT JOIN dbo.MAIN_QUESTION_FILTER6 mqf6 ON mq.Filter_6 = mqf6.Code -- Size LEFT JOIN dbo.MAIN_QUESTION_FILTER7 mqf7 ON mq.Filter_7 = mqf7.Code -- Shade LEFT JOIN dbo.MAIN_QUESTION_FILTER8 mqf8 ON mq.Filter_8 = mqf8.Code -- SubCatgory LEFT JOIN dbo.MAIN_QUESTION_FILTER9 mqf9 ON mq.Filter_9 = mqf9.Code -- SubBrand LEFT JOIN dbo.MAIN_QUESTION_FILTER10 mqf10 ON mq.Filter_10 = mqf10.Code -- MktGroup LEFT JOIN dbo.MAIN_QUESTION_FILTER11 mqf11 ON mq.Filter_11 = mqf11.Code --SubMktGroup --LEFT JOIN ROUTE_USER_ASSIGNMENT rua on rua.Route_ID = ml.ML_ID -- 'SalesRep' --LEFT JOIN USER_LOGIN ul ON ul.[User_ID] = rua.[User_ID] Where CONVERT(DATETIME, a.Billing_date) = @StartDate AND (a.Sold_to_party IN (Select Value from STRING_SPLIT(@CustCode,',')) OR @CustCode = '') AND (a.Ship_to_party IN (@ShipToCode) OR @ShipToCode = '') AND ( Z.Zone_ID IN (@ZoneID) OR @ZoneID = -1) AND ( RE.ID IN (@RegionID) OR @RegionID = -1) AND ( ST.State_ID IN (@StateID) OR @StateID = -1) AND ( AR.ID IN (@AreaID) OR @AreaID = -1) AND ( CC.Chain_ID IN (@ChainID) OR @ChainID = -1) AND ( CH.Channel_ID IN (@ChannelID) OR @ChannelID = -1) AND ( subchain.SubChain_ID IN (@SubChainID) OR @SubChainID = -1) AND ( subch.SubChannel_ID IN (@SubChannelID) OR @SubChannelID = -1) AND ( G.Grade_ID IN (@GradeID) OR @GradeID = -1) AND ( a.USER_ID IN (@SalesRep) OR @SalesRep = -1) AND ( mq.Quest_Code IN (Select Value from STRING_SPLIT(@SKU,',')) OR @SKU = '') AND ( mqf1.ID IN (@AgencyID) OR @AgencyID = -1) AND ( mqf2.ID IN (@BrandID) OR @BrandID = -1) AND ( mqf3.ID IN (@CategoryID) OR @CategoryID = -1) AND ( mqf9.ID IN (@SubBrandID) OR @SubBrandID = -1) AND ( mqf8.ID IN (@SubCategoryID) OR @SubCategoryID = -1) AND ( mqf4.ID IN (@PackTypeID) OR @PackTypeID = -1) AND ( mqf5.ID IN (@FunctionID) OR @FunctionID = -1) AND ( mqf6.ID IN (@PackSizeID) OR @PackSizeID = -1) AND ( mqf7.ID IN (@ShadeID) OR @ShadeID = -1) AND ( mqf10.ID IN (@MarketingGrpID) OR @MarketingGrpID = -1) AND ( mqf11.ID IN (@SubMarketingGrpID) OR @SubMarketingGrpID = -1) --AND (a.Product_hierarchy = @ParentSKUCode OR @ParentSKUCode = '') ) TblCC Where TblCC.Billing_Type Not In ('ZCC2','ZCN2','ZCN4','ZCN5','ZCN6') Group By Case When @GrpByLevel1 = 'Customer' Then [BILL_TO_NAME] When @GrpByLevel1 = 'Ship To' Then [Ship To] When @GrpByLevel1 = 'Bill-To' Then [Zone] When @GrpByLevel1 = 'Region' Then [Region] When @GrpByLevel1 = 'State' Then [State] When @GrpByLevel1 = 'Area' Then [Area] When @GrpByLevel1 = 'Chain' Then [Chain] When @GrpByLevel1 = 'Channel' Then [Channel] When @GrpByLevel1 = 'Sub Chain' Then [Sub Chain] When @GrpByLevel1 = 'Sub Channel' Then [Sub Channel] When @GrpByLevel1 = 'Grade' Then [Grade] --When @GrpByLevel1 = 'Sales Route' Then ST.State_ID When @GrpByLevel1 = 'Sales Rep' Then [Sales Rep] When @GrpByLevel1 = 'SKU' Then [SKU] When @GrpByLevel1 = 'Agency' Then [Agency] When @GrpByLevel1 = 'Brand' Then [Brand] When @GrpByLevel1 = 'Category' Then [Category] When @GrpByLevel1 = 'Sub Brand' Then [Sub Brand] When @GrpByLevel1 = 'Sub Category' Then [Sub Category] When @GrpByLevel1 = 'Type' Then [Type] When @GrpByLevel1 = 'Function' Then [Function] When @GrpByLevel1 = 'Size' Then [Size] When @GrpByLevel1 = 'Shade' Then [Shade] When @GrpByLevel1 = 'Marketing Group' Then [Marketing Group] When @GrpByLevel1 = 'Sub Marketing Group' Then [Sub Marketing Group] --When @GrpByLevel1 = 'Parent SKU Code' Then ST.State_ID End , Case When @GrpByLevel2 = 'Customer' Then [BILL_TO_NAME] When @GrpByLevel2 = 'Ship To' Then [Ship To] When @GrpByLevel2 = 'Bill-To' Then [Zone] When @GrpByLevel2 = 'Region' Then [Region] When @GrpByLevel2 = 'State' Then [State] When @GrpByLevel2 = 'Area' Then [Area] When @GrpByLevel2 = 'Chain' Then [Chain] When @GrpByLevel2 = 'Channel' Then [Channel] When @GrpByLevel2 = 'Sub Chain' Then [Sub Chain] When @GrpByLevel2 = 'Sub Channel' Then [Sub Channel] When @GrpByLevel2 = 'Grade' Then [Grade] --When @GrpByLevel1 = 'Sales Route' Then ST.State_ID When @GrpByLevel2 = 'Sales Rep' Then [Sales Rep] When @GrpByLevel2 = 'SKU' Then [SKU] When @GrpByLevel2 = 'Agency' Then [Agency] When @GrpByLevel2 = 'Brand' Then [Brand] When @GrpByLevel2 = 'Category' Then [Category] When @GrpByLevel2 = 'Sub Brand' Then [Sub Brand] When @GrpByLevel2 = 'Sub Category' Then [Sub Category] When @GrpByLevel2 = 'Type' Then [Type] When @GrpByLevel2 = 'Function' Then [Function] When @GrpByLevel2 = 'Size' Then [Size] When @GrpByLevel2 = 'Shade' Then [Shade] When @GrpByLevel2 = 'Marketing Group' Then [Marketing Group] When @GrpByLevel2 = 'Sub Marketing Group' Then [Sub Marketing Group] --When @GrpByLevel1 = 'Parent SKU Code' Then ST.State_ID End , Case When @GrpByLevel3 = 'Customer' Then [BILL_TO_NAME] When @GrpByLevel3 = 'Ship To' Then [Ship To] When @GrpByLevel3 = 'Bill-To' Then [Zone] When @GrpByLevel3 = 'Region' Then [Region] When @GrpByLevel3 = 'State' Then [State] When @GrpByLevel3 = 'Area' Then [Area] When @GrpByLevel3 = 'Chain' Then [Chain] When @GrpByLevel3 = 'Channel' Then [Channel] When @GrpByLevel3 = 'Sub Chain' Then [Sub Chain] When @GrpByLevel3 = 'Sub Channel' Then [Sub Channel] When @GrpByLevel3 = 'Grade' Then [Grade] --When @GrpByLevel1 = 'Sales Route' Then ST.State_ID When @GrpByLevel3 = 'Sales Rep' Then [Sales Rep] When @GrpByLevel3 = 'SKU' Then [SKU] When @GrpByLevel3 = 'Agency' Then [Agency] When @GrpByLevel3 = 'Brand' Then [Brand] When @GrpByLevel3 = 'Category' Then [Category] When @GrpByLevel3 = 'Sub Brand' Then [Sub Brand] When @GrpByLevel3 = 'Sub Category' Then [Sub Category] When @GrpByLevel3 = 'Type' Then [Type] When @GrpByLevel3 = 'Function' Then [Function] When @GrpByLevel3 = 'Size' Then [Size] When @GrpByLevel3 = 'Shade' Then [Shade] When @GrpByLevel3 = 'Marketing Group' Then [Marketing Group] When @GrpByLevel3 = 'Sub Marketing Group' Then [Sub Marketing Group] End, TblCC.ITEM_CODE, TblCC.ITEM_DESCRIPTION,[UOM] ) TableE Where TableE.Grouplevel1 = TableA.Grouplevel1 AND TableE.Grouplevel2 = TableA.Grouplevel2 AND TableE.Grouplevel3 = TableA.Grouplevel3 ),0) As 'NET_SALES_AMT' , ISNULL(( Select ISNULL([NET_SALES_QTY],0) From ( Select SUM([NET_SALES_QTY]) As 'NET_SALES_QTY', ITEM_CODE, ITEM_DESCRIPTION, '' AS [UOM], Case When @GrpByLevel1 = 'Customer' Then [BILL_TO_NAME] When @GrpByLevel1 = 'Ship To' Then [Ship To] When @GrpByLevel1 = 'Bill-To' Then [Zone] When @GrpByLevel1 = 'Region' Then [Region] When @GrpByLevel1 = 'State' Then [State] When @GrpByLevel1 = 'Area' Then [Area] When @GrpByLevel1 = 'Chain' Then [Chain] When @GrpByLevel1 = 'Channel' Then [Channel] When @GrpByLevel1 = 'Sub Chain' Then [Sub Chain] When @GrpByLevel1 = 'Sub Channel' Then [Sub Channel] When @GrpByLevel1 = 'Grade' Then [Grade] --When @GrpByLevel1 = 'Sales Route' Then ST.State_ID When @GrpByLevel1 = 'Sales Rep' Then [Sales Rep] When @GrpByLevel1 = 'SKU' Then [SKU] When @GrpByLevel1 = 'Agency' Then [Agency] When @GrpByLevel1 = 'Brand' Then [Brand] When @GrpByLevel1 = 'Category' Then [Category] When @GrpByLevel1 = 'Sub Brand' Then [Sub Brand] When @GrpByLevel1 = 'Sub Category' Then [Sub Category] When @GrpByLevel1 = 'Type' Then [Type] When @GrpByLevel1 = 'Function' Then [Function] When @GrpByLevel1 = 'Size' Then [Size] When @GrpByLevel1 = 'Shade' Then [Shade] When @GrpByLevel1 = 'Marketing Group' Then [Marketing Group] When @GrpByLevel1 = 'Sub Marketing Group' Then [Sub Marketing Group] --When @GrpByLevel1 = 'Parent SKU Code' Then ST.State_ID End AS [Grouplevel1], Case When @GrpByLevel2 = 'Customer' Then [BILL_TO_NAME] When @GrpByLevel2 = 'Ship To' Then [Ship To] When @GrpByLevel2 = 'Bill-To' Then [Zone] When @GrpByLevel2 = 'Region' Then [Region] When @GrpByLevel2 = 'State' Then [State] When @GrpByLevel2 = 'Area' Then [Area] When @GrpByLevel2 = 'Chain' Then [Chain] When @GrpByLevel2 = 'Channel' Then [Channel] When @GrpByLevel2 = 'Sub Chain' Then [Sub Chain] When @GrpByLevel2 = 'Sub Channel' Then [Sub Channel] When @GrpByLevel2 = 'Grade' Then [Grade] --When @GrpByLevel1 = 'Sales Route' Then ST.State_ID When @GrpByLevel2 = 'Sales Rep' Then [Sales Rep] When @GrpByLevel2 = 'SKU' Then [SKU] When @GrpByLevel2 = 'Agency' Then [Agency] When @GrpByLevel2 = 'Brand' Then [Brand] When @GrpByLevel2 = 'Category' Then [Category] When @GrpByLevel2 = 'Sub Brand' Then [Sub Brand] When @GrpByLevel2 = 'Sub Category' Then [Sub Category] When @GrpByLevel2 = 'Type' Then [Type] When @GrpByLevel2 = 'Function' Then [Function] When @GrpByLevel2 = 'Size' Then [Size] When @GrpByLevel2 = 'Shade' Then [Shade] When @GrpByLevel2 = 'Marketing Group' Then [Marketing Group] When @GrpByLevel2 = 'Sub Marketing Group' Then [Sub Marketing Group] --When @GrpByLevel1 = 'Parent SKU Code' Then ST.State_ID End AS [Grouplevel2], Case When @GrpByLevel3 = 'Customer' Then [BILL_TO_NAME] When @GrpByLevel3 = 'Ship To' Then [Ship To] When @GrpByLevel3 = 'Bill-To' Then [Zone] When @GrpByLevel3 = 'Region' Then [Region] When @GrpByLevel3 = 'State' Then [State] When @GrpByLevel3 = 'Area' Then [Area] When @GrpByLevel3 = 'Chain' Then [Chain] When @GrpByLevel3 = 'Channel' Then [Channel] When @GrpByLevel3 = 'Sub Chain' Then [Sub Chain] When @GrpByLevel3 = 'Sub Channel' Then [Sub Channel] When @GrpByLevel3 = 'Grade' Then [Grade] --When @GrpByLevel1 = 'Sales Route' Then ST.State_ID When @GrpByLevel3 = 'Sales Rep' Then [Sales Rep] When @GrpByLevel3 = 'SKU' Then [SKU] When @GrpByLevel3 = 'Agency' Then [Agency] When @GrpByLevel3 = 'Brand' Then [Brand] When @GrpByLevel3 = 'Category' Then [Category] When @GrpByLevel3 = 'Sub Brand' Then [Sub Brand] When @GrpByLevel3 = 'Sub Category' Then [Sub Category] When @GrpByLevel3 = 'Type' Then [Type] When @GrpByLevel3 = 'Function' Then [Function] When @GrpByLevel3 = 'Size' Then [Size] When @GrpByLevel3 = 'Shade' Then [Shade] When @GrpByLevel3 = 'Marketing Group' Then [Marketing Group] When @GrpByLevel3 = 'Sub Marketing Group' Then [Sub Marketing Group] End AS [Grouplevel3] From ( Select ISNULL(a.Bill_Type_Description,'') 'Billing_Type' ,ISNULL(a.Bill_Type_Description,'') 'Bill_Type_Description' ,ISNULL(ml.[Name],'') 'BILL_TO_NAME' ,ISNULL(a.Sold_to_party,'') 'Bill_To_CustomerCode' ,ISNULL(a.Material,'') 'ITEM_CODE' ,ISNULL(mq.Quest_Desc,'') 'ITEM_DESCRIPTION' ,ISNULL(a.Sales_unit,'') 'UOM' ,ISNULL(a.Ship_to_party,'') 'Ship To' ,ISNULL(Z.[Name],'') 'Zone' ,ISNULL(RE.[Name],'') 'Region' ,ISNULL(ST.[Desc],'') 'State' ,ISNULL(AR.[Name],'') 'Area' ,ISNULL(CC.[Name],'') 'Chain' ,ISNULL(CH.[Name],'') 'Channel' ,ISNULL(subchain.[Name],'') 'Sub Chain' ,ISNULL(subch.[Name],'') 'Sub Channel' ,ISNULL(G.[Name],'') 'Grade' ,ISNULL(a.SALES_PERSON,'') 'Sales Rep' ,ISNULL(mq.Quest_Desc,'') 'SKU' ,ISNULL(mqf1.[Name],'') 'Agency' ,ISNULL(mqf2.[Name],'') 'Brand' ,ISNULL(mqf3.[Name],'') 'Category' ,ISNULL(mqf9.[Name],'') 'Sub Brand' ,ISNULL(mqf8.[Name],'') 'Sub Category' ,ISNULL(mqf4.[Name],'') 'Type' ,ISNULL(mqf5.[Name],'') 'Function' ,ISNULL(mqf6.[Name],'') 'Size' ,ISNULL(mqf7.[Name],'') 'Shade' ,ISNULL(mqf10.[Name],'')'Marketing Group' ,ISNULL(mqf11.[Name],'') 'Sub Marketing Group' ,convert(decimal(18,2) ,a.Price) 'NET_SALES_AMT' ,convert(decimal(18,2) ,a.[Bill_Qty]) 'NET_SALES_QTY' ,a.Billing_date 'INVOICE_DATE' -- YYYY-MM-DD ,DAY(CONVERT(DATETIME, a.Billing_date)) 'DAY' FROM dbo.DM_ORDER_SALES_REPORT a with (nolock) --left outer join VW_INTEG_CUST#FILTER ml on ML.ML_NO = a.Sold_to_party --left outer join VW_INTEG_SKU#DMS mq on mq.Quest_Code = a.Material LEFT OUTER JOIN MAIN_QUESTION mq on mq.Quest_Code = a.Material -- SKU LEFT OUTER JOIN MAIN_LOCATION ml on ML.ML_NO = a.Sold_to_party --LEFT OUTER JOIN [dbo].[SFA_SO_RESULTS] b on b.Trans_ID = a.OrderNo_M1 AND b.Quest_ID = mq.Quest_ID LEFT JOIN Zone Z ON Z.Zone_ID = ml.Zone -- Zone LEFT JOIN Region RE ON RE.ID = ml.Region -- Region LEFT JOIN STATE ST ON ST.State_ID = ml.State -- State LEFT JOIN AREA AR ON AR.ID = ml.Area -- Area LEFT JOIN CHAIN CC ON CC.Chain_ID = ml.Chain -- Chain LEFT JOIN CHANNEL CH ON CH.Channel_ID = ml.Channel -- Channel LEFT JOIN GRADE G ON G.Grade_ID = ml.Grade -- Grade LEFT JOIN SUB_CHAIN subchain ON subchain.SubChain_ID = ml.SubChain_ID -- SubChain LEFT JOIN SUB_CHANNEL subch ON subch.SubChannel_ID = ml.SubChannel_ID -- SubChannel --LEFT OUTER JOIN MAIN_QUESTION mq on mq.Quest_ID = b.Quest_ID -- SKU LEFT JOIN dbo.MAIN_QUESTION_FILTER1 mqf1 ON mq.Filter_1 = mqf1.Code -- Manufacture LEFT JOIN dbo.MAIN_QUESTION_FILTER2 mqf2 ON mq.Filter_2 = mqf2.Code -- Brand LEFT JOIN dbo.MAIN_QUESTION_FILTER3 mqf3 ON mq.Filter_3 = mqf3.Code -- Category LEFT JOIN dbo.MAIN_QUESTION_FILTER4 mqf4 ON mq.Filter_4 = mqf4.Code -- Type LEFT JOIN dbo.MAIN_QUESTION_FILTER5 mqf5 ON mq.Filter_5 = mqf5.Code -- [Function] LEFT JOIN dbo.MAIN_QUESTION_FILTER6 mqf6 ON mq.Filter_6 = mqf6.Code -- Size LEFT JOIN dbo.MAIN_QUESTION_FILTER7 mqf7 ON mq.Filter_7 = mqf7.Code -- Shade LEFT JOIN dbo.MAIN_QUESTION_FILTER8 mqf8 ON mq.Filter_8 = mqf8.Code -- SubCatgory LEFT JOIN dbo.MAIN_QUESTION_FILTER9 mqf9 ON mq.Filter_9 = mqf9.Code -- SubBrand LEFT JOIN dbo.MAIN_QUESTION_FILTER10 mqf10 ON mq.Filter_10 = mqf10.Code -- MktGroup LEFT JOIN dbo.MAIN_QUESTION_FILTER11 mqf11 ON mq.Filter_11 = mqf11.Code --SubMktGroup --LEFT JOIN ROUTE_USER_ASSIGNMENT rua on rua.Route_ID = ml.ML_ID -- 'SalesRep' --LEFT JOIN USER_LOGIN ul ON ul.[User_ID] = rua.[User_ID] Where CONVERT(DATETIME, a.Billing_date) = @StartDate AND (a.Sold_to_party IN (Select Value from STRING_SPLIT(@CustCode,',')) OR @CustCode = '') AND (a.Ship_to_party IN (@ShipToCode) OR @ShipToCode = '') AND ( Z.Zone_ID IN (@ZoneID) OR @ZoneID = -1) AND ( RE.ID IN (@RegionID) OR @RegionID = -1) AND ( ST.State_ID IN (@StateID) OR @StateID = -1) AND ( AR.ID IN (@AreaID) OR @AreaID = -1) AND ( CC.Chain_ID IN (@ChainID) OR @ChainID = -1) AND ( CH.Channel_ID IN (@ChannelID) OR @ChannelID = -1) AND ( subchain.SubChain_ID IN (@SubChainID) OR @SubChainID = -1) AND ( subch.SubChannel_ID IN (@SubChannelID) OR @SubChannelID = -1) AND ( G.Grade_ID IN (@GradeID) OR @GradeID = -1) AND ( a.USER_ID IN (@SalesRep) OR @SalesRep = -1) AND ( mq.Quest_Code IN (Select Value from STRING_SPLIT(@SKU,',')) OR @SKU = '') AND ( mqf1.ID IN (@AgencyID) OR @AgencyID = -1) AND ( mqf2.ID IN (@BrandID) OR @BrandID = -1) AND ( mqf3.ID IN (@CategoryID) OR @CategoryID = -1) AND ( mqf9.ID IN (@SubBrandID) OR @SubBrandID = -1) AND ( mqf8.ID IN (@SubCategoryID) OR @SubCategoryID = -1) AND ( mqf4.ID IN (@PackTypeID) OR @PackTypeID = -1) AND ( mqf5.ID IN (@FunctionID) OR @FunctionID = -1) AND ( mqf6.ID IN (@PackSizeID) OR @PackSizeID = -1) AND ( mqf7.ID IN (@ShadeID) OR @ShadeID = -1) AND ( mqf10.ID IN (@MarketingGrpID) OR @MarketingGrpID = -1) AND ( mqf11.ID IN (@SubMarketingGrpID) OR @SubMarketingGrpID = -1) --AND (a.Product_hierarchy = @ParentSKUCode OR @ParentSKUCode = '') ) TblBB Where TblBB.Billing_Type Not In ('ZCC2','ZCN2','ZCN4','ZCN5','ZCN6') group by Case When @GrpByLevel1 = 'Customer' Then [BILL_TO_NAME] When @GrpByLevel1 = 'Ship To' Then [Ship To] When @GrpByLevel1 = 'Bill-To' Then [Zone] When @GrpByLevel1 = 'Region' Then [Region] When @GrpByLevel1 = 'State' Then [State] When @GrpByLevel1 = 'Area' Then [Area] When @GrpByLevel1 = 'Chain' Then [Chain] When @GrpByLevel1 = 'Channel' Then [Channel] When @GrpByLevel1 = 'Sub Chain' Then [Sub Chain] When @GrpByLevel1 = 'Sub Channel' Then [Sub Channel] When @GrpByLevel1 = 'Grade' Then [Grade] --When @GrpByLevel1 = 'Sales Route' Then ST.State_ID When @GrpByLevel1 = 'Sales Rep' Then [Sales Rep] When @GrpByLevel1 = 'SKU' Then [SKU] When @GrpByLevel1 = 'Agency' Then [Agency] When @GrpByLevel1 = 'Brand' Then [Brand] When @GrpByLevel1 = 'Category' Then [Category] When @GrpByLevel1 = 'Sub Brand' Then [Sub Brand] When @GrpByLevel1 = 'Sub Category' Then [Sub Category] When @GrpByLevel1 = 'Type' Then [Type] When @GrpByLevel1 = 'Function' Then [Function] When @GrpByLevel1 = 'Size' Then [Size] When @GrpByLevel1 = 'Shade' Then [Shade] When @GrpByLevel1 = 'Marketing Group' Then [Marketing Group] When @GrpByLevel1 = 'Sub Marketing Group' Then [Sub Marketing Group] --When @GrpByLevel1 = 'Parent SKU Code' Then ST.State_ID End , Case When @GrpByLevel2 = 'Customer' Then [BILL_TO_NAME] When @GrpByLevel2 = 'Ship To' Then [Ship To] When @GrpByLevel2 = 'Bill-To' Then [Zone] When @GrpByLevel2 = 'Region' Then [Region] When @GrpByLevel2 = 'State' Then [State] When @GrpByLevel2 = 'Area' Then [Area] When @GrpByLevel2 = 'Chain' Then [Chain] When @GrpByLevel2 = 'Channel' Then [Channel] When @GrpByLevel2 = 'Sub Chain' Then [Sub Chain] When @GrpByLevel2 = 'Sub Channel' Then [Sub Channel] When @GrpByLevel2 = 'Grade' Then [Grade] --When @GrpByLevel1 = 'Sales Route' Then ST.State_ID When @GrpByLevel2 = 'Sales Rep' Then [Sales Rep] When @GrpByLevel2 = 'SKU' Then [SKU] When @GrpByLevel2 = 'Agency' Then [Agency] When @GrpByLevel2 = 'Brand' Then [Brand] When @GrpByLevel2 = 'Category' Then [Category] When @GrpByLevel2 = 'Sub Brand' Then [Sub Brand] When @GrpByLevel2 = 'Sub Category' Then [Sub Category] When @GrpByLevel2 = 'Type' Then [Type] When @GrpByLevel2 = 'Function' Then [Function] When @GrpByLevel2 = 'Size' Then [Size] When @GrpByLevel2 = 'Shade' Then [Shade] When @GrpByLevel2 = 'Marketing Group' Then [Marketing Group] When @GrpByLevel2 = 'Sub Marketing Group' Then [Sub Marketing Group] --When @GrpByLevel1 = 'Parent SKU Code' Then ST.State_ID End , Case When @GrpByLevel3 = 'Customer' Then [BILL_TO_NAME] When @GrpByLevel3 = 'Ship To' Then [Ship To] When @GrpByLevel3 = 'Bill-To' Then [Zone] When @GrpByLevel3 = 'Region' Then [Region] When @GrpByLevel3 = 'State' Then [State] When @GrpByLevel3 = 'Area' Then [Area] When @GrpByLevel3 = 'Chain' Then [Chain] When @GrpByLevel3 = 'Channel' Then [Channel] When @GrpByLevel3 = 'Sub Chain' Then [Sub Chain] When @GrpByLevel3 = 'Sub Channel' Then [Sub Channel] When @GrpByLevel3 = 'Grade' Then [Grade] --When @GrpByLevel1 = 'Sales Route' Then ST.State_ID When @GrpByLevel3 = 'Sales Rep' Then [Sales Rep] When @GrpByLevel3 = 'SKU' Then [SKU] When @GrpByLevel3 = 'Agency' Then [Agency] When @GrpByLevel3 = 'Brand' Then [Brand] When @GrpByLevel3 = 'Category' Then [Category] When @GrpByLevel3 = 'Sub Brand' Then [Sub Brand] When @GrpByLevel3 = 'Sub Category' Then [Sub Category] When @GrpByLevel3 = 'Type' Then [Type] When @GrpByLevel3 = 'Function' Then [Function] When @GrpByLevel3 = 'Size' Then [Size] When @GrpByLevel3 = 'Shade' Then [Shade] When @GrpByLevel3 = 'Marketing Group' Then [Marketing Group] When @GrpByLevel3 = 'Sub Marketing Group' Then [Sub Marketing Group] End, TblBB.ITEM_CODE, TblBB.ITEM_DESCRIPTION,[UOM] )TableD Where TableD.Grouplevel1 = TableA.Grouplevel1 AND TableD.Grouplevel2 = TableA.Grouplevel2 AND TableD.Grouplevel3 = TableA.Grouplevel3 ),0) As 'NET_SALES_QTY' , ISNULL( (Select [FOC_AMT] From ( Select SUM([FOC_AMT]) As [FOC_AMT] , ITEM_CODE, ITEM_DESCRIPTION , '' AS [UOM] --, SUM([SALES_QTY]) As 'SALES_QTY' , Case When @GrpByLevel1 = 'Customer' Then [BILL_TO_NAME] When @GrpByLevel1 = 'Ship To' Then [Ship To] When @GrpByLevel1 = 'Zone' Then [Zone] When @GrpByLevel1 = 'Region' Then [Region] When @GrpByLevel1 = 'State' Then [State] When @GrpByLevel1 = 'Area' Then [Area] When @GrpByLevel1 = 'Chain' Then [Chain] When @GrpByLevel1 = 'Channel' Then [Channel] When @GrpByLevel1 = 'Sub Chain' Then [Sub Chain] When @GrpByLevel1 = 'Sub Channel' Then [Sub Channel] When @GrpByLevel1 = 'Grade' Then [Grade] --When @GrpByLevel1 = 'Sales Route' Then ST.State_ID When @GrpByLevel1 = 'Sales Rep' Then [Sales Rep] When @GrpByLevel1 = 'SKU' Then [SKU] When @GrpByLevel1 = 'Agency' Then [Agency] When @GrpByLevel1 = 'Brand' Then [Brand] When @GrpByLevel1 = 'Category' Then [Category] When @GrpByLevel1 = 'Sub Brand' Then [Sub Brand] When @GrpByLevel1 = 'Sub Category' Then [Sub Category] When @GrpByLevel1 = 'Type' Then [Type] When @GrpByLevel1 = 'Function' Then [Function] When @GrpByLevel1 = 'Size' Then [Size] When @GrpByLevel1 = 'Shade' Then [Shade] When @GrpByLevel1 = 'Marketing Group' Then [Marketing Group] When @GrpByLevel1 = 'Sub Marketing Group' Then [Sub Marketing Group] --When @GrpByLevel1 = 'Parent SKU Code' Then ST.State_ID End As [Grouplevel1], Case When @GrpByLevel2 = 'Customer' Then [BILL_TO_NAME] When @GrpByLevel2 = 'Ship To' Then [Ship To] When @GrpByLevel2 = 'Zone' Then [Zone] When @GrpByLevel2 = 'Region' Then [Region] When @GrpByLevel2 = 'State' Then [State] When @GrpByLevel2 = 'Area' Then [Area] When @GrpByLevel2 = 'Chain' Then [Chain] When @GrpByLevel2 = 'Channel' Then [Channel] When @GrpByLevel2 = 'Sub Chain' Then [Sub Chain] When @GrpByLevel2 = 'Sub Channel' Then [Sub Channel] When @GrpByLevel2 = 'Grade' Then [Grade] --When @GrpByLevel1 = 'Sales Route' Then ST.State_ID When @GrpByLevel2 = 'Sales Rep' Then [Sales Rep] When @GrpByLevel2 = 'SKU' Then [SKU] When @GrpByLevel2 = 'Agency' Then [Agency] When @GrpByLevel2 = 'Brand' Then [Brand] When @GrpByLevel2 = 'Category' Then [Category] When @GrpByLevel2 = 'Sub Brand' Then [Sub Brand] When @GrpByLevel2 = 'Sub Category' Then [Sub Category] When @GrpByLevel2 = 'Type' Then [Type] When @GrpByLevel2 = 'Function' Then [Function] When @GrpByLevel2 = 'Size' Then [Size] When @GrpByLevel2 = 'Shade' Then [Shade] When @GrpByLevel2 = 'Marketing Group' Then [Marketing Group] When @GrpByLevel2 = 'Sub Marketing Group' Then [Sub Marketing Group] --When @GrpByLevel1 = 'Parent SKU Code' Then ST.State_ID End As [Grouplevel2], Case When @GrpByLevel3 = 'Customer' Then [BILL_TO_NAME] When @GrpByLevel3 = 'Ship To' Then [Ship To] When @GrpByLevel3 = 'Zone' Then [Zone] When @GrpByLevel3 = 'Region' Then [Region] When @GrpByLevel3 = 'State' Then [State] When @GrpByLevel3 = 'Area' Then [Area] When @GrpByLevel3 = 'Chain' Then [Chain] When @GrpByLevel3 = 'Channel' Then [Channel] When @GrpByLevel3 = 'Sub Chain' Then [Sub Chain] When @GrpByLevel3 = 'Sub Channel' Then [Sub Channel] When @GrpByLevel3 = 'Grade' Then [Grade] --When @GrpByLevel1 = 'Sales Route' Then ST.State_ID When @GrpByLevel3 = 'Sales Rep' Then [Sales Rep] When @GrpByLevel3 = 'SKU' Then [SKU] When @GrpByLevel3 = 'Agency' Then [Agency] When @GrpByLevel3 = 'Brand' Then [Brand] When @GrpByLevel3 = 'Category' Then [Category] When @GrpByLevel3 = 'Sub Brand' Then [Sub Brand] When @GrpByLevel3 = 'Sub Category' Then [Sub Category] When @GrpByLevel3 = 'Type' Then [Type] When @GrpByLevel3 = 'Function' Then [Function] When @GrpByLevel3 = 'Size' Then [Size] When @GrpByLevel3 = 'Shade' Then [Shade] When @GrpByLevel3 = 'Marketing Group' Then [Marketing Group] When @GrpByLevel3 = 'Sub Marketing Group' Then [Sub Marketing Group] --When @GrpByLevel1 = 'Parent SKU Code' Then ST.State_ID End As [Grouplevel3] From ( Select ISNULL(a.Bill_Type_Description,'') 'Billing_Type' ,ISNULL(a.Bill_Type_Description,'') 'Bill_Type_Description' ,ISNULL(ml.[Name],'') 'BILL_TO_NAME' ,ISNULL(a.Sold_to_party,'') 'Bill_To_CustomerCode' ,ISNULL(a.Material,'') 'ITEM_CODE' ,ISNULL(mq.Quest_Desc,'') 'ITEM_DESCRIPTION' ,ISNULL(a.Sales_unit,'') 'UOM' ,ISNULL(a.Ship_to_party,'') 'Ship To' ,ISNULL(Z.[Name],'') 'Zone' ,ISNULL(RE.[Name],'') 'Region' ,ISNULL(ST.[Desc],'') 'State' ,ISNULL(AR.[Name],'') 'Area' ,ISNULL(CC.[Name],'') 'Chain' ,ISNULL(CH.[Name],'') 'Channel' ,ISNULL(subchain.[Name],'') 'Sub Chain' ,ISNULL(subch.[Name],'') 'Sub Channel' ,ISNULL(G.[Name],'') 'Grade' ,ISNULL(a.SALES_PERSON,'') 'Sales Rep' ,ISNULL(mq.Quest_Desc,'') 'SKU' ,ISNULL(mqf1.[Name],'') 'Agency' ,ISNULL(mqf2.[Name],'') 'Brand' ,ISNULL(mqf3.[Name],'') 'Category' ,ISNULL(mqf9.[Name],'') 'Sub Brand' ,ISNULL(mqf8.[Name],'') 'Sub Category' ,ISNULL(mqf4.[Name],'') 'Type' ,ISNULL(mqf5.[Name],'') 'Function' ,ISNULL(mqf6.[Name],'') 'Size' ,ISNULL(mqf7.[Name],'') 'Shade' ,ISNULL(mqf10.[Name],'')'Marketing Group' ,ISNULL(mqf11.[Name],'') 'Sub Marketing Group' ,convert(decimal(18,2) ,a.Price) 'FOC_AMT' --,convert(decimal(18,2) ,a.[Bill_Qty ]) 'RETURN_SALES_QTY' ,a.Billing_date 'INVOICE_DATE' -- YYYY-MM-DD ,DAY(CONVERT(DATETIME, a.Billing_date)) 'DAY' FROM dbo.DM_ORDER_SALES_REPORT a with (nolock) LEFT OUTER JOIN MAIN_QUESTION mq on mq.Quest_Code = a.Material -- SKU LEFT OUTER JOIN MAIN_LOCATION ml on ML.ML_NO = a.Sold_to_party LEFT JOIN Zone Z ON Z.Zone_ID = ml.Zone -- Zone LEFT JOIN Region RE ON RE.ID = ml.Region -- Region LEFT JOIN STATE ST ON ST.State_ID = ml.State -- State LEFT JOIN AREA AR ON AR.ID = ml.Area -- Area LEFT JOIN CHAIN CC ON CC.Chain_ID = ml.Chain -- Chain LEFT JOIN CHANNEL CH ON CH.Channel_ID = ml.Channel -- Channel LEFT JOIN GRADE G ON G.Grade_ID = ml.Grade -- Grade LEFT JOIN SUB_CHAIN subchain ON subchain.SubChain_ID = ml.SubChain_ID -- SubChain LEFT JOIN SUB_CHANNEL subch ON subch.SubChannel_ID = ml.SubChannel_ID -- SubChannel --LEFT OUTER JOIN MAIN_QUESTION mq on mq.Quest_ID = b.Quest_ID -- SKU LEFT JOIN dbo.MAIN_QUESTION_FILTER1 mqf1 ON mq.Filter_1 = mqf1.Code -- Manufacture LEFT JOIN dbo.MAIN_QUESTION_FILTER2 mqf2 ON mq.Filter_2 = mqf2.Code -- Brand LEFT JOIN dbo.MAIN_QUESTION_FILTER3 mqf3 ON mq.Filter_3 = mqf3.Code -- Category LEFT JOIN dbo.MAIN_QUESTION_FILTER4 mqf4 ON mq.Filter_4 = mqf4.Code -- Type LEFT JOIN dbo.MAIN_QUESTION_FILTER5 mqf5 ON mq.Filter_5 = mqf5.Code -- [Function] LEFT JOIN dbo.MAIN_QUESTION_FILTER6 mqf6 ON mq.Filter_6 = mqf6.Code -- Size LEFT JOIN dbo.MAIN_QUESTION_FILTER7 mqf7 ON mq.Filter_7 = mqf7.Code -- Shade LEFT JOIN dbo.MAIN_QUESTION_FILTER8 mqf8 ON mq.Filter_8 = mqf8.Code -- SubCatgory LEFT JOIN dbo.MAIN_QUESTION_FILTER9 mqf9 ON mq.Filter_9 = mqf9.Code -- SubBrand LEFT JOIN dbo.MAIN_QUESTION_FILTER10 mqf10 ON mq.Filter_10 = mqf10.Code -- MktGroup LEFT JOIN dbo.MAIN_QUESTION_FILTER11 mqf11 ON mq.Filter_11 = mqf11.Code --SubMktGroup --LEFT JOIN ROUTE_USER_ASSIGNMENT rua on rua.Route_ID = ml.ML_ID -- 'SalesRep' --LEFT JOIN USER_LOGIN ul ON ul.[User_ID] = rua.[User_ID] Where CONVERT(DATETIME, a.Billing_date) = @StartDate --CONVERT(Datetime, '2023-01-01') --@StartDate AND CONVERT(decimal(18,2), a.Price) = 0.00 AND (a.Sold_to_party IN (Select Value from STRING_SPLIT(@CustCode,',')) OR @CustCode = '') AND (a.Ship_to_party = @ShipToCode OR @ShipToCode = '') AND ( Z.Zone_ID = @ZoneID OR @ZoneID = -1) AND ( RE.ID = @RegionID OR @RegionID = -1) AND ( ST.State_ID = @StateID OR @StateID = -1) AND ( AR.ID = @AreaID OR @AreaID = -1) AND ( CC.Chain_ID = @ChainID OR @ChainID = -1) AND ( CH.Channel_ID = @ChannelID OR @ChannelID = -1) AND ( subchain.SubChain_ID = @SubChainID OR @SubChainID = -1) AND ( subch.SubChannel_ID = @SubChannelID OR @SubChannelID = -1) AND ( G.Grade_ID = @GradeID OR @GradeID = -1) AND ( a.USER_ID = @SalesRep OR @SalesRep = -1) AND ( mq.Quest_Code IN (Select Value from STRING_SPLIT(@SKU,',')) OR @SKU = '') AND ( mqf1.ID = @AgencyID OR @AgencyID = -1) AND ( mqf2.ID = @BrandID OR @BrandID = -1) AND ( mqf3.ID = @CategoryID OR @CategoryID = -1) AND ( mqf9.ID = @SubBrandID OR @SubBrandID = -1) AND ( mqf8.ID = @SubCategoryID OR @SubCategoryID = -1) AND ( mqf4.ID = @PackTypeID OR @PackTypeID = -1) AND ( mqf5.ID = @FunctionID OR @FunctionID = -1) AND ( mqf6.ID = @PackSizeID OR @PackSizeID = -1) AND ( mqf7.ID = @ShadeID OR @ShadeID = -1) AND ( mqf10.ID = @MarketingGrpID OR @MarketingGrpID = -1) AND ( mqf11.ID = @SubMarketingGrpID OR @SubMarketingGrpID = -1) ) TblAA --Where --TblAA.Billing_Type In ('ZBC1','ZCN2','ZCN4','ZCN5','ZCN6') group by Case When @GrpByLevel1 = 'Customer' Then [BILL_TO_NAME] When @GrpByLevel1 = 'Ship To' Then [Ship To] When @GrpByLevel1 = 'Zone' Then [Zone] When @GrpByLevel1 = 'Region' Then [Region] When @GrpByLevel1 = 'State' Then [State] When @GrpByLevel1 = 'Area' Then [Area] When @GrpByLevel1 = 'Chain' Then [Chain] When @GrpByLevel1 = 'Channel' Then [Channel] When @GrpByLevel1 = 'Sub Chain' Then [Sub Chain] When @GrpByLevel1 = 'Sub Channel' Then [Sub Channel] When @GrpByLevel1 = 'Grade' Then [Grade] --When @GrpByLevel1 = 'Sales Route' Then ST.State_ID When @GrpByLevel1 = 'Sales Rep' Then [Sales Rep] When @GrpByLevel1 = 'SKU' Then [SKU] When @GrpByLevel1 = 'Agency' Then [Agency] When @GrpByLevel1 = 'Brand' Then [Brand] When @GrpByLevel1 = 'Category' Then [Category] When @GrpByLevel1 = 'Sub Brand' Then [Sub Brand] When @GrpByLevel1 = 'Sub Category' Then [Sub Category] When @GrpByLevel1 = 'Type' Then [Type] When @GrpByLevel1 = 'Function' Then [Function] When @GrpByLevel1 = 'Size' Then [Size] When @GrpByLevel1 = 'Shade' Then [Shade] When @GrpByLevel1 = 'Marketing Group' Then [Marketing Group] When @GrpByLevel1 = 'Sub Marketing Group' Then [Sub Marketing Group] --When @GrpByLevel1 = 'Parent SKU Code' Then ST.State_ID End , Case When @GrpByLevel2 = 'Customer' Then [BILL_TO_NAME] When @GrpByLevel2 = 'Ship To' Then [Ship To] When @GrpByLevel2 = 'Zone' Then [Zone] When @GrpByLevel2 = 'Region' Then [Region] When @GrpByLevel2 = 'State' Then [State] When @GrpByLevel2 = 'Area' Then [Area] When @GrpByLevel2 = 'Chain' Then [Chain] When @GrpByLevel2 = 'Channel' Then [Channel] When @GrpByLevel2 = 'Sub Chain' Then [Sub Chain] When @GrpByLevel2 = 'Sub Channel' Then [Sub Channel] When @GrpByLevel2 = 'Grade' Then [Grade] --When @GrpByLevel1 = 'Sales Route' Then ST.State_ID When @GrpByLevel2 = 'Sales Rep' Then [Sales Rep] When @GrpByLevel2 = 'SKU' Then [SKU] When @GrpByLevel2 = 'Agency' Then [Agency] When @GrpByLevel2 = 'Brand' Then [Brand] When @GrpByLevel2 = 'Category' Then [Category] When @GrpByLevel2 = 'Sub Brand' Then [Sub Brand] When @GrpByLevel2 = 'Sub Category' Then [Sub Category] When @GrpByLevel2 = 'Type' Then [Type] When @GrpByLevel2 = 'Function' Then [Function] When @GrpByLevel2 = 'Size' Then [Size] When @GrpByLevel2 = 'Shade' Then [Shade] When @GrpByLevel2 = 'Marketing Group' Then [Marketing Group] When @GrpByLevel2 = 'Sub Marketing Group' Then [Sub Marketing Group] --When @GrpByLevel1 = 'Parent SKU Code' Then ST.State_ID End , Case When @GrpByLevel3 = 'Customer' Then [BILL_TO_NAME] When @GrpByLevel3 = 'Ship To' Then [Ship To] When @GrpByLevel3 = 'Zone' Then [Zone] When @GrpByLevel3 = 'Region' Then [Region] When @GrpByLevel3 = 'State' Then [State] When @GrpByLevel3 = 'Area' Then [Area] When @GrpByLevel3 = 'Chain' Then [Chain] When @GrpByLevel3 = 'Channel' Then [Channel] When @GrpByLevel3 = 'Sub Chain' Then [Sub Chain] When @GrpByLevel3 = 'Sub Channel' Then [Sub Channel] When @GrpByLevel3 = 'Grade' Then [Grade] --When @GrpByLevel1 = 'Sales Route' Then ST.State_ID When @GrpByLevel3 = 'Sales Rep' Then [Sales Rep] When @GrpByLevel3 = 'SKU' Then [SKU] When @GrpByLevel3 = 'Agency' Then [Agency] When @GrpByLevel3 = 'Brand' Then [Brand] When @GrpByLevel3 = 'Category' Then [Category] When @GrpByLevel3 = 'Sub Brand' Then [Sub Brand] When @GrpByLevel3 = 'Sub Category' Then [Sub Category] When @GrpByLevel3 = 'Type' Then [Type] When @GrpByLevel3 = 'Function' Then [Function] When @GrpByLevel3 = 'Size' Then [Size] When @GrpByLevel3 = 'Shade' Then [Shade] When @GrpByLevel3 = 'Marketing Group' Then [Marketing Group] When @GrpByLevel3 = 'Sub Marketing Group' Then [Sub Marketing Group] End, TblAA.ITEM_CODE, TblAA.ITEM_DESCRIPTION,[UOM] ) TableD Where TableD.Grouplevel1 = TableA.[Grouplevel1] AND TableD.Grouplevel2 = TableA.[Grouplevel2] AND TableD.Grouplevel3 = TableA.[Grouplevel3] ),0) As [FOC_AMT] , ISNULL( (Select [FOC_QTY] From ( Select SUM([FOC_QTY]) As [FOC_QTY] , ITEM_CODE, ITEM_DESCRIPTION , '' AS [UOM] --, SUM([SALES_QTY]) As 'SALES_QTY' , Case When @GrpByLevel1 = 'Customer' Then [BILL_TO_NAME] When @GrpByLevel1 = 'Ship To' Then [Ship To] When @GrpByLevel1 = 'Zone' Then [Zone] When @GrpByLevel1 = 'Region' Then [Region] When @GrpByLevel1 = 'State' Then [State] When @GrpByLevel1 = 'Area' Then [Area] When @GrpByLevel1 = 'Chain' Then [Chain] When @GrpByLevel1 = 'Channel' Then [Channel] When @GrpByLevel1 = 'Sub Chain' Then [Sub Chain] When @GrpByLevel1 = 'Sub Channel' Then [Sub Channel] When @GrpByLevel1 = 'Grade' Then [Grade] --When @GrpByLevel1 = 'Sales Route' Then ST.State_ID When @GrpByLevel1 = 'Sales Rep' Then [Sales Rep] When @GrpByLevel1 = 'SKU' Then [SKU] When @GrpByLevel1 = 'Agency' Then [Agency] When @GrpByLevel1 = 'Brand' Then [Brand] When @GrpByLevel1 = 'Category' Then [Category] When @GrpByLevel1 = 'Sub Brand' Then [Sub Brand] When @GrpByLevel1 = 'Sub Category' Then [Sub Category] When @GrpByLevel1 = 'Type' Then [Type] When @GrpByLevel1 = 'Function' Then [Function] When @GrpByLevel1 = 'Size' Then [Size] When @GrpByLevel1 = 'Shade' Then [Shade] When @GrpByLevel1 = 'Marketing Group' Then [Marketing Group] When @GrpByLevel1 = 'Sub Marketing Group' Then [Sub Marketing Group] --When @GrpByLevel1 = 'Parent SKU Code' Then ST.State_ID End As [Grouplevel1], Case When @GrpByLevel2 = 'Customer' Then [BILL_TO_NAME] When @GrpByLevel2 = 'Ship To' Then [Ship To] When @GrpByLevel2 = 'Zone' Then [Zone] When @GrpByLevel2 = 'Region' Then [Region] When @GrpByLevel2 = 'State' Then [State] When @GrpByLevel2 = 'Area' Then [Area] When @GrpByLevel2 = 'Chain' Then [Chain] When @GrpByLevel2 = 'Channel' Then [Channel] When @GrpByLevel2 = 'Sub Chain' Then [Sub Chain] When @GrpByLevel2 = 'Sub Channel' Then [Sub Channel] When @GrpByLevel2 = 'Grade' Then [Grade] --When @GrpByLevel1 = 'Sales Route' Then ST.State_ID When @GrpByLevel2 = 'Sales Rep' Then [Sales Rep] When @GrpByLevel2 = 'SKU' Then [SKU] When @GrpByLevel2 = 'Agency' Then [Agency] When @GrpByLevel2 = 'Brand' Then [Brand] When @GrpByLevel2 = 'Category' Then [Category] When @GrpByLevel2 = 'Sub Brand' Then [Sub Brand] When @GrpByLevel2 = 'Sub Category' Then [Sub Category] When @GrpByLevel2 = 'Type' Then [Type] When @GrpByLevel2 = 'Function' Then [Function] When @GrpByLevel2 = 'Size' Then [Size] When @GrpByLevel2 = 'Shade' Then [Shade] When @GrpByLevel2 = 'Marketing Group' Then [Marketing Group] When @GrpByLevel2 = 'Sub Marketing Group' Then [Sub Marketing Group] --When @GrpByLevel1 = 'Parent SKU Code' Then ST.State_ID End As [Grouplevel2], Case When @GrpByLevel3 = 'Customer' Then [BILL_TO_NAME] When @GrpByLevel3 = 'Ship To' Then [Ship To] When @GrpByLevel3 = 'Zone' Then [Zone] When @GrpByLevel3 = 'Region' Then [Region] When @GrpByLevel3 = 'State' Then [State] When @GrpByLevel3 = 'Area' Then [Area] When @GrpByLevel3 = 'Chain' Then [Chain] When @GrpByLevel3 = 'Channel' Then [Channel] When @GrpByLevel3 = 'Sub Chain' Then [Sub Chain] When @GrpByLevel3 = 'Sub Channel' Then [Sub Channel] When @GrpByLevel3 = 'Grade' Then [Grade] --When @GrpByLevel1 = 'Sales Route' Then ST.State_ID When @GrpByLevel3 = 'Sales Rep' Then [Sales Rep] When @GrpByLevel3 = 'SKU' Then [SKU] When @GrpByLevel3 = 'Agency' Then [Agency] When @GrpByLevel3 = 'Brand' Then [Brand] When @GrpByLevel3 = 'Category' Then [Category] When @GrpByLevel3 = 'Sub Brand' Then [Sub Brand] When @GrpByLevel3 = 'Sub Category' Then [Sub Category] When @GrpByLevel3 = 'Type' Then [Type] When @GrpByLevel3 = 'Function' Then [Function] When @GrpByLevel3 = 'Size' Then [Size] When @GrpByLevel3 = 'Shade' Then [Shade] When @GrpByLevel3 = 'Marketing Group' Then [Marketing Group] When @GrpByLevel3 = 'Sub Marketing Group' Then [Sub Marketing Group] --When @GrpByLevel1 = 'Parent SKU Code' Then ST.State_ID End As [Grouplevel3] From ( Select ISNULL(a.Bill_Type_Description,'') 'Billing_Type' ,ISNULL(a.Bill_Type_Description,'') 'Bill_Type_Description' ,ISNULL(ml.[Name],'') 'BILL_TO_NAME' ,ISNULL(a.Sold_to_party,'') 'Bill_To_CustomerCode' ,ISNULL(a.Material,'') 'ITEM_CODE' ,ISNULL(mq.Quest_Desc,'') 'ITEM_DESCRIPTION' ,ISNULL(a.Sales_unit,'') 'UOM' ,ISNULL(a.Ship_to_party,'') 'Ship To' ,ISNULL(Z.[Name],'') 'Zone' ,ISNULL(RE.[Name],'') 'Region' ,ISNULL(ST.[Desc],'') 'State' ,ISNULL(AR.[Name],'') 'Area' ,ISNULL(CC.[Name],'') 'Chain' ,ISNULL(CH.[Name],'') 'Channel' ,ISNULL(subchain.[Name],'') 'Sub Chain' ,ISNULL(subch.[Name],'') 'Sub Channel' ,ISNULL(G.[Name],'') 'Grade' ,ISNULL(a.SALES_PERSON,'') 'Sales Rep' ,ISNULL(mq.Quest_Desc,'') 'SKU' ,ISNULL(mqf1.[Name],'') 'Agency' ,ISNULL(mqf2.[Name],'') 'Brand' ,ISNULL(mqf3.[Name],'') 'Category' ,ISNULL(mqf9.[Name],'') 'Sub Brand' ,ISNULL(mqf8.[Name],'') 'Sub Category' ,ISNULL(mqf4.[Name],'') 'Type' ,ISNULL(mqf5.[Name],'') 'Function' ,ISNULL(mqf6.[Name],'') 'Size' ,ISNULL(mqf7.[Name],'') 'Shade' ,ISNULL(mqf10.[Name],'')'Marketing Group' ,ISNULL(mqf11.[Name],'') 'Sub Marketing Group' ,convert(decimal(18,2) ,a.[Bill_Qty]) 'FOC_QTY' --,convert(decimal(18,2) ,a.[Bill_Qty ]) 'RETURN_SALES_QTY' ,a.Billing_date 'INVOICE_DATE' -- YYYY-MM-DD ,DAY(CONVERT(DATETIME, a.Billing_date)) 'DAY' FROM dbo.DM_ORDER_SALES_REPORT a with (nolock) LEFT OUTER JOIN MAIN_QUESTION mq on mq.Quest_Code = a.Material -- SKU LEFT OUTER JOIN MAIN_LOCATION ml on ML.ML_NO = a.Sold_to_party LEFT JOIN Zone Z ON Z.Zone_ID = ml.Zone -- Zone LEFT JOIN Region RE ON RE.ID = ml.Region -- Region LEFT JOIN STATE ST ON ST.State_ID = ml.State -- State LEFT JOIN AREA AR ON AR.ID = ml.Area -- Area LEFT JOIN CHAIN CC ON CC.Chain_ID = ml.Chain -- Chain LEFT JOIN CHANNEL CH ON CH.Channel_ID = ml.Channel -- Channel LEFT JOIN GRADE G ON G.Grade_ID = ml.Grade -- Grade LEFT JOIN SUB_CHAIN subchain ON subchain.SubChain_ID = ml.SubChain_ID -- SubChain LEFT JOIN SUB_CHANNEL subch ON subch.SubChannel_ID = ml.SubChannel_ID -- SubChannel --LEFT OUTER JOIN MAIN_QUESTION mq on mq.Quest_ID = b.Quest_ID -- SKU LEFT JOIN dbo.MAIN_QUESTION_FILTER1 mqf1 ON mq.Filter_1 = mqf1.Code -- Manufacture LEFT JOIN dbo.MAIN_QUESTION_FILTER2 mqf2 ON mq.Filter_2 = mqf2.Code -- Brand LEFT JOIN dbo.MAIN_QUESTION_FILTER3 mqf3 ON mq.Filter_3 = mqf3.Code -- Category LEFT JOIN dbo.MAIN_QUESTION_FILTER4 mqf4 ON mq.Filter_4 = mqf4.Code -- Type LEFT JOIN dbo.MAIN_QUESTION_FILTER5 mqf5 ON mq.Filter_5 = mqf5.Code -- [Function] LEFT JOIN dbo.MAIN_QUESTION_FILTER6 mqf6 ON mq.Filter_6 = mqf6.Code -- Size LEFT JOIN dbo.MAIN_QUESTION_FILTER7 mqf7 ON mq.Filter_7 = mqf7.Code -- Shade LEFT JOIN dbo.MAIN_QUESTION_FILTER8 mqf8 ON mq.Filter_8 = mqf8.Code -- SubCatgory LEFT JOIN dbo.MAIN_QUESTION_FILTER9 mqf9 ON mq.Filter_9 = mqf9.Code -- SubBrand LEFT JOIN dbo.MAIN_QUESTION_FILTER10 mqf10 ON mq.Filter_10 = mqf10.Code -- MktGroup LEFT JOIN dbo.MAIN_QUESTION_FILTER11 mqf11 ON mq.Filter_11 = mqf11.Code --SubMktGroup --LEFT JOIN ROUTE_USER_ASSIGNMENT rua on rua.Route_ID = ml.ML_ID -- 'SalesRep' --LEFT JOIN USER_LOGIN ul ON ul.[User_ID] = rua.[User_ID] Where CONVERT(DATETIME, a.Billing_date) = @StartDate --CONVERT(Datetime, '2023-01-01') --@StartDate AND CONVERT(decimal(18,2), a.Price) = 0.00 AND (a.Sold_to_party IN (Select Value from STRING_SPLIT(@CustCode,',')) OR @CustCode = '') AND (a.Ship_to_party = @ShipToCode OR @ShipToCode = '') AND ( Z.Zone_ID = @ZoneID OR @ZoneID = -1) AND ( RE.ID = @RegionID OR @RegionID = -1) AND ( ST.State_ID = @StateID OR @StateID = -1) AND ( AR.ID = @AreaID OR @AreaID = -1) AND ( CC.Chain_ID = @ChainID OR @ChainID = -1) AND ( CH.Channel_ID = @ChannelID OR @ChannelID = -1) AND ( subchain.SubChain_ID = @SubChainID OR @SubChainID = -1) AND ( subch.SubChannel_ID = @SubChannelID OR @SubChannelID = -1) AND ( G.Grade_ID = @GradeID OR @GradeID = -1) AND ( a.USER_ID = @SalesRep OR @SalesRep = -1) AND ( mq.Quest_Code IN (Select Value from STRING_SPLIT(@SKU,',')) OR @SKU = '') AND ( mqf1.ID = @AgencyID OR @AgencyID = -1) AND ( mqf2.ID = @BrandID OR @BrandID = -1) AND ( mqf3.ID = @CategoryID OR @CategoryID = -1) AND ( mqf9.ID = @SubBrandID OR @SubBrandID = -1) AND ( mqf8.ID = @SubCategoryID OR @SubCategoryID = -1) AND ( mqf4.ID = @PackTypeID OR @PackTypeID = -1) AND ( mqf5.ID = @FunctionID OR @FunctionID = -1) AND ( mqf6.ID = @PackSizeID OR @PackSizeID = -1) AND ( mqf7.ID = @ShadeID OR @ShadeID = -1) AND ( mqf10.ID = @MarketingGrpID OR @MarketingGrpID = -1) AND ( mqf11.ID = @SubMarketingGrpID OR @SubMarketingGrpID = -1) ) TblAA --Where --TblAA.Billing_Type In ('ZBC1','ZCN2','ZCN4','ZCN5','ZCN6') group by Case When @GrpByLevel1 = 'Customer' Then [BILL_TO_NAME] When @GrpByLevel1 = 'Ship To' Then [Ship To] When @GrpByLevel1 = 'Zone' Then [Zone] When @GrpByLevel1 = 'Region' Then [Region] When @GrpByLevel1 = 'State' Then [State] When @GrpByLevel1 = 'Area' Then [Area] When @GrpByLevel1 = 'Chain' Then [Chain] When @GrpByLevel1 = 'Channel' Then [Channel] When @GrpByLevel1 = 'Sub Chain' Then [Sub Chain] When @GrpByLevel1 = 'Sub Channel' Then [Sub Channel] When @GrpByLevel1 = 'Grade' Then [Grade] --When @GrpByLevel1 = 'Sales Route' Then ST.State_ID When @GrpByLevel1 = 'Sales Rep' Then [Sales Rep] When @GrpByLevel1 = 'SKU' Then [SKU] When @GrpByLevel1 = 'Agency' Then [Agency] When @GrpByLevel1 = 'Brand' Then [Brand] When @GrpByLevel1 = 'Category' Then [Category] When @GrpByLevel1 = 'Sub Brand' Then [Sub Brand] When @GrpByLevel1 = 'Sub Category' Then [Sub Category] When @GrpByLevel1 = 'Type' Then [Type] When @GrpByLevel1 = 'Function' Then [Function] When @GrpByLevel1 = 'Size' Then [Size] When @GrpByLevel1 = 'Shade' Then [Shade] When @GrpByLevel1 = 'Marketing Group' Then [Marketing Group] When @GrpByLevel1 = 'Sub Marketing Group' Then [Sub Marketing Group] --When @GrpByLevel1 = 'Parent SKU Code' Then ST.State_ID End , Case When @GrpByLevel2 = 'Customer' Then [BILL_TO_NAME] When @GrpByLevel2 = 'Ship To' Then [Ship To] When @GrpByLevel2 = 'Zone' Then [Zone] When @GrpByLevel2 = 'Region' Then [Region] When @GrpByLevel2 = 'State' Then [State] When @GrpByLevel2 = 'Area' Then [Area] When @GrpByLevel2 = 'Chain' Then [Chain] When @GrpByLevel2 = 'Channel' Then [Channel] When @GrpByLevel2 = 'Sub Chain' Then [Sub Chain] When @GrpByLevel2 = 'Sub Channel' Then [Sub Channel] When @GrpByLevel2 = 'Grade' Then [Grade] --When @GrpByLevel1 = 'Sales Route' Then ST.State_ID When @GrpByLevel2 = 'Sales Rep' Then [Sales Rep] When @GrpByLevel2 = 'SKU' Then [SKU] When @GrpByLevel2 = 'Agency' Then [Agency] When @GrpByLevel2 = 'Brand' Then [Brand] When @GrpByLevel2 = 'Category' Then [Category] When @GrpByLevel2 = 'Sub Brand' Then [Sub Brand] When @GrpByLevel2 = 'Sub Category' Then [Sub Category] When @GrpByLevel2 = 'Type' Then [Type] When @GrpByLevel2 = 'Function' Then [Function] When @GrpByLevel2 = 'Size' Then [Size] When @GrpByLevel2 = 'Shade' Then [Shade] When @GrpByLevel2 = 'Marketing Group' Then [Marketing Group] When @GrpByLevel2 = 'Sub Marketing Group' Then [Sub Marketing Group] --When @GrpByLevel1 = 'Parent SKU Code' Then ST.State_ID End , Case When @GrpByLevel3 = 'Customer' Then [BILL_TO_NAME] When @GrpByLevel3 = 'Ship To' Then [Ship To] When @GrpByLevel3 = 'Zone' Then [Zone] When @GrpByLevel3 = 'Region' Then [Region] When @GrpByLevel3 = 'State' Then [State] When @GrpByLevel3 = 'Area' Then [Area] When @GrpByLevel3 = 'Chain' Then [Chain] When @GrpByLevel3 = 'Channel' Then [Channel] When @GrpByLevel3 = 'Sub Chain' Then [Sub Chain] When @GrpByLevel3 = 'Sub Channel' Then [Sub Channel] When @GrpByLevel3 = 'Grade' Then [Grade] --When @GrpByLevel1 = 'Sales Route' Then ST.State_ID When @GrpByLevel3 = 'Sales Rep' Then [Sales Rep] When @GrpByLevel3 = 'SKU' Then [SKU] When @GrpByLevel3 = 'Agency' Then [Agency] When @GrpByLevel3 = 'Brand' Then [Brand] When @GrpByLevel3 = 'Category' Then [Category] When @GrpByLevel3 = 'Sub Brand' Then [Sub Brand] When @GrpByLevel3 = 'Sub Category' Then [Sub Category] When @GrpByLevel3 = 'Type' Then [Type] When @GrpByLevel3 = 'Function' Then [Function] When @GrpByLevel3 = 'Size' Then [Size] When @GrpByLevel3 = 'Shade' Then [Shade] When @GrpByLevel3 = 'Marketing Group' Then [Marketing Group] When @GrpByLevel3 = 'Sub Marketing Group' Then [Sub Marketing Group] End, TblAA.ITEM_CODE, TblAA.ITEM_DESCRIPTION,[UOM] ) TableD Where TableD.Grouplevel1 = TableA.[Grouplevel1] AND TableD.Grouplevel2 = TableA.[Grouplevel2] AND TableD.Grouplevel3 = TableA.[Grouplevel3] ),0) As [FOC_QTY] FROM ( Select SUM([RETURN_SALES_AMT]) As 'RETURN_SALES_AMT' , SUM([RETURN_SALES_QTY]) As 'RETURN_SALES_QTY' , ITEM_CODE, ITEM_DESCRIPTION , '' AS [UOM] , Case When @GrpByLevel1 = 'Customer' Then [BILL_TO_NAME] When @GrpByLevel1 = 'Ship To' Then [Ship To] When @GrpByLevel1 = 'Bill-To' Then [Zone] When @GrpByLevel1 = 'Region' Then [Region] When @GrpByLevel1 = 'State' Then [State] When @GrpByLevel1 = 'Area' Then [Area] When @GrpByLevel1 = 'Chain' Then [Chain] When @GrpByLevel1 = 'Channel' Then [Channel] When @GrpByLevel1 = 'Sub Chain' Then [Sub Chain] When @GrpByLevel1 = 'Sub Channel' Then [Sub Channel] When @GrpByLevel1 = 'Grade' Then [Grade] --When @GrpByLevel1 = 'Sales Route' Then ST.State_ID When @GrpByLevel1 = 'Sales Rep' Then [Sales Rep] When @GrpByLevel1 = 'SKU' Then [SKU] When @GrpByLevel1 = 'Agency' Then [Agency] When @GrpByLevel1 = 'Brand' Then [Brand] When @GrpByLevel1 = 'Category' Then [Category] When @GrpByLevel1 = 'Sub Brand' Then [Sub Brand] When @GrpByLevel1 = 'Sub Category' Then [Sub Category] When @GrpByLevel1 = 'Type' Then [Type] When @GrpByLevel1 = 'Function' Then [Function] When @GrpByLevel1 = 'Size' Then [Size] When @GrpByLevel1 = 'Shade' Then [Shade] When @GrpByLevel1 = 'Marketing Group' Then [Marketing Group] When @GrpByLevel1 = 'Sub Marketing Group' Then [Sub Marketing Group] --When @GrpByLevel1 = 'Parent SKU Code' Then ST.State_ID End As [Grouplevel1], Case When @GrpByLevel2 = 'Customer' Then [BILL_TO_NAME] When @GrpByLevel2 = 'Ship To' Then [Ship To] When @GrpByLevel2 = 'Bill-To' Then [Zone] When @GrpByLevel2 = 'Region' Then [Region] When @GrpByLevel2 = 'State' Then [State] When @GrpByLevel2 = 'Area' Then [Area] When @GrpByLevel2 = 'Chain' Then [Chain] When @GrpByLevel2 = 'Channel' Then [Channel] When @GrpByLevel2 = 'Sub Chain' Then [Sub Chain] When @GrpByLevel2 = 'Sub Channel' Then [Sub Channel] When @GrpByLevel2 = 'Grade' Then [Grade] --When @GrpByLevel1 = 'Sales Route' Then ST.State_ID When @GrpByLevel2 = 'Sales Rep' Then [Sales Rep] When @GrpByLevel2 = 'SKU' Then [SKU] When @GrpByLevel2 = 'Agency' Then [Agency] When @GrpByLevel2 = 'Brand' Then [Brand] When @GrpByLevel2 = 'Category' Then [Category] When @GrpByLevel2 = 'Sub Brand' Then [Sub Brand] When @GrpByLevel2 = 'Sub Category' Then [Sub Category] When @GrpByLevel2 = 'Type' Then [Type] When @GrpByLevel2 = 'Function' Then [Function] When @GrpByLevel2 = 'Size' Then [Size] When @GrpByLevel2 = 'Shade' Then [Shade] When @GrpByLevel2 = 'Marketing Group' Then [Marketing Group] When @GrpByLevel2 = 'Sub Marketing Group' Then [Sub Marketing Group] --When @GrpByLevel1 = 'Parent SKU Code' Then ST.State_ID End As [Grouplevel2], Case When @GrpByLevel3 = 'Customer' Then [BILL_TO_NAME] When @GrpByLevel3 = 'Ship To' Then [Ship To] When @GrpByLevel3 = 'Bill-To' Then [Zone] When @GrpByLevel3 = 'Region' Then [Region] When @GrpByLevel3 = 'State' Then [State] When @GrpByLevel3 = 'Area' Then [Area] When @GrpByLevel3 = 'Chain' Then [Chain] When @GrpByLevel3 = 'Channel' Then [Channel] When @GrpByLevel3 = 'Sub Chain' Then [Sub Chain] When @GrpByLevel3 = 'Sub Channel' Then [Sub Channel] When @GrpByLevel3 = 'Grade' Then [Grade] --When @GrpByLevel1 = 'Sales Route' Then ST.State_ID When @GrpByLevel3 = 'Sales Rep' Then [Sales Rep] When @GrpByLevel3 = 'SKU' Then [SKU] When @GrpByLevel3 = 'Agency' Then [Agency] When @GrpByLevel3 = 'Brand' Then [Brand] When @GrpByLevel3 = 'Category' Then [Category] When @GrpByLevel3 = 'Sub Brand' Then [Sub Brand] When @GrpByLevel3 = 'Sub Category' Then [Sub Category] When @GrpByLevel3 = 'Type' Then [Type] When @GrpByLevel3 = 'Function' Then [Function] When @GrpByLevel3 = 'Size' Then [Size] When @GrpByLevel3 = 'Shade' Then [Shade] When @GrpByLevel3 = 'Marketing Group' Then [Marketing Group] When @GrpByLevel3 = 'Sub Marketing Group' Then [Sub Marketing Group] --When @GrpByLevel1 = 'Parent SKU Code' Then ST.State_ID End As [Grouplevel3] From ( Select ISNULL(a.Bill_Type_Description,'') 'Billing_Type' ,ISNULL(a.Bill_Type_Description,'') 'Bill_Type_Description' ,ISNULL(ml.[Name],'') 'BILL_TO_NAME' ,ISNULL(a.Sold_to_party,'') 'Bill_To_CustomerCode' ,ISNULL(a.Material,'') 'ITEM_CODE' ,ISNULL(mq.Quest_Desc,'') 'ITEM_DESCRIPTION' ,ISNULL(a.Sales_unit,'') 'UOM' ,ISNULL(a.Ship_to_party,'') 'Ship To' ,ISNULL(Z.[Name],'') 'Zone' ,ISNULL(RE.[Name],'') 'Region' ,ISNULL(ST.[Desc],'') 'State' ,ISNULL(AR.[Name],'') 'Area' ,ISNULL(CC.[Name],'') 'Chain' ,ISNULL(CH.[Name],'') 'Channel' ,ISNULL(subchain.[Name],'') 'Sub Chain' ,ISNULL(subch.[Name],'') 'Sub Channel' ,ISNULL(G.[Name],'') 'Grade' ,ISNULL(a.SALES_PERSON,'') 'Sales Rep' ,ISNULL(mq.Quest_Desc,'') 'SKU' ,ISNULL(mqf1.[Name],'') 'Agency' ,ISNULL(mqf2.[Name],'') 'Brand' ,ISNULL(mqf3.[Name],'') 'Category' ,ISNULL(mqf9.[Name],'') 'Sub Brand' ,ISNULL(mqf8.[Name],'') 'Sub Category' ,ISNULL(mqf4.[Name],'') 'Type' ,ISNULL(mqf5.[Name],'') 'Function' ,ISNULL(mqf6.[Name],'') 'Size' ,ISNULL(mqf7.[Name],'') 'Shade' ,ISNULL(mqf10.[Name],'')'Marketing Group' ,ISNULL(mqf11.[Name],'') 'Sub Marketing Group' --,ISNULL(SUM(a.FF_Loose + a.FF_Medium + a.FF_Whole),0) 'NET_SALES_QTY' --,ISNULL(convert(decimal(18,2) ,b.Total_Amt - isnull(b.Amt_Disc,0) + isnull(b.Tax_Amt,0)), 0) 'NET_SALES_AMT' ,convert(decimal(18,2) ,a.Price) 'RETURN_SALES_AMT' ,convert(decimal(18,2) ,a.[Bill_Qty]) 'RETURN_SALES_QTY' -- ,ISNULL(SUM(a.FF_Loose + a.FF_Medium + a.FF_Whole) --Over (PARTITION BY a.ItemCode, mq.Quest_Desc, a.InvoiceDate),0) 'NET_SALES_QTY' ,a.Billing_date 'INVOICE_DATE' -- YYYY-MM-DD ,DAY(CONVERT(DATETIME, a.Billing_date)) 'DAY' --,MONTH(CONVERT(DATETIME, a.Billing_date)) 'MONTHLY' FROM dbo.DM_ORDER_SALES_REPORT a with (nolock) --left outer join VW_INTEG_CUST#FILTER ml on ML.ML_NO = a.Sold_to_party --left outer join VW_INTEG_SKU#DMS mq on mq.Quest_Code = a.Material LEFT OUTER JOIN MAIN_QUESTION mq on mq.Quest_Code = a.Material -- SKU LEFT OUTER JOIN MAIN_LOCATION ml on ML.ML_NO = a.Sold_to_party --LEFT OUTER JOIN [dbo].[SFA_SO_RESULTS] b on b.Trans_ID = a.OrderNo_M1 AND b.Quest_ID = mq.Quest_ID LEFT JOIN Zone Z ON Z.Zone_ID = ml.Zone -- Zone LEFT JOIN Region RE ON RE.ID = ml.Region -- Region LEFT JOIN STATE ST ON ST.State_ID = ml.State -- State LEFT JOIN AREA AR ON AR.ID = ml.Area -- Area LEFT JOIN CHAIN CC ON CC.Chain_ID = ml.Chain -- Chain LEFT JOIN CHANNEL CH ON CH.Channel_ID = ml.Channel -- Channel LEFT JOIN GRADE G ON G.Grade_ID = ml.Grade -- Grade LEFT JOIN SUB_CHAIN subchain ON subchain.SubChain_ID = ml.SubChain_ID -- SubChain LEFT JOIN SUB_CHANNEL subch ON subch.SubChannel_ID = ml.SubChannel_ID -- SubChannel --LEFT OUTER JOIN MAIN_QUESTION mq on mq.Quest_ID = b.Quest_ID -- SKU LEFT JOIN dbo.MAIN_QUESTION_FILTER1 mqf1 ON mq.Filter_1 = mqf1.Code -- Manufacture LEFT JOIN dbo.MAIN_QUESTION_FILTER2 mqf2 ON mq.Filter_2 = mqf2.Code -- Brand LEFT JOIN dbo.MAIN_QUESTION_FILTER3 mqf3 ON mq.Filter_3 = mqf3.Code -- Category LEFT JOIN dbo.MAIN_QUESTION_FILTER4 mqf4 ON mq.Filter_4 = mqf4.Code -- Type LEFT JOIN dbo.MAIN_QUESTION_FILTER5 mqf5 ON mq.Filter_5 = mqf5.Code -- [Function] LEFT JOIN dbo.MAIN_QUESTION_FILTER6 mqf6 ON mq.Filter_6 = mqf6.Code -- Size LEFT JOIN dbo.MAIN_QUESTION_FILTER7 mqf7 ON mq.Filter_7 = mqf7.Code -- Shade LEFT JOIN dbo.MAIN_QUESTION_FILTER8 mqf8 ON mq.Filter_8 = mqf8.Code -- SubCatgory LEFT JOIN dbo.MAIN_QUESTION_FILTER9 mqf9 ON mq.Filter_9 = mqf9.Code -- SubBrand LEFT JOIN dbo.MAIN_QUESTION_FILTER10 mqf10 ON mq.Filter_10 = mqf10.Code -- MktGroup LEFT JOIN dbo.MAIN_QUESTION_FILTER11 mqf11 ON mq.Filter_11 = mqf11.Code --SubMktGroup --LEFT JOIN ROUTE_USER_ASSIGNMENT rua on rua.Route_ID = ml.ML_ID -- 'SalesRep' --LEFT JOIN USER_LOGIN ul ON ul.[User_ID] = rua.[User_ID] Where CONVERT(DATETIME, a.Billing_date) = @StartDate--CONVERT(Datetime, '2023-01-01') --@StartDate --AND CONVERT(DATETIME, a.Billing_date) <= @EndDate--CONVERT(Datetime, '2023-04-30')--@EndDate --AND b.Trans_Type = 'SOR' --AND a.OrderNo_M1 IS NOT NULL AND (a.Sold_to_party = @CustCode OR @CustCode = '') AND (a.Ship_to_party = @ShipToCode OR @ShipToCode = '') AND ( Z.Zone_ID = @ZoneID OR @ZoneID = -1) AND ( RE.ID = @RegionID OR @RegionID = -1) AND ( ST.State_ID = @StateID OR @StateID = -1) AND ( AR.ID = @AreaID OR @AreaID = -1) AND ( CC.Chain_ID = @ChainID OR @ChainID = -1) AND ( CH.Channel_ID = @ChannelID OR @ChannelID = -1) AND ( subchain.SubChain_ID = @SubChainID OR @SubChainID = -1) AND ( subch.SubChannel_ID = @SubChannelID OR @SubChannelID = -1) AND ( G.Grade_ID = @GradeID OR @GradeID = -1) AND ( a.USER_ID = @SalesRep OR @SalesRep = -1) AND ( mq.Quest_Code = @SKU OR @SKU = '') AND ( mqf1.ID = @AgencyID OR @AgencyID = -1) AND ( mqf2.ID = @BrandID OR @BrandID = -1) AND ( mqf3.ID = @CategoryID OR @CategoryID = -1) AND ( mqf9.ID = @SubBrandID OR @SubBrandID = -1) AND ( mqf8.ID = @SubCategoryID OR @SubCategoryID = -1) AND ( mqf4.ID = @PackTypeID OR @PackTypeID = -1) AND ( mqf5.ID = @FunctionID OR @FunctionID = -1) AND ( mqf6.ID = @PackSizeID OR @PackSizeID = -1) AND ( mqf7.ID = @ShadeID OR @ShadeID = -1) AND ( mqf10.ID = @MarketingGrpID OR @MarketingGrpID = -1) AND ( mqf11.ID = @SubMarketingGrpID OR @SubMarketingGrpID = -1) --AND (a.Product_hierarchy = @ParentSKUCode OR @ParentSKUCode = '') ) TblAA Where TblAA.Billing_Type In ('ZCC2','ZCN2','ZCN4','ZCN5','ZCN6') group by Case When @GrpByLevel1 = 'Customer' Then [BILL_TO_NAME] When @GrpByLevel1 = 'Ship To' Then [Ship To] When @GrpByLevel1 = 'Bill-To' Then [Zone] When @GrpByLevel1 = 'Region' Then [Region] When @GrpByLevel1 = 'State' Then [State] When @GrpByLevel1 = 'Area' Then [Area] When @GrpByLevel1 = 'Chain' Then [Chain] When @GrpByLevel1 = 'Channel' Then [Channel] When @GrpByLevel1 = 'Sub Chain' Then [Sub Chain] When @GrpByLevel1 = 'Sub Channel' Then [Sub Channel] When @GrpByLevel1 = 'Grade' Then [Grade] --When @GrpByLevel1 = 'Sales Route' Then ST.State_ID When @GrpByLevel1 = 'Sales Rep' Then [Sales Rep] When @GrpByLevel1 = 'SKU' Then [SKU] When @GrpByLevel1 = 'Agency' Then [Agency] When @GrpByLevel1 = 'Brand' Then [Brand] When @GrpByLevel1 = 'Category' Then [Category] When @GrpByLevel1 = 'Sub Brand' Then [Sub Brand] When @GrpByLevel1 = 'Sub Category' Then [Sub Category] When @GrpByLevel1 = 'Type' Then [Type] When @GrpByLevel1 = 'Function' Then [Function] When @GrpByLevel1 = 'Size' Then [Size] When @GrpByLevel1 = 'Shade' Then [Shade] When @GrpByLevel1 = 'Marketing Group' Then [Marketing Group] When @GrpByLevel1 = 'Sub Marketing Group' Then [Sub Marketing Group] --When @GrpByLevel1 = 'Parent SKU Code' Then ST.State_ID End , Case When @GrpByLevel2 = 'Customer' Then [BILL_TO_NAME] When @GrpByLevel2 = 'Ship To' Then [Ship To] When @GrpByLevel2 = 'Bill-To' Then [Zone] When @GrpByLevel2 = 'Region' Then [Region] When @GrpByLevel2 = 'State' Then [State] When @GrpByLevel2 = 'Area' Then [Area] When @GrpByLevel2 = 'Chain' Then [Chain] When @GrpByLevel2 = 'Channel' Then [Channel] When @GrpByLevel2 = 'Sub Chain' Then [Sub Chain] When @GrpByLevel2 = 'Sub Channel' Then [Sub Channel] When @GrpByLevel2 = 'Grade' Then [Grade] --When @GrpByLevel1 = 'Sales Route' Then ST.State_ID When @GrpByLevel2 = 'Sales Rep' Then [Sales Rep] When @GrpByLevel2 = 'SKU' Then [SKU] When @GrpByLevel2 = 'Agency' Then [Agency] When @GrpByLevel2 = 'Brand' Then [Brand] When @GrpByLevel2 = 'Category' Then [Category] When @GrpByLevel2 = 'Sub Brand' Then [Sub Brand] When @GrpByLevel2 = 'Sub Category' Then [Sub Category] When @GrpByLevel2 = 'Type' Then [Type] When @GrpByLevel2 = 'Function' Then [Function] When @GrpByLevel2 = 'Size' Then [Size] When @GrpByLevel2 = 'Shade' Then [Shade] When @GrpByLevel2 = 'Marketing Group' Then [Marketing Group] When @GrpByLevel2 = 'Sub Marketing Group' Then [Sub Marketing Group] --When @GrpByLevel1 = 'Parent SKU Code' Then ST.State_ID End , Case When @GrpByLevel3 = 'Customer' Then [BILL_TO_NAME] When @GrpByLevel3 = 'Ship To' Then [Ship To] When @GrpByLevel3 = 'Bill-To' Then [Zone] When @GrpByLevel3 = 'Region' Then [Region] When @GrpByLevel3 = 'State' Then [State] When @GrpByLevel3 = 'Area' Then [Area] When @GrpByLevel3 = 'Chain' Then [Chain] When @GrpByLevel3 = 'Channel' Then [Channel] When @GrpByLevel3 = 'Sub Chain' Then [Sub Chain] When @GrpByLevel3 = 'Sub Channel' Then [Sub Channel] When @GrpByLevel3 = 'Grade' Then [Grade] --When @GrpByLevel1 = 'Sales Route' Then ST.State_ID When @GrpByLevel3 = 'Sales Rep' Then [Sales Rep] When @GrpByLevel3 = 'SKU' Then [SKU] When @GrpByLevel3 = 'Agency' Then [Agency] When @GrpByLevel3 = 'Brand' Then [Brand] When @GrpByLevel3 = 'Category' Then [Category] When @GrpByLevel3 = 'Sub Brand' Then [Sub Brand] When @GrpByLevel3 = 'Sub Category' Then [Sub Category] When @GrpByLevel3 = 'Type' Then [Type] When @GrpByLevel3 = 'Function' Then [Function] When @GrpByLevel3 = 'Size' Then [Size] When @GrpByLevel3 = 'Shade' Then [Shade] When @GrpByLevel3 = 'Marketing Group' Then [Marketing Group] When @GrpByLevel3 = 'Sub Marketing Group' Then [Sub Marketing Group] End ,TblAA.ITEM_CODE, TblAA.ITEM_DESCRIPTION,[UOM] ) TableA )TableB End