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

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
Lorjust BayneSr. AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
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).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB Reporting Tools

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.