Comparing Records from Multiple Temp tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Comparing Records from Multiple Temp tables

Author

Topic

Hakuzen
Starting Member

24 Posts

Posted - 2009-05-29 : 17:45:05

Hello, I am currently ammending an Sproc for my company to add certain functionality.. I am having a problem, though.

I have two temp tables, and I am trying to Compare a datetime record in one table, against datetime records in the second table (Starting with the most recent).

This is my current code(Just the code I've written new, not the entire sproc):

Create table #multiticket
            (   
      idTSDetail int,
      CallCenterFSEID int,
      CustomerID int,
      ContactName varchar(250),
      ContactPhone varchar(50),
      DateContacted datetime,
      TimeContacted int,
      TimeCalledBack int,
      EquipmentID int,
      CodeID int,
      Comments varchar(6000),
      PumpID int,
      TableID int,
      LevelID int,
      TimeStampID int,
      SpecialRequest int,
      CreditTermsID int,
      DateRequestedDue datetime,
      DateLatestDue datetime,
      DateCustomerConfirm datetime,
      DateEstArrival datetime,
      DateArrived datetime,
      DateEstDeparture datetime,
      DateDeparted datetime,
      FSEID int,
      TSR varchar(30),
      TravelEstID int,
      IdleTimeID int,
      DateTSRReturn datetime,
      TravelActual int,
      IdleActual int,
      HrsOnSite int,
      DateInstallAck datetime,
      DateCoreAudit datetime,
      DateTripReport datetime,
      Deleted int,
      dtCreated datetime,
      CreatedBy varchar(50),
      UpdatedBy varchar(50),
      dtLastUpdated datetime,
      CancelCall int,
      DateCallBackDue datetime,
      DateFirstCallBack datetime,
      CallBackContact varchar(250),
      InstallScore varchar(30),
      DateInstallReturned datetime,
      TimeContactedStr varchar(30),
      TimeCalledBackStr varchar(30),
      WOEmailed int,
      ContactPhoneAlt varchar(50),
      SpecialFSE int,
      SpecialRestrict varchar(50),
      EmailAddress varchar(250),
      PartsReqd int,
      PartsOrderedBy int,
      PartsOrdered int,
      DateSchedCallBackDue datetime,
      DateSchedCallBackDone datetime,
      DateSpecialRequest datetime,
      DatePartsETA datetime,
      WorkOrder varchar(20),
      PurchaseOrder varchar(20),
      PMQuoteNbr int,
      CustomerReadinessID int,
      TrainingLocID int,
      MultiTicket int,
      Color varchar(20),
      CallBackDone int,
      DisplayOn int)
      
            INSERT INTO #multiticket (idTSDetail, CallCenterFSEID, CustomerID, ContactName, ContactPhone, DateContacted, TimeContacted, TimeCalledBack, EquipmentID, CodeID, Comments, PumpID, TableID, LevelID, TimeStampID, SpecialRequest,
CreditTermsID, DateRequestedDue, DateLatestDue, DateCustomerConfirm, DateEstArrival, DateArrived, DateEstDeparture, DateDeparted, FSEID, TSR, TravelEstID, IdleTimeID, DateTSRReturn, TravelActual, IdleActual, HrsOnSite, DateInstallAck,
 DateCoreAudit, DateTripReport, Deleted, dtCreated, CreatedBy, UpdatedBy, dtLastUpdated, CancelCall, DateCallBackDue, DateFirstCallBack, CallBackContact, InstallScore, DateInstallReturned, TimeContactedStr, TimeCalledBackStr, WOEmailed,
 ContactPhoneAlt, SpecialFSE, SpecialRestrict, EmailAddress, PartsReqd, PartsOrderedBy, PartsOrdered, DateSchedCallBackDue, DateSchedCallBackDone, DateSpecialRequest, DatePartsETA, WorkOrder, PurchaseOrder, PMQuoteNbr, CustomerReadinessID,
 TrainingLocID,MultiTicket,Color, CallBackDone, DisplayOn) 
      SELECT idTSDetail, CallCenterFSEID, CustomerID, ContactName, ContactPhone, DateContacted, TimeContacted, TimeCalledBack, EquipmentID, CodeID, Comments, PumpID, TableID, LevelID, TimeStampID, SpecialRequest,
