Help Using Calendar Table to get records dated in next X working days

Transact-SQL
1

I have a very simple query but need to apply some date calculation logic to the where clause. I also have a calendar table which I believe may help but never having used it before, I'm not quite sure how to do so. The query is like this

SELECT CONTNO, ITEMNO, HIREDATE
FROM CONTITEMS

HIREDATE is a datetime field and I need to end up with 2 queries to obtain the following:

  1. where HIREDATE = the next working day after today (so if today was 11/01/2019 then it would return records where HIREDATE was 14/01/2019)
  2. where HIREDATE is in the next 5 working days.(so if today was 10/01/2019, it would return records where the HIREDATE was 11, 14, 15, 16 and 17/01/2019)

The Calendar table looks like this: CalTable
and has a column called CalWork to denote if a day is working date or not. But exactly how to join and get what I need I'm not sure so any help would be appreciated.

Many thanks
Martyn

2

something like this? It would help if you provided ddl and sample data as well as expected results.

drop table if exists #c
go
Drop table if exists #h
go

Create table #h
(ContItem int,
 HireDate date)

create table #c
(CalDate Date,
 CalWork bit)

insert into #c values 
('1/7/2019',1),
('1/8/2019',1),
('1/9/2019',1),
('1/10/2019',1),
('1/11/2019',1),
('1/12/2019',0),
('1/13/2019',0),
('1/14/2019',1),
('1/15/2019',1),
('1/16/2019',1),
('1/17/2019',1),
('1/18/2019',1),
('1/19/2019',1),
('1/20/2019',1),
('1/21/2019',1),
('1/22/2019',1),
('1/23/2019',1),
('1/24/2019',1),
('1/25/2019',1),
('1/26/2019',1),
('1/27/2019',1)

insert into #h values
(1, '1/11/2019'),
(2, '1/10/2019')

select *, (select min(CalDate) from #c c
			where c.CalDate > h.HireDate
			  and c.CalWork = 1) as NextWorkDate
, STUFF( 
        (       SELECT top 5 ',' + Cast(CalDate as varchar(1000)) 
		          FROM #C C
				 WHERE CalWork = 1
				  and CalDate > h.Hiredate
				  order by CalDate
				for XML PATH('')
		),
		1,1,'') as Next5Days
  from #h h
3

Hi, thanks for reply, it returns all rows though from the CONTNO table which is not what I need. please see some scripts below to create the hire table and the calendar table:

INSERT INTO CONTITEMS (CONTNO, ITEMNO, HIREDATE)
VALUES ('0000370768', 'GRADING', '2019-01-10 00:00:00.000')

INSERT INTO CONTITEMS (CONTNO, ITEMNO, HIREDATE)
VALUES ('0000370768', 'TLBUCKET24', '2019-01-10 00:00:00.000')

INSERT INTO CONTITEMS (CONTNO, ITEMNO, HIREDATE)
VALUES ('0000370768', 'TLBUCKET30', '2019-01-10 00:00:00.000')

INSERT INTO CONTITEMS (CONTNO, ITEMNO, HIREDATE)
VALUES ('0000370768', 'TLBUCKET42', '2019-01-10 00:00:00.000')

INSERT INTO CONTITEMS (CONTNO, ITEMNO, HIREDATE)
VALUES ('0000353555', '1030/031', '2019-01-15 00:00:00.000')

INSERT INTO CONTITEMS (CONTNO, ITEMNO, HIREDATE)
VALUES ('0000370785', 'RUBTRACKS13', '2019-01-15 00:00:00.000')

INSERT INTO CONTITEMS (CONTNO, ITEMNO, HIREDATE)
VALUES ('0000370785', 'GRADING', '2019-01-16 00:00:00.000')

INSERT INTO CONTITEMS (CONTNO, ITEMNO, HIREDATE)
VALUES ('0000370785', 'TLBUCKET24', '2019-01-16 00:00:00.000')

INSERT INTO CONTITEMS (CONTNO, ITEMNO, HIREDATE)
VALUES ('0000370785', 'TLBUCKET42', '2019-01-16 00:00:00.000')

