DRUIDRUID DRUIDRUID - 1 year ago 61
SQL Question

Way to send report with sp_send_dbmail that only has records available

This SP is called on by sp_send_dbmail, which is executed daily on a job. When this does not have any records to show on report, I do not want the section sent with '0 rows affected' I still want to report the sections that have records available.

here is my sp it actually has 15 separate select statements, but for the sake of saving space I am only showing 2

------------------------------------------THIS IS FOR DEPARTMENT CODE [FS - FD]
DECLARE
@Now2 DATETIME,
@EndReportDate2 DATETIME,
@StartReportDate2 DATETIME
SET @Now2 = GETDATE()
SET @EndReportDate2 = DATEADD(dd, DATEDIFF(dd, 0, @Now2), -1)
SET @StartReportDate2 = DATEADD(dd, DATEDIFF(dd, 0, @Now2), -1)
SELECT StatDate = TimeLog.EventDate
,[ID#] = a.ID
,Codes = (a.DeptCode + '-' + a.OpCode)
,TotalTime = convert(time(0),dateadd(second,sum(datediff(second,StartTime,FinishTime)),0))
,Units = SUM(Units)
,UPH = cast(isnull(sum(Units) / nullif(sum(datediff(minute,StartTime,FinishTime))*1.0,0),0.0)*60 as decimal(10,0))
,[Goal%] = (convert(varchar,cast((isnull(sum(Units) / nullif(sum(datediff(minute,StartTime,FinishTime))*1.0,0),0.0)*60)/1552*100 as decimal(10,0))) + '%')
,AssociateName = (b.FirstName + ' ' + b.LastName)
FROM PTW.dbo.TimeLog a LEFT JOIN PTW.dbo.AssociateInfo b
ON a.ID = b.ID
WHERE EventDate BETWEEN @StartReportDate2 AND @EndReportDate2 AND DeptCode = 'FS' AND OpCode = 'FD'
GROUP BY a.EventDate, a.ID, a.DeptCode, a.OpCode, b.FirstName, b.LastName
ORDER BY UPH DESC
------------------------------------------THIS IS FOR DEPARTMENT CODE [FS - FT]
DECLARE
@Now3 DATETIME,
@EndReportDate3 DATETIME,
@StartReportDate3 DATETIME
SET @Now3 = GETDATE()
SET @EndReportDate3 = DATEADD(dd, DATEDIFF(dd, 0, @Now3), -1)
SET @StartReportDate3 = DATEADD(dd, DATEDIFF(dd, 0, @Now3), -1)
SELECT StatDate = a.EventDate
,[ID#] = a.ID
,Codes = (a.DeptCode + '-' + a.OpCode)
,TotalTime = convert(time(0),dateadd(second,sum(datediff(second,StartTime,FinishTime)),0))
,Units = SUM(Units)
,UPH = cast(isnull(sum(Units) / nullif(sum(datediff(minute,StartTime,FinishTime))*1.0,0),0.0)*60 as decimal(10,0))
,[Goal%] = (convert(varchar,cast((isnull(sum(Units) / nullif(sum(datediff(minute,StartTime,FinishTime))*1.0,0),0.0)*60)/295*100 as decimal(10,0))) + '%')
,AssociateName = (b.FirstName + ' ' + b.LastName)
FROM PTW.dbo.TimeLog a LEFT JOIN PTW.dbo.AssociateInfo b
ON a.ID = b.ID
WHERE EventDate BETWEEN @StartReportDate3 AND @EndReportDate3 AND DeptCode = 'FS' AND OpCode = 'FT'
GROUP BY a.EventDate, a.ID, a.DeptCode, a.OpCode, b.FirstName, b.LastName
ORDER BY UPH DESC


Here where I call for it with sp_send_dbmail

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBMail,
@recipients = '[email protected]',
@subject = 'FLAT_Daily',
@query = N'EXEC PTW.dbo.SP_FLAT_Daily',
@query_attachment_filename = 'FLAT_Daily.txt'


Here are my results

StatDate ID# Codes TotalTime Units UPH Goal% AssociateName
-------- --- ----- --------- ----- --- ----- -------------
7/24/2017 1234567 FS-FD 03:40:00 0 0 0% MY NAME

(1 rows affected)
StatDate ID# Codes TotalTime Units UPH Goal% AssociateName
-------- --- ----- --------- ----- --- ----- -------------

(0 rows affected)


Some days I have records on both, but like the example sometimes only one portion has records. How can I send only what has records available?

Answer Source

One way to approach this would be to move the reusable query into its own procedure, and execute that procedure from your daily report procedure.

This still just uses if exists() to determine whether or not to return a result set as suggested by Nick McDermaid, but it simplifies the code.

create procedure dbo.SP_FLAT_Daily_Query (
    @StartReportDate datetime 
  , @EndReportDate   datetime 
  , @DeptCode        char(2) 
  , @OpCode          char(2)
  , @Goal            decimal(10,2)
) as 
begin;
  --set nocount on; /* removes (N row(s) affected message)
  if exists (
    select 1 
    from TimeLog t
    where t.EventDate >= @StartReportDate
      and t.EventDate <= @EndReportDate
      and t.DeptCode = @DeptCode
      and t.OpCode = @OpCode
    )
  begin;
    select 
        StatDate = convert(char(10),t.EventDate,120)
      , t.ID
      , Codes = (t.DeptCode + '-' +  t.OpCode)
      , TotalTime = right('0' + convert(varchar(9),(sum(datediff(second,StartTime,FinishTime)) / 3600 )),2) + ':' 
                  + right('0' + convert(varchar(2),(sum(datediff(second,StartTime,FinishTime)) / 60) % 60 ),2) + ':' 
                  + right('0' + convert(varchar(2),(sum(datediff(second,StartTime,FinishTime)) % 60 )),2)
      , Units = sum(Units)
      , UPH = cast(isnull(sum(Units) / nullif(sum(datediff(minute,StartTime,FinishTime))*1.0,0),0.0)*60  as decimal(10,0))
      , [Goal%] = (convert(varchar(30),cast((isnull(sum(Units) / nullif(sum(datediff(minute,StartTime,FinishTime))*1.0,0),0.0)*60)/@Goal*100 as decimal(10,0))) + '%')
      , AssociateName = isnull(ai.FirstName + ' ','') + isnull(ai.LastName,'')
    from TimeLog t
      left join AssociateInfo ai
        on t.Id = ai.Id
    where t.EventDate >= @StartReportDate
      and t.EventDate <= @EndReportDate
      and t.DeptCode = @DeptCode
      and t.OpCode = @OpCode
    group by t.EventDate, t.id, t.DeptCode, t.OpCode, ai.FirstName, ai.LastName
    order by t.id desc;
  end;
end;
go

Then in your daily procedure, reuse your variables and execute the procedure for the query with each set of variables needed.

The code in the example below is verbose and repeats the same value assignments for the same variables, but I left it this way to make it easy to adjust for your actual code.

create procedure dbo.SP_Flat_Daily as 
begin;
  --set nocount on; /* removes (N row(s) affected message)
  declare 
      @Now             datetime
    , @StartReportDate datetime 
    , @EndReportDate   datetime 
    , @DeptCode        char(2) 
    , @OpCode          char(2)
    , @Goal            decimal(10,2);
  ------------------------------------------THIS IS FOR DEPARTMENT CODE [FS - FT]
  select 
      @Now = getdate()
    , @StartReportDate = dateadd(day, datediff(day, 0, getdate()), -1)
    , @EndReportDate   = dateadd(day, datediff(day, 0, getdate()), -1)
    , @DeptCode = 'fs'
    , @OpCode   = 'ft'
    , @Goal     = 295.0;
  exec dbo.SP_FLAT_Daily_Query @StartReportDate, @EndReportDate, @DeptCode, @OpCode, @Goal;  
  ------------------------------------------THIS IS FOR DEPARTMENT CODE [NA - NA]
  select 
      @Now = getdate()
    , @StartReportDate = dateadd(day, datediff(day, 0, getdate()), -1)
    , @EndReportDate   = dateadd(day, datediff(day, 0, getdate()), -1)
    , @DeptCode = 'na'
    , @OpCode   = 'na'
    , @Goal     = 0;
  exec dbo.SP_FLAT_Daily_Query @StartReportDate, @EndReportDate, @DeptCode, @OpCode, @Goal;
  ------------------------------------------THIS IS FOR DEPARTMENT CODE [FS - FD]
  select 
      @Now = getdate()
    , @StartReportDate = dateadd(day, datediff(day, 0, getdate()), -1)
    , @EndReportDate   = dateadd(day, datediff(day, 0, getdate()), -1)
    , @DeptCode = 'fs'
    , @OpCode   = 'fd'
    , @Goal     = 1552.0;
  exec dbo.SP_FLAT_Daily_Query @StartReportDate, @EndReportDate, @DeptCode, @OpCode, @Goal;
end;
go
exec dbo.SP_Flat_Daily;

dbfiddle.uk demo: http://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=72525c7478361b0df8171fd224f68f7e

returns:

StatDate   ID          Codes TotalTime Units       UPH     Goal   AssociateName
---------- ----------- ----- --------- ----------- ------- ------ ------------------
2017-07-26 2375935     fs-ft 03:44:00  263         70      24%    Druid Druid

(1 row(s) affected)

StatDate   ID          Codes TotalTime Units       UPH     Goal   AssociateName
---------- ----------- ----- --------- ----------- ------- ------ ------------------
2017-07-26 11259       fs-fd 04:50:00  553         114     7%     Sql Zim
2017-07-26 25          fs-fd 24:59:59  176         7       0%     Nick McDermaid

(2 row(s) affected)

As you can see, there is no message or empty result set for na-na.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download