Aggregate functions and Group by on Varchar values

I have grouped by the following 3 fields in the RAMS section below in 4 seperate SELECT statements below. I did this because I could not get past an error where it resulted in an Aggregate & Group By error in the 4 selects below in regards to the following fields.
            CUST_ID.IDVALUE,
            CUST_ID.ID_TITLE,
            PD.PRD_R_BRANDNAME

It is my understanding that I would typically need to create a Agregate fuction such as a SUM if the values were numeric. If that is true and I need to do that. What do you do in the case of a VARCHAR,....? I have data being returned but now worried that I do not have the right syntaX in the code. Can anyone forsee any problems with doing it the way I did.
Please advise. Thank you.
USE [DATA_04]
GO
/****** Object:  StoredProcedure [dbo].[Rpt_SalesSummaryAdopted_sproc_RB]    Script Date: 10/11/2013 12:15:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Rpt_SalesSummaryAdopted_sproc_RB] 
 @Cus_no_Start varchar(12),
 @Cus_no_End varchar(12), 
 @DB_Include int -- 0 = Macola Only, 1 = RAMS Only, 2 = Macola and RAMS 
 AS

-- for testing

-- drop table #SalesSummary
-- declare @Cus_no_Start varchar(12), @Cus_no_End varchar(12)
-- set @Cus_no_Start = '0'
-- --set @Cus_no_Start = '1505'
-- set @Cus_no_End = '1999'

-- end testing

----------------------------------------------------
-- front zero fill  customer numbers
----------------------------------------------------
If @Cus_no_Start <> '*'
BEGIN
  set @Cus_no_Start = right('000000000000' + @Cus_no_Start,12)
  set @Cus_no_End   = right('000000000000' + @Cus_no_End,12)
END
-- print @Cus_no_Start
-- print @Cus_no_End

----------------------------------------------------
-- get static variables from date tables
----------------------------------------------------
declare @CurrFromDate int, @CurrToDate int, @CurrToDateYTD int, @CurrFromDateYTD int,
@PriorFromDateYTD int, @PriorToDateYTD int, @CurrWeekBeginDate int, @CurrWeekEndDate int,
@PriorWeekBeginDate int, @PriorWeekEndDate int, @PriorFromDate int, @PriorToDate int
select
   @CurrWeekEndDate 	= CurrWeekEndDate, 
   @CurrToDateYTD 	= CurrToDateYTD,
   @CurrFromDateYTD	= CurrFromDateYTD,
   @CurrFromDate 	= CurrFromDate,
   @CurrToDate 		= CurrToDate, 
   @CurrToDateYTD 	= CurrToDateYTD,
   @CurrWeekBeginDate 	= CurrWeekBeginDate,
   @CurrWeekEndDate 	= CurrWeekEndDate,
   @PriorWeekBeginDate 	= PriorWeekBeginDate,
   @PriorWeekEndDate 	= PriorWeekEndDate,
   @PriorFromDateYTD 	= PriorFromDateYTD,
   @PriorToDateYTD 	= PriorToDateYTD,
   @PriorFromDate 	= PriorFromDate,
   @PriorToDate 	= PriorToDate
from dbo.Rpt_ReportingDates
declare @CurrentWeek decimal(8,0), @NumberOfWeeks int, @CurrToDate_Period tinyint, @CurrToDateYTD_FiscalYear smallint
select 
   @CurrentWeek 	= CurrentWeek,
   @NumberOfWeeks 	= NumberOfWeeks,
   @CurrToDate_Period 	= CurrToDate_Period,
   @CurrToDateYTD_FiscalYear = CurrToDateYTD_FiscalYear
from dbo.vwFiscalDates

------------------------------------------------------------
-- create temp table for Sales Summary
------------------------------------------------------------

Create table #SalesSummary 
(
	[data_source] varchar(12),--  what database is the data coming from  macola or rams?
	PerType varchar(18) NULL,
	CM_SELLZONE Varchar (12) Null,
	[IDVALUE] Varchar (6) Null,
	[ID_TITLE]Varchar (40) Null,
	[CurrWeekEndDate] [int] NULL ,
	[CurrToDate] [int] NULL ,
	[CurrToDateYTD] [int] NULL ,
	[CurrentWeek] [decimal](8, 0) NULL ,
	[NumberOfWeeks] [int] NULL ,
	[CurrToDate_Period] [tinyint] NULL ,
	[CurrToDateYTD_FiscalYear] [smallint] NULL ,
	[BRAND] Varchar (100) Null,
	[BRAND_ID] Varchar (6) Null,
	Item_no varchar(15),
	Gross_Sales decimal(38,2) NULL,
	Cost decimal(38,2) NULL

	
	
)


----------------------------------------------------
-- insert for MTD Current and Prior MACOLA
----------------------------------------------------
if (@DB_Include = 0 or @DB_Include = 2) 
BEGIN
INSERT INTO #SalesSummary 

SELECT DISTINCT

	'Macola',
	'2 - MTD Current',
	ARCUSFIL_SQL.Terr,
	'0', -- IDVALUE
	'0', -- ID_TITLE
	@CurrWeekEndDate as CurrWeekEndDate, 
    @CurrToDate as CurrToDate, 
    @CurrToDateYTD as CurrToDateYTD, 
    @CurrentWeek AS CurrentWeek,
    @NumberofWeeks AS NumberofWeeks,
    @CurrToDate_Period AS CurrToDate_Period,
    @CurrToDateYTD_FiscalYear AS CurrToDateYTD_FiscalYear,
    '0', -- BRAND
    '0', -- BRAND_ID
	OELINHST_SQL.Item_no, 
	Gross_Sales = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @CurrFromDate AND @CurrToDate 
         THEN sls_amt ELSE 0 END),
	Cost = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @CurrFromDate AND @CurrToDate 
         THEN cost_amt ELSE 0 END)
    
       
FROM OELINHST_SQL
	INNER JOIN OEHDRHST_SQL 
	ON OEHDRHST_SQL.Ord_type = OELINHST_SQL.Ord_type 
	AND OEHDRHST_SQL.Ord_no = OELINHST_SQL.Ord_no 
	AND OEHDRHST_SQL.Inv_no = OELINHST_SQL.Inv_no
	INNER JOIN ARCUSFIL_SQL 
	ON ARCUSFIL_SQL.Cus_no = OELINHST_SQL.Cus_no
	LEFT OUTER JOIN DATA_04.dbo.IMITMIDX_SQL IMITMIDX_SQL 
	ON OELINHST_SQL.item_no = IMITMIDX_SQL.item_no
	
	
WHERE  	(@Cus_no_Start = '*' or (ARCUSFIL_SQL.Cus_no >= @Cus_no_Start 
		and ARCUSFIL_SQL.Cus_no <= @Cus_no_End))
GROUP BY ARCUSFIL_SQL.Terr, OELINHST_SQL.Item_no

------------------------------------------------------
---- insert for MTD Current and Prior MACOLA
------------------------------------------------------
INSERT INTO #SalesSummary 

SELECT DISTINCT

	'Macola',
	'2 - MTD Prior',
	ARCUSFIL_SQL.Terr,
	'0', -- IDVALUE
	'0', -- ID_TITLE
	@CurrWeekEndDate as CurrWeekEndDate, 
    @CurrToDate as CurrToDate, 
    @CurrToDateYTD as CurrToDateYTD, 
    @CurrentWeek AS CurrentWeek,
    @NumberofWeeks AS NumberofWeeks,
    @CurrToDate_Period AS CurrToDate_Period,
    @CurrToDateYTD_FiscalYear AS CurrToDateYTD_FiscalYear,
    '0', -- BRAND
    '0', -- BRAND_ID
	OELINHST_SQL.Item_no, 
	Gross_Sales = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @PriorFromDate AND @PriorToDate 
         THEN sls_amt ELSE 0 END),
	Cost = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @PriorFromDate AND @PriorToDate 
         THEN cost_amt ELSE 0 END)
    
    
FROM OELINHST_SQL
	INNER JOIN OEHDRHST_SQL 
	ON OEHDRHST_SQL.Ord_type = OELINHST_SQL.Ord_type 
	AND OEHDRHST_SQL.Ord_no = OELINHST_SQL.Ord_no 
	AND OEHDRHST_SQL.Inv_no = OELINHST_SQL.Inv_no
	INNER JOIN ARCUSFIL_SQL 
	ON ARCUSFIL_SQL.Cus_no = OELINHST_SQL.Cus_no
	LEFT OUTER JOIN DATA_04.dbo.IMITMIDX_SQL IMITMIDX_SQL 
	ON OELINHST_SQL.item_no = IMITMIDX_SQL.item_no
WHERE  
	(@Cus_no_Start = '*' or (ARCUSFIL_SQL.Cus_no >= @Cus_no_Start 
		and ARCUSFIL_SQL.Cus_no <= @Cus_no_End))
GROUP BY ARCUSFIL_SQL.Terr, OELINHST_SQL.Item_no
------------------------------------------------------
---- insert for YTD Current MACOLA
------------------------------------------------------
INSERT INTO #SalesSummary 

SELECT DISTINCT

	'Macola',
	'3 - YTD Current',
	ARCUSFIL_SQL.Terr,
	'0', -- IDVALUE
	'0', -- ID_TITLE
	@CurrWeekEndDate as CurrWeekEndDate, 
    @CurrToDate as CurrToDate, 
    @CurrToDateYTD as CurrToDateYTD, 
    @CurrentWeek AS CurrentWeek,
    @NumberofWeeks AS NumberofWeeks,
    @CurrToDate_Period AS CurrToDate_Period,
    @CurrToDateYTD_FiscalYear AS CurrToDateYTD_FiscalYear,
    '0', -- BRAND
    '0', -- BRAND_ID
	OELINHST_SQL.Item_no, 
	Gross_Sales = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @CurrFromDateYTD AND @CurrToDateYTD 
         THEN sls_amt ELSE 0 END),
	Cost = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @CurrFromDateYTD AND @CurrToDateYTD 
         THEN cost_amt ELSE 0 END)
    
    
FROM OELINHST_SQL
	INNER JOIN OEHDRHST_SQL 
	ON OEHDRHST_SQL.Ord_type = OELINHST_SQL.Ord_type 
	AND OEHDRHST_SQL.Ord_no = OELINHST_SQL.Ord_no 
	AND OEHDRHST_SQL.Inv_no = OELINHST_SQL.Inv_no
	INNER JOIN ARCUSFIL_SQL 
	ON ARCUSFIL_SQL.Cus_no = OELINHST_SQL.Cus_no
	LEFT OUTER JOIN DATA_04.dbo.IMITMIDX_SQL IMITMIDX_SQL 
        ON OELINHST_SQL.item_no = IMITMIDX_SQL.item_no  -- added RB
	WHERE  
	(@Cus_no_Start = '*' or (ARCUSFIL_SQL.Cus_no >= @Cus_no_Start 
		and ARCUSFIL_SQL.Cus_no <= @Cus_no_End))
GROUP BY ARCUSFIL_SQL.Terr, OELINHST_SQL.Item_no

------------------------------------------------------
---- insert for YTD Prior MACOLA
------------------------------------------------------

INSERT INTO #SalesSummary 

SELECT DISTINCT

	'Macola',
	'3 - YTD Prior',
	ARCUSFIL_SQL.Terr,
	'0', -- IDVALUE
	'0', -- ID_TITLE
	@CurrWeekEndDate as CurrWeekEndDate, 
    @CurrToDate as CurrToDate, 
    @CurrToDateYTD as CurrToDateYTD, 
    @CurrentWeek AS CurrentWeek,
    @NumberofWeeks AS NumberofWeeks,
    @CurrToDate_Period AS CurrToDate_Period,
    @CurrToDateYTD_FiscalYear AS CurrToDateYTD_FiscalYear,
    '0', -- BRAND
    '0', -- BRAND_ID
	OELINHST_SQL.Item_no, 
	Gross_Sales = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @PriorFromDateYTD AND @PriorToDateYTD 
         THEN sls_amt ELSE 0 END),
	Cost = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @PriorFromDateYTD AND @PriorToDateYTD 
         THEN cost_amt ELSE 0 END)
   
    
FROM OELINHST_SQL
	INNER JOIN OEHDRHST_SQL 
	ON OEHDRHST_SQL.Ord_type = OELINHST_SQL.Ord_type 
	AND OEHDRHST_SQL.Ord_no = OELINHST_SQL.Ord_no 
	AND OEHDRHST_SQL.Inv_no = OELINHST_SQL.Inv_no
	INNER JOIN ARCUSFIL_SQL 
	ON ARCUSFIL_SQL.Cus_no = OELINHST_SQL.Cus_no
	LEFT OUTER JOIN DATA_04.dbo.IMITMIDX_SQL IMITMIDX_SQL 
	ON OELINHST_SQL.item_no = IMITMIDX_SQL.item_no  -- added RB
WHERE  
	(@Cus_no_Start = '*' or (ARCUSFIL_SQL.Cus_no >= @Cus_no_Start and ARCUSFIL_SQL.Cus_no <= @Cus_no_End))
GROUP BY ARCUSFIL_SQL.Terr, OELINHST_SQL.Item_no
END

--------------------------------------------------------------------------------------------------
-- Here is where the Weekly, Monthly, Yearly RAMS data gets added to the #SalesSummary work table
--------------------------------------------------------------------------------------------------

----------------------------------------------------
-- insert for MTD Current RAMS
----------------------------------------------------

if (@DB_Include = 1 or @DB_Include = 2) 
BEGIN

INSERT INTO #SalesSummary 

SELECT DISTINCT

		'RAMS',
		'2 - MTD Current',
		CM.CM_SELLZONE,
		CUST_ID.IDVALUE,
		CUST_ID.ID_TITLE,
		@CurrWeekEndDate as CurrWeekEndDate, 
		@CurrToDate as CurrToDate, 
		@CurrToDateYTD as CurrToDateYTD, 
		@CurrentWeek AS CurrentWeek,
		@NumberofWeeks AS NumberofWeeks,
		@CurrToDate_Period AS CurrToDate_Period,
		@CurrToDateYTD_FiscalYear AS CurrToDateYTD_FiscalYear,
		PR_ID.ID_DESC AS BRAND,
		PD.PRD_R_BRANDNAME AS BRAND_ID,
		OD.OD_PRODUCT as Item_no,
		Gross_Sales = SUM(CASE WHEN (OH.OH_SALE_TYPE = 'O' AND OH.OH_CUSTOMER_TYPE_ID IN ('000001','000003') 
									AND CM.CM_CUST_SUB_TYPE <> 3 -- SBT = 3 (Pay By Scan)
									AND OD.OD_RETURN_REASON_CODE IN ('000000','000001') -- Sale or BuyBack
									AND CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @CurrFromDate AND @CurrToDate)
							   THEN COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_FINAL_PRICE , 0)
							   ELSE 0 END),
		Cost = SUM(CASE WHEN (OH.OH_SALE_TYPE = 'O' AND OH.OH_CUSTOMER_TYPE_ID IN ('000001','000003') 
									AND CM.CM_CUST_SUB_TYPE <> 3 AND OD.OD_RETURN_REASON_CODE IN ('000000','000001') 
									AND CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @CurrFromDate AND @CurrToDate)									
							   THEN	COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_PRODUCT_COST_PRICE,0)
							   ELSE 0 END)
		
						   
FROM    CHLROS05.RAMSDB.dbo.ROSS_SALESHDR AS OH
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_CUSTOMER AS CM
                  ON OH.OH_CUSTOMER_NUMBER = CM.CM_CUSTKEY
                  AND CM_ACCT_GLPOSTS = '000004'
                  AND left(CM.CM_SELLZONE,1) = '4'
                  AND CM.CM_CUST_SUB_TYPE <> 3
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as CUST_ID  
                        ON CM.CM_SELLZONE = CUST_ID.IDVALUE
                        AND CUST_ID.ID_TITLE = N'Sale Zone'
                        INNER JOIN  CHLROS05.RAMSDB.dbo.ROSS_SALESDTL AS OD         
                        ON OD.OD_TICKET_NUMBER = OH_TICKET_NUMBER                                           
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_PRODUCTS AS PD
                        ON PD.PRD_PRODUCT = OD.OD_PRODUCT   
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as PR_ID  
                        ON PD.PRD_R_BRANDNAME = PR_ID.IDVALUE
                        AND PR_ID.ID_TITLE = N'Product Brand Name'

--WHERE     OH.OH_SALE_TYPE = 'O'
--          AND (ID.ID_TITLE = N'Sale Zone')

GROUP BY    PR_ID.ID_DESC,
            OD.OD_PRODUCT,
            CM.CM_SELLZONE,
            CUST_ID.IDVALUE,
            CUST_ID.ID_TITLE,
            PD.PRD_R_BRANDNAME
            
					
----------------------------------------------------
-- insert for MTD Prior RAMS
----------------------------------------------------	

INSERT INTO #SalesSummary 

SELECT DISTINCT

		'RAMS',
		'2 - MTD Prior',
		CM.CM_SELLZONE,
		CUST_ID.IDVALUE,
		CUST_ID.ID_TITLE,
		@CurrWeekEndDate as CurrWeekEndDate, 
		@CurrToDate as CurrToDate, 
		@CurrToDateYTD as CurrToDateYTD, 
		@CurrentWeek AS CurrentWeek,
		@NumberofWeeks AS NumberofWeeks,
		@CurrToDate_Period AS CurrToDate_Period,
		@CurrToDateYTD_FiscalYear AS CurrToDateYTD_FiscalYear,
		PR_ID.ID_DESC AS BRAND,
		PD.PRD_R_BRANDNAME AS BRAND_ID,
		OD.OD_PRODUCT as Item_no,
		Gross_Sales = SUM(CASE WHEN (OH.OH_SALE_TYPE = 'O' AND OH.OH_CUSTOMER_TYPE_ID IN ('000001','000003') 
									AND CM.CM_CUST_SUB_TYPE <> 3 -- SBT = 3 (Pay By Scan)
									AND OD.OD_RETURN_REASON_CODE IN ('000000','000001') -- Sale or BuyBack
									AND CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @PriorFromDate AND @PriorToDate)
							   THEN COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_FINAL_PRICE , 0)
							   ELSE 0 END),
		Cost = SUM(CASE WHEN (OH.OH_SALE_TYPE = 'O' AND OH.OH_CUSTOMER_TYPE_ID IN ('000001','000003') 
									AND CM.CM_CUST_SUB_TYPE <> 3 AND OD.OD_RETURN_REASON_CODE IN ('000000','000001') 
									AND CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @PriorFromDate AND @PriorToDate)									
							   THEN	COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_PRODUCT_COST_PRICE,0)
							   ELSE 0 END)
		
						   
FROM    CHLROS05.RAMSDB.dbo.ROSS_SALESHDR AS OH
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_CUSTOMER AS CM
                  ON OH.OH_CUSTOMER_NUMBER = CM.CM_CUSTKEY
                  AND CM_ACCT_GLPOSTS = '000004'
                  AND left(CM.CM_SELLZONE,1) = '4'
                  AND CM.CM_CUST_SUB_TYPE <> 3
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as CUST_ID  
                        ON CM.CM_SELLZONE = CUST_ID.IDVALUE
                        AND CUST_ID.ID_TITLE = N'Sale Zone'
            INNER JOIN  CHLROS05.RAMSDB.dbo.ROSS_SALESDTL AS OD         
                        ON OD.OD_TICKET_NUMBER = OH_TICKET_NUMBER                                           
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_PRODUCTS AS PD
                        ON PD.PRD_PRODUCT = OD.OD_PRODUCT   
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as PR_ID  
                        ON PD.PRD_R_BRANDNAME = PR_ID.IDVALUE
                        AND PR_ID.ID_TITLE = N'Product Brand Name'

--WHERE     OH.OH_SALE_TYPE = 'O'
--          AND (ID.ID_TITLE = N'Sale Zone')

GROUP BY    PR_ID.ID_DESC,
            OD.OD_PRODUCT,
            CM.CM_SELLZONE,
            CUST_ID.IDVALUE,
            CUST_ID.ID_TITLE,
            PD.PRD_R_BRANDNAME	
					
----------------------------------------------------
-- insert for YTD Current RAMS
----------------------------------------------------

INSERT INTO #SalesSummary 
SELECT DISTINCT

		'RAMS',
		'3 - YTD Current',
		CM.CM_SELLZONE,
		CUST_ID.IDVALUE AS IDVALUE,
		CUST_ID.ID_TITLE AS ID_TITLE,
		@CurrWeekEndDate as CurrWeekEndDate, 
		@CurrToDate as CurrToDate, 
		@CurrToDateYTD as CurrToDateYTD, 
		@CurrentWeek AS CurrentWeek,
		@NumberofWeeks AS NumberofWeeks,
		@CurrToDate_Period AS CurrToDate_Period,
		@CurrToDateYTD_FiscalYear AS CurrToDateYTD_FiscalYear,
		PR_ID.ID_DESC AS BRAND,
		PD.PRD_R_BRANDNAME AS BRAND_ID,
		OD.OD_PRODUCT as Item_no,
		Gross_Sales = SUM(CASE WHEN (OH.OH_SALE_TYPE = 'O' AND OH.OH_CUSTOMER_TYPE_ID IN ('000001','000003') 
									AND CM.CM_CUST_SUB_TYPE <> 3 -- SBT = 3 (Pay By Scan)
									AND OD.OD_RETURN_REASON_CODE IN ('000000','000001') -- Sale or BuyBack
									AND CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @CurrFromDateYTD AND @CurrToDateYTD)
							   THEN COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_FINAL_PRICE , 0)
							   ELSE 0 END),
		Cost = SUM(CASE WHEN (OH.OH_SALE_TYPE = 'O' AND OH.OH_CUSTOMER_TYPE_ID IN ('000001','000003') 
									AND CM.CM_CUST_SUB_TYPE <> 3 AND OD.OD_RETURN_REASON_CODE IN ('000000','000001') 
									AND CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @CurrFromDateYTD AND @CurrToDateYTD)
							   THEN	COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_PRODUCT_COST_PRICE,0)
							   ELSE 0 END)
		
FROM    CHLROS05.RAMSDB.dbo.ROSS_SALESHDR AS OH
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_CUSTOMER AS CM
                  ON OH.OH_CUSTOMER_NUMBER = CM.CM_CUSTKEY
                  AND CM_ACCT_GLPOSTS = '000004'
                  AND left(CM.CM_SELLZONE,1) = '4'
                  AND CM.CM_CUST_SUB_TYPE <> 3
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as CUST_ID  
                        ON CM.CM_SELLZONE = CUST_ID.IDVALUE
                        AND CUST_ID.ID_TITLE = N'Sale Zone'
            INNER JOIN  CHLROS05.RAMSDB.dbo.ROSS_SALESDTL AS OD         
                        ON OD.OD_TICKET_NUMBER = OH_TICKET_NUMBER                                           
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_PRODUCTS AS PD
                        ON PD.PRD_PRODUCT = OD.OD_PRODUCT   
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as PR_ID  
                        ON PD.PRD_R_BRANDNAME = PR_ID.IDVALUE
                        AND PR_ID.ID_TITLE = N'Product Brand Name'

--WHERE     OH.OH_SALE_TYPE = 'O'
--          AND (ID.ID_TITLE = N'Sale Zone')

GROUP BY    PR_ID.ID_DESC,
            OD.OD_PRODUCT,
            CM.CM_SELLZONE,
            CUST_ID.IDVALUE,
            CUST_ID.ID_TITLE,
            PD.PRD_R_BRANDNAME




----------------------------------------------------
-- insert for YTD Prior RAMS
----------------------------------------------------

INSERT INTO #SalesSummary 

SELECT DISTINCT

		'RAMS',
		'3 - YTD Prior',
		CM.CM_SELLZONE,
		CUST_ID.IDVALUE,
		CUST_ID.ID_TITLE,
		@CurrWeekEndDate as CurrWeekEndDate, 
		@CurrToDate as CurrToDate, 
		@CurrToDateYTD as CurrToDateYTD, 
		@CurrentWeek AS CurrentWeek,
		@NumberofWeeks AS NumberofWeeks,
		@CurrToDate_Period AS CurrToDate_Period,
		@CurrToDateYTD_FiscalYear AS CurrToDateYTD_FiscalYear,
		PR_ID.ID_DESC AS BRAND,
		PD.PRD_R_BRANDNAME AS BRAND_ID,
		OD.OD_PRODUCT as Item_no,
		Gross_Sales = SUM(CASE WHEN (OH.OH_SALE_TYPE = 'O' AND OH.OH_CUSTOMER_TYPE_ID IN ('000001','000003') 
									AND CM.CM_CUST_SUB_TYPE <> 3 -- SBT = 3 (Pay By Scan)
									AND OD.OD_RETURN_REASON_CODE IN ('000000','000001') -- Sale or BuyBack
									AND CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @PriorFromDateYTD AND @PriorToDateYTD)
							   THEN COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_FINAL_PRICE , 0)
							   ELSE 0 END),
		Cost = SUM(CASE WHEN (OH.OH_SALE_TYPE = 'O' AND OH.OH_CUSTOMER_TYPE_ID IN ('000001','000003') 
									AND CM.CM_CUST_SUB_TYPE <> 3 AND OD.OD_RETURN_REASON_CODE IN ('000000','000001') 
									AND CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @PriorFromDateYTD AND @PriorToDateYTD)
							   THEN	COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_PRODUCT_COST_PRICE,0)
							   ELSE 0 END)
		
FROM    CHLROS05.RAMSDB.dbo.ROSS_SALESHDR AS OH
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_CUSTOMER AS CM
                  ON OH.OH_CUSTOMER_NUMBER = CM.CM_CUSTKEY
                  AND CM_ACCT_GLPOSTS = '000004'
                  AND left(CM.CM_SELLZONE,1) = '4'
                  AND CM.CM_CUST_SUB_TYPE <> 3
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as CUST_ID  
                        ON CM.CM_SELLZONE = CUST_ID.IDVALUE
                        AND CUST_ID.ID_TITLE = N'Sale Zone'
            INNER JOIN  CHLROS05.RAMSDB.dbo.ROSS_SALESDTL AS OD         
                        ON OD.OD_TICKET_NUMBER = OH_TICKET_NUMBER                                           
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_PRODUCTS AS PD
                        ON PD.PRD_PRODUCT = OD.OD_PRODUCT   
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as PR_ID  
                        ON PD.PRD_R_BRANDNAME = PR_ID.IDVALUE
                        AND PR_ID.ID_TITLE = N'Product Brand Name'

--WHERE     OH.OH_SALE_TYPE = 'O'
--          AND (ID.ID_TITLE = N'Sale Zone')

GROUP BY    PR_ID.ID_DESC,
            OD.OD_PRODUCT,
            CM.CM_SELLZONE,
            CUST_ID.IDVALUE,
            CUST_ID.ID_TITLE,
            PD.PRD_R_BRANDNAME

			
END -- DB_Include = 1 or 2
			
----------------------------------------------------
-- final select
----------------------------------------------------


   	select * from #SalesSummary

Open in new window

RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>What do you do in the case of a VARCHAR,....?
Assuming that all values in the varchar column are numeric, you first have to weed out any non-numeric values using the ISNUMERIC function, convert the column to a numeric data type, then you can do you aggregating.
SELECT goo, foo, SUM(CAST(boo as numeric)) as boo_sum
FROM yourtable
WHERE ISNUMERIC(boo) = 1
GROUP BY goo, foo

-- Also, you'll want to handle any values that are not numeric, and can't be added

SELECT boo
FROM yourtable
WHERE ISNUMERIC(boo) = 0

Open in new window

This begs the obvious question, 'Why do you have all-numeric values in a column that is varchar?'
0
virtuadeptCommented:
If you have a varchar that you want to include but not be part of the GROUP BY clause the easiest way to do this is to use a MIN(varchar_column) or MAX(varchar_column) on it, as long as they are all going to have the same value or you only care about one value. If you care about multiple values for that column then you need to just add those columns to the group by clause, because they are PART of the grouping.

Another way to go is do your GROUP BY with the minimum columns needed and the aggregate functions needed in a sub-select, then join that to an outer select that has the other information you want to show.

Example:

SELECT Employee_Name, Employee_Title, Month, Number_of_Sales
FROM (
   SELECT Employee_ID, Datename(Month,Sales_Date) as Month, COUNT(*) as Number_of_Sales
   FROM SALES_TABLE   
   GROUP BY Employee_ID, Datename(Month,Sales_Date)
) as S
INNER JOIN Employee_Table as E
ON S.Employee_ID = E.Employee_ID

Open in new window

0
PortletPaulfreelancerCommented:
>>Can anyone forsee any problems with doing it the way I did.
Yes, performance.

You are repeating several queries when you could be performing fewer queries
= more time/effort.

But I don't think you need to apply aggregates to these fields anyway, they just need to be part of the GROUP BY (which you are doing anyway).

also, you are using "SELECT DISTINCT" with each one of those multiple queries
:sigh:
even more performance issues because of this.

DISTINCT is *usually* bad

A good rule of thumb -- if you need a distinct list of values in a single column or two, DISTINCT is the way to go.  But if your results "don't look right" or you see some duplicate rows and can't figure out why, do NOT just add DISTINCT to your SELECT to "fix it" !!  Step back, look at your joins, and re-write your query properly.  Even worse, I've seen people simply add DISTINCT to all their SELECT's right from the start, to preemptively "avoid duplicates".  Not good.  (Side note: If you are a DISTINCT abuser, try adding meaningful primary keys to your tables).
Why I Hate DISTINCT
The effects of DISTINCT in a SQL query

---------------------------
By the way: You can apply aggregate functions MIN() and MAX() to varchar, it 'just depends' on what it is you are trying to achieve. e.g.
CREATE TABLE AnyTable
    ([Code] varchar(1), [Value] int)
;
    
INSERT INTO AnyTable
    ([Code], [Value])
VALUES
    ('a', 1),
    ('b', 2),
    ('c', 3),
    ('x', 24),
    ('y', 25),
    ('x', 26)
;

**Query **:

SELECT
        min(code)  AS min_code
      , max(code)  AS max_code
      , sum(value) AS sum_value
FROM anytable

**[Results]**:

| MIN_CODE | MAX_CODE | SUM_VALUE |
|----------|----------|-----------|
|        a |        y |        81 |



[1]: http://sqlfiddle.com/#!3/2fb81/2

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

PortletPaulfreelancerCommented:
without going into too much detail
- and without the information needed to remove the DISTINCT,
- and without changing the structure of your temp table,
something like this would require much less computation time:
WITH
CTE AS (
            SELECT DISTINCT /* I loathe this distinct, it should go, but I can't do it by myself */
                    'RAMS',
                    CM.CM_SELLZONE,
                    CUST_ID.IDVALUE,
                    CUST_ID.ID_TITLE,
                    @CurrWeekEndDate as CurrWeekEndDate, 
                    @CurrToDate as CurrToDate, 
                    @CurrToDateYTD as CurrToDateYTD, 
                    @CurrentWeek AS CurrentWeek,
                    @NumberofWeeks AS NumberofWeeks,
                    @CurrToDate_Period AS CurrToDate_Period,
                    @CurrToDateYTD_FiscalYear AS CurrToDateYTD_FiscalYear,
                    PR_ID.ID_DESC AS BRAND,
                    PD.PRD_R_BRANDNAME AS BRAND_ID,
                    OD.OD_PRODUCT as Item_no,
                    
                    --'2 - MTD Current',
                    CurrentGross_Sales = SUM(CASE WHEN (OH.OH_SALE_TYPE = 'O' AND OH.OH_CUSTOMER_TYPE_ID IN ('000001','000003') 
                                                AND CM.CM_CUST_SUB_TYPE <> 3 -- SBT = 3 (Pay By Scan)
                                                AND OD.OD_RETURN_REASON_CODE IN ('000000','000001') -- Sale or BuyBack
                                                AND CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @CurrFromDate AND @CurrToDate)
                                           THEN COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_FINAL_PRICE , 0)
                                           ELSE 0 END),
                    CurrentCost = SUM(CASE WHEN (OH.OH_SALE_TYPE = 'O' AND OH.OH_CUSTOMER_TYPE_ID IN ('000001','000003') 
                                                AND CM.CM_CUST_SUB_TYPE <> 3 AND OD.OD_RETURN_REASON_CODE IN ('000000','000001') 
                                                AND CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @CurrFromDate AND @CurrToDate)									
                                           THEN	COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_PRODUCT_COST_PRICE,0)
                                           ELSE 0 END)

                    --'2 - MTD Prior',
                    PriorGross_Sales = SUM(CASE WHEN (OH.OH_SALE_TYPE = 'O' AND OH.OH_CUSTOMER_TYPE_ID IN ('000001','000003') 
                                                AND CM.CM_CUST_SUB_TYPE <> 3 -- SBT = 3 (Pay By Scan)
                                                AND OD.OD_RETURN_REASON_CODE IN ('000000','000001') -- Sale or BuyBack
                                                AND CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @PriorFromDate AND @PriorToDate)
                                           THEN COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_FINAL_PRICE , 0)
                                           ELSE 0 END),
                    PriorCost = SUM(CASE WHEN (OH.OH_SALE_TYPE = 'O' AND OH.OH_CUSTOMER_TYPE_ID IN ('000001','000003') 
                                                AND CM.CM_CUST_SUB_TYPE <> 3 AND OD.OD_RETURN_REASON_CODE IN ('000000','000001') 
                                                AND CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @PriorFromDate AND @PriorToDate)									
                                           THEN	COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_PRODUCT_COST_PRICE,0)
                                           ELSE 0 END)

                    --'3 - YTD Current',
                    CurrentYTDGross_Sales = SUM(CASE WHEN (OH.OH_SALE_TYPE = 'O' AND OH.OH_CUSTOMER_TYPE_ID IN ('000001','000003') 
                                                AND CM.CM_CUST_SUB_TYPE <> 3 -- SBT = 3 (Pay By Scan)
                                                AND OD.OD_RETURN_REASON_CODE IN ('000000','000001') -- Sale or BuyBack
                                                AND CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @CurrFromDateYTD AND @CurrToDateYTD)
                                           THEN COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_FINAL_PRICE , 0)
                                           ELSE 0 END),
                    CurrentYTDCost = SUM(CASE WHEN (OH.OH_SALE_TYPE = 'O' AND OH.OH_CUSTOMER_TYPE_ID IN ('000001','000003') 
                                                AND CM.CM_CUST_SUB_TYPE <> 3 AND OD.OD_RETURN_REASON_CODE IN ('000000','000001') 
                                                AND CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @CurrFromDateYTD AND @CurrToDateYTD)
                                           THEN	COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_PRODUCT_COST_PRICE,0)
                                           ELSE 0 END)
                    

                    --'3 - YTD Prior',
                    PriorYTDGross_Sales = SUM(CASE WHEN (OH.OH_SALE_TYPE = 'O' AND OH.OH_CUSTOMER_TYPE_ID IN ('000001','000003') 
                                                AND CM.CM_CUST_SUB_TYPE <> 3 -- SBT = 3 (Pay By Scan)
                                                AND OD.OD_RETURN_REASON_CODE IN ('000000','000001') -- Sale or BuyBack
                                                AND CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @PriorFromDateYTD AND @PriorToDateYTD)
                                           THEN COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_FINAL_PRICE , 0)
                                           ELSE 0 END),
                    PriorYTDCost = SUM(CASE WHEN (OH.OH_SALE_TYPE = 'O' AND OH.OH_CUSTOMER_TYPE_ID IN ('000001','000003') 
                                                AND CM.CM_CUST_SUB_TYPE <> 3 AND OD.OD_RETURN_REASON_CODE IN ('000000','000001') 
                                                AND CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @PriorFromDateYTD AND @PriorToDateYTD)
                                           THEN	COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_PRODUCT_COST_PRICE,0)
                                           ELSE 0 END)
            FROM    CHLROS05.RAMSDB.dbo.ROSS_SALESHDR AS OH
                        INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_CUSTOMER AS CM
                              ON OH.OH_CUSTOMER_NUMBER = CM.CM_CUSTKEY
                              AND CM_ACCT_GLPOSTS = '000004'
                              AND left(CM.CM_SELLZONE,1) = '4'
                              AND CM.CM_CUST_SUB_TYPE <> 3
                        INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as CUST_ID  
                                    ON CM.CM_SELLZONE = CUST_ID.IDVALUE
                                    AND CUST_ID.ID_TITLE = N'Sale Zone'
                        INNER JOIN  CHLROS05.RAMSDB.dbo.ROSS_SALESDTL AS OD         
                                    ON OD.OD_TICKET_NUMBER = OH_TICKET_NUMBER                                           
                        INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_PRODUCTS AS PD
                                    ON PD.PRD_PRODUCT = OD.OD_PRODUCT   
                        INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as PR_ID  
                                    ON PD.PRD_R_BRANDNAME = PR_ID.IDVALUE
                                    AND PR_ID.ID_TITLE = N'Product Brand Name'

            --WHERE     OH.OH_SALE_TYPE = 'O'
            --          AND (ID.ID_TITLE = N'Sale Zone')

            GROUP BY    PR_ID.ID_DESC,
                        OD.OD_PRODUCT,
                        CM.CM_SELLZONE,
                        CUST_ID.IDVALUE,
                        CUST_ID.ID_TITLE,
                        PD.PRD_R_BRANDNAME
         )
