SQL 2K

I got a table 'Sales' with columns

CompanyID
Deptid
ChrgAmt
Load

I want to generate a report with Top 10 Deptid (in terms of ChrgAmt, Load)
for each CompanyID.

Thanks for your time.

Mike

Which version of SQL Server are you using? In SQL 2005, you can use the
RANK() function with PARTITION BY companyid and ORDER BY ChrgAmt, Load to
get the results. In SQL 2000, you'll have to mostly use a self join or a
correlated subquery to derive the required subset. Also you'll have to
decide on how you'd like to handle the ties as well.

For an exact query, post your table structures, sample data & expected
results. For details refer to: www.aspfaq.com/5006

Its SQL 2000

I tried with self join BUT NO-LUCK..

Thanks
Mike


Show quoteHide quote

1. USE CURSOR OR WHILE LOOP TO PASS IN COMPANYID, AND THEN DO YOU TOP
10 AND PUT THE RESULT ON A TEMP TABLE
AND THEN SELECT FROM THE TEMP TABLE

THIS REQUIRES SOME PROGRAMMING.


MS User wrote:
Show quoteHide quote

<SQL***@gmail.com> wrote in message
Not if use Rac:)

www.rac4sql.net

http://racster.blogspot.com

Look, a note from Steve
But not Haiku, I believe
Sell RAC have no fun

Show quoteHide quote

That made me laugh :)


Unfortunately, diet coke through the nose really burns.....


Stu


Arnie Rowland wrote:
Show quoteHide quote

Wuss...



Show quoteHide quote

See my post here:

http://www.accessforums.net/ftopic7683.html


I never really found a good solution


I was able to find the TOP 1 ........  using this code.

Now I wrote the comment above about 8 months before I found the technique
below.

If you can use the technique below.. and alter it to find the  'Top 2'
completions ....... per Emp (or something like that)
then PLEASE post it here.

/*
Lets say you take a class, called "CPR" at the YMCA about every year.
You and alot of other people take CPR.

Whenever you take the class, you get a certain amount of credits for that
class.
The credits for the class CRP can change over the years.

You take it in 2004, and you received 3 credits for it.
You take it in 2005, and they upped the ante, and you get 3.5 credits for
it.
You take it in 2006, and they change it to 2 credits for some reason.



Lets say you have a business rule.
You need to discover how many credits each person received when they took
their CPR class.
But the business rule says "Only get the credits from the last time each
person took the class"

So, if I took the class in 2004, 2005, and 2006, only the 2006 occurence
counts, because its the most recent time I took it.
Even if the credits for 2006 are lessthan the credits for 2004 or 2005, I
want the 2006 credit values.
Looking above, it means I would want to get a "2" back, because that's what
I received in 2006, my most recent date.

Here is a sample script to show how you accomplish this in one query.
*/

-- START TSQL CODE

set nocount on
declare @CourseCompletions table ( EmpID int , CourseID int ,
CompletionDate datetime ,   Credits decimal(10,2)  )
insert into @CourseCompletions (EmpID , CourseID , CompletionDate ,
redits  ) values (101 , 1 , '1/1/2004' , 11.1    )
insert into @CourseCompletions (EmpID , CourseID , CompletionDate ,
redits  ) values (101 , 2 , '1/1/2004' , 22.2    )
insert into @CourseCompletions (EmpID , CourseID , CompletionDate ,
redits  ) values (101 , 1 , '1/1/2005' , 11.1    )
insert into @CourseCompletions (EmpID , CourseID , CompletionDate ,
redits  ) values (101 , 2 , '1/1/2005' , 22.2    )
insert into @CourseCompletions (EmpID , CourseID , CompletionDate ,
redits  ) values (101 , 1 , '1/1/2006' , 1.11    )
insert into @CourseCompletions (EmpID , CourseID , CompletionDate ,
redits  ) values (101 , 2 , '1/1/2006' , 2.22   )

