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!

 

[Haircut]

This should do the trick in SQL Server

with dateRange as
(
	select case when min(CreatedDate) < min(DeletedDate) then min(CreatedDate) else min(DeletedDate) end minDate,
		   case when max(CreatedDate) > max(DeletedDate) then max(CreatedDate) else max(DeletedDate) end maxDate
	from users
),
dates as
(
    select convert(datetime, (select minDate from dateRange)) as dt
    union all
    select dateadd(day, 1, dt)
    from dates
    where dt < (select maxDate from dateRange)
)
select dt "Date",
	  (select count(*) from users u where u.CreatedDate <= dt and ISNULL(u.DeletedDate, '31-Dec-9999') > dt) "UserCount",
	  (select count(*) from users u where u.CreatedDate = dt) "CreatedCount",
	  (select count(*) from users u where u.DeletedDate = dt) "DeletedCount"
from dates

I'm not making any claims about how efficient it is though
I would also argue that it's possibly not the thing that should be done as a SQL query either.

If you are set on doing this in SQL then I'd suggest redesigning how the data is stored.
You're not doing yourself any favours with how the table structure is at present.
It would be easier to query this sort of historical data if it was modelled as a slowly changing dimension - probably a type 2 is what you want for this.

http://en.wikipedia.org/wiki/Slowly_changing_dimension#Type_2

 

[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!

 

[Haircut]

Cool, glad it (almost) worked straight away.