We help IT Professionals succeed at work.
Get Started

Create Day by Day Count of Clients

Last Modified: 2016-03-17
I currently have a VBA code loop that does this for me (so technically I have a working solution) but I'm hoping that there is an SQL solution that might be faster.  I have about 1 million client records. They have a ServiceStartDate and a ServiceEndDate.  The ServiceEndDate will be null if the service is still active.  What I need is a recordset that contains 1 row for every day since around 1/1/09 when the company started showing the number of accounts active that day by UtilityID.  Nice to have would also be the number of accounts added that day and accounts dropped that day.  So input would be.
UtilityID, Acct #, ServiceStartDate, ServiceEndDate
1, abc, 1/1/9, 1/5/9
1, aaa, 1/3/9
1, aab, 1/3/9,

output would be
UtilityID, CalendarDate, Active, Added, Dropped
1, 1/1/9, 1,1,0
1, 1/2/9, 1, 0, 0
1, 1/3/9, 3, 2, 0
1, 1/4/9, 3, 0, 0
1, 1/5/9, 2,0, 1
1, 1/6/9, 2, 0, 0
So just taking UtilityID 1 for 3 accounts over 6 days, this is what I end up with.  Every day, I have a count of the number of active accounts.  Added and dropped if you can calculate it at the same time would be good but otherwise, it is pretty easy to calculate by summing the recordset by start and end dates so I can add that later.  It is counting by day that is the problem.

I have tried this by joining to a table of days.  There are about 3500 days in this time frame but a Cartesian Product of 1,000,000 x 3500 is huge and way too slow.  I am hoping there is some slick T-SQL syntax that can generate these intermediate records.  The Cartesian Product method works fine for smaller recordsets but fails for one of this size.  The current code loop does the process one day at a time.
Watch Question
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
This problem has been solved!
Unlock 1 Answer and 23 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE