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
supergirl2008Asked:
Who is Participating?
 
tigin44Connect With a Mentor Commented:
simply modify it as described in the code... Since I dont have enough knowlegde about data I didnt write down exec code instead some explanations..
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          
--Declare a loop that will loop on the offices either cursor or a while loop
--BEGIN
-- Do the required corrections to return the record set for the related office by using the loop parameter.
   	          
	 --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       
-- WHERE office = loopVariable
		   FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) +      
	   N'</table>' ;      
	      
	  EXEC msdb.dbo.sp_send_dbmail       
	   @profile_name = 'SQLEmailer',      
	   @recipients='USIQPCDotnetTeam@iqpc.com', 
-- @recipients will be modified according to the loopVariable 	       
	   @subject = 'results for spIncompleteRegistrationsGet',      
	   @body = @tableHTML,      
	   @body_format = 'HTML' ;      
	            
	         
	 END          
	 ELSE          
	  SELECT ContactID FROM Contact WHERE ContactID = 0          
-- END
-- Close loop of offices..	  
END

Open in new window

0
 
tigin44Commented:
you can do that in your procedure... if you can provide your code we may guide you
0
 
SharathData EngineerCommented:
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)
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
supergirl2008Author Commented:
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

0
 
SharathData EngineerCommented:
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

0
 
supergirl2008Author Commented:
basically reiterated what i said. no actual solution was provided
0
All Courses

From novice to tech pro — start learning today.