Select
						Month(SO_SalesOrder.OrderDate) As OrderMonth,
						BASE_Product.Name As PipCode,
						BASE_Product.Description As Description,
						SO_SalesOrder_Line.Quantity As Quantity,
						SO_SalesOrder_Line.UnitPrice As Price,
						BASE_Customer.Name As Customer
				INTO #temp	
   			From BASE_Product 
				Inner Join SO_SalesOrder_Line 
						On BASE_Product.ProdId = SO_SalesOrder_Line.ProdId
				Inner Join SO_SalesOrder 
						On SO_SalesOrder.SalesOrderId = SO_SalesOrder_Line.SalesOrderId 
				Inner Join BASE_Customer 
						On BASE_Customer.CustomerId = SO_SalesOrder.CustomerId
				Where
						SO_SalesOrder.SalesOrderId = SO_SalesOrder_Line.SalesOrderId And
						BASE_Product.ProdId = SO_SalesOrder_Line.ProdId And
						BASE_Customer.CustomerId = SO_SalesOrder.CustomerId And
					    (Month(SO_SalesOrder.OrderDate) = Month(Current_TimeStamp) And
						SO_SalesOrder.OrderStatus <> 10 And
						SO_SalesOrder.Custom2 = 'Y' And
						Year(SO_SalesOrder.OrderDate) = Year(Current_TimeStamp))

       SELECT A.PipCode,B.Description,A.MaxPrice,B.Customer, B.Quantity FROM 
		(
			SELECT PipCode,MAX(Price) AS MaxPrice FROM #temp GROUP BY PipCode
		)A 
		INNER JOIN 
		(
			SELECT OrderMonth,PipCode,Description,Quantity,Price,Customer FROM #temp
		)B
		ON A.PipCode = B.PipCode AND A.MaxPrice = B.Price

quote:

---

Originally posted by shaheedfazal

It's not clear from your post how this would apply to my scenario as you are only dealing with two tables. Are you saying that I should create a list of all prices quoted this month and then cross apply the customer?

---

quote:

---

Originally posted by shaheedfazal

Hi Rohit,

DECLARE @customers VACRHAR(1000)
DECLARE @count INT
DECLARE @max_count INT

SET @count = 1
SELECT @max_count = MAX(id) FROM #temp_customers

WHILE (@count < @max_count)
BEGIN
IF(@count = 1)
SET @customers = (SELECT customers FROM #temp_customers WHERE id = @count)
ELSE
SET @customers = @customers + '/' + (SELECT customers FROM #temp_customers WHERE id = @count)

SET @count = @count + 1
END

I have run into the following error:

Msg 2715, Level 16, State 3, Line 53
Column, parameter, or variable #3: Cannot find data type VACRHAR.
Parameter or variable '@customers' has an invalid data type.

I don't understand why this error is coming up as VARCHAR is a normal data type in sql?

---

SELECT
						Month(SO_SalesOrder.OrderDate) As OrderMonth,
						BASE_Product.Name As PipCode,
						BASE_Product.Description As Description,
						SO_SalesOrder_Line.Quantity As Quantity,
						SO_SalesOrder_Line.UnitPrice As Price,
						BASE_Customer.Name As Customer
				INTO #temp	
   			        FROM BASE_Product 
				Inner Join SO_SalesOrder_Line 
						On BASE_Product.ProdId = SO_SalesOrder_Line.ProdId
				Inner Join SO_SalesOrder 
						On SO_SalesOrder.SalesOrderId = SO_SalesOrder_Line.SalesOrderId 
				Inner Join BASE_Customer 
						On BASE_Customer.CustomerId = SO_SalesOrder.CustomerId
				Where
						SO_SalesOrder.SalesOrderId = SO_SalesOrder_Line.SalesOrderId And
						BASE_Product.ProdId = SO_SalesOrder_Line.ProdId And
						BASE_Customer.CustomerId = SO_SalesOrder.CustomerId And
					    (Month(SO_SalesOrder.OrderDate) = Month(Current_TimeStamp) And
						SO_SalesOrder.OrderStatus <> 10 And
						SO_SalesOrder.Custom2 = 'Y' And
						Year(SO_SalesOrder.OrderDate) = Year(Current_TimeStamp))

 
CREATE TABLE #temp_customers
(
  id INT IDENTITY(1,1),
  PipCode VARCHAR(100),
  customers VARCHAR(50)
)

INSERT INTO #temp_customers
SELECT A.PipCode,B.Customer  FROM 
(
     SELECT PipCode,MAX(Price) AS MaxPrice FROM #temp GROUP BY PipCode
)A 
INNER JOIN 
(
      SELECT PipCode,Description,Price,Customer,ordermonth FROM #temp
)B
ON A.PipCode = B.PipCode AND A.MaxPrice = B.Price
ORDER BY b.description ASC	

CREATE TABLE #temp_Customers_combined
(
  PipCode VARCHAR(100),
  customers VARCHAR(1000)
)

DECLARE @count_outer INT
DECLARE @max_count_outer INT

SET @count_outer = 1
SELECT @max_count_outer = (SELECT COUNT(DISTINCT PipCode) FROM #temp_customers)

WHILE(@count_outer <= @max_count_outer)
BEGIN
        DECLARE @pip_code VARCHAR(100)
	DECLARE @customers VARCHAR(1000)
	DECLARE @count INT
	DECLARE @max_count INT

	SET @customers = ''
	SELECT  @pip_code = PipCode FROM #temp_customers WHERE id = @count_outer
				
	SELECT @count = MIN(id) FROM #temp_customers WHERE PipCode = @pip_code
	SELECT @max_count = MAX(id) FROM #temp_customers WHERE PipCode = @pip_code
		
	WHILE (@count <= @max_count)
	BEGIN
	  IF(LEN(@customers) = 0)
	    SET @customers = (SELECT customers FROM #temp_customers WHERE id = @count AND PipCode = @pip_code) 
	  ELSE
	    SET @customers = @customers + '/' + (SELECT customers FROM #temp_customers WHERE id = @count AND PipCode = @pip_code)
  	   
            SET @count = @count + 1
	END
	
	INSERT INTO #temp_Customers_combined
	SELECT @pip_code,@customers
	SET @customers = ''
	SET @count_outer = @count_outer + 1				
END

SELECT TOP 1000 T.PipCode,Description,MAX(Price),C.customers AS Buyerquotinghighestprice, OrderMonth FROM #temp T
INNER JOIN #temp_Customers_combined C
		ON T.PipCode = C.PipCode
group by T.pipcode, description,OrderMonth,C.customers
order by description