-- SELECt Distinct ActivityBeginDt, ActivityBeginDt, JournalDesc, JournalTypeCode, JournalClassificationCode
-- From tblJournals
-- Where ActivityCode in
-- (
--'RLA','RLA ALLEN','RLA ATL',
--'RLA AUSTIN','RLA CHAR','RLA COL','RLA CP','RLA EVANST','RLA HENDER','RLA INDY1',
--'RLA INDY2','RLA IRVING','RLA LISLE','RLA LV','RLA MPLS','RLA PHILLY','RLA PORTLA',
--'RLA RALEIG','RLA SANDIE','RLA SAT','RLA STL','RLA STLOUI','RLA TX','SUMMIT')
declare @activityCode varchar(10),
@ActivityName VarChar (500),
@BeginDate varChar (100),
@EndDate varChar (100),
@ConventionId int,
@regStatuscode varchar(100),
@DelTypeCode varchar(100)
Select @regStatusCode = 'REG'
Select @DelTypecode = 'Col'
declare curTest cursor for
Select Distinct
a.ActivityCode,
a.ActivityName,
(Select top 1 ActivityBeginDt From tblJournals where tblJournals.ActivityCode = a.ActivityCode Order by ActivityBeginDt desc),
(Select top 1 ActivityEndDt From tblJournals where tblJournals.ActivityCode = a.ActivityCode Order by ActivityEndDt desc)
from tlkActivityCodes a
Where a.ActivityName in
('Convention 1989','Convention 1991','Convention 1993',
'Convention 1995','Convention 1997','Convention 1999','Convention 2001',
'Convention 2003','Convention 2005','Convention 2007','Convention 2009',
'Convention 2011','Convention 2013','Convention 2015','Convention 2017'
)
Order by ActivityCode
open curTest
fetch next from curTest into
@activityCode,
@ActivityName ,
@BeginDate,
@EndDate
while @@fetch_status = 0
begin
Insert into dbo.tblConventions
(Convention,BeginDt,EndDt,Active, LastModBy,LastModOn,CreatedBy,CreatedOn)
Select Left(@ActivityName,100), @BeginDate, @EndDate,0, 'PatriotConv', Convert(varchar(20),getdate(),101), 'PatriotConv', Convert(varchar(20),getdate(),101)
Select @ConventionId = Scope_Identity()
Insert Into
dbo.tblConventionRegTypes
(RegistrationTypeCode,ConventionId, RegistrationType,CreateInvoice)
Select @ActivityCode,@ConventionId,'Registrant',0
Insert into dbo.tblConventionRegistrants
(PersonID,
ConventionID,
FirstName,
LastName,
RepChapter,
RepChapterCode,
RegStatusCode,
RegTypeCode,
DelTypeCode,
LastModBy,
LastModOn,
CreatedBy,
CreatedOn,
RegNote,
ArriveAirportTran,
DepartAirportTran)
Select
tblJournals.PersonID,
@ConventionId,
tblPeople.Firstname,
tblPeople.LastName,
ChapterName,
ChapterCode ,
'REG',
@activityCode,
CASE
When JournalTypeCode= 'Volunteer' Then 'Fac'
Else 'UG'
End ,
tblJournals.LastModBy,
tblJournals.LastModOn,
tblJournals.CreatedBy,
tblJournals.CreatedOn,
Convert(Varchar(100),activitydollaramount),
0,
0
From tblJournals
Join tblPeople on tblJournals.PersonId = tblPeople.PersonId
Left Join tblMemberships on tblPeople.personId = tblmemberships.PersonId
Left Join tblchapterAffiliations CA on CA.membershipId = tblmemberships.MembershipId and CA.preferred = 1
Left Join tblChapters C on C.chapterId = CA.ChapterId
where Activitycode = @activityCode
fetch next from curTest into
@activityCode,
@ActivityName ,
@BeginDate,
@EndDate
end
close curTest
deallocate curTest