CreditTermsID, DateRequestedDue, DateLatestDue, DateCustomerConfirm, DateEstArrival, DateArrived, DateEstDeparture, DateDeparted, FSEID, TSR, TravelEstID, IdleTimeID, DateTSRReturn, TravelActual, IdleActual, HrsOnSite, DateInstallAck,
 DateCoreAudit, DateTripReport, Deleted, dtCreated, CreatedBy, UpdatedBy, dtLastUpdated, CancelCall, DateCallBackDue, DateFirstCallBack, CallBackContact, InstallScore, DateInstallReturned, TimeContactedStr, TimeCalledBackStr, WOEmailed,
 ContactPhoneAlt, SpecialFSE, SpecialRestrict, EmailAddress, PartsReqd, PartsOrderedBy, PartsOrdered, DateSchedCallBackDue, DateSchedCallBackDone, DateSpecialRequest, DatePartsETA, WorkOrder, PurchaseOrder, PMQuoteNbr, CustomerReadinessID,
 TrainingLocID, 0, Null, 0, 0 FROM flowplan.dbo.TSDetail where @StartDate <= DateEstArrival

	Select FSEID,  Max(idTSDetail)MaxID, convert(datetime,'1/1/1900',101) as DateFirstCallBack into #CompareTemp from #MultiTicket
		Group By FSEID
	Update #CompareTemp set DateFirstCallBack=Null
		
	Update dbo.TSDetail SET DateFirstCallBack=DateEstArrival From 
		dbo.TSDetail WHERE DateFirstCallBack = Null
		
	Update dbo.TSDetail SET DateCustomerConfirm=DateEstDeparture From 
		dbo.TSDetail WHERE DateCustomerConfirm = Null
		
	Update #compareTemp SET DateFirstCallBack=#multiticket.DateEstArrival From 
		#MultiTicket WHERE #MultiTicket.IDTSDetail=MaxID


		
	Update #MultiTicket
		Set MultiTicket =1
	From #CompareTemp
		inner join #MultiTicket on #CompareTemp.FSEID = #multiticket.FSEID
			Where convert(datetime,#CompareTemp.DateFirstCallBack,101) <= convert(datetime,#multiticket.DateCustomerConfirm,101)
				And #CompareTemp.FSEID = #multiticket.FSEID

The logic is, Take the latest Arrival Date for an FSE(Person) and compare it to the departure dates for that same FSE's other tickets for that week to see if any overlap.

As of right now, the code works only for the first record, but I cannot figure out how to get it to loop through the next 6 records. Somebody suggested a cursor, but I've heard cursors are very bad practice and having looked at it, also didn't seem like it was the best solution.

I'm not very advanced in this field, what would make sense to me is to change MaxID to MaxID - 1, then re-do the compare, then set it to -2 and re-do the compare.. but obviously that function does not exist, and the ID# for multiple records on the same day are not neccisarily gonna be only 1 digit less.

Does anyone have any tips? i've been stumped on this one for a week.. If you need any additional information let me know.

Thanks!
-Danial

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-30 : 03:32:26

seems like what you need is this


Create table #multiticket
            (   
      idTSDetail int,
      CallCenterFSEID int,
      CustomerID int,
      ContactName varchar(250),
      ContactPhone varchar(50),
      DateContacted datetime,
      TimeContacted int,
      TimeCalledBack int,
      EquipmentID int,
      CodeID int,
      Comments varchar(6000),
      PumpID int,
      TableID int,
      LevelID int,
      TimeStampID int,
      SpecialRequest int,
      CreditTermsID int,
      DateRequestedDue datetime,
      DateLatestDue datetime,
      DateCustomerConfirm datetime,
      DateEstArrival datetime,
      DateArrived datetime,
      DateEstDeparture datetime,
      DateDeparted datetime,
      FSEID int,
      TSR varchar(30),
      TravelEstID int,
      IdleTimeID int,
      DateTSRReturn datetime,
      TravelActual int,
      IdleActual int,
      HrsOnSite int,
      DateInstallAck datetime,
      DateCoreAudit datetime,
      DateTripReport datetime,
      Deleted int,
      dtCreated datetime,
      CreatedBy varchar(50),
      UpdatedBy varchar(50),
      dtLastUpdated datetime,
      CancelCall int,
      DateCallBackDue datetime,
      DateFirstCallBack datetime,
      CallBackContact varchar(250),
      InstallScore varchar(30),
      DateInstallReturned datetime,
      TimeContactedStr varchar(30),
      TimeCalledBackStr varchar(30),
      WOEmailed int,
      ContactPhoneAlt varchar(50),
      SpecialFSE int,
      SpecialRestrict varchar(50),
      EmailAddress varchar(250),
      PartsReqd int,
      PartsOrderedBy int,
      PartsOrdered int,
      DateSchedCallBackDue datetime,
      DateSchedCallBackDone datetime,
      DateSpecialRequest datetime,
      DatePartsETA datetime,
      WorkOrder varchar(20),
      PurchaseOrder varchar(20),
      PMQuoteNbr int,
      CustomerReadinessID int,
      TrainingLocID int,
      MultiTicket int,
      Color varchar(20),
      CallBackDone int,
      DisplayOn int)
      
            INSERT INTO #multiticket (idTSDetail, CallCenterFSEID, CustomerID, ContactName, ContactPhone, DateContacted, TimeContacted, TimeCalledBack, EquipmentID, CodeID, Comments, PumpID, TableID, LevelID, TimeStampID, SpecialRequest,
CreditTermsID, DateRequestedDue, DateLatestDue, DateCustomerConfirm, DateEstArrival, DateArrived, DateEstDeparture, DateDeparted, FSEID, TSR, TravelEstID, IdleTimeID, DateTSRReturn, TravelActual, IdleActual, HrsOnSite, DateInstallAck,
 DateCoreAudit, DateTripReport, Deleted, dtCreated, CreatedBy, UpdatedBy, dtLastUpdated, CancelCall, DateCallBackDue, DateFirstCallBack, CallBackContact, InstallScore, DateInstallReturned, TimeContactedStr, TimeCalledBackStr, WOEmailed,
 ContactPhoneAlt, SpecialFSE, SpecialRestrict, EmailAddress, PartsReqd, PartsOrderedBy, PartsOrdered, DateSchedCallBackDue, DateSchedCallBackDone, DateSpecialRequest, DatePartsETA, WorkOrder, PurchaseOrder, PMQuoteNbr, CustomerReadinessID,
 TrainingLocID,MultiTicket,Color, CallBackDone, DisplayOn) 
      SELECT idTSDetail, CallCenterFSEID, CustomerID, ContactName, ContactPhone, DateContacted, TimeContacted, TimeCalledBack, EquipmentID, CodeID, Comments, PumpID, TableID, LevelID, TimeStampID, SpecialRequest,
