How to get newly inserted records in SQL Server per day

Question

I am using a SQL Server database, and I need to get the total number of newly inserted records per day, per week, per month, and per year separately. I have a column in my SQL Server database that records the date and time (the data type is datetime).

I used the following code to get the daily records but doesn't work

SQL:

select count(*) 
from dbo.Firsttimer 
where (Signed_in) = date(date_sub(now());

Please how do I achieve this?

Well, for starters, SQL Server has no functions called date, date_sub() or now(). What are they supposed to do? — user1443098, Aug 08 '18 at 14:11
First, what do you mean my *"doesn't work"*? Second, `date_sub` is a `mysql` function, for `sql server` try `dateadd` instead. Here is a similar question: https://stackoverflow.com/q/15619659/4730201 — Ricardo Pontual, Aug 08 '18 at 14:12
Please spend some time reading https://learn.microsoft.com/en-us/sql/t-sql/functions/date-and-time-data-types-and-functions-transact-sql?view=sql-server-2017 and working through the examples there. — user1443098, Aug 08 '18 at 14:19

Zohar Peled · Answer 1 · 2018-08-08T14:20:20.667

In SQL Server, you get the current DateTime value using the built in GetDate() method.

So to get all the records that have today's date in a specific DateTime column you can do something like this:

DECLARE @FromDate date = CAST(GETDATE() AS date)
DECLARE @ToDate date = DATEADD(DAY, 1, @FromDate)

SELECT COUNT(*) 
FROM dbo.Firsttimer 
WHERE [Signed_in] >= @FromDate
AND [Signed_in] < @ToDate

To get the first and last day of the week you can read this SO post,
first day and last day of the month on this SO post,
and first and last day of the year on this SO post

score 0 · Answer 2 · answered Aug 08 '18 at 14:20

You'll need to use conditional aggregation, i.e. a count(case...), along with the getdate() and dateadd() functions.

select
    Today = cast(getdate() as date)
    ,TodayCount = count(case when cast(Signed_in as date) = cast(getdate() as date) then Signed_in)
    ,RollingWeekCount = count(case when cast(Signed_in as date) >= dateadd(day,-7,cast(getdate() as date)) then Signed_in)
    ,Rolling30Days = count(case when cast(Signed_in as date) >= dateadd(day,-30,cast(getdate() as date)) then Signed_in)
    ,Rolling365Days = count(case when cast(Signed_in as date) >= dateadd(day,-365,cast(getdate() as date)) then Signed_in)
from
    YourTable

If you wanted this split out for each week, each month, each year... then that's best handled another way. However you didn't specify this so I provided a rolling method.

How to get newly inserted records in SQL Server per day

2 Answers2