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: 3203

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: 11381
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, 9 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: 17601 More actions · Answer 1

Jonathan AC Roberts

SSCoach

Points: 17601

December 17, 2020 at 4:59 pm

#3821668

"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: 3203 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.