Return Top row from group of records

Please tell me if there is another better way to do the following.

COmpanySales
-------
compSalesID int,
i_company_id int,
i_year int,
i_quarter int,
i_sales_amount float,
i_fte_number int -- latest number of employees in the company

I know the table design stinks. But I have no other go but to work with it. Pleas edon't dwell into the design aspects.



Select y.i_company_id,y.i_year,y.i_Sales_Amount,y.i_FTE_number
-------------------From
-------------------(Select i_company_id, max(i_year) i_year
------------------------From COmpanySales
------------------------group by i_company_id) x,

------------------------(Select s.i_company_id,s.i_year,s.i_Sales_Amount,fte.i_FTE_number
--------------------------From
-------------------------------(Select i_company_id, i_year, sum(i_Sales_amount) i_Sales_Amount
-------------------------------From CompanySales
-------------------------------Group by i_company_id, i_year
-------------------------------) s ,
-------------------------------(Select i_company_id,i_year,i_quarter,i_FTE_number
----------------------------------------From CompanySales a
----------------------------------------where exists(Select * from
--------------------------------------------------------(Select i_company_id, Max(i_year) i_year, Max(i_quarter)i_quarter
--------------------------------------------------------From CompanySales
--------------------------------------------------------Group by i_company_id )f
--------------------------------Where a.i_company_id = f.i_company_id and a.i_year = f.i_year and a.i_quarter = f.i_quarter))Fte

------------------------Where s.i_company_id = fte.i_company_id )y

-------------------where x.i_company_id = y.i_company_id and x.i_year = y.i_year
Usha Rani

Sample Data: data is not ordered.
Company:
companyid and other fields
20
21
22
CompanySales data
1, 20, 1999, 1, 15, 100
2, 20, 1999, 2, 20, 90
3, 20, 1999, 3, 30, 120
4, 20, 2000, 1, 15, 100
5, 20, 2000, 2, 20, 90
6, 20, 2000, 4, 30, 120
7, 21, 1999, 1, 20, 100
8, 21, 1999, 2, 30, 110
9, 21, 2000, 1, 20, 100
10, 21, 2000, 2, 30, 110
Need to get sum sales of latest year, per company and latest FTE

Usha Rani

quote:
Need to get sum sales of latest year, per company and latest FTE

I may be overlooking something. But I think it should be as simple as

SELECT y.i_company_id,y.i_year,SUM(y.i_Sales_Amount),
(SELECT i_fte_number FROM COmpanySales FTE
WHERE FTE.i_company_id = Y.i_company_id
AND FTE.i_year= Y.i_year
AND FTE.i_quarter = (SELECT MAX(i_quarter)
FROM COmpanySales Q
WHERE FTE.i_company_id = Q.i_company_id
AND FTE.i_year= Q.i_year))
FROM COmpanySales Y
WHERE Year = (SELECT MAX(Year) FROM COmpanySales i
WHERE i.i_company_id = Y.i_company_id)

--untested

















Roji. P. Thomas
http://toponewithties.blogspot.com

SELECT y.i_company_id,y.i_year,SUM(y.i_Sales_Amount),
(SELECT i_fte_number FROM COmpanySales FTE
WHERE FTE.i_company_id = Y.i_company_id
AND FTE.i_year= Y.i_year
AND FTE.i_quarter = (SELECT MAX(i_quarter)
FROM COmpanySales Q
WHERE FTE.i_company_id = Q.i_company_id
AND FTE.i_year= Q.i_year))
FROM COmpanySales Y
WHERE Year = (SELECT MAX(Year) FROM COmpanySales i
WHERE i.i_company_id = Y.i_company_id)

Group by y.i_company_id, y.i_year

Must add group by.

Thank you. This seems simpler. Will check for performance .

Thank you once again.

Usha Rani

Sorry! Tomas, The query is not performing well.

The Quer cost relative to the batch when I executed both mine and yours is:
Usha:Thomas
25%:75%

I meant that I need simpler but more efficient query.

Thank you for trying.

Usha Rani

Your main subquery has three embedded subqueries, all of which are looking for MAX() values on three columns, all for the same i_company_id in the CompanySales table.

You can do the three aggregates in a single derived table, and join on the aliases for the MAX() expressions ...

Thanks to both of you.

Adriaan is this what you meant.

Select y.i_company_id,y.i_year,y.i_Sales_Amount,y.i_FTE_number
From
(Select i_company_id, i_year, Sum(i_Sales_Amount)i_Sales_Amount, Max(i_FTE_number)i_FTE_number
From CompanySales
Group By i_company_id, i_year)y
Where i_year = (SELECT MAX(i_year) FROM CompanySales I WHERE I.i_company_id = y.i_company_id)

Order By y.i_company_id, y.i_year desc





Usha Rani

