SQL JOB with html report

DavidSousa
August 4, 2023
269 views
1 vote
2 Answers

This is the code that is supposed to return an html report by and ssms job.



DECLARE @DataInicialMesAnterior DATE, @DataFinalMesAnterior DATE;
SET @DataInicialMesAnterior = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0);
SET @DataFinalMesAnterior = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0));

DECLARE @tableHTML2 NVARCHAR(MAX), @corpo2 AS NVARCHAR(50);
SET @tableHTML2 =
    N'<font size="1"><H1>Produção Mensal Tecelagem Entre ' + CONVERT(VARCHAR, @DataInicialMesAnterior, 104) + ' - ' + CONVERT(VARCHAR, @DataFinalMesAnterior, 104) + '  </H1> ' +
    N'<style> th {color:#FFFFFF; background-color: #6193AC } </style>' +
    N'<table border="1" cellpadding="3" cellspacing="0" style="width: 100%">' +
    N'<tr><th rowspan="3">Maquina</th><th rowspan="3">Artigo</th><th rowspan="3">Descrição</th><th rowspan="3">Total Metros</th><th rowspan="3">Total Kgs</th></tr>' +
    N'<tr></tr><tr><th></th></tr>' +
    CAST((
        WITH ProductionData AS (
            SELECT 
                'Tear - ' + LTRIM(RTRIM(STR(cast(substring(BI.U_MED1, 2, 2) as int)))) as td, 
                BI.REF as td1, 
                ISNULL(ST.design, '') as td2, --design
                SUM(CASE WHEN stobs.u_dobramal=1 THEN BI.QTT*2 ELSE bi.qtt END) as 'Metros Produzidos', -- Soma de metros por tear e ref
                SUM(CAST(BI.U_MED3 as float)) as 'Kgs Produzidos' -- Soma de kgs por tear e ref
            FROM carlom.dbo.BI bi (nolock)
            LEFT JOIN carlom.dbo.st ST (nolock) ON ST.ref = BI.REF
            INNER JOIN carlom.dbo.stobs stobs (nolock) ON st.ref = stobs.ref 
            WHERE BI.NDOS = 20 
                AND BI.u_data2 BETWEEN @DataInicialMesAnterior AND @DataFinalMesAnterior
            GROUP BY 'Tear - ' + LTRIM(RTRIM(STR(cast(substring(BI.U_MED1, 2, 2) as int)))), BI.REF, ST.design
        ), Totals AS (
            SELECT 
                td, 
                td1, 
                MAX(td2) as td2, 
                SUM([Metros Produzidos]) as 'Metros Produzidos', 
                SUM([Kgs Produzidos]) as 'Kgs Produzidos'
            FROM ProductionData
            GROUP BY td, td1
            HAVING SUM([Metros Produzidos]) > 0 OR SUM([Kgs Produzidos]) > 0
        ), AllData AS (
            SELECT 
                td, 
                td1, 
                MAX(td2) as td2, 
                SUM([Metros Produzidos]) as 'Metros Produzidos', 
                SUM([Kgs Produzidos]) as 'Kgs Produzidos'
            FROM Totals
            GROUP BY td, td1
            HAVING SUM([Metros Produzidos]) > 0 OR SUM([Kgs Produzidos]) > 0
        ), OrderedData AS (
            SELECT 
                td, 
                td1, 
                MAX(td2) as td2, 
                SUM([Metros Produzidos]) as 'Metros Produzidos', 
                SUM([Kgs Produzidos]) as 'Kgs Produzidos',
                0 as TotalOrder
            FROM AllData
            GROUP BY td, td1
            UNION ALL
            SELECT 
                'Total' as td, 
                '' as td1, 
                '' as td2, 
                SUM([Metros Produzidos]) as 'Metros Produzidos', 
                SUM([Kgs Produzidos]) as 'Kgs Produzidos',
                1 as TotalOrder
            FROM AllData
        )
        SELECT 
            td, 
            td1, 
            MAX(td2) as td2, 
            MAX([Metros Produzidos]) as 'Metros Produzidos', 
            MAX([Kgs Produzidos]) as 'Kgs Produzidos'
        FROM OrderedData
        GROUP BY td, td1, TotalOrder
        ORDER BY 
            CASE WHEN TotalOrder = 1 THEN 1 ELSE 0 END, 
            CASE WHEN ISNUMERIC(SUBSTRING(td, LEN('Tear - ') + 1, LEN(td))) = 1 THEN CAST(SUBSTRING(td, LEN('Tear - ') + 1, LEN(td)) AS INT) ELSE 99999 END, 
            td
        FOR XML PATH('tr'), TYPE
    ).value('.', 'NVARCHAR(MAX)')) + N'</font></table>';

SET @corpo2 = 'Produção do Mês';

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'PHC',
    @recipients = '[email protected]',
    @subject = @corpo2,
    @body = @tableHTML2,
    @body_format = 'HTML';