INSERT INTO #SalesSummary 
SELECT

                    'RAMS',
                    CM.CM_SELLZONE,
                    CUST_ID.IDVALUE,
                    CUST_ID.ID_TITLE,
                    CurrWeekEndDate, 
                    CurrToDate, 
                    CurrToDateYTD, 
                    CurrentWeek,
                    NumberofWeeks,
                    CurrToDate_Period,
                    CurrToDateYTD_FiscalYear,
                    BRAND,
                    BRAND_ID,
                    Item_no,
                    CurrentGross_Sales as GrossSales,
                    CurrentCost as Cost
FROM CTE

SELECT

                    'RAMS',
                    CM.CM_SELLZONE,
                    CUST_ID.IDVALUE,
                    CUST_ID.ID_TITLE,
                    CurrWeekEndDate, 
                    CurrToDate, 
                    CurrToDateYTD, 
                    CurrentWeek,
                    NumberofWeeks,
                    CurrToDate_Period,
                    CurrToDateYTD_FiscalYear,
                    BRAND,
                    BRAND_ID,
                    Item_no,
                    PriorGross_Sales as GrossSales,
                    PriorCost as Cost
FROM CTE

SELECT

                    'RAMS',
                    CM.CM_SELLZONE,
                    CUST_ID.IDVALUE,
                    CUST_ID.ID_TITLE,
                    CurrWeekEndDate, 
                    CurrToDate, 
                    CurrToDateYTD, 
                    CurrentWeek,
                    NumberofWeeks,
                    CurrToDate_Period,
                    CurrToDateYTD_FiscalYear,
                    BRAND,
                    BRAND_ID,
                    Item_no,
                    CurrentYTDGross_Sales as GrossSales,
                    CurrentYTDCost as Cost
FROM CTE

SELECT

                    'RAMS',
                    CM.CM_SELLZONE,
                    CUST_ID.IDVALUE,
                    CUST_ID.ID_TITLE,
                    CurrWeekEndDate, 
                    CurrToDate, 
                    CurrToDateYTD, 
                    CurrentWeek,
                    NumberofWeeks,
                    CurrToDate_Period,
                    CurrToDateYTD_FiscalYear,
                    BRAND,
                    BRAND_ID,
                    Item_no,
                    PriorYTDGross_Sales as GrossSales,
                    PriorYTDCost as Cost
FROM CTE

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
please refer to questions at ID39592789, ID39592841, ID39592917
and suggestion, ID39593314
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
Man you guys have the best tools and tricks. Thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.