Number of rows in a table between two dates

Question

Post reply

Number of rows in a table between two dates

astrid 69000

Hall of Fame

Points: 3208

December 17, 2020 at 4:03 pm

Go to Answer

#3821643

Hi,

I have a table that has dates and customers, I am trying to count the number of customers that signed up in the last hour up to that signed up. I have been trying to do a row_number count, but I am not sure how to add the "CREATE_DATE between 'CREATE_DATE and DATEADD(HOUR,-1,CREATE_DATE).

CREATE TABLE #CUSTOMERS
(
[CREATE_DATE] DATETIME,
CUSTOMERID INT
)


INSERT INTO #CUSTOMERS ([CREATE_DATE], CUSTOMERID) VALUES ('2020-01-31 16:59:07.000', 1001);
INSERT INTO #CUSTOMERS ([CREATE_DATE], CUSTOMERID) VALUES ('2020-01-31 16:59:07.000', 1002);
INSERT INTO #CUSTOMERS ([CREATE_DATE], CUSTOMERID) VALUES ('2020-01-31 16:59:07.000', 1003);
INSERT INTO #CUSTOMERS ([CREATE_DATE], CUSTOMERID) VALUES ('2020-01-31 16:59:07.000', 1004);
INSERT INTO #CUSTOMERS ([CREATE_DATE], CUSTOMERID) VALUES ('2020-01-31 12:34:01.000', 1005);
INSERT INTO #CUSTOMERS ([CREATE_DATE], CUSTOMERID) VALUES ('2020-01-31 12:34:01.000', 1006);
INSERT INTO #CUSTOMERS ([CREATE_DATE], CUSTOMERID) VALUES ('2020-01-31 13:10:52.000', 1007);
INSERT INTO #CUSTOMERS ([CREATE_DATE], CUSTOMERID) VALUES ('2020-01-31 13:11:19.000', 1008);
INSERT INTO #CUSTOMERS ([CREATE_DATE], CUSTOMERID) VALUES ('2020-01-31 13:12:54.000', 1009);
INSERT INTO #CUSTOMERS ([CREATE_DATE], CUSTOMERID) VALUES ('2020-01-31 13:13:08.000', 10010);
INSERT INTO #CUSTOMERS ([CREATE_DATE], CUSTOMERID) VALUES ('2020-01-31 13:13:39.000', 10011);

Mike01

SSChampion

Points: 11395
More actions
December 17, 2020 at 8:59 pm
Answer
#3821759
Something like this?
```
select CustomerID, Create_Date,
(select count(1)
from #Customers c1
where c1.Create_date between DateAdd(hour, -1, c.Create_date) and c.Create_date) as NumberOfSignups
--,Count(1)
from #Customers c
where c.Create_date between DateAdd(hour, -1, c.Create_date) and c.Create_date
order by 1
```
- This reply was modified 3 years, 11 months ago by Mike01.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 1

"I am trying to count the number of customers that signed up in the last hour "

select COUNT(*)
  from #CUSTOMERS
 where CREATE_DATE > DATEADD(HOUR,-1,CURRENT_TIMESTAMP)

astrid 69000 Hall of Fame Points: 3208 More actions · Answer 2

Thanks, but that is not what I am looking for.

The end result should be CREATE_DATE, CUSTOMERID, NUMBEROFSIGNUPS

And each NUMBEROFSIGNUPS will give you the # of signups that were created on that last hour.

I was trying to add a row count but I am not picturing how to do it with the ROW_NUMBER() OVER (PARTITION BY CREATE_DATE.. but I dont think that is the answer.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

It is not clear what your expected results are - can you show what you expect given the example data you provided?

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

astrid 69000 Hall of Fame Points: 3208 More actions · Answer 4

I apologize for not being clearer. I need to count the number of signups that occurred on the last hour of that signup.

For example if we look at this data, when customer 10011 signed up, there were 7 signed up on that time (I did it on excel so I didn't count seconds)

Capture

astrid 69000 Hall of Fame Points: 3208 More actions · Answer 5

astrid 69000

Hall of Fame

Points: 3208

December 17, 2020 at 9:03 pm

#3821766

Thanks to both of you, Mike01 it works, thanks!!