Tsql query for missing days numbers

Question

Hi All,

I have a requirement like 7 days(week), it creates table day1- A1 and day2 - A2

day3- A3 ........................................ day7 - A7.

I have a job to update this info in one table. it stores info failed day number.

If any day fails to create table i want to find the day numbers.

Ex: if the job fails at day2 - A2 and today day7 - A7

output: 3,4,5,6,7

if the job fails at day6 - A6 and today day5 - A5

output : 7,1,2,3,4,5,6

Answer 1

CREATE TABLE SAMPLE (ID INT,FIRST INT,LAST INT)
INSERT INTO SAMPLE VALUES(1,4,7)
INSERT INTO SAMPLE VALUES(2,1,3) 
INSERT INTO SAMPLE VALUES(3,4,6) 
go
WITH onetoseven AS (
   SELECT n
   FROM   (VALUES(1), (2), (3), (4), (5), (6), (7)) AS N(n)
)
SELECT ID, string_agg(convert(varchar(1), n), ',')
FROM   SAMPLE S
JOIN   onetoseven o ON o.n NOT BETWEEN FIRST AND LAST
GROUP  BY ID
go
DROP TABLE SAMPLE

Answer 2

Hi All,

table structure:

Create table sample (Id int ,first int, last int)

insert into sample values (1,3,5)

output: 6,7,1,2

insert into sample values (2,4,2)

output: 3

insert into sample values (3,5,1)

output: 2,3,4

Thanks in Advance.

Answer 3

(Answer deleted)

Answer 4

Check some experimental queries:

select s.Id, string_agg((t.d - 1) % 7 + 1, ',') within group (order by t.d) as d
from sample s
cross apply (values (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13)) t(d)
where 
	s.last > s.first and t.d between s.last + 1 and s.first + 7 - 1
or  s.last < s.first and t.d between s.last + 1 and s.first - 1
group by s.Id

or:

select s.id, (t.d - 1) % 7 + 1 as d
from sample s
cross apply (values (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13)) t(d)
where 
	s.last > s.first and t.d between s.last + 1 and s.first + 7 - 1
or  s.last < s.first and t.d between s.last + 1 and s.first - 1
order by s.Id, t.d