Consider the query below

DECLARE @DateTimeStart DATETIME
DECLARE @DateTimeEnd DATETIME

SET @dateTimeStart = '2001-04-04'
SET @dateTimeEnd = '2001-04-06'

DECLARE @QH TABLE 
            (
                dt date, 
                QueueName varchar(10)
            )

INSERT INTO @QH (Dt, QueueName)
VALUES ('2001-04-04', 'Queue01'),
       ('2001-04-05', 'Queue01'),
       ('2001-04-06', 'Queue01'),
       ('2001-04-04', 'Queue02'),
       ('2001-04-05', 'Queue02'),
       ('2001-04-06', 'Queue02')

-- SELECT * FROM @QH

DECLARE @SNH TABLE
             (
                 dt date, 
                 QueueName varchar(10),
                 SN varchar(10)
             )

INSERT INTO @SNH (Dt, QueueName, SN)
VALUES ('2001-04-04', 'Queue01', 'Q01SN01'),
       ('2001-04-05', 'Queue01', 'Q01SN01'),
       ('2001-04-06', 'Queue01', 'Q01SN01'),
       ('2001-04-04', 'Queue02', 'Q02SN01'),
       ('2001-04-05', 'Queue02', 'Q02SN01'),
       ('2001-04-06', 'Queue02', 'Q02SN02')

-- SELECT * FROM @SNH

SELECT
    MIN(QHCore.dt) clnStartDate, 
    MAX(QHCore.dt) clnEndDate, 
    QHCore.QueueName AS clnQueueName, 
    SNHStart.SN AS clnSNStart, 
    SNHEnd.SN AS clnSNEnd
FROM    
    @QH QHCore, @QH QHStart, @QH QHEnd, @SNH SNHStart, @SNH SNHEnd
WHERE
    QHCore.QueueName = QHStart.QueueName
    AND QHCore.QueueName = QHEnd.QueueName
    AND QHCore.QueueName = SNHStart.QueueName
    AND QHCore.QueueName = SNHEnd.QueueName
    AND SNHStart.dt = @DateTimeStart
    AND SNHEnd.dt = @DateTimeEnd
GROUP BY 
    QHCore.QueueName, SNHStart.SN, SNHEnd.SN

When @DateTimeStart and @DateTimeEnd matches those existing in @SNH table, it selects into nice looking table,

but what if there's no corresponding entry in @SNH as below

insert into @SNH(Dt, QueueName, SN)
values
--('2001-04-04','Queue01','Q01SN01'),
('2001-04-05','Queue01','Q01SN01'),
('2001-04-06','Queue01','Q01SN01'),
('2001-04-04','Queue02','Q02SN01'),
('2001-04-05','Queue02','Q02SN01'),
('2001-04-06','Queue02','Q02SN02')

then the entire line is not selected.

So, is there a way to Select into the resulting table empty value somehow?

For it to look as follows

CodePudding user response：

I think that a LEFT join of @QH to @SNH and window functions will do what you want.

It is not clear whether you want the date limits to be applied on both tables or just on @SNH.

If you want all the dates of @QH, even if they are not between the date limits:

SELECT DISTINCT
       MIN(q.Dt) OVER (PARTITION BY q.QueueName) clnStartDate,
       MAX(q.Dt) OVER (PARTITION BY q.QueueName) clnEndDate,
       q.QueueName clnQueueName,    
       FIRST_VALUE(s.SN) OVER (PARTITION BY q.QueueName ORDER BY q.Dt) clnSNStart,
       FIRST_VALUE(s.SN) OVER (PARTITION BY q.QueueName ORDER BY q.Dt DESC) clnSNEnd
FROM @QH q LEFT JOIN @SNH s
ON s.QueueName = q.QueueName AND s.Dt = q.Dt
AND s.Dt BETWEEN @DateTimeStart AND @DateTimeEnd;

If you want to apply the limits to both tables:

SELECT DISTINCT
       MIN(q.Dt) OVER (PARTITION BY q.QueueName) clnStartDate,
       MAX(q.Dt) OVER (PARTITION BY q.QueueName) clnEndDate,
       q.QueueName clnQueueName,    
       FIRST_VALUE(s.SN) OVER (PARTITION BY q.QueueName ORDER BY q.Dt) clnSNStart,
       FIRST_VALUE(s.SN) OVER (PARTITION BY q.QueueName ORDER BY q.Dt DESC) clnSNEnd
FROM @QH q LEFT JOIN @SNH s
ON s.QueueName = q.QueueName AND s.Dt = q.Dt
WHERE q.Dt BETWEEN @DateTimeStart AND @DateTimeEnd;