MSSQL Loop - How to

Question

I need your help on this please: I have one table in which ID_Erreur_CallBack could be NULL, 0, 1, 2, 3, 4, 5 or 6. If I execute:

SELECT ID_SkillGroup AS Competence
    ,ID_Erreur_CallBack
    ,count(*) AS Nombre_de_Ocurrences
    ,CASE ID_Erreur_CallBack
        WHEN '0'
            THEN 'Abandon pendant l’exécution du script (le client a raccroché)'
        WHEN '1'
            THEN 'Ligne occupée'
        WHEN '2'
            THEN 'Le client n’a pas confirmé en appuyant sur étoile'
        WHEN '3'
            THEN 'Le client n’a pas décroché'
        WHEN '4'
            THEN 'Le numéro de téléphone du client est invalide'
        WHEN '5'
            THEN 'Problemes de ressources lors de l’appel'
        WHEN '6'
            THEN 'Echec de l’appel'
        ELSE 'Appel réussi'
        END AS Description_Erreur
FROM dbo.TB_WCB_USER
WHERE Date_Heure_Insert BETWEEN '01/11/2015'
        AND '01/12/2015'
    AND TYPE = 'WCB'
GROUP BY ID_SkillGroup
    ,ID_Erreur_CallBack
ORDER BY ID_SkillGroup
    ,ID_Erreur_CallBack ASC

And I get for example: Result

I want to make a SELECT statement where I can vary ID_Erreur_CallBack from 0 to 6 and NULL even if Nombre_de_Ocurrences is 0 (or NULL). Something like: Result I wish

Thank you!

Answer 1

Thank for the answer Juan Carlos.

I finally used CROSS JOIN

/*Creation de table temporaire et remplisage avec NULL*/
WITH T1 AS (
SELECT n = 0
UNION ALL
SELECT n + 1 FROM T1 WHERE n < 6
)

SELECT n INTO #Temp_Cesar FROM T1
INSERT INTO #Temp_Cesar (n) VALUES (NULL)
SELECT t1.ID_SkillGroup as Competence,t2.n as ID_Erreur_CallBack
INTO #Temp_Cesar1
FROM dbo.TB_SKILLGROUP t1
CROSS JOIN #Temp_Cesar t2
ORDER BY t1.ID_SkillGroup
ALTER TABLE #Temp_Cesar1 ADD Nombre_de_Ocurrences INT NULL, Description_Erreur VARCHAR(30) NULL;
select Competence,isnull(ID_Erreur_CallBack,99) as ID_Erreur_CallBack,isnull(Nombre_de_Ocurrences,0) as Nombre_de_Ocurrences,
Case ID_Erreur_CallBack
WHEN '0' THEN 'Abandon pendant l’exécution du script (le client a raccroché)'
WHEN '1' THEN 'Ligne occupée'
WHEN '2' THEN 'Le client n’a pas confirmé en appuyant sur étoile'
WHEN '3' THEN 'Le client n’a pas décroché'
WHEN '4' THEN 'Le numéro de téléphone du client est invalide'
WHEN '5' THEN 'Problemes de ressources lors de l’appel'
WHEN '6' THEN 'Echec de l’appel'
ELSE 'Appel réussi'
END AS Description_Erreur
into #Temp_Cesar11 from #Temp_Cesar1

Answer 2

Instead of using a CASE You create a table with your errors description, that way is easy to mantain if you want add or update text, dont need change code.

like errors

 error_id  error_text
    1        err1
    2        err2
    3        err3
    ....
   100      'Appel réussi'

THEN use LEFT JOIN

  select ID_SkillGroup as Competence,
         E.error_id as ID_Erreur_CallBack,
         E.error_text as Description_Erreur,
         count(*) as Nombre_de_Ocurrences
  FROM errors E
  LEFT JOIN TB_WCB_USER W
         ON E.error_id = ID_Erreur_CallBack

I see you have an ELSE on your CASE

You can do something like this

  LEFT JOIN TB_WCB_USER W
         ON E.error_id = CASE ID_Erreur_CallBack
                              WHEN > 99 then 100   -- 'Appel réussi'
                              ELSE ID_Erreur_CallBack
                          END