November 1, 2004 at 10:58 am
Hello there,
I have created a table that stores ID, CustomerID, Date and some other fields.
Now I want to create a view that lists the TOP 10 Customers per Month which have the highest Count(ID) in that Table. The View should list data for all Months in the table.
How can I do that?
THX
Markus
November 1, 2004 at 11:59 am
I included a $ column since you sounded as if you wanted the top 10 customers, I assumed you meant in purchasing... You could simply insert these into a #TempTable then call them at the end for a report if that is what you are working upon...
DECLARE @LastMonth datetime,
@StartMonth datetime
SELECT @StartMonth = '01/01/2000' -- arbitrary choice
SELECT @LastMonth = (SELECT MAX( CONVERT( datetime, [Date])) FROM TableName WHERE ISDATE( [Date]) = 1)
WHILE @StartMonth <= @LastMonth
BEGIN
SELECT TOP 10 [ID], CustomerID, CONVERT( varchar, [Date], 101) AS [Date], DollarAmount
FROM Awards
WHERE( DATEPART( year, [Date]) = DATEPART( year, @StartMonth)
AND DATEPART( month, [Date]) = DATEPART( month, @StartMonth))
ORDER BY DollarAmount DESC
SELECT @StartMonth = (SELECT DATEADD( month, 1, @StartMonth))
END
I wasn't born stupid - I had to study.
November 2, 2004 at 12:54 am
Thanks for your suggestion. I don't want to get the $ amount. Just the top 10 customers with the most records per Month.
Problem:
I want to create a view to do it. Not a procedure. So I need a single SQL-Statement to get the results.
November 2, 2004 at 1:58 am
Hi Markus,
I think I've found a way to get the results you're looking for in a single query. If you like you can substitue the first view in the second by inline views, but this makes the code even less readable:
create view CountPerMonthView
as
select CustomerID
, convert(datetime
,'01-' + substring(convert(varchar(10)
,YourDateColumn
,105)
,4, 10)
,105) YourConvertedDateColumn
, count(ID) YourCountID
from YourTable
group by CustomerID
, convert(datetime
,'01-' + substring(convert(varchar(10)
,YourDateColumn
,105)
,4, 10)
,105)
go
create view Top10PerMonthView
as
select CustomerID
, YourConvertedDateColumn
, YourCountID
from CountPerMonthView v
where convert(varchar(10), YourConvertedDateColumn, 105) +
'_' + cast(CustomerID as varchar(10))
in
(select top 10 convert(varchar(10), YourConvertedDateColumn, 105) +
'_' + cast(CustomerID as varchar(10))
from CountPerMonthView
where YourConvertedDateColumn = v.YourConvertedDateColumn
order by YourCountID desc
)
go
I've tested this code and it seems to work, though not very fast.
I would have liked the possibility to use
...CustomerID, YourConvertedDateColumn in
(select CustomerID, YourConvertedDateColumn
...
)
(Oracle supports this)
but now I had to force the 2 columns into 1.
Hope this helps.
Cheers,
Henk
November 3, 2004 at 12:59 am
Yes, that's it. Thanks a lot.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply