title: Complex SQL Query to NHibernate DetachedCriteria or HQL
body: <p>I have the following SQL Query returning the results I need:</p>
  
  <pre><code>SELECT     
  Person.FirstName,Person.LastName,OrganisationUnit.Name AS UnitName, RS_SkillsArea.Name AS SkillsArea, Activity.Name AS ActivityName, Activity.CLASS, Activity.StartsOn, Activity.EndsOn, 
  
  SUM(ActivityCost.CostAmount) / 
  
  NULLIF(
   (
    SELECT COUNT(Registration.ActivityId) FROM         
    Registration INNER JOIN AttemptResultsSummary ON Registration.CurrentResultId = AttemptResultsSummary.AttemptResultsSummaryId AND 
    Registration.RegistrationId = AttemptResultsSummary.RegistrationId
    WHERE     (Registration.Status = 1) AND (Registration.ActivityId = Activity.ActivityId) 
    AND (AttemptResultsSummary.AttendanceStatus &lt;&gt; 1)
   ) 
   ,0) 
   AS IndividualCost
  
   FROM         Registration AS Registration_1 INNER JOIN
                        Activity ON Registration_1.ActivityId = Activity.ActivityId INNER JOIN
                        Person ON Registration_1.PersonId = Person.PersonId INNER JOIN
                        OrganisationUnit ON Person.OrganisationUnitId = OrganisationUnit.OrganisationUnitId INNER JOIN
                        AttemptResultsSummary ON Registration_1.CurrentResultId = AttemptResultsSummary.AttemptResultsSummaryId AND 
                        Registration_1.RegistrationId = AttemptResultsSummary.RegistrationId AND Activity.ActivityId = AttemptResultsSummary.ActivityId AND 
                        Person.PersonId = AttemptResultsSummary.PersonId INNER JOIN 
                        ActivityCost ON Activity.ActivityId = ActivityCost.ActivityId LEFT OUTER JOIN
                            (SELECT     Category.Name, Category.CategoryId
                              FROM          Category INNER JOIN
                                                     CategoryGroup ON Category.[Group] = CategoryGroup.CategoryGroupId
                              WHERE      (CategoryGroup.Name = N'Skills Area')) AS RS_SkillsArea INNER JOIN
                        ActivityInCategory ON RS_SkillsArea.CategoryId = ActivityInCategory.CategoryId ON Activity.ActivityId = ActivityInCategory.ActivityId
  
  AND AttemptResultsSummary.AttendanceStatus &lt;&gt; 1
  
  
  
  GROUP BY RS_SkillsArea.Name, Person.FirstName,Person.LastName,Activity.Name, Activity.CLASS, Activity.StartsOn, Activity.EndsOn, Activity.ActivityId, OrganisationUnit.Name, 
                        AttemptResultsSummary.CompletionStatus, AttemptResultsSummary.AttendanceStatus
  
  HAVING AttemptResultsSummary.AttendanceStatus &lt;&gt; 1
  </code></pre>
  
  <p>Essentially is there any way using either DetachedCriteria or HQL to do the same against the entities rather than direct SQL?</p>
  
  <p>The two challenges are: </p>
  
  <ol>
  <li><p>The query for cost calculation per row. </p></li>
  <li><p>The derived table join (which needs to be an outer join as this value may not exist)</p></li>
  </ol>
  
  <p>I'd appreciate any pointers. I'd rather not use SQL because of infrastructure changes and the issues with (lack of) refactoring support</p>