By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tableA(
[項目] NVARCHAR(5),
[廠商] NVARCHAR(5),
[購入日期] DATE
);
DECLARE @startDt DATE = '2019/04/26',
@endDt DATE = '2019/04/30'
INSERT INTO tableA VALUES (N'牙刷','A','2019/04/26');
INSERT INTO tableA VALUES (N'牙刷','A','2019/04/27');
INSERT INTO tableA VALUES (N'牙刷','B','2019/04/26');
INSERT INTO tableA VALUES (N'水壺','A','2019/04/30');
INSERT INTO tableA VALUES (N'水壺','B','2019/05/15');
WITH CTE AS (
SELECT @startDt startDt,@endDt endDt
UNION ALL
SELECT DATEADD(DAY,1,startDt),endDt
FROM CTE
WHERE DATEADD(DAY,1,startDt)<=endDt
),CTE2 AS(
SELECT 項目,廠商,startDt
FROM (
SELECT [項目],
[廠商],
COUNT(CASE WHEN [購入日期] BETWEEN @startDt and @endDt THEN 1 END) cnt
FROM tableA
GROUP BY [項目],[廠商]
HAVING COUNT(CASE WHEN [購入日期] BETWEEN @startDt and @endDt THEN 1 END) > 0
) t1 CROSS JOIN CTE
)
SELECT 項目,廠商,[購入日期]
FROM tableA
WHERE [購入日期] < @startDt OR [購入日期] > @endDt
| 項目 | 廠商 | 購入日期 |
|---|---|---|
| 水壺 | A | 2019-04-26 |
| 水壺 | A | 2019-04-27 |
| 水壺 | A | 2019-04-28 |
| 水壺 | A | 2019-04-29 |
| 水壺 | A | 2019-04-30 |
| 水壺 | B | 2019-05-15 |
| 牙刷 | A | 2019-04-26 |
| 牙刷 | A | 2019-04-27 |
| 牙刷 | A | 2019-04-28 |
| 牙刷 | A | 2019-04-29 |
| 牙刷 | A | 2019-04-30 |
| 牙刷 | B | 2019-04-26 |
| 牙刷 | B | 2019-04-27 |
| 牙刷 | B | 2019-04-28 |
| 牙刷 | B | 2019-04-29 |
| 牙刷 | B | 2019-04-30 |
Warning: Null value is eliminated by an aggregate or other SET operation.