August 20, 2002 at 3:49 pm
Tracking_User_Info
PK_TrackingID
SignOffFlag
EmpCount
EmpNum
UserName
PlanSpecInfo
NumOfAccts
SSNum
LName
FName
LineNumber
EmpCode
OldPIN
NewPIN
CallStartDate
CallStartTime
FaxNumber
CallEndDate
CallEndTime
NumOfTrackingEntries (total updated from User_Session table)
Tracking_User_Session( 1 record for each page the user visits corresponding to PK_TrackingID in User_Info table)
PK_TrackingID
TrackignEntryNumber
TrackingPageNumber
*/Combine these two tables into one table Tracking on TrackingID
field. Structure: /*
Tracking
PK_TrackingID
SignOffFlag
EmpCount
EmpNum
UserName
PlanSpecInfo
NumOfAccts
SSNum
LName
FName
LineNumber
EmpCode
OldPIN
NewPIN
CallStartDate
CallStartTime
FaxNumber
CallEndDate
CallEndTime
NumOfTrackingEntries
TrackingEntryNumber (repeated 250X)
** Number of TrackingEntryNumber fields with TrackingPageNumber data varies depending upon each user's session.
TrackingEntryNumber field actually contains TrackingPageNumber data from User_Session table, because the
fields would be named TrackingEntryNumber1, 2, 3....250 corresponding to the TrackingEntryNumber
fields for each TrackingPageNumber in Tracking_User_Session table.
I need to combine two tables, Tracking_User_Info, which creates a record every time a user logs on to a site, and Tracking_User_Session, which contains a corresponding record for each page the user visits to be exported to a text file called Tracking.txt. The number of Tracking_User_Session records vary from session to session, however, there must be 250 TrackingEntryNumber fields for each record created by Tracking_User_Info in the combined Tracking table or Tracking text file, ultimately, because that is the format of the text file used for processing. So, for each TrackingEntryNumber field in the Tracking table not containing TrackingPageNumber data, it must be populated with exactly 12 0's. Should a temp table be used or can the text file be created dynamically from these two tables in an ActiveX Script. I don't have much experience with ActiveX, so any advice would help. If anything is unclear, let me know and i'll clarify. Again any advice hear would help. I know how to combine tables and handle some unknown values in ActiveX, but not sure of the logic for filling in those fields based on a dynamic number of TrackingPageNumber fields.
August 20, 2002 at 5:23 pm
You can use ActiveX, but why not just use SQL?
I have to call it a night, but this should get you started.
--drop table _my_temp
declare @sql varchar(8000)
declare @sql2 varchar(8000)
set @sql = 'create table _my_temp(TrackingID int, NumOfTrackingEntries int'
declare @i int
set @i = 1
while @i < 150
begin
set @sql = @sql + 'TPN' + cast(@i as varchar(3)) + ' char(12) default ''000000000000'''
set @sql = @sql + ','
set @i = @i + 1
end
set @sql2 = ''
while @i < 251
begin
set @sql2 = @sql2 + 'TPN' + cast(@i as varchar(3)) + ' char(12) default ''000000000000'''
if @i < 251
set @sql2 = @sql2 + ','
set @i = @i + 1
end
set @sql2 = @sql2 + ')'
execute (@sql + @sql2)
declare @myinsert varchar(8000)
declare @myvalues varchar(8000)
declare @TrackingID int
declare @TrackingPageNumber char(12)
declare @counter int
declare @lastID int
set @myinsert = 'insert into _my_temp(TrackingID, numOfTrackingEntries, bla, bla, bla,'
set @myvalues = 'select TrackingID, count(*), bla bla bla,'
declare crosstab cursor for
select
t2.trackingID, t2.TrackingPageNumber
from
table2 t2 join t1 on t2.id = t1.id
where
rundate < @LastReportDate
Fetch from crosstab into
@TrackingID, @TrackingPageNumber
set @lastID = @TrackingID
set @counter = 1
while @@fetchstatus = 1
begin
if @lastID = @TrackingID
begin
set @myinsert = @myinsert + 'TPA' + cast(@counter as varchar(3))
set @myvalues = @myvalues + '' + @TrackingPageNumber + ''
end
end
fetch next from crosstab into
@trackingID, @TrackingPageNumber
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply