StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSql Server Subquery Looping
text
Body
copied!<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>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload