StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>To make this remotely effective, I recommend creating a <code>calendar</code> table. One that contains each and every date of interest. You then use that as a template on which to join your data.</p> <p>Equally, things improve further if you have <code>person</code> table to act as the template for the name dimension of your results.</p> <p>You can then use a correlated sub-query in your join, to pick which record in <code>Schedule</code> matches the <code>calendar</code>, <code>person</code> template you have created.</p> <pre><code>SELECT * FROM calendar CROSS JOIN person LEFT JOIN schedule ON schedule.name = person.name AND schedule.start_date = (SELECT MAX(start_date) FROM schedule WHERE name = person.name AND start_date <= calendar.date) WHERE calendar.date >= <yourStartDate> AND calendar.date <= <yourEndDate> etc </code></pre> <p><br/> Often, however, it is more efficient to deal with it in one of two other ways...</p> <p>Don't allow gaps in the data in the first place. Have a nightly batch process, or some other business logic that ensures all relevant dat apoints are populated.</p> <p>Or deal with it in your client. Return each dimension in you report (data, and name) as seperate data sets to act as your templates, and then return the data as your final data set. Your client can itterate over the data and fill in the blanks as appropriate. It's more code, but can actually use less resource overall than trying to fill-the-gaps with SQL.</p> <p><em>(If your client side code does this slowly, post another question examining that code. Provided that the data is sorted, this is acutally quite quick to do in most languages.)</em></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