CreditTermsID, DateRequestedDue, DateLatestDue, DateCustomerConfirm, DateEstArrival, DateArrived, DateEstDeparture, DateDeparted, FSEID, TSR, TravelEstID, IdleTimeID, DateTSRReturn, TravelActual, IdleActual, HrsOnSite, DateInstallAck,
 DateCoreAudit, DateTripReport, Deleted, dtCreated, CreatedBy, UpdatedBy, dtLastUpdated, CancelCall, DateCallBackDue, DateFirstCallBack, CallBackContact, InstallScore, DateInstallReturned, TimeContactedStr, TimeCalledBackStr, WOEmailed,
 ContactPhoneAlt, SpecialFSE, SpecialRestrict, EmailAddress, PartsReqd, PartsOrderedBy, PartsOrdered, DateSchedCallBackDue, DateSchedCallBackDone, DateSpecialRequest, DatePartsETA, WorkOrder, PurchaseOrder, PMQuoteNbr, CustomerReadinessID,
 TrainingLocID, 0, Null, 0, 0 FROM flowplan.dbo.TSDetail where @StartDate <= DateEstArrival

	
	Update dbo.TSDetail SET DateFirstCallBack=DateEstArrival From 
		dbo.TSDetail WHERE DateFirstCallBack IS Null
		
	Update dbo.TSDetail SET DateCustomerConfirm=DateEstDeparture From 
		dbo.TSDetail WHERE DateCustomerConfirm IS Null
		
	
UPDATE m1
SET m1.MultiTicket =1
FROM #MultiTicket m1
INNER JOIN (SELECT FSEID,DateFirstCallBack
            FROM
             (SELECT FSEID,DateFirstCallBack,ROW_NUMBER() OVER(PARTITION BY FSEID ORDER BY idTSDetail DESC) AS Seq
              FROM #MultiTicket)m
            WHERE Seq=1
            )m2
ON m2.FSEID=m1.FSEID
WHERE m1.DateCustomerConfirm>DATEADD(dd,DATEDIFF(dd,0,m2.DateFirstCallBack),0)

Hakuzen
Starting Member

24 Posts

Posted - 2009-06-01 : 12:51:17

Hello!

Unfortunatly I have just been told by my boss (after trying to implement your code) that we are on SQL2000 still, which sadly does not include the ROW_NUMBER function.. Any ideas on a work around? I thank you deeply for all of your kind support.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-01 : 13:54:02

[code]
Create table #multiticket
(
idTSDetail int,
CallCenterFSEID int,
CustomerID int,
ContactName varchar(250),
ContactPhone varchar(50),
DateContacted datetime,
TimeContacted int,
TimeCalledBack int,
EquipmentID int,
CodeID int,
Comments varchar(6000),
PumpID int,
TableID int,
LevelID int,
TimeStampID int,
SpecialRequest int,
CreditTermsID int,
DateRequestedDue datetime,
DateLatestDue datetime,
DateCustomerConfirm datetime,
DateEstArrival datetime,
DateArrived datetime,
DateEstDeparture datetime,
DateDeparted datetime,
FSEID int,
TSR varchar(30),
TravelEstID int,
IdleTimeID int,
DateTSRReturn datetime,
TravelActual int,
IdleActual int,
HrsOnSite int,
DateInstallAck datetime,
DateCoreAudit datetime,
DateTripReport datetime,
Deleted int,
dtCreated datetime,
CreatedBy varchar(50),
UpdatedBy varchar(50),
dtLastUpdated datetime,
CancelCall int,
DateCallBackDue datetime,
DateFirstCallBack datetime,
CallBackContact varchar(250),
InstallScore varchar(30),
DateInstallReturned datetime,
TimeContactedStr varchar(30),
TimeCalledBackStr varchar(30),
WOEmailed int,
ContactPhoneAlt varchar(50),
SpecialFSE int,
SpecialRestrict varchar(50),
EmailAddress varchar(250),
PartsReqd int,
PartsOrderedBy int,
PartsOrdered int,
DateSchedCallBackDue datetime,
DateSchedCallBackDone datetime,
DateSpecialRequest datetime,
DatePartsETA datetime,
WorkOrder varchar(20),
PurchaseOrder varchar(20),
PMQuoteNbr int,
CustomerReadinessID int,
TrainingLocID int,
MultiTicket int,
Color varchar(20),
CallBackDone int,
DisplayOn int)

INSERT INTO #multiticket (idTSDetail, CallCenterFSEID, CustomerID, ContactName, ContactPhone, DateContacted, TimeContacted, TimeCalledBack, EquipmentID, CodeID, Comments, PumpID, TableID, LevelID, TimeStampID, SpecialRequest,
CreditTermsID, DateRequestedDue, DateLatestDue, DateCustomerConfirm, DateEstArrival, DateArrived, DateEstDeparture, DateDeparted, FSEID, TSR, TravelEstID, IdleTimeID, DateTSRReturn, TravelActual, IdleActual, HrsOnSite, DateInstallAck,
DateCoreAudit, DateTripReport, Deleted, dtCreated, CreatedBy, UpdatedBy, dtLastUpdated, CancelCall, DateCallBackDue, DateFirstCallBack, CallBackContact, InstallScore, DateInstallReturned, TimeContactedStr, TimeCalledBackStr, WOEmailed,
ContactPhoneAlt, SpecialFSE, SpecialRestrict, EmailAddress, PartsReqd, PartsOrderedBy, PartsOrdered, DateSchedCallBackDue, DateSchedCallBackDone, DateSpecialRequest, DatePartsETA, WorkOrder, PurchaseOrder, PMQuoteNbr, CustomerReadinessID,
TrainingLocID,MultiTicket,Color, CallBackDone, DisplayOn)
SELECT idTSDetail, CallCenterFSEID, CustomerID, ContactName, ContactPhone, DateContacted, TimeContacted, TimeCalledBack, EquipmentID, CodeID, Comments, PumpID, TableID, LevelID, TimeStampID, SpecialRequest,
CreditTermsID, DateRequestedDue, DateLatestDue, DateCustomerConfirm, DateEstArrival, DateArrived, DateEstDeparture, DateDeparted, FSEID, TSR, TravelEstID, IdleTimeID, DateTSRReturn, TravelActual, IdleActual, HrsOnSite, DateInstallAck,
DateCoreAudit, DateTripReport, Deleted, dtCreated, CreatedBy, UpdatedBy, dtLastUpdated, CancelCall, DateCallBackDue, DateFirstCallBack, CallBackContact, InstallScore, DateInstallReturned, TimeContactedStr, TimeCalledBackStr, WOEmailed,
ContactPhoneAlt, SpecialFSE, SpecialRestrict, EmailAddress, PartsReqd, PartsOrderedBy, PartsOrdered, DateSchedCallBackDue, DateSchedCallBackDone, DateSpecialRequest, DatePartsETA, WorkOrder, PurchaseOrder, PMQuoteNbr, CustomerReadinessID,
TrainingLocID, 0, Null, 0, 0 FROM flowplan.dbo.TSDetail where @StartDate <= DateEstArrival

