SQL problem could you tell me how to query a time period there were many events-CodePudding

A person (open_id) bought a member has a start date (the start_date) and (end_date) every day during the call active member now every day I want to find an active member of the how should find

Finally want to form table

CodePudding user response:

 select 
Time='2020-9-16' 
, effective membership=count (open_id) 
From A 
Where the start_date & gt; And end_date='2020-9-16' & lt;

='2020-9-16'

CodePudding user response:

reference 1/f, raccoon dog cat ga response:

 select 
Time='2020-9-16' 
, effective membership=count (open_id) 
From A 
Where the start_date & gt; And end_date='2020-9-16' & lt;='2020-9-16'

This I need to see a day in class number is constantly change the filter

CodePudding user response:

 
The CREATE TABLE # T 
(ID VARCHAR (10), 
STARTDATE DATE, 
ENDDATE DATE) 

INSERT INTO # T 
SELECT the 'A', '2020-08-15', '2020-09-16' UNION ALL 
SELECT 'B', '2020-07-10', '2020-10-20' UNION ALL 
SELECT the 'C', '2020-10-25', '2020-10-26' 

The SELECT SINGLE_DAY, COUNT (*) AS AMOUNT_MEMBER 
The FROM 
(SELECT ID, DATEADD (DAY, NUMBER, STARTDATE) AS SINGLE_DAY FROM # T A 
JOIN the MASTER. The DBO. SPT_VALUES B ON NUMBER<=DATEDIFF (DAY, STARTDATE, ENDDATE) 
AND TYPE="P") AS A 
GROUP BY SINGLE_DAY 
The ORDER BY SINGLE_DAY