the code retrieves the following errors

Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword ‘WITH’.
Msg 319, Level 15, State 1, Line 13
Incorrect syntax near the keyword ‘with’. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 26
Incorrect syntax near ‘,’.
Msg 102, Level 15, State 1, Line 36
Incorrect syntax near ‘,’.
Msg 102, Level 15, State 1, Line 46
Incorrect syntax near ‘,’.
Msg 102, Level 15, State 1, Line 79
Incorrect syntax near ‘)’.

Answers

You’ve dumped a bunch of CTE declarations inside a CAST function; that isn’t allow. CTEs must be defined at the start of the statement not half way through. YOu also have a CAST but no AS <Data Type> clause. I half address that, but you’ll need to add the data type.

DECLARE @DataInicialMesAnterior DATE, @DataFinalMesAnterior DATE;
SET @DataInicialMesAnterior = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0);
SET @DataFinalMesAnterior = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0));

DECLARE @tableHTML2 NVARCHAR(MAX), @corpo2 AS NVARCHAR(50);
WITH ProductionData AS (
            SELECT 
                'Tear - ' + LTRIM(RTRIM(STR(cast(substring(BI.U_MED1, 2, 2) as int)))) as td, 
                BI.REF as td1, 
                ISNULL(ST.design, '') as td2, --design
                SUM(CASE WHEN stobs.u_dobramal=1 THEN BI.QTT*2 ELSE bi.qtt END) as 'Metros Produzidos', -- Soma de metros por tear e ref
                SUM(CAST(BI.U_MED3 as float)) as 'Kgs Produzidos' -- Soma de kgs por tear e ref
            FROM carlom.dbo.BI bi (nolock)
            LEFT JOIN carlom.dbo.st ST (nolock) ON ST.ref = BI.REF
            INNER JOIN carlom.dbo.stobs stobs (nolock) ON st.ref = stobs.ref 
            WHERE BI.NDOS = 20 
                AND BI.u_data2 BETWEEN @DataInicialMesAnterior AND @DataFinalMesAnterior
            GROUP BY 'Tear - ' + LTRIM(RTRIM(STR(cast(substring(BI.U_MED1, 2, 2) as int)))), BI.REF, ST.design
        ), Totals AS (
            SELECT 
                td, 
                td1, 
                MAX(td2) as td2, 
                SUM([Metros Produzidos]) as 'Metros Produzidos', 
                SUM([Kgs Produzidos]) as 'Kgs Produzidos'
            FROM ProductionData
            GROUP BY td, td1
            HAVING SUM([Metros Produzidos]) > 0 OR SUM([Kgs Produzidos]) > 0
        ), AllData AS (
            SELECT 
                td, 
                td1, 
                MAX(td2) as td2, 
                SUM([Metros Produzidos]) as 'Metros Produzidos', 
                SUM([Kgs Produzidos]) as 'Kgs Produzidos'
            FROM Totals
            GROUP BY td, td1
            HAVING SUM([Metros Produzidos]) > 0 OR SUM([Kgs Produzidos]) > 0
        ), OrderedData AS (
            SELECT 
                td, 
                td1, 
                MAX(td2) as td2, 
                SUM([Metros Produzidos]) as 'Metros Produzidos', 
                SUM([Kgs Produzidos]) as 'Kgs Produzidos',
                0 as TotalOrder
            FROM AllData
            GROUP BY td, td1
            UNION ALL
            SELECT 
                'Total' as td, 
                '' as td1, 
                '' as td2, 
                SUM([Metros Produzidos]) as 'Metros Produzidos', 
                SUM([Kgs Produzidos]) as 'Kgs Produzidos',
                1 as TotalOrder
            FROM AllData
        )
SELECT @tableHTML2 =
    N'<font size="1"><H1>Produção Mensal Tecelagem Entre ' + CONVERT(VARCHAR, @DataInicialMesAnterior, 104) + N' - ' + CONVERT(VARCHAR, @DataFinalMesAnterior, 104) + N'  </H1> ' +
    N'<style> th {color:#FFFFFF; background-color: #6193AC } </style>' +
    N'<table border="1" cellpadding="3" cellspacing="0" style="width: 100%">' +
    N'<tr><th rowspan="3">Maquina</th><th rowspan="3">Artigo</th><th rowspan="3">Descrição</th><th rowspan="3">Total Metros</th><th rowspan="3">Total Kgs</th></tr>' +
    N'<tr></tr><tr><th></th></tr>' +
    CAST((
        SELECT 
            td, 
            td1, 
            MAX(td2) as td2, 
            MAX([Metros Produzidos]) as 'Metros Produzidos', 
            MAX([Kgs Produzidos]) as 'Kgs Produzidos'
        FROM OrderedData
        GROUP BY td, td1, TotalOrder
        ORDER BY 
            CASE WHEN TotalOrder = 1 THEN 1 ELSE 0 END, 
            CASE WHEN ISNUMERIC(SUBSTRING(td, LEN('Tear - ') + 1, LEN(td))) = 1 THEN CAST(SUBSTRING(td, LEN('Tear - ') + 1, LEN(td)) AS INT) ELSE 99999 END, 
            td
        FOR XML PATH('tr'), TYPE
    ).value('.', 'NVARCHAR(MAX)') AS <DATA TYPE>) + N'</font></table>';

