SQL Server All dates returned even other column data is absent

d8tt03nd 于 2023-03-17 发布在其他

关注(0)|答案(1)|浏览(152)

This is the script and data

CREATE TABLE TB_TEST (
    ManDate date,
    Amount float
)    

INSERT INTO TB_Test Values ('2023-Mar-14', 13.0)
INSERT INTO TB_Test Values ('2023-Mar-13', 13.5)
INSERT INTO TB_Test Values ('2023-Mar-10', 12.8)
INSERT INTO TB_Test Values ('2023-Mar-8', 14.6)

And the query returns data

SELECT Mandate, Amount 
FROM TB_TEST

However, if my client does not make any products on some dates, they want to show the no production dates with Amount = 0 like rows in yellow as below:

This means that the Mandate column shows everyday until the current date.

Thank you in advance

sql-server

来源：https://stackoverflow.com/questions/75750907/all-dates-returned-even-other-column-data-is-absent

1条答案

按热度按时间

eyh26e7m1#

If you have a date range for the query, then you could use a CTE to generate the dates that will have no products. If I modify an example I've found here - https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/ - the solution would look like this:

DECLARE @StartDate  date = '20230101';

DECLARE @CutoffDate date = '20230315';

;WITH seq(n) AS 
(
  SELECT 0 UNION ALL SELECT n + 1 FROM seq
  WHERE n < DATEDIFF(DAY, @StartDate, @CutoffDate)
),
d(d) AS 
(
  SELECT DATEADD(DAY, n, @StartDate) FROM seq
)
SELECT d as Mandate, (case when tb.Amount is not null then tb.Amount else 0) as Amount FROM d
LEFT OUTER JOIN TB_TEST tb on d.Mandate = t.d
ORDER BY d desc
OPTION (MAXRECURSION 0);

This query would select all the dates based on the range you provided, and then join them to your table that has your actual dates/amounts in it. For any row that has an amount, it takes the amount from your table - for any other row it sets the Amount to be 0 (see the case statement).

赞(0）回复(0）举报 2023-03-17

我来回答

SQL Server All dates returned even other column data is absent

1条答案

相关问题

热门标签

最新问答