Stored procedure two date parameters on how to insert this time table automatically records in the a-CodePudding

This time I query the 2020-7-1-2020-7-1, for example, an existing table A (USERID, FNAME) data are respectively 1001 zhang
1002 li si
I want to realize the query results are as follows:

1001 the 2020-7-1 morning
1001 the 2020-7-1
in the afternoon1001 the 2020-7-2 morning
1001 the 2020-7-2
in the afternoon,,,,,,,,,,,,,
1001 the 2020-7-13 morning
1001 the 2020-7-13
in the afternoon1002 the 2020-7-1 morning
1002 the 2020-7-1
in the afternoon1002 the 2020-7-2 morning
1002 the 2020-7-2
in the afternoon,,,,,,,,,,,,,
1002 the 2020-7-13 morning
1002 the 2020-7-13
in the afternoon

CodePudding user response:

 

The CREATE TABLE # T 
(USER_ID VARCHAR (4), 
FNAME NVARCHAR (10)) 

INSERT INTO # T 
SELECT '1001', 'zhang' UNION ALL 
SELECT '1002', 'bill' 

DECLARE @ the START_DATE DATE 
DECLARE @ END_DATE DATE 

The SET @ the START_DATE='2020-07-01' 
The SET @ END_DATE='2020-07-13' 


The SELECT USER_ID, DATEADD (DAY, NUMBER, @ the START_DATE) AS DT, DAY_PART 
FROM the MASTER. The DBO. SPT_VALUES A 
The JOIN 
(SELECT the 'morning' AS DAY_PART 
UNION ALL 
SELECT 'afternoon') AS B ON 1=1 
JOIN the AS C # T ON 1=1 
WHERE TYPE='P' AND NUMBER<=DATEDIFF (DAY @ the START_DATE, @ END_DATE) 
The ORDER BY USER_ID, DT