SET @corpo2 = N'Produção do Mês';

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'PHC',
    @recipients = '[email protected]',
    @subject = @corpo2,
    @body = @tableHTML2,
    @body_format = 'HTML';

It seems you just wanted to make sure you get nvarchar(max), but FOR XML anyway does that if you don’t specify ,TYPE.

There are various other issues with the code also:

LEFT JOIN followed by an INNER JOIN which is dependent on the former makes no sense: either both should be INNER or both LEFT (or you can nest the join conditions).
Do not use nolock, it has serious data integrity implications.
Totals and AllData are eaxctly the same. There is no further aggregation being done, because they are grouping by the same columns.
OrderedData and the UNION ALL can also be elided by using GROUPING SETS. The GROUPING() function will tell you whether the row has been grouped for a column.
LTRIM(RTRIM(STR(cast(substring(...) as int)))) is nonsense: integers casted to string don’t need trimming. Futhermore why cast to int only to cast it back? If you want to make sure it’s an int without throwing an error, just use TRY_CAST.
Don’t add TEAR - onto the value at the beginning, as it’s less efficient for grouping, and you have to query it back out afterwards. Instead tack it on in the final stage.
Always specify VARCHAR and NVARCHAR with a length.
Do not use '' to quote column names, only [].
Instead of ORDER BY CASE WHEN TotalOrder = 1 THEN 1 ELSE 0 END just do ODRER BY TotalOrder.
Use EOMONTH to get the end of a month, and use DATEFROMPARTS to construct dates. In newer versions you can also use DATETRUNC(month.

DECLARE @DataFinalMesAnterior DATE = EOMONTH(GETDATE(), -1);
DECLARE @DataInicialMesAnterior DATE = DATEFROMPARTS(DATEPART(year, @DataFinalMesAnterior), DATEPART(month, @DataFinalMesAnterior), 1);

DECLARE @tableHTML2 NVARCHAR(MAX), @corpo2 AS NVARCHAR(50);

WITH ProductionData AS (
    SELECT 
      ISNULL(TRY_CAST(SUBSTRING(BI.U_MED1, 2, 2) as int), 99999) as td, 
      BI.REF as td1, 
      ISNULL(ST.design, '') as td2, --design
      SUM(BI.QTT * IIF(stobs.u_dobramal = 1, 2, 1)) as [Metros Produzidos], -- Soma de metros por tear e ref
      SUM(CAST(BI.U_MED3 as float)) as [Kgs Produzidos] -- Soma de kgs por tear e ref
    FROM carlom.dbo.BI bi
    INNER JOIN carlom.dbo.st ST ON ST.ref = BI.REF
    INNER JOIN carlom.dbo.stobs stobs ON st.ref = stobs.ref 
    WHERE BI.NDOS = 20 
      AND BI.u_data2 BETWEEN @DataInicialMesAnterior AND @DataFinalMesAnterior
    GROUP BY
      ISNULL(TRY_CAST(SUBSTRING(BI.U_MED1, 2, 2) as int), 99999),
      BI.REF,
      ST.design
),

Totals AS (
    SELECT
      td AS tdOrdering,
      IIF(GROUPING(td) = 0, CONCAT('Tear - ', IIF(td = 99999, NULL, td)), 'Total') AS td, 
      IIF(GROUPING(td) = 0, td1, '') AS td1, 
      IIF(GROUPING(td) = 0, MAX(td2), '') as td2, 
      SUM([Metros Produzidos]) as [Metros Produzidos],
      SUM([Kgs Produzidos]) as [Kgs Produzidos],
      GROUPING(td) as TotalOrder
    FROM ProductionData
    GROUP BY GROUPING SETS (
        (td, td1),
        ()
    )
    HAVING SUM([Metros Produzidos]) > 0
        OR SUM([Kgs Produzidos]) > 0
)

SELECT @tableHTML2 =
    N'<font size="1"><H1>Produção Mensal Tecelagem Entre ' + CONVERT(NVARCHAR(30), @DataInicialMesAnterior, 104) + N' - ' + CONVERT(NVARCHAR(30), @DataFinalMesAnterior, 104) + N'  </H1> ' +
    N'<style> th {color:#FFFFFF; background-color: #6193AC } </style>' +
    N'<table border="1" cellpadding="3" cellspacing="0" style="width: 100%">' +
    N'<tr><th rowspan="3">Maquina</th><th rowspan="3">Artigo</th><th rowspan="3">Descrição</th><th rowspan="3">Total Metros</th><th rowspan="3">Total Kgs</th></tr>' +
    N'<tr></tr><tr><th></th></tr>' +
    (
        SELECT 
          td,
          td1,
          td2,
          [Metros Produzidos],
          [Kgs Produzidos]
        FROM OrderedData
        ORDER BY 
            TotalOrder,
            tdOrdering
        FOR XML PATH('tr')
    ) + N'</font></table>';

Please signup or login to give your own answer.

Click here to cancel reply.