INSERT INTO CONTITEMS (CONTNO, ITEMNO, HIREDATE)
VALUES ('0000370847', '1040/170', '2019-01-18 00:00:00.000');


 CREATE TABLE Calendar (CalCode VARCHAR(2) NOT NULL PRIMARY KEY, CalDate DATE NOT NULL, CalWork BIT NOT NULL, CalName VARCHAR(9) NOT NULL, CalDay INT NOT NULL);

INSERT INTO Calendar (CalCode, CalDate, CalWork, CalName, CalDay)
VALUES ('BH', '2019-01-10', 1, 'Thursday', 10);

INSERT INTO Calendar (CalCode, CalDate, CalWork, CalName, CalDay)
VALUES ('BH', '2019-01-11', 1, 'Friday', 11);

INSERT INTO Calendar (CalCode, CalDate, CalWork, CalName, CalDay)
VALUES ('BH', '2019-01-12', 0, 'Saturday', 12);

INSERT INTO Calendar (CalCode, CalDate, CalWork, CalName, CalDay)
VALUES ('BH', '2019-01-13', 0, 'Sunday', 13);

INSERT INTO Calendar (CalCode, CalDate, CalWork, CalName, CalDay)
VALUES ('BH', '2019-01-14', 1, 'Monday', 14);

INSERT INTO Calendar (CalCode, CalDate, CalWork, CalName, CalDay)
VALUES ('BH', '2019-01-15', 1, 'Tuesday', 15);

INSERT INTO Calendar (CalCode, CalDate, CalWork, CalName, CalDay)
VALUES ('BH', '2019-01-16', 1, 'Wednesday', 16);

INSERT INTO Calendar (CalCode, CalDate, CalWork, CalName, CalDay)
VALUES ('BH', '2019-01-17', 1, 'Thursday', 17);

INSERT INTO Calendar (CalCode, CalDate, CalWork, CalName, CalDay)
VALUES ('BH', '2019-01-18', 1, 'Friday', 18);

INSERT INTO Calendar (CalCode, CalDate, CalWork, CalName, CalDay)
VALUES ('BH', '2019-01-19', 0, 'Saturday', 19);

For the first query, I want to return the records for the next working day so the output would be:

+------------+------------+-------------------------+
|   CONTNO   |   ITEMNO   |        HIREDATE         |
+------------+------------+-------------------------+
| 0000370768 | TLBUCKET42 | 2019-01-10 00:00:00.000 |
| 0000370768 | GRADING    | 2019-01-10 00:00:00.000 |
| 0000370768 | TLBUCKET24 | 2019-01-10 00:00:00.000 |
| 0000370768 | TLBUCKET30 | 2019-01-10 00:00:00.000 |
+------------+------------+-------------------------+

For the second query, I want to see the records for the next 5 working days so the result would be:

+------------+-------------+-------------------------+
|   CONTNO   |   ITEMNO    |        HIREDATE         |
+------------+-------------+-------------------------+
| 0000353555 | 1030/031    | 2019-01-15 00:00:00.000 |
| 0000370768 | TLBUCKET42  | 2019-01-10 00:00:00.000 |
| 0000370768 | GRADING     | 2019-01-10 00:00:00.000 |
| 0000370768 | TLBUCKET24  | 2019-01-10 00:00:00.000 |
| 0000370768 | TLBUCKET30  | 2019-01-10 00:00:00.000 |
| 0000370785 | GRADING     | 2019-01-16 00:00:00.000 |
| 0000370785 | RUBTRACKS13 | 2019-01-15 00:00:00.000 |
| 0000370785 | TLBUCKET42  | 2019-01-16 00:00:00.000 |
| 0000370785 | TLBUCKET24  | 2019-01-16 00:00:00.000 |
+------------+-------------+-------------------------+

The record for OONTNO 0000370847 should not show as it is outside the next 5 working days.

Thanks
Martyn

4

You can actually do either with a single query, just change the number of workdays to list.

DECLARE @number_of_workdays int
SET @number_of_workdays = 1 --or 5

SELECT CONTNO, ITEMNO, HIREDATE
FROM dbo.CONTITEMS
WHERE HIREDATE IN (
    --DECLARE @number_of_workdays int = 5
    SELECT TOP (@number_of_workdays) CalDate
    FROM dbo.Calendar 
    WHERE CalDate > GETDATE() AND CalWork = 1
    ORDER BY CalDate
    )
1 Like
5

Indeed it is, and it works perfectly - thank you!