I have three tables and a range of two dates:
Services
ServicesClients
ServicesClientsDone
@StartDate
@EndDate
Services:
ID | Name
1 | Supervisor
2 | Monitor
3 | Manufacturer
ServicesClients:
IDServiceClient | IDClient | IDService
1 | 1 | 1
2 | 1 | 2
3 | 2 | 2
4 | 2 | 3
ServicesClientsDone:
IDServiceClient | Period
1 | 201208
3 | 201210
Period = YYYYMM
I need to insert into ServicesClientsDone the months range from @StartDate up @EndDate. I have also a temporary table (#Periods) with the following list:
Period
201208
201209
201210
The query I need is to give me back the following list:
IDServiceClient | Period
1 | 201209
1 | 201210
2 | 201208
2 | 201209
2 | 201210
3 | 201208
3 | 201209
4 | 201208
4 | 201209
4 | 201210
Which are client services but the ranks of the temporary table, not those who are already inserted
This is what i have:
Table periods:
DECLARE @i int
DECLARE @mm int
DECLARE @yyyy int,
DECLARE @StartDate datetime
DECLARE @EndDate datetime
set @EndDate = (SELECT GETDATE())
set @StartDate = (SELECT DATEADD(MONTH, -3,GETDATE()))
CREATE TABLE #Periods (Period int)
set @i = 0
WHILE @i <= DATEDIFF(MONTH, @StartDate , @EndDate )
BEGIN
SET @mm= DATEPART(MONTH, DATEADD(MONTH, @i, @FechaInicio))
SET @yyyy= DATEPART(YEAR, DATEADD(MONTH, @i, @FechaInicio))
INSERT INTO #Periods (Period)
VALUES (CAST(@yyyy as varchar(4)) + RIGHT('00'+CONVERT(varchar(6), @mm), 2))
SET @i = @i + 1;
END
Relation between ServicesClients and Services:
SELECT s.Name, sc.IDClient FROM Services
JOIN ServicesClients AS sc
ON sc.IDService = s.ID
Services already done and when:
SELECT s.Name, scd.Period FROM Services
JOIN ServicesClients AS sc
ON sc.IDService = s.ID
JOIN ServicesClientsDone AS scd
ON scd.IDServiceClient = sc.IDServiceClient
