[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 214
  • Last Modified:

How can I expand a table row for each day within a range using VB.net 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 Bayne
Lorjust Bayne
2 Solutions
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).

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now