Hi I am trying to create an SQL view in Visual studio.
I want to filter out results to show the maximum sales for each customer based on each month and year and to display a description of the sales
I have the following statement:
SELECT Customer, Month, Year, sales, Description
FROM dbo.salestable
GROUP BY Customer, Month, Year, sales, Description
HAVING (sales =
(SELECT MAX(dbo.salestable.ppm) AS Expr1))
This doesn't seem to be working as I get duplicates for each month rather than one entry for each customer per month which should be the maximum sales for that customer.
I think the group by is causing an issue but it forces me to group each field.
Hopefully there is a simple solution?
Thank you for your help in advance.
Loading
Jignesh TrivediPosted Mar 6, 2013, 11:18 PM
i think there is problem with sales include in Group by statement.
please refer following TSQL, it might help you
Create Table #temp
(
Customer varchar(50),
Month varchar(5),
Year varchar(4),
sales int,
Description varchar(50)
)
Insert into #temp values('10000','Jan','2013',2000,'test')
Insert into #temp values('10000','Jan','2013',5000,'test')
Insert into #temp values('10000','Feb','2013',3000,'test')
Insert into #temp values('20000','Jan','2013',3000,'test')
Insert into #temp values('20000','Feb','2013',3000,'test')
Insert into #temp values('20000','Jan','2013',4000,'test')
Insert into #temp values('30000','Jan','2013',5000,'test')
Insert into #temp values('30000','Feb','2013',6000,'test')
Insert into #temp values('30000','March','2013',7000,'test')
Insert into #temp values('30000','Jan','2013',3000,'test')
Insert into #temp values('30000','Feb','2013',3000,'test')
select * from #temp
SELECT Customer, Month, Year, max(sales) as Sales, Description
FROM #temp
GROUP BY Customer, Month, Year, Description
hope this will help you