Update dbo.TSDetail SET DateFirstCallBack=DateEstArrival From
dbo.TSDetail WHERE DateFirstCallBack IS Null

Update dbo.TSDetail SET DateCustomerConfirm=DateEstDeparture From
dbo.TSDetail WHERE DateCustomerConfirm IS Null

UPDATE m1
SET m1.MultiTicket =1
FROM #MultiTicket m1
INNER JOIN (SELECT t1.FSEID,t1.DateFirstCallBack
FROM #MultiTicket t1
INNER JOIN (SELECT FSEID,MAX(idTSDetail) AS Latest
FROM #MultiTicket
GROUP BY FSEID)t2
ON t2.FSEID=t1.FSEID
AND t2.Latest=t1.FSEID
)m2
ON m2.FSEID=m1.FSEID
WHERE m1.DateCustomerConfirm>DATEADD(dd,DATEDIFF(dd,0,m2.DateFirstCallBack),0)
[/code]

Hakuzen
Starting Member

24 Posts

Posted - 2009-06-01 : 14:16:59

Hello again,

It seems that the code does not work. it doesn't seem to be returning any true results for MultiTicket where those results should exist.

I had a hunch and added

Update #MultiTicket
set MultiTicket = m1.MultiTicket
from #MultiTicket m1

at the end of the code but it made no effect.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-01 : 14:23:04

can you illustrate with some sample data a case where it doesnt work and you expect it to work?

Hakuzen
Starting Member

24 Posts

Posted - 2009-06-01 : 15:11:10

well in all honesty it is not returning any results as true at all.

for example:

69601 W 6-May Carlos Rodriguez
69920 W 6-May Carlos Rodriguez
69983 W 7-May Carlos Rodriguez -DateCustomer = 05/06/2009
69984 W 7-May Carlos Rodriguez -DateFirst = 05/06/2009

Carlos has a multi ticket on both the 6th and the 7th; which means that on the 7th, the DateFirstCallBack field for the first record fetched for this person is less than or equal to the DateCustomerConfirm for the next ticket fetched on that same day. In this specific instance, they are equal. the code should return a 1 for multiticket in this case.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-02 : 13:06:51

can you provide field names as well along with data so that i can provide you query with correct columns.

Hakuzen
Starting Member

24 Posts

Posted - 2009-06-02 : 17:12:55

quote:
Originally posted by visakh16

can you provide field names as well along with data so that i can provide you query with correct columns.

how many records do you need? can you be a little more specific? Thanks again for your continued assistance!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-03 : 13:48:16

its not the number that matters but column names. please see below for guidelines for posting a question

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Hakuzen
Starting Member

24 Posts

Posted - 2009-06-03 : 14:31:35

