Home / Questions / 313856
Accepted
Huntzberger
Asked: 2020-12-05 11:36:04 +0800 CST 2020-12-05 11:36:04 +0800 CST

Show a default value when fetching a query with no results

  • 772

I have the following query, which compares data from one table to another. Now it turns out that query A may not return records, so in the WHERE V.Week between A.Week and S.Week if A.Week does not contain anything, I need to put the number 27. Any ideas? Thanks greetings

IdUser  Codigo  Cantidad
112 02158   225
112 02422   25
112 03173   80
112 03353   550
112 03739   701

The following is my code:

    select V.IdUser, V.Codigo, sum(V.Cantidad) as Cantidad

    FROM (select max(S.Semana) as Semana, V.IdUser, V.Codigo from SVisitas V

    left join Calendario S ON V.Fecha between S.Inicio and S.Fin 

    group by V.IdUser, V.Codigo)A 

    left join (select * from(select IdUser,Codigo,Temporada,SUM(Cantidad) 
as Cantidad,Semana from SCantidadesP group by IdUser,Codigo,Temporada,Cantidad,Semana)V
 where V.Cantidad <> 0)V on A.IdUser=V.IdUser and A.Codigo=V.Codigo 

    left join (Select * from Calendario where Getdate() between Inicio and
 Fin)S on V.Temporada=S.Temporada WHERE V.Semana between A.Semana and 
S.Semana 
    group by V.IdUser,V.Codigo

these are my boards

SVisitas 
IdUser int
Codigo varchar(5)
Fecha datetime

Calendario 
Semana int
Inicio datetime
Fin datetime
Temporada int

SCantidadesP
IdUser int
Codigo varchar(5)
Cantidad int
Semana int
Temporada int

Here I leave an example of the data that my tables store, what I need is to see all the records of my SCantidadesP table even if the codes are not in the SVisitas table

SVisitas 
IdUser Codigo   Fecha
112    02158    2019-11-12
112   02422    2019-12-03

Calendario 
Temporada Semana    Inicio       Fin
1920          45    2019-11-04   2019-11-10  
1920          46    2019-11-11   2019-11-17  
1920          47    2019-11-18   2019-11-24  
1920          48    2019-11-25   2019-12-01  
1920          49    2019-12-02   2019-12-08  
1920          50    2019-12-09   2019-12-15  
1920          51    2019-12-16   2019-12-22  
1920          52    2019-12-23   2019-12-29  

SCantidadesP
IdUser Codigo Cantidad Semana Temporada 
112    02158   234       50    1920
112    02458   1000      45    1920
112    02565   654       45    1920
112    02157   287       51    1920
112    02159   098       49    1920
sql-server

3 Answers

  1. Best Answer

    If you need somewhere that the possible value of a field that comes empty is replaced by another value... You will need to use the ISNULL function of SQL SERVER.

    Usage would be like this:

    SELECT * FROM MY_TABLA WHERE ISNULL(MI_COLUMNA_NULA, 27) == 27

    In this way, when the value of MY_COLUMN_NULL is null, the null will be replaced by a 27.

    Corrected to adapt it to the correct order of the tables:

    select V.IdUser, V.Codigo, sum(V.Cantidad) as Cantidad

FROM (select * from(select IdUser,Codigo,Temporada,SUM(Cantidad) as Cantidad
from SCantidadesP group by IdUser,Cod_Prod,Temporada,Cantidad
) V1 where V1.Cantidad <> 0) V

left join (select max(S.Semana) as Semana, V.IdUser, V.Codigo from SVisitas V
 left join Calendario S ON V.Fecha between S.Inicio and S.Fin 
group by V.IdUser, V.Codigo) A on A.IdUser=V.IdUser and A.Codigo=V.Codigo 

left join (Select * from Calendario where Getdate() between Inicio and Fin
)S on V.Temporada=S.Temporada

WHERE V.Semana between A.Semana and S.Semana 
group by V.IdUser,V.Codigo

    The documentation page: https://docs.microsoft.com/es-es/sql/t-sql/functions/isnull-transact-sql?view=sql-server-ver15

    I complete the answer with a link to a blog that explains very clearly how to use joins: how inner left right full join works

    Here you will see why now the data you want should come out, and how the A.Week will be null, so you must use the ISNULL(A.Week, 27) Here you will see why now the data you want should come out, and how the A.Week will be null, so you must use the ISNULL(A.Week, 27)

    Hope that helps, best regards!

    • 3

  2. You can make a modification in their tables

    SCantidadesP
IdUser int
Codigo varchar(5)
Cantidad int
Semana int  DEFAULT 27    // poner un valor por defecto
Temporada int
    • 1

  3. Hi Huntzberger: You can do it this way. I am only going to put you, what would be the set A, understanding that what you need is that A of all all, returns a result where its Week = 27, but its idUser and its code will be 0.

    SELECT U2.Semana, U2.IdUser, U2.Codigo FROM (
SELECT COUNT(*) OVER(ORDER BY (SELECT NULL)) AS NUMROWS, orden, Semana,IdUser,Codigo

FROM
(
SELECT 1 AS orden
     , MAX(S.Semana) AS Semana
     , V.IdUser
     , V.Codigo
FROM SVisitas V
     LEFT JOIN Calendario S ON V.Fecha BETWEEN S.Inicio AND S.Fin
       GROUP BY V.IdUser
              , V.Codigo
UNION ALL
SELECT 0
     , 27
     , 0
     , 0
FROM(VALUES(1)) tbl(n)
) AS u
) AS U2
WHERE U2.NUMROWS >1 AND orden > 0
OR U2.NUMROWS= 1 AND orden = 0

    Basically, to the SVisits and Calendar query, we add a row that contains a 27 for the week.

    On the output of this set called u, we use a window function to get how many records there are in U.

    In the output of this set called u2, we filter if the number of rows is > 1 we collect all the records of Svisits and Calendar because we choose those with the order column a 1 and if it only has 1 record, then we collect the order 0 which is 27.

    So U2 would be your set A, then your query would continue as is.

    • 1