Return 0 values where corresponding type is missing-CodePudding

I need a select query where I get amount of visits, type of visit to sort it and in which dates this happend.

My problem is that I dont get 0s at times where there are no visits.

I am using this code:

        SELECT COUNT(*) AS numberOfVisits,
               CONVERT(DATE, CONVERT(VARCHAR(10), CAST(dateOfVisit AS DATE), 121)) AS typeOfVisit,
               typeOfVisit
        FROM WEB
        WHERE dateOfVisit >= '1-1-2021'
        AND dateOfVisit <= '1-31-2021'
        GROUP BY CAST(dateOfVisit AS DATE), typeOfVisit
        ORDER BY CAST(dateOfVisit AS DATE)

/*i am converting and casting the date for other reasons that i need in 
my API/frontend and i also get the condition of date from api 1-1-2021 
and 1-31-2021 are just for sample*/

and with this i get the following results: https://imgur.com/a/o3c9A2p

The columns Follow as: numberOfVisits | dateOfVisit | typeOfVisit

As you can see I have 4 types. The first 3 rows contain types 2,3,4 but not 1; I want to have an extra row here with value 0, same date and type 1.

The actual value 0 is also not written in the database and neither is null

The desired result would be like this:

0   2021-01-04  1
1   2021-01-04  2
10  2021-01-04  3
2   2021-01-04  4

and if the result would only show that there were visits of type 2 then it would be something like this:

0   2021-01-04  1
1   2021-01-04  2
0   2021-01-04  3
0   2021-01-04  4

CodePudding user response：

You can't count if there is no table record of date. You need to have calendar table with every date to start select from then left join your visit table and count.

It would be something like this:

if OBJECT_ID('tempdb..#Calendar') is not null drop table #Calendar

create table #Calendar (adDate date)

declare @dDate date = '2021-01-01'
while @dDate <= '2021-01-31'
begin
    insert into #Calendar (adDate)
    select @dDate

    set @dDate = dateadd(DAY, 1, @dDate)
end

select
    c.adDate,
    w.typeOfVisit,
    COUNT(w.visit) as VisitCount

from #Calendar c
left join web w on w.dateOfVisit = c.adDate
group by 
    c.adDate,
    w.typeOfVisit
order by
    c.adDate

If you want to have 0 visits by type also, not just by day then you can do it like this:

if OBJECT_ID('tempdb..#Calendar') is not null drop table #Calendar

create table #Calendar (adDate date, anTypeOfVisit int)

declare 
    @dDate date = '2021-01-01',
    @nTypeOfVisit int
while @dDate <= '2021-01-31'
begin
    set @nTypeOfVisit = 1
    while @nTypeOfVisit <=4
    begin
        insert into #Calendar (adDate, anTypeOfVisit)
        select @dDate, @nTypeOfVisit

        set @nTypeOfVisit = @nTypeOfVisit   1
    end

    set @dDate = dateadd(DAY, 1, @dDate)
end

select
    c.adDate,
    c.anTypeOfVisit,
    COUNT(w.dateofvisit) as VisitCount

from #Calendar c
left join web w on w.dateOfVisit = c.adDate and w.typeofvisit = c.anTypeOfVisit

group by 
    c.adDate,
    c.anTypeOfVisit
order by
    c.adDate,
    c.anTypeOfVisit

CodePudding user response：

You need to create a table that contains a row for each visit type for each date. Then do a left join to bring in the counts from your actual data

DROP TABLE IF EXISTS #Visit 
CREATE TABLE #Visit (ID INT IDENTITY(1,1) ,DateOfVisit Date,TypeOfVisit int)
INSERT INTO #Visit
VALUES ('2021-01-04',2),('2021-01-04',3),('2021-01-04',3),('2021-01-04',4);

WITH cte_DistinctVisitDate AS (
    SELECT DateOfVisit
    FROM #Visit
    WHERE DateOfVisit BETWEEN '2020-01-04' AND '2021-01-04' /*Always use YYYY-MM-DD for params as it is culture-agnostic*/
    GROUP BY DateOfVisit
),
cte_TypePerDate AS (
    /*One row for each type on each visit date*/
    SELECT DateOfVisit,TypeOfVisit
    FROM cte_DistinctVisitDate
    CROSS JOIN (VALUES (1),(2),(3),(4)) AS B(TypeOfVisit) /*I am hard coding raw data, but you should use lookup table that contains each visit type*/
),
cte_VisitCount  AS (
    /*Actual counts, but only lists visit types with visits*/
    SELECT DateOfVisit,TypeOfVisit,VisitCnt = COUNT(*)
    FROM #Visit
    GROUP BY DateOfVisit,TypeOfVisit
)

SELECT A.DateOfVisit,A.TypeOfVisit,VisitCnt = ISNULL(B.VisitCnt,0)
FROM cte_TypePerDate AS A
LEFT JOIN cte_VisitCount AS B
    ON A.DateOfVisit = B.DateOfVisit
    AND A.TypeOfVisit = B.TypeOfVisit