asked on

hot to break a dataset?

i have sql that makes the below result set. see the excel file.

now i have this result set emailed to the entire team... i need to make it smarter so that each office (office_id field) gets only their parts of the data set and not others.

how can i splice this dataset into seperate parts based on office?

with it broken up i can then email each batch of records with matching office_id fields to the correlated email for that office (dropoff_email_recip)
dataset.xls

tigin44

you can do that in your procedure... if you can provide your code we may guide you

Sharath S

create a procedure as below.

create procedure sp_email(@officeID int) as
begin
select * from (yourquery) as t1 where officeID = @officeID
end

you can execute this procedure with officeID as parameter.

execute sp_email(1)

(or)

execute sp_email(2)

supergirl2008

ASKER

the proc is attached and i want to break the temp table that holds all the data by office id and send all the records for a particular officeID to the email address in the dropoff_email_recip field.

i have this proc set to run as a job hourly... hence the sql time code at the top...

CREATE PROCEDURE [dbo].[spIncompleteRegistrationsGet]           
 -- Add the parameters for the stored procedure here          
          
AS          
BEGIN          
 -- SET NOCOUNT ON added to prevent extra result sets from          
 -- interfering with SELECT statements.          
 SET NOCOUNT ON;          
          
 DECLARE @RegistrationID INT          
 DECLARE @RegistrationCount INT          
 DECLARE @EventName VARCHAR(100)  
 DECLARE @dropoff_email_recip VARCHAR(200)  
 DECLARE @SkyCode VARCHAR(50)          
    -- Insert statements for procedure here          
          
 --Verify that there are incomplete registrations in the past hour          
 select @RegistrationCount=COUNT(RegistrationID) from dbo.Registration          
  where Status < 3           
  and ModifiedDate > dateadd(mi, -120, GETDATE())          
  and ModifiedDate <= dateadd(mi, -60, GETDATE())          
                
 IF (@RegistrationCount > 0)          
 BEGIN          
  select @RegistrationID=RegistrationID, 
		 @EventName=Event.Name, 
		 @SkyCode=SkySessionCode,
		 @dropoff_email_recip =  office.dropoff_email_recip
  from dbo.Registration           
	JOIN dbo.Event ON dbo.Event.EventID = dbo.Registration.EventID
	join dbo.office ON Event.ProductionOfficeID = office.OfficeID
   where Status < 3           
   and Registration.ModifiedDate > dateadd(mi, -120, GETDATE())          
   and Registration.ModifiedDate <= dateadd(mi, -60, GETDATE())          
             
             
   select ContactID, SkyID, FirstName, LastName, Email, Phone, @EventName AS Event, @SkyCode As SkyCode       
   into #tmpRegFallOff      
   from Contact where ContactID IN           
   (select ContactID from dbo.RegistrationDelegate where RegistrationID = @RegistrationID)          
         
   select * from  #tmpRegFallOff      
         
  DECLARE @tableHTML  NVARCHAR(MAX) ;      
  SET @tableHTML =      
   N'<H1>Incomplete Registrations</H1>' +      
   N'<table border="1">' +      
   N'<tr><th>ContactID</th><th>SkyID</th>' +      
   N'<th>FirstName</th><th>LastName</th><th>Email</th>' +      
   N'<th>Phone</th> <th>Event</th> <th>SkyCode</th> </tr>' +      
   CAST ( ( SELECT td = isnull(ContactID,'null'),       '',      
       td = isnull(SkyID,'null'), '',      
       td = isnull(FirstName,'null'), '',      
       td = isnull(LastName,'null'), '',      
       td = isnull(Email,'null'), '',      
       td = isnull(Phone,'null'), '',      
       td = isnull(Event,'null'), '',      
       td = isnull(SkyCode,'null'), ''      
       FROM #tmpRegFallOff       
       FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) +      
   N'</table>' ;      
      
  EXEC msdb.dbo.sp_send_dbmail       
   @profile_name = 'SQLEmailer',      
   @recipients='USIQPCDotnetTeam@iqpc.com',      
   @subject = 'results for spIncompleteRegistrationsGet',      
   @body = @tableHTML,      
   @body_format = 'HTML' ;      
            
         
 END          
 ELSE          
  SELECT ContactID FROM Contact WHERE ContactID = 0          
END

Open in new window

Sharath S

try this.

invoke the sp with officeID as paramter.

execute spIncompleteRegistrationsGet(1)

CREATE PROCEDURE [dbo].[spIncompleteRegistrationsGet] (@officeID int)           
 -- Add the parameters for the stored procedure here          
          
AS          
BEGIN          
 -- SET NOCOUNT ON added to prevent extra result sets from          
 -- interfering with SELECT statements.          
 SET NOCOUNT ON;          
          
 DECLARE @RegistrationID INT          
 DECLARE @RegistrationCount INT          
 DECLARE @EventName VARCHAR(100)  
 DECLARE @dropoff_email_recip VARCHAR(200)  
 DECLARE @SkyCode VARCHAR(50)          
    -- Insert statements for procedure here          
          
 --Verify that there are incomplete registrations in the past hour          
 select @RegistrationCount=COUNT(RegistrationID) from dbo.Registration          
  where Status < 3           
  and ModifiedDate > dateadd(mi, -120, GETDATE())          
  and ModifiedDate <= dateadd(mi, -60, GETDATE())          
                
 IF (@RegistrationCount > 0)          
 BEGIN          
  select @RegistrationID=RegistrationID, 
                 @EventName=Event.Name, 
                 @SkyCode=SkySessionCode,
                 @dropoff_email_recip =  office.dropoff_email_recip
  from dbo.Registration           
        JOIN dbo.Event ON dbo.Event.EventID = dbo.Registration.EventID
        join dbo.office ON Event.ProductionOfficeID = office.OfficeID
   where Status < 3           
   and Registration.ModifiedDate > dateadd(mi, -120, GETDATE())          
   and Registration.ModifiedDate <= dateadd(mi, -60, GETDATE())          
   and office.OfficeID = @officeID        
             
   select ContactID, SkyID, FirstName, LastName, Email, Phone, @EventName AS Event, @SkyCode As SkyCode       
   into #tmpRegFallOff      
   from Contact where ContactID IN           
   (select ContactID from dbo.RegistrationDelegate where RegistrationID = @RegistrationID)          
         
   select * from  #tmpRegFallOff      
         
  DECLARE @tableHTML  NVARCHAR(MAX) ;      
  SET @tableHTML =      
   N'<H1>Incomplete Registrations</H1>' +      
   N'<table border="1">' +      
   N'<tr><th>ContactID</th><th>SkyID</th>' +      
   N'<th>FirstName</th><th>LastName</th><th>Email</th>' +      
   N'<th>Phone</th> <th>Event</th> <th>SkyCode</th> </tr>' +      
   CAST ( ( SELECT td = isnull(ContactID,'null'),       '',      
       td = isnull(SkyID,'null'), '',      
       td = isnull(FirstName,'null'), '',      
       td = isnull(LastName,'null'), '',      
       td = isnull(Email,'null'), '',      
       td = isnull(Phone,'null'), '',      
       td = isnull(Event,'null'), '',      
       td = isnull(SkyCode,'null'), ''      
       FROM #tmpRegFallOff       
       FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) +      
   N'</table>' ;      
      
  EXEC msdb.dbo.sp_send_dbmail       
   @profile_name = 'SQLEmailer',      
   @recipients='USIQPCDotnetTeam@iqpc.com',      
   @subject = 'results for spIncompleteRegistrationsGet',      
   @body = @tableHTML,      
   @body_format = 'HTML' ;      
            
         
 END          
 ELSE          
  SELECT ContactID FROM Contact WHERE ContactID = 0          
END

Open in new window

ASKER CERTIFIED SOLUTION

tigin44

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

supergirl2008

ASKER

basically reiterated what i said. no actual solution was provided