insert into @CourseCompletions (EmpID , CourseID , CompletionDate ,
redits  ) values (102 , 1 , '3/1/2004' , 33.3    )
insert into @CourseCompletions (EmpID , CourseID , CompletionDate ,
redits  ) values (102 , 2 , '3/1/2004' , 44.4    )
insert into @CourseCompletions (EmpID , CourseID , CompletionDate ,
redits  ) values (102 , 1 , '3/1/2005' , 33.3    )
insert into @CourseCompletions (EmpID , CourseID , CompletionDate ,
redits  ) values (102 , 2 , '3/1/2005' , 44.4    )
insert into @CourseCompletions (EmpID , CourseID , CompletionDate ,
redits  ) values (102 , 1 , '3/1/2006' , 3.33    )
insert into @CourseCompletions (EmpID , CourseID , CompletionDate ,
redits  ) values (102 , 2 , '3/1/2006' , 4.44    )

insert into @CourseCompletions (EmpID , CourseID , CompletionDate ,
redits  ) values (103 , 1 , '6/1/2003' , 5.55    )
insert into @CourseCompletions (EmpID , CourseID , CompletionDate ,
redits  ) values (103 , 2 , '6/1/2003' , 7.77    )
insert into @CourseCompletions (EmpID , CourseID , CompletionDate ,
redits  ) values (103 , 1 , '6/1/2004' , 55.5    )
insert into @CourseCompletions (EmpID , CourseID , CompletionDate ,
redits  ) values (103 , 2 , '6/1/2004' , 77.7   )
insert into @CourseCompletions (EmpID , CourseID , CompletionDate ,
redits  ) values (103 , 1 , '6/1/2005' , 55.5    )
insert into @CourseCompletions (EmpID , CourseID , CompletionDate ,
redits  ) values (103 , 2 , '6/1/2005' , 77.7    )
insert into @CourseCompletions (EmpID , CourseID , CompletionDate ,
redits  ) values (103 , 1 , '6/1/2006' , 5.55    )
insert into @CourseCompletions (EmpID , CourseID , CompletionDate ,
redits  ) values (103 , 2 , '6/1/2006' , 7.77   )
--select * from @CourseCompletions

SELECT MAX(CompletionDate) FROM @CourseCompletions WHERE EmpID = 101 and
CourseID = 1
--This returns '2006-01-01', which is obviously the latest date for this
Emp. Can I use the same technique to find the Credits?

SELECT MAX(Credits) FROM @CourseCompletions WHERE EmpID = 101 and CourseID =
1
--This returns 11.10, which was the Credits in '1/1/2004', but NOT the
Credits for the Max(CompletionDate).... we can clearly see that a new, lower
Credits went into effect on 6/1/2006. We didn't get the correct Credits by
using MAX. How can we find the Credits that corresponds to that MAX date? We
have to join to a subquery, as follows:

SELECT
   CreditsList.EmpID ,
   CreditsList.CourseID,
   CreditsList.CompletionDate as MostRecentCompletionDate,
   CreditsList.Credits
FROM @CourseCompletions AS CreditsList
INNER JOIN
   (
SELECT
  EmpID ,
  CourseID,
          MAX(CompletionDate) AS LastDate
       FROM
  @CourseCompletions
       GROUP BY
  EmpID , CourseID
   ) AS LastDatesAsADerivedTable
   ON
CreditsList.EmpID = LastDatesAsADerivedTable.EmpID
AND
CreditsList.CourseID = LastDatesAsADerivedTable.CourseID
AND
CreditsList.CompletionDate = LastDatesAsADerivedTable.LastDate

--WHERE CreditsList.EmpID = 101
ORDER BY
CreditsList.EmpID , CreditsList.CourseID

I believe this will do what you ask:

SELECT CompanyID, DeptID, sum(ChrgAmt) as TotalAmt
  FROM Sales as A
WHERE DeptID IN
       (select TOP 10 B.DeptID
          from Sales as B
         where A.CompanyID = B.CompanID
         group by B.DeptID
         order by sum(ChrgAmt) desc)
GROUP BY CompanyID, DeptID

Roy Harvey
Beacon Falls, CT

Show quoteHide quote

Thanks Roy, that worked with a minor change!!!

ORDER BY A.CompanyID, A.DeptID



Show quoteHide quote

UDF that take a UDT as a parameter (CLR)
Best way to insert data into tables without primary keys
What is new in Sql Server 2005 cursors?
SQL 2000 Database Performance
How does a view work