troubleshooting Question

Create Day by Day Count of Clients

Avatar of PatHartman
PatHartmanFlag for United States of America asked on
Microsoft AccessMicrosoft SQL Server
23 Comments1 Solution97 ViewsLast Modified:
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.
Join our community to see this answer!
Unlock 1 Answer and 23 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 23 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros