SQL query for user counts

[HungryHippos]

Hello good citizens of OCUK,

I've got a SQL table that contains information about users in different accounts/companies. Each night a script runs against AD, finds all of the users, and then looks at the table data in SQL and does the following:

• If the user is a new user it inserts a new row for it.
• If the user was in SQL the night before, but is now gone, it tags the DeletedDate column with the current date.
• If the user still exists it just updates the row with any new/current info.

So I'd like to be able to step back in time to see how many users a particular company had on any given date. I think I can do that with the data I have been building/collecting.

The Users table in SQL has the following columns for this purpose:

[CompanyName] [nvarchar](255) NULL
[createTimeStamp] [datetime] NULL
[DeletedDate] [date] NULL

I've come up with this script, can anyone think of a better/more efficient way of pulling this query data?

I think it works OK but I am not a SQL god or anything, and SQL can do a lot I am not aware about

-- Temp Table
CREATE TABLE #UserCounts
(
	[Date] date,
	[UserCount] int
);

-- How many days to go back
DECLARE @DayCount int;
SET @DayCount = -30;

-- Company Name
DECLARE @CompanyName varchar(255);
SET @CompanyName = 'Test Company'

-- Loop through X days
WHILE (@DayCount < 0)
BEGIN
	-- Increment Date var
	SET @DayCount = @DayCount + 1;
	
	-- Get Current Date positionally
	DECLARE @CurrentDate date;
	SET @CurrentDate = CAST(DATEADD(d, @DayCount, GETDATE()) AS date);

	-- Insert Current Date values into Temp Table
	INSERT INTO #UserCounts
	SELECT
	@CurrentDate AS [Date],
	COUNT (createTimeStamp) AS [UserCount]
	FROM Users
	WHERE CompanyName = @CompanyName
	AND CAST(createTimeStamp AS date) <= @CurrentDate
	AND (DeletedDate IS NULL OR DeletedDate > @CurrentDate)
END;

Essentially my thinking is that for any given date I am going back through, as long as the createTimeStamp (taken from AD) is the current date or less, and as long as the DeletedDate field is either NULL (still exists) or greater than the current date (deleted on a later date) then I can do a count of those users in that criteria to get the number of users who were still present on that date.

 

[fretted]

Do you need to build a table for every day in the last 30 days? or do you only need to know how many users on a specific day?

 

[HungryHippos]

Do you need to build a table for every day in the last 30 days? or do you only need to know how many users on a specific day?

No I just reference the original table, but store the results in a new table, in this case a temp table is OK for testing.

For specific dates nope but I'd like to be able to chart the data on a bar chart for the last 30 days for example so running totals for each day works for me, by passing that in as a param I could theoretically change the time frame going back pretty easily.

The query I've posted seems to work OK my question is more about if the approach I've used is the best approach for this task?