Thank you Frank <br />I did as per your suggession and it is the best for performance.<br /><br /><br />SELECT t1.i_year, t1.i_company_id, SUM(t1.i_Sales_Amount) i_Sales_Amount, <br /> (SELECT MAX(i_CompanyYearlyData_id )<br /> FROM CompanySales<br /> WHERE i_company_id = t1.i_company_id<br /> GROUP BY i_company_id) FTE<br /> FROM CompanySales t1<br /> WHERE t1.i_year = (SELECT MAX(i_year)<br /> FROM CompanySales<br /> WHERE i_company_id = t1.i_company_id) <br /> GROUP BY t1.i_company_id, t1.i_year<br />Order By t1.i_company_id, t1.i_year desc<br /><br />Thank you.[<img src='/community/emoticons/emotion-1.gif' alt='' />] But is there more simpler way....... He he eh<br /><br />Actually I don't get correct picture of FTE number, If I include Otherwise it is a good thought to use the CompanySales ID<br /><br /><br />Usha Rani

I was thinking along these lines ... You need the latest i_quarter of the latest i_year for each i_company_id:

select cs.i_company_id, max(cs.i_quarter)
from CompanySales cs
where cs.i_year IN (select max(x.i_year) from CompanySales x where x.i_company_id = cs.i_company_id)
group by c.i_company_id

Add this as a derived table to your main aggregate query:

SELECT y.i_company_id, y.i_year, y.i_quarter, Sum(y.i_Sales_Amount) i_Sales_Amount, Max(y.i_FTE_number) i_FTE_number
FROM CompanySales y
INNER JOIN
(select cs.i_company_id, max(cs.i_quarter) lastqtr, max(cs.i_year) lastyr
from CompanySales cs
where cs.i_year IN (select max(x.i_year) from CompanySales x where x.i_company_id = cs.i_company_id)
group by c.i_company_id) x
ON y.i_company_id = x.i_company_id AND y.i_year = x.lastyr AND y.i_quarter = x.lastqtr
GROUP BY y.i_company_id, y.i_year

This filters the results to the last known quarter of the last known year for each i_company_id. Note that the last quarter may be a different time period for each company.

This would be so much easier if you had a combined year/quarter column ...

Dear Frank!

I need latest FTE for the year, for that company id and not total of FTE. Thank you.

Usha Rani

Can you post the DDL + Sample Data (In SQL, Not in English, as CREATE AND INSERT Statements).

Roji. P. Thomas
http://toponewithties.blogspot.com

Thanks Frank. The peformance is the same, but correct. Thank you.

Usha Rani

You are right Adriaan. This is what I have done in the first query and yours is performing better and simpler. But not much diff in Performance why?


Usha Rani

If you can add a composite field for yyyy-q (2006-3, etc.) and index that, then you can run simpler and better performing queries.

As a workaround, you could see if creating a temp working table with proper indexes [edit]and that composite field[/edit] can improve matters ...

Frank!
We have 45000 rows at present
and the indexes are on:
CompanySales Table
PK i_CompanyYearlyData_id identity clustured
IX i_company_id
Company
i_company_id identity clus:


WIll add check it out Adrian.


Thank you.

Usha Rani

quote:Originally posted by PAMUR

Sample Data: data is not ordered.
Company:
companyid and other fields
20
21
22
CompanySales data
1, 20, 1999, 1, 15, 100
2, 20, 1999, 2, 20, 90
3, 20, 1999, 3, 30, 120
4, 20, 2000, 1, 15, 100
5, 20, 2000, 2, 20, 90
6, 20, 2000, 4, 30, 120
7, 21, 1999, 1, 20, 100
8, 21, 1999, 2, 30, 110
9, 21, 2000, 1, 20, 100
10, 21, 2000, 2, 30, 110
Need to get sum sales of latest year, per company and latest FTE

Usha Rani

Is the expected result one i posted bellow?

co, year, sum, fte
-------------------
20, 2000, 65, 120
21, 2000, 100, 110

If I got your intention right try next query:


select t.i_company_id, t.i_year, sum(c.i_sales_amount), t.i_fte_number
from (
select a.i_company_id, a.i_year, a.i_fte_number
from
(
select i_company_ID, max(10*i_year + i_quarter) as i_quarter
from COmpanySales
group by icompany_ID, i_year
) as b
inner loop join companySales a on b.i_company_Id = a.i_company_id and
a.i_year = b.i_quarter /10 and
a.i_quarter = b.i_quarter % 10
option (force order)
) as t
inner loop join companySales c on c.i_company_id = t.i_company_id and
c.i_year = t.i_year
group by t.i_company_id, t.i_year, t.i_fte_number
option (force order)

This query is far from simple, but I am curious about performance. I didn't test it, I hope the syntax is correct.

quote:
We have 45000 rows at present
and the indexes are on:
CompanySales Table
PK i_CompanyYearlyData_id identity clustured
IX i_company_id
Company
i_company_id identity clus:

Index names don't help us, we need to know columns indexes consist of. For the query proposed above optimal index will contain columns i_company_id, i_year, i_quarter in that order.

Another (much simpler) solution that may even be faster then most solutions offered (not sure about that) involves the use of cursor.