Select value = 0 if no value to select in SQL Server-CodePudding

Here is my code to get Total of Day

CREATE PROCEDURE [dbo].[hipos_BC_BH_TEST]
    @From_day datetime,
    @To_day datetime
AS
BEGIN
    declare @fd datetime
    set @fd = @From_day

    declare @dt table (Day_get nvarchar(50), Total float)
    
    while @fd <= @To_day
    begin
        insert into @dt
        Select Day_get = Convert(nvarchar(10), @fd, 20), Total = sum(isnull(Total,0))
        from hipos_BH_Info 
        where Convert(nvarchar(10), Day_get, 20) = @fd
        and Cancel_Huy = 0
        group by  Day_get, Total

        set @fd = @fd   1  --Add 1 day
    end
    Select Day_get, Total = isnull(sum(Total),0)
    from @dt
    group by Day_get
END

After exec from '2021-09-01' to '2021-09-10' the result is

Day_get Total
2021-09-01  1106000
2021-09-02  3249600
2021-09-05  316000
2021-09-07  129000
2021-09-08  1547600
2021-09-09  271000
2021-09-10  4765900

And I need it show day '2021-09-03' and '2021-09-04' with Total = 0 as

Day_get Total
2021-09-01  1106000
2021-09-02  3249600
2021-09-03  0
2021-09-04  0
2021-09-05  316000
2021-09-07  129000
2021-09-08  1547600
2021-09-09  271000
2021-09-10  4765900

How can I? Please help me.

CodePudding user response：

You shouldn't ever be doing things like this in a loop. First, let's see how to generate a set for all the dates in your range:

DECLARE @From_day date = '20210901',
        @To_day   date = '20210910';
  
;WITH n(n) AS 
(
  SELECT 1 UNION ALL SELECT n 1 FROM n
  WHERE n <= DATEDIFF(DAY, @From_day, @To_day)
)
SELECT Day_get = DATEADD(DAY, n-1, @From_day) 
  FROM n ORDER BY Day_get;

Results:

Day_get
----------
2021-09-01
2021-09-02
2021-09-03
2021-09-04
2021-09-05
2021-09-06
2021-09-07
2021-09-08
2021-09-09
2021-09-10

Now you just need to left join those days to the data in your table. So now your (one!) query can just become:

;WITH n(n) AS 
(
  SELECT 1 UNION ALL SELECT n 1 FROM n
  WHERE n <= DATEDIFF(DAY, @From_day, @To_day)
),
d(Day_get) AS 
(
  SELECT DATEADD(DAY, n-1, @From_day) FROM n
)
INSERT INTO @dt(Day_get, Total)
  SELECT d.Day_get, COALESCE(SUM(h.Total),0)
  FROM d
  LEFT OUTER JOIN dbo.hipos_BH_Info AS h
    ON h.Day_get >= d.Day_get
   AND h.Day_get <  DATEADD(DAY, 1, d.Day_get)
   AND h.Cancel_Huy = 0
  GROUP BY d.Day_get;

Also, please don't ever check for date matching by converting dates to a string and comparing the strings, and always use the schema prefix...

Note that if your date range can exceed 100 days, you'll need OPTION (MAXRECURSION n).

CodePudding user response：

Just delete the group by and swap isnull and sum in selecting data

insert into @dt
Select Day_get = Convert(nvarchar(10), @fd, 20), Total = isnull(sum(Total),0)
from hipos_BH_Info 
where Convert(nvarchar(10), Day_get, 20) = @fd
and Cancel_Huy = 0