title: Sql Server Subquery Looping
body: <p>I have a subquery that sums the days a Listing has been active. There are 3 different priorities for these listings that we want to analyze seperately. The subqueries works fine but I have to repeat myself 3 times and i can't help feeling like someone could point me in a more elegant direction.</p>
  
  <p>I'm looking for something along the lines of</p>
  
  <pre><code>Declare @Priorities text = ['H','M','L']
  
  Foreach(priority in priorities)
    (SELECT SUM(
          CASE
              WHEN(jlh_inner.StartDate IS NULL) THEN 0
              WHEN(jlh_inner.EndDate IS NULL) THEN
                  DATEDIFF(dd, jlh_inner.StartDate, GETDATE())
              ELSE
                  DATEDIFF(dd, jlh_inner.StartDate, jlh_inner.EndDate)
          END)
      FROM ListingHistory jlh_inner
      WHERE jlh_inner.JobTitleId = jlh.JobTitleId
      AND jlh_inner.OfficeCode = rof.code
      AND jlh_inner.Priority = priority)
  </code></pre>
  
  <p>Full code right now is:</p>
  
  <pre><code>BEGIN
  SELECT rof.location AS location,
      jlh.TitleId AS TitleId,
      jt.Title AS Title,
      (SELECT SUM(
          CASE
              WHEN(jlh_inner.StartDate IS NULL) THEN 0
              WHEN(jlh_inner.EndDate IS NULL) THEN
                  DATEDIFF(dd, jlh_inner.StartDate, GETDATE())
              ELSE
                  DATEDIFF(dd, jlh_inner.StartDate, jlh_inner.EndDate)
          END)
      FROM ListingHistory jlh_inner
      WHERE jlh_inner.JobTitleId = jlh.JobTitleId
      AND jlh_inner.OfficeCode = rof.code
      AND jlh_inner.Priority = 'H') AS HighPriorityDays,
      (SELECT SUM(
          CASE
              WHEN(jlh_inner.StartDate IS NULL) THEN 0
              WHEN(jlh_inner.EndDate IS NULL) THEN
                  DATEDIFF(dd, jlh_inner.StartDate, GETDATE())
              ELSE
                  DATEDIFF(dd, jlh_inner.StartDate, jlh_inner.EndDate)
          END)
      FROM ListingHistory jlh_inner
      WHERE jlh_inner.JobTitleId = jlh.JobTitleId
      AND jlh_inner.OfficeCode = rof.code
      AND jlh_inner.Priority = 'M') AS MediumPriorityDays,
      (SELECT SUM(
          CASE
              WHEN(jlh_inner.StartDate IS NULL) THEN 0
              WHEN(jlh_inner.EndDate IS NULL) THEN
                  DATEDIFF(dd, jlh_inner.StartDate, GETDATE())
              ELSE
                  DATEDIFF(dd, jlh_inner.StartDate, jlh_inner.EndDate)
          END)
      FROM ListingHistory jlh_inner
      WHERE jlh_inner.JobTitleId = jlh.JobTitleId
      AND jlh_inner.OfficeCode = rof.code
      AND jlh_inner.Priority = 'L') AS LowPriorityDays
  
  FROM Offices rof,
      ListingHistory jlh,
      JobTitle jt
  
  WHERE rof.code = jlh.OfficeCode
  AND jt.JobTitleID = jlh.JobTitleId
  
  GROUP BY rof.location, rof.code, jlh.TitleId, jt.Title
  
  ORDER BY location
  </code></pre>
  
  <p>END</p>