CREATE  PROC dbo.swn_sp 
     	       
AS
/********************************************************************
*	Program Name	:  
*          
*				
*********************************************************************
*
*	Modification History: 	 
*********************************************************************

--*************************************************************
--SET SQL STMT BASED ON SQL TYPE PASSED IN 
--*************************************************************/

/* Create a Temp Table */

Create Table  #TP_DGC(
	station_nbr char (20),
	station_name  varchar (40),
	group_code char (12),
	beg_eff_date_DGC datetime,
	end_eff_date_DGC datetime)
	 

--------------Selection Gathering Table------------------- 
 
 
DECLARE @groupcode			char (12),
	    @stationnumber 		char(20),
	    @stationame			varchar(40),
 	    @begdateG			datetime,
	    @endeffdateG		datetime,
    	    @Records			int,
	    @record_found_flag		char(1),
	    @current_date 		datetime


SELECT Distinct a.station_nbr, c.station_name, a.group_code,  
 a.beg_eff_date As beg_eff_date_DGC, a.end_eff_date As End_eff_date_DGC
 from TIES_Gathering.dbo.station_group a 
 Inner Join TIES_Gathering.dbo.station c on a.station_nbr = c.station_nbr  
 Inner Join TIES_Gathering.dbo.station_type d on a.station_nbr = d.station_nbr  
 Inner Join TIES_Gathering.dbo.group_detail e on a.group_code = e.group_code
Where ((e.group_type = 'NPN')or(e.group_type = 'WTF')or(e.group_type = 'NTF'))and
((d.type_code = 'WHM')or (d.type_code = 'CRP')) and (a.pipeline_code = '100000')
Order by  a.station_nbr, a.group_code


INSERT INTO  #TP_DGC(station_nbr,station_name,group_code,beg_eff_date_DGC,end_eff_date_DGC)
	values (@stationnumber, @stationame,@groupcode,@begdateG,@endeffdateG)


--------------Selection Marketing Table------------------- 


Create Table  #TP_SES(
	station_nbr char (20),
	station_name  varchar (40),
	group_code char (12),
	beg_eff_date_SES datetime,
	end_eff_date_SES datetime)
	 


DECLARE @groupcode_M			char (12),
	    @stationnumber_M 		char(20),
	    @stationame_M			varchar(40),
 	    @begdateM			datetime,
	    @endeffdateM	 datetime,
    	    @Records_M			int,
	    @record_found_flag_M		char(1)


Select Distinct a.station_nbr,b.station_name,a.group_code, a.beg_eff_date As beg_eff_date_SES, a.end_eff_date  As End_eff_date_SES
From TIES_Marketing.dbo.station_group a
Inner Join TIES_Marketing.dbo.station b on a.station_nbr = b.station_nbr 
Inner Join TIES_Marketing.dbo.station_type c on a.station_nbr = c.station_nbr  
Inner Join TIES_Marketing.dbo.group_detail e on a.group_code = e.group_code
Where ((c.type_code = 'WHM')or (c.type_code = 'CRP')) and (a.pipeline_code = '100001')
Order by  a.station_nbr, a.group_code



INSERT INTO  #TP_SES(station_nbr,station_name,group_code,beg_eff_date_SES,end_eff_date_SES)
	values (@stationnumber, @stationame,@groupcode,@begdateM,@endeffdateM)




---------------compare both tables--------------------
Begin

Select #TP_SES.station_nbr, #TP_SES.station_name, #TP_SES.station_group, 
#TP_DGC.beg_eff_date As beg_eff_date_DGC,  #TP_DGC.end_eff_date As End_eff_date_DGC,
#TP_SES.beg_eff_date As beg_eff_date_SES,#TP_SES.end_eff_date  As End_eff_date_SES
from #TP_SES left outer join  #TP_DGC  on station_nbr=station_nb
end 

      
----- drop temporary tables---
  
drop table #TP_DGC
drop table #TP_SES

go

 

quote:

---

Originally posted by jsmith8858

Great! Thanks for letting us know.

- Jeff
http://weblogs.sqlteam.com/JeffS

---