SQL - building timeline data?

[HungryHippos]

Bear with me here. I have a table that stores users, the users are incrementally merged fairly frequently to capture user creations/deletions over time, eventually days/weeks/months etc.

If a user has been deleted it marks the DeletedDate column in the table with the date of the sync, otherwise it leaves the column as NULL valued.

The CreatedDate and DeletedDate columns are both stored in MS SQL as Date values only, not DateTime.

I store the data somewhat like this example pic, only with more rows and more columns, but you hopefully may get the idea:



I would like to be able to query this data and track the changes over time. The tracked data should look something like this:



So each day it checks the table, any users not marked as deleted, created on or before the current day, should be added to the total count, also with summary values for Created/Deleted that day.

As you can see from my example, it would enable me to graph the changes over time which is something that would be pretty cool.

I could probably snapshot the data each day with another script, into another table, but if any execution is missed for that it would have no results to pull back, if I can query the raw incremental data it may be better in the long run.

I would leverage excel for this, but I want to put this on a web page, so need the raw query data really. Any ideas on how to achieve this anyone?

Thanks!

 

[HungryHippos]

Thanks Haircut will give it a go tomorrow

You are probably right, it may not be the most efficient way to store the data, but it does sort of work and my data set isn't too big. It may prove easier for me to simply have a script compiling the data every day into another table for querying though.

Wish I knew more about SQL and how best to store data/indexes etc, but not many people at work know more about it than I do

 

[HungryHippos]

I had to add the following line:

option (maxrecursion 0)

Onto the end but otherwise hit the nail on the head and worked first time! execution time was very quick (sub 1 sec) but then my data set isn't too big, only 2-3K rows at the moment.

Thanks dude!