How can I expand a table row for each day within a range using 2012 and/or SQL?

Posted on 2014-08-22
Last Modified: 2015-02-07
This is probably a very basic issue, but I'm a noobie. I don't really know how much detail to give but here we go.

I want to take  Table A  and create Table B to show one row for each date within the Checkin/CheckOut date range in Table A.

Table A
CheckIn              CheckOut      Name
8/1/2014      8/5/2014      Watson, JB

Table B
CheckIn              CheckOut      Name               DateValue
8/1/2014      8/5/2014      Watson, JB        8/1/2014
8/1/2014      8/5/2014      Watson, JB        8/2/2014
8/1/2014      8/5/2014      Watson, JB        8/3/2014
8/1/2014      8/5/2014      Watson, JB       8/4/2014
8/1/2014      8/5/2014      Watson, JB        8/5/2014
Question by:Lorjust Bayne
    LVL 100

    Assisted Solution

    Are you building a report?

    If so you could create a formula to do that.

    Local StringVar strOut;
    Local DateVar DateIndex;
    For DateIndex := {Checkin} to {CheckOut} do
        strOut := strOut + CStr({CheckIN}) + "   " + CStr({CheckOut}) + "   "  + {Name} + "   " + CStr(DateIndex) + chr(13);

    Open in new window

    LVL 22

    Accepted Solution

    Create a CALENDAR table with one row for each date.  Join to that table with a condition of:
    CALENDAR.Cal_Date >= B.Checkin AND CALENDAR.Cal_Date < B.Checkout.

    If your connection method doesn't support this types of joins, remove the join and apply the condition to the record selection formula.

    By the way, a CALENDAR table is very useful for a variety of other reporting needs. It is routinely included in data warehouse designs (where we refer to it as the DATE Dimension).

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
    Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now