I have a table by the name Roster:
| Username | Roster Date |
|---|---|
| Staff1 | 2022-09-12 |
| Staff1 | 2022-09-13 |
| Staff2 | 2022-09-30 |
| Staff3 | 2022-10-01 |
| Staff3 | 2022-10-02 |
| Staff3 | 2022-09-11 |
I need a query that extracts username with roster >= 2022-09-12 and insert into another table by the name paycode_value_compute that has the following columns:
| Username | From Date | To Date |
|---|
The from date and to date should be similar. The dates should range from 12th September onwards until Max(Roster Date) of the respective username. The final outcome should be something per the following:
| Username | From Date | To Date |
|---|---|---|
| Staff1 | 2022-09-12 | 2022-09-12 |
| Staff1 | 2022-09-13 | 2022-09-13 |
| Staff2 | 2022-09-30 | 2022-09-30 |
| Staff3 | 2022-10-01 | 2022-10-01 |
| Staff3 | 2022-10-02 | 2022-10-02 |
Could someone assist me on this?
CodePudding user response：
INSERT INTO PAYCODE_VALUE
SELECT Username, [Roster Date], [Roster Date]
FROM Roster
WHERE [Roster Date] >= '2022-09-12'
CodePudding user response：
INSERT INTO [dbo].[PayCode_Value_Compute]
( Username, [From Date], [To Date] )
SELECT [Username], [Roster Date], [Roster Date]
FROM [dbo].[Roster]
WHERE [Roster Date] >= '2022-09-12'
Btw: Don't use spaces in the column names.