Hi,
I need to project data into the following DTO based on projection.
public class TerritoryKPIInfo
{
private int id;
private int noOfPersons;
public int Id
{
get { return id; }
set { id = value; }
}
public int NoOfPersons
{
get { return noOfPersons; }
set { noOfPersons = value; }
}
}
The query to project data into dto is
SELECT X.potentialId, Count(X.personId) as NoOfPersons
FROM
(
SELECT PP.personId,
(SELECT id FROM Persons.Potential WHERE weight = Max(PO.weight)) potentialId
FROM Territory.Territory T,Territory.TerritoryDetail TD, Persons.PersonProduct PP,Territory.ProductPortfolioDetail PPD, Persons.Potential PO, Persons.Usage US
WHERE T.id = 50
AND TD.territoryId = T.id AND 92 BETWEEN TD.startPeriod AND ISNULL(TD.endPeriod, 9999999)
AND PP.personId = TD.personId AND PP.productId = PPD.productId
AND PPD.productPortfolioId = T.productPortfolioId AND PPD.principal = 1
AND PO.id = PP.potentialId
AND US.id = PP.usageId
Group By PP.personId) X
Group By X.potentialId
Please help me to define the resultset fields for the above query.
Regards
Prabhu