Hopefully this is what you we're requesting.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_TSEdit_TSDetail]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TSEdit] DROP CONSTRAINT FK_TSEdit_TSDetail
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_TSWOCriticalCare_TSDetail]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TSWOCriticalCare] DROP CONSTRAINT FK_TSWOCriticalCare_TSDetail
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_TSWODefects_TSDetail]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TSWODefects] DROP CONSTRAINT FK_TSWODefects_TSDetail
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_TSWOTSRDetails_TSDetail]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TSWOTSRDetails] DROP CONSTRAINT FK_TSWOTSRDetails_TSDetail
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TSDetail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TSDetail]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TSFSE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TSFSE]
GO

CREATE TABLE [dbo].[TSDetail] (
                [idTSDetail] [int] IDENTITY (1, 1) NOT NULL ,
                [CallCenterFSEID] [int] NOT NULL ,
                [CustomerID] [int] NOT NULL ,
                [ContactName] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
                [ContactPhone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
                [DateContacted] [datetime] NOT NULL ,
                [TimeContacted] [int] NULL ,
                [TimeCalledBack] [int] NULL ,
                [EquipmentID] [int] NULL ,
                [CodeID] [int] NOT NULL ,
                [Comments] [varchar] (6000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
                [PumpID] [int] NULL ,
                [TableID] [int] NULL ,
                [LevelID] [int] NOT NULL ,
                [TimeStampID] [int] NOT NULL ,
                [SpecialRequest] [int] NOT NULL ,
                [CreditTermsID] [int] NULL ,
                [DateRequestedDue] [datetime] NULL ,
                [DateLatestDue] [datetime] NULL ,
                [DateCustomerConfirm] [datetime] NULL ,
                [DateEstArrival] [datetime] NULL ,
                [DateArrived] [datetime] NULL ,
                [DateEstDeparture] [datetime] NULL ,
                [DateDeparted] [datetime] NULL ,
                [FSEID] [int] NULL ,
                [TSR] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
                [TravelEstID] [int] NULL ,
                [IdleTimeID] [int] NULL ,
                [DateTSRReturn] [datetime] NULL ,
                [TravelActual] [int] NULL ,
                [IdleActual] [int] NULL ,
                [HrsOnSite] [int] NULL ,
                [DateInstallAck] [datetime] NULL ,
                [DateCoreAudit] [datetime] NULL ,
                [DateTripReport] [datetime] NULL ,
                [Deleted] [int] NOT NULL ,
                [dtCreated] [datetime] NOT NULL ,
                [CreatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
                [UpdatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
                [dtLastUpdated] [datetime] NULL ,
                [CancelCall] [int] NOT NULL ,
                [DateCallBackDue] [datetime] NULL ,
                [DateFirstCallBack] [datetime] NULL ,
                [CallBackContact] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
                [InstallScore] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
                [DateInstallReturned] [datetime] NULL ,
                [TimeContactedStr] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
                [TimeCalledBackStr] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
                [WOEmailed] [int] NULL ,
                [ContactPhoneAlt] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
                [SpecialFSE] [int] NULL ,
                [SpecialRestrict] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
                [EmailAddress] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
                [PartsReqd] [int] NULL ,
                [PartsOrderedBy] [int] NULL ,
                [PartsOrdered] [int] NULL ,
                [DateSchedCallBackDue] [datetime] NULL ,
                [DateSchedCallBackDone] [datetime] NULL ,
                [DateSpecialRequest] [datetime] NULL ,
                [DatePartsETA] [datetime] NULL ,
                [WorkOrder] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
                [PurchaseOrder] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
                [PMQuoteNbr] [int] NULL ,
                [CustomerReadinessID] [int] NULL ,
                [TrainingLocID] [int] NULL ,
                [MultiTicket] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TSFSE] (
                [idTSFSE] [int] IDENTITY (1, 1) NOT NULL ,
                [FSEName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
                [Region] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
                [State] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
                [IsActive] [int] NOT NULL ,
                [UpdatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
                [dtLastUpdated] [datetime] NULL ,
                [dtCreated] [datetime] NOT NULL ,
                [EmailAddress] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
                [HomeZipCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
                [MultiTicket] [int] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TSDetail] WITH NOCHECK ADD 
                CONSTRAINT [PK_TSDetail] PRIMARY KEY  CLUSTERED 
                (
                                [idTSDetail]
                ) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[TSFSE] WITH NOCHECK ADD 
                CONSTRAINT [PK_TSFSE] PRIMARY KEY  CLUSTERED 
                (
                                [idTSFSE]
                ) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[TSDetail] ADD 
                CONSTRAINT [DF_TSDetail_SpecialRequest] DEFAULT (0) FOR [SpecialRequest],
                CONSTRAINT [DF_TSDetail_Deleted] DEFAULT (0) FOR [Deleted],
                CONSTRAINT [DF_TechSchedDetail_dtCreated] DEFAULT (getdate()) FOR [dtCreated],
                CONSTRAINT [DF_TSDetail_CancelCall] DEFAULT (0) FOR [CancelCall],
                CONSTRAINT [DF_TSDetail_WOEmailed] DEFAULT (0) FOR [WOEmailed],
                CONSTRAINT [DF_TSDetail_PMQuoteNbr] DEFAULT (0) FOR [PMQuoteNbr],
                CONSTRAINT [DF_TSDetail_CustomerReadinessID] DEFAULT (0) FOR [CustomerReadinessID]
GO

 CREATE  INDEX [IX_TSDetail] ON [dbo].[TSDetail]([DateContacted] DESC ) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[TSFSE] ADD 
                CONSTRAINT [DF_TSFSE_IsActive] DEFAULT (1) FOR [IsActive],
                CONSTRAINT [DF_TSFSE_dtCreated] DEFAULT (getdate()) FOR [dtCreated],
                CONSTRAINT [IX_TSFSE] UNIQUE  NONCLUSTERED 
                (
                                [FSEName],
                                [Region],
                                [State]
                ) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

let me know if you need any other information^^ Thank you!

Hakuzen
Starting Member

24 Posts

Posted - 2009-06-03 : 17:14:00

I'd also like to give you the most recent copy of my code so that you might see more clearly what I am trying to do:

Create table #multiticket
            (   
      idTSDetail int,
      CallCenterFSEID int,
      CustomerID int,
      ContactName varchar(250),
      ContactPhone varchar(50),
      DateContacted datetime,
      TimeContacted int,
      TimeCalledBack int,
      EquipmentID int,
      CodeID int,
      Comments varchar(6000),
      PumpID int,
      TableID int,
      LevelID int,
      TimeStampID int,
      SpecialRequest int,
      CreditTermsID int,
      DateRequestedDue datetime,
      DateLatestDue datetime,
      DateCustomerConfirm datetime,
      DateEstArrival datetime,
      DateArrived datetime,
      DateEstDeparture datetime,
      DateDeparted datetime,
      FSEID int,
      TSR varchar(30),
      TravelEstID int,
      IdleTimeID int,
      DateTSRReturn datetime,
      TravelActual int,
      IdleActual int,
      HrsOnSite int,
      DateInstallAck datetime,
      DateCoreAudit datetime,
      DateTripReport datetime,
      Deleted int,
      dtCreated datetime,
      CreatedBy varchar(50),
      UpdatedBy varchar(50),
      dtLastUpdated datetime,
      CancelCall int,
      DateCallBackDue datetime,
      DateFirstCallBack datetime,
      CallBackContact varchar(250),
      InstallScore varchar(30),
      DateInstallReturned datetime,
      TimeContactedStr varchar(30),
      TimeCalledBackStr varchar(30),
      WOEmailed int,
      ContactPhoneAlt varchar(50),
      SpecialFSE int,
      SpecialRestrict varchar(50),
      EmailAddress varchar(250),
      PartsReqd int,
      PartsOrderedBy int,
      PartsOrdered int,
      DateSchedCallBackDue datetime,
      DateSchedCallBackDone datetime,
      DateSpecialRequest datetime,
      DatePartsETA datetime,
      WorkOrder varchar(20),
      PurchaseOrder varchar(20),
      PMQuoteNbr int,
      CustomerReadinessID int,
      TrainingLocID int,
      MultiTicket int,
      Color varchar(20),
      CallBackDone int,
      DisplayOn int)
      
            INSERT INTO #multiticket (idTSDetail, CallCenterFSEID, CustomerID, ContactName, ContactPhone, DateContacted, TimeContacted, TimeCalledBack, EquipmentID, CodeID, Comments, PumpID, TableID, LevelID, TimeStampID, SpecialRequest,
CreditTermsID, DateRequestedDue, DateLatestDue, DateCustomerConfirm, DateEstArrival, DateArrived, DateEstDeparture, DateDeparted, FSEID, TSR, TravelEstID, IdleTimeID, DateTSRReturn, TravelActual, IdleActual, HrsOnSite, DateInstallAck,
 DateCoreAudit, DateTripReport, Deleted, dtCreated, CreatedBy, UpdatedBy, dtLastUpdated, CancelCall, DateCallBackDue, DateFirstCallBack, CallBackContact, InstallScore, DateInstallReturned, TimeContactedStr, TimeCalledBackStr, WOEmailed,
 ContactPhoneAlt, SpecialFSE, SpecialRestrict, EmailAddress, PartsReqd, PartsOrderedBy, PartsOrdered, DateSchedCallBackDue, DateSchedCallBackDone, DateSpecialRequest, DatePartsETA, WorkOrder, PurchaseOrder, PMQuoteNbr, CustomerReadinessID,
 TrainingLocID,MultiTicket,Color, CallBackDone, DisplayOn) 
      SELECT idTSDetail, CallCenterFSEID, CustomerID, ContactName, ContactPhone, DateContacted, TimeContacted, TimeCalledBack, EquipmentID, CodeID, Comments, PumpID, TableID, LevelID, TimeStampID, SpecialRequest,
CreditTermsID, DateRequestedDue, DateLatestDue, DateCustomerConfirm, DateEstArrival, DateArrived, DateEstDeparture, DateDeparted, FSEID, TSR, TravelEstID, IdleTimeID, DateTSRReturn, TravelActual, IdleActual, HrsOnSite, DateInstallAck,
 DateCoreAudit, DateTripReport, Deleted, dtCreated, CreatedBy, UpdatedBy, dtLastUpdated, CancelCall, DateCallBackDue, DateFirstCallBack, CallBackContact, InstallScore, DateInstallReturned, TimeContactedStr, TimeCalledBackStr, WOEmailed,
 ContactPhoneAlt, SpecialFSE, SpecialRestrict, EmailAddress, PartsReqd, PartsOrderedBy, PartsOrdered, DateSchedCallBackDue, DateSchedCallBackDone, DateSpecialRequest, DatePartsETA, WorkOrder, PurchaseOrder, PMQuoteNbr, CustomerReadinessID,
 TrainingLocID, 0, Null, 0, 0 FROM flowplan.dbo.TSDetail where @StartDate <= DateEstArrival

	Select FSEID,  Max(idTSDetail) MaxID, convert(datetime,'1/1/1900',101) as DateFirstCallBack into #CompareTemp from #MultiTicket
		Group By FSEID
	Update #CompareTemp set DateFirstCallBack=Null
		
	Update dbo.TSDetail SET DateFirstCallBack=DateEstArrival From 
		dbo.TSDetail WHERE DateFirstCallBack = Null
		
	Update dbo.TSDetail SET DateCustomerConfirm=DateEstDeparture From 
		dbo.TSDetail WHERE DateCustomerConfirm = Null
		
	Update #compareTemp SET DateFirstCallBack=#multiticket.DateEstArrival From 
		#MultiTicket WHERE #MultiTicket.IDTSDetail=MaxID
		
	Update #multiticket SET DateCustomerConfirm=DateEstDeparture From 
		#comparetemp WHERE #MultiTicket.IDTSDetail=MaxID


	declare @RowNum int
	Update #multiticket SET DateCustomerConfirm=DateEstDeparture From  #multiticket
			inner join #comparetemp on #MultiTicket.FSEID = #CompareTemp.FSEID
		where DateCustomerConfirm > DateEstDeparture And #CompareTemp.FSEID = #multiticket.FSEID
	set @RowNum = 0 
	WHILE @RowNum < 7
	BEGIN
		Update #MultiTicket
		Set MultiTicket =1
		From #CompareTemp
		inner join #MultiTicket on #CompareTemp.FSEID = #multiticket.FSEID
		Where #MultiTicket.DateCustomerConfirm >= #CompareTemp.DateFirstCallBack
		And #CompareTemp.FSEID = #multiticket.FSEID

     	Update #multiticket SET DateCustomerConfirm=DateEstDeparture From  #multiticket
		inner join #comparetemp on #MultiTicket.FSEID = #CompareTemp.FSEID
		where DateCustomerConfirm > DateEstDeparture And #CompareTemp.FSEID = #multiticket.FSEID
		
		Update #comparetemp SET DateFirstCallBack=DateEstArrival From  #multiticket
		inner join #comparetemp on #MultiTicket.FSEID = #CompareTemp.FSEID
		where #comparetemp.DateFirstCallBack > DateEstArrival And #CompareTemp.FSEID = #multiticket.FSEID
		

       set @RowNum = @RowNum + 1
	END

Hakuzen
Starting Member

24 Posts

Posted - 2009-06-26 : 18:50:49

I am still trying to fix this problem.. I've tried many different ways of doing it but I cant seem to get it to work. Here is a snippet of my most recent code.. Please help if you can ~_~

Create table #multiticket
            (   
      idTSDetail int,
      CallCenterFSEID int,
      CustomerID int,
      ContactName varchar(250),
      ContactPhone varchar(50),
      DateContacted datetime,
      TimeContacted int,
      TimeCalledBack int,
      EquipmentID int,
      CodeID int,
      Comments varchar(6000),
      PumpID int,
      TableID int,
      LevelID int,
      TimeStampID int,
      SpecialRequest int,
      CreditTermsID int,
      DateRequestedDue datetime,
      DateLatestDue datetime,
      DateCustomerConfirm datetime,
      DateEstArrival datetime,
      DateArrived datetime,
      DateEstDeparture datetime,
      DateDeparted datetime,
      FSEID int,
      TSR varchar(30),
      TravelEstID int,
      IdleTimeID int,
      DateTSRReturn datetime,
      TravelActual int,
      IdleActual int,
      HrsOnSite int,
      DateInstallAck datetime,
      DateCoreAudit datetime,
      DateTripReport datetime,
      Deleted int,
      dtCreated datetime,
      CreatedBy varchar(50),
      UpdatedBy varchar(50),
      dtLastUpdated datetime,
      CancelCall int,
      DateCallBackDue datetime,
      DateFirstCallBack datetime,
      CallBackContact varchar(250),
      InstallScore varchar(30),
      DateInstallReturned datetime,
      TimeContactedStr varchar(30),
      TimeCalledBackStr varchar(30),
      WOEmailed int,
      ContactPhoneAlt varchar(50),
      SpecialFSE int,
      SpecialRestrict varchar(50),
      EmailAddress varchar(250),
      PartsReqd int,
      PartsOrderedBy int,
      PartsOrdered int,
      DateSchedCallBackDue datetime,
      DateSchedCallBackDone datetime,
      DateSpecialRequest datetime,
      DatePartsETA datetime,
      WorkOrder varchar(20),
      PurchaseOrder varchar(20),
      PMQuoteNbr int,
      CustomerReadinessID int,
      TrainingLocID int,
      MultiTicket int,
      Color varchar(20),
      CallBackDone int,
      DisplayOn int)
      
            INSERT INTO #multiticket (idTSDetail, CallCenterFSEID, CustomerID, ContactName, ContactPhone, DateContacted, TimeContacted, TimeCalledBack, EquipmentID, CodeID, Comments, PumpID, TableID, LevelID, TimeStampID, SpecialRequest,
CreditTermsID, DateRequestedDue, DateLatestDue, DateCustomerConfirm, DateEstArrival, DateArrived, DateEstDeparture, DateDeparted, FSEID, TSR, TravelEstID, IdleTimeID, DateTSRReturn, TravelActual, IdleActual, HrsOnSite, DateInstallAck,
 DateCoreAudit, DateTripReport, Deleted, dtCreated, CreatedBy, UpdatedBy, dtLastUpdated, CancelCall, DateCallBackDue, DateFirstCallBack, CallBackContact, InstallScore, DateInstallReturned, TimeContactedStr, TimeCalledBackStr, WOEmailed,
 ContactPhoneAlt, SpecialFSE, SpecialRestrict, EmailAddress, PartsReqd, PartsOrderedBy, PartsOrdered, DateSchedCallBackDue, DateSchedCallBackDone, DateSpecialRequest, DatePartsETA, WorkOrder, PurchaseOrder, PMQuoteNbr, CustomerReadinessID,
 TrainingLocID,MultiTicket,Color, CallBackDone, DisplayOn) 
      SELECT idTSDetail, CallCenterFSEID, CustomerID, ContactName, ContactPhone, DateContacted, TimeContacted, TimeCalledBack, EquipmentID, CodeID, Comments, PumpID, TableID, LevelID, TimeStampID, SpecialRequest,
CreditTermsID, DateRequestedDue, DateLatestDue, DateCustomerConfirm, DateEstArrival, DateArrived, DateEstDeparture, DateDeparted, FSEID, TSR, TravelEstID, IdleTimeID, DateTSRReturn, TravelActual, IdleActual, HrsOnSite, DateInstallAck,
 DateCoreAudit, DateTripReport, Deleted, dtCreated, CreatedBy, UpdatedBy, dtLastUpdated, CancelCall, DateCallBackDue, DateFirstCallBack, CallBackContact, InstallScore, DateInstallReturned, TimeContactedStr, TimeCalledBackStr, WOEmailed,
 ContactPhoneAlt, SpecialFSE, SpecialRestrict, EmailAddress, PartsReqd, PartsOrderedBy, PartsOrdered, DateSchedCallBackDue, DateSchedCallBackDone, DateSpecialRequest, DatePartsETA, WorkOrder, PurchaseOrder, PMQuoteNbr, CustomerReadinessID,
 TrainingLocID, 0, Null, 0, 0 FROM flowplan.dbo.TSDetail 

	Select FSEID,  Max(idTSDetail) MaxID, convert(datetime,'1/1/1900',101) as DateFirstCallBack into #CompareTemp from #MultiTicket
		Group By FSEID
		
	Update #compareTemp SET #comparetemp.DateFirstCallBack=#multiticket.DateEstArrival From 
		#MultiTicket WHERE #MultiTicket.IDTSDetail=MaxID and #CompareTemp.FSEID = #multiticket.FSEID
		
	Update #multiticket SET #multiticket.DateCustomerConfirm=#multiticket.DateEstDeparture From 
		#MultiTicket inner join #CompareTemp on #CompareTemp.FSEID = #multiticket.FSEID
		WHERE (#MultiTicket.IDTSDetail < #Comparetemp.MaxID and @Startdate < #Multiticket.DateEstDeparture) and #CompareTemp.FSEID = #multiticket.FSEID

	declare @RowNum int

	set @RowNum = 0 
		WHILE @RowNum < 6
	
	BEGIN
		
		Update #Comparetemp
			Set #Comparetemp.DateFirstCallBack = #MultiTicket.DateEstArrival
		from #MultiTicket inner join #CompareTemp on #CompareTemp.FSEID = #multiticket.FSEID
			where (#MultiTicket.IDTSDetail < #Comparetemp.MaxID and @Startdate <= #Multiticket.DateEstArrival) and #CompareTemp.FSEID = #multiticket.FSEID
		
		Update #MultiTicket
		    set #MultiTicket.DateCustomerConfirm = #MultiTicket.DateEstDeparture
		from #MultiTicket inner join #CompareTemp on #CompareTemp.FSEID = #multiticket.FSEID
			where (#MultiTicket.IDTSDetail < #Comparetemp.MaxID and @Startdate <= #comparetemp.DateFirstCallBack) and #CompareTemp.FSEID = #multiticket.FSEID
		
		Update #MultiTicket
			Set MultiTicket =1
		From #CompareTemp
			inner join #MultiTicket on #CompareTemp.FSEID = #multiticket.FSEID
				Where #MultiTicket.DateCustomerConfirm >= #CompareTemp.DateFirstCallBack
					and #CompareTemp.FSEID = #multiticket.FSEID
   
			set @RowNum = @RowNum + 1
	END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources