The caveats are: you have to run this on SQL Server 2005 or newer to use the WITH keyword, and if any individual date range is more than 100 days, you’ll get an error by default [you can use OPTION (MAXRECURSION value) to increase that limit]. UPDATE: I’ve added a WHERE clause to prevent the error related to the default recursion limit. This change will leave out date ranges larger than 100 days, which is fine for me because anything larger than two weeks would be a data entry error in my situation.

DECLARE @dateranges TABLE (range_id VARCHAR(2), date_begin DATETIME, date_end DATETIME)
INSERT @dateranges SELECT 'A', '2010-01-01', '2010-01-03'
INSERT @dateranges SELECT 'B', '2008-02-27', '2008-03-01'
INSERT @dateranges SELECT 'C', '2010-04-26', '2010-04-26'
INSERT @dateranges SELECT 'D', '2000-02-01', '2003-02-20'

;WITH cte (id, d)
     AS (SELECT tbl.range_id AS id
                ,tbl.date_begin AS d
           FROM @dateranges tbl
          WHERE DATEDIFF(DAY, tbl.date_begin, tbl.date_end) <= 100
         UNION ALL
         SELECT tbl.range_id AS id
                ,DATEADD(DAY, 1, cte.d) AS d
           FROM cte
                INNER JOIN @dateranges tbl
                  ON cte.id = tbl.range_id
          WHERE cte.d < tbl.date_end)
SELECT id AS range_id
       ,d AS date_within_range
  FROM cte
 ORDER BY id, d

You start with this:

and end up with this:

Notice that “D” is filtered out of these results.

AssemblyInfo.cs (partial)

// Styles
[assembly: WebResource("GizmonicInstitute.WebControls.SampleControl.css", "text/css", PerformSubstitution = true)]

// Images
[assembly: WebResource("GizmonicInstitute.WebControls.cog.png", "image/png")]
[assembly: WebResource("GizmonicInstitute.WebControls.inner_shadow.png", "image/png")]

SampleControl.css

.SampleControl_TextBox {
 padding: 4px 0 0 4px;
 height: 1.5em;
 border: solid 1px Black;
 background-color: Yellow;
 background-image: url('<%=WebResource("GizmonicInstitute.WebControls.inner_shadow.png")%>');
 background-repeat: no-repeat;
 background-position: left top;
}

.SampleControl_Button {
 width: 2em;
 height: 2em;
 border-width: 2px;
 background-color: #EAEAEA;
 background-image: url('<%=WebResource("GizmonicInstitute.WebControls.cog.png")%>');
 background-repeat: no-repeat;
 background-position: center center;
}

SampleControl.cs

1    #region Using directives
2    using System;
3    using System.Collections.Generic;
4    using System.ComponentModel;
5    using System.Text;
6    using System.Web;
7    using System.Web.UI;
8    using System.Web.UI.HtmlControls;
9    using System.Web.UI.WebControls;
10   #endregion
11   
12   namespace GizmonicInstitute.WebControls {
13   
14       [Designer(typeof(SampleControlDesigner))]
15       [DefaultProperty("TextBoxText")]
16       [ToolboxData("<{0}:SampleControl runat=server>")]
17       public class SampleControl : CompositeControl {
18   
19           protected Label lblSample;
20           protected TextBox txtSample;
21           protected Button btnSample;
22   
23           [Bindable(true)]
24           [Category("Appearance")]
25           [DefaultValue("")]
26           [Description("Gets or sets the text caption displayed in the Button control.")]
27           public string LabelText {
28               get {
29                   object o = ViewState["LabelText"];
30                   return (o == null) ? String.Empty : (string)o;
31               }
32               set {
33                   ViewState["LabelText"] = value;
34                   RecreateChildControls();
35               }
36           }
37   
38           [Bindable(true)]
39           [Category("Appearance")]
40           [DefaultValue("")]
41           [Description("Gets or sets the text caption displayed in the Button control.")]
42           public string TextBoxText {
43               get {
44                   object o = ViewState["TextBoxText"];
45                   return (o == null) ? String.Empty : (string)o;
46               }
47               set {
48                   ViewState["TextBoxText"] = value;
49                   RecreateChildControls();
50               }
51           }
52   
53           [Bindable(true)]
54           [Category("Appearance")]
55           [DefaultValue("")]
56           [Description("Gets or sets the text caption displayed in the Button control.")]
57           public string ButtonText {
58               get {
59                   object o = ViewState["ButtonText"];
60                   return (o == null) ? String.Empty : (string)o;
61               }
62               set {
63                   ViewState["ButtonText"] = value;
64                   RecreateChildControls();
65               }
66           }
67   
68           protected void btnSample_Click(Object sender, EventArgs e) {
69               txtSample.Text = String.Format("Clicked at {0:h:mm:ss tt}", DateTime.Now );
70           }
71   
72           protected override void CreateChildControls() {
73               lblSample = new Label();
74               txtSample = new TextBox();
75               btnSample = new Button();
76   
77               lblSample.Text = this.LabelText;
78               txtSample.Text = this.TextBoxText;
79               btnSample.Text = this.ButtonText;
80   
81               txtSample.CssClass = "SampleControl_TextBox";
82               btnSample.CssClass = "SampleControl_Button";
83               btnSample.Click += new EventHandler(btnSample_Click);
84   
85               Controls.Add(lblSample);
86               Controls.Add(txtSample);
87               Controls.Add(btnSample);
88           }
89   
90           protected override void OnPreRender(EventArgs e) {
91               // Add embedded style sheet to parent page
92               HtmlLink cssLink = new HtmlLink();
93               cssLink.ID = "SampleControlCss";
94               cssLink.Href = Page.ClientScript.GetWebResourceUrl(this.GetType(), "GizmonicInstitute.WebControls.SampleControl.css");
95               cssLink.Attributes.Add("rel", "stylesheet");
96               cssLink.Attributes.Add("type", "text/css");
97               bool CssLinkAlreadyExists = false;
98               foreach (Control headctrl in Page.Header.Controls) {
99                   if (headctrl.ID == cssLink.ID) {
100                      CssLinkAlreadyExists = true;
101                      break;
102                  }
103              }
104              if (!CssLinkAlreadyExists) {
105                  Page.Header.Controls.AddAt(0, cssLink);
106              }
107          }
108  
109      }
110  }

SampleControlDesigner.cs

1    #region Using directives
2    using System;
3    using System.Web.UI;
4    using System.Text.RegularExpressions;
5    #endregion
6    
7    namespace GizmonicInstitute.WebControls {
8    
9        public class SampleControlDesigner : System.Web.UI.Design.ControlDesigner {
10           public override string GetDesignTimeHtml() {
11               System.Reflection.Assembly l_asm = System.Reflection.Assembly.GetExecutingAssembly();
12               System.IO.Stream l_css_res = l_asm.GetManifestResourceStream("GizmonicInstitute.WebControls.SampleControl.css");
13               System.IO.StreamReader l_stream = new System.IO.StreamReader(l_css_res);
14               string ls_css = l_stream.ReadToEnd();
15   
16               const string SUBSTITUTION_PATTERN = @"(<%=)\s*(WebResource\("")(?.+)\s*(""\)%>)";
17               Regex theRegex = new Regex(SUBSTITUTION_PATTERN);
18               ls_css = theRegex.Replace(ls_css, new MatchEvaluator(PerformSubstitution));
19   
20               return "" + base.GetDesignTimeHtml();
21           }
22   
23           private string PerformSubstitution(Match m) {
24               SampleControl thisWeightControl = (SampleControl)Component;
25               string replacedString = m.ToString();
26               replacedString = replacedString.Replace(m.Value, thisWeightControl.Page.ClientScript.GetWebResourceUrl(this.GetType(), m.Groups["resourceName"].Value));
27               return replacedString;
28           }
29       }
30   
31   }