December 8, 2009 at 12:12 pm
Dont know if am having a bad day, but I'm trying to work out a way of join the two tables attached below together, I have attached some sample data for them as well
The Activity table shows all PDA activity, the field OnSite is the field I want to match the ApptDate to in the Appointment table.
My problem is that if you look at JobNo 122054 in the Appointment table there are 3 Appts but in the Activity you will see 5 under same 122054 using field db_serv_job_no
How can I link the 2 tables together to show each Appointment with the appropriate PDA activity?
CREATE TABLE [dbo].[engineer_activity](
[id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[db_resp_job_no] [int] NULL,
[db_serv_job_no] [int] NULL,
[engineer_id] [int] NOT NULL,
[on_route] [datetime] NULL,
[on_site] [datetime] NULL,
[on_depart] [datetime] NULL,
[on_abort] [datetime] NULL,
[noaccess] [bit] NULL,
[abort_reason] [varchar](100) NULL,
CONSTRAINT [PK_engineer_activity] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY]
) ON [PRIMARY]
Activity Table
([PropID] INT
,[UPRN] VARCHAR(50)
,[Appt] VARCHAR(5)
,[JobNo] INT
,[ApptDate] DATETIME
,[NA] DATETIME
,[NoAccess] VARCHAR(3)
,[CompletedOrNot] DATETIME
,[Contract] VARCHAR(100)
,[Area] VARCHAR(50)
,[Region] VARCHAR(12)
,[JobType] VARCHAR(3)
,[Weekday] INT
,[Day] INT
,[Week] INT
,[Month] INT
,[Year] INT)
December 10, 2009 at 4:37 am
How can I link the 2 tables together to show each Appointment with the appropriate PDA activity?
Its baffling me :w00t:
December 10, 2009 at 12:41 pm
If you only want to match the On_site to apptdate on the DAY then you need to scrub off the time from both the date time fields. If the record in one table was inserted at 2:18:21 PM and it's corresponding record was inserted into the other table at 2:18:46 PM then the join will fail because the milliseconds are different. So, in your join condition, you will have to remove time from the equality or otherwise scrub the fields to match your required granularity (day, minute, hour, etc).
This is the fastest way to scrub off time that I have found:
SELECT CONVERT(DateTime, FLOOR( CONVERT( Float, GETDATE())))
If you are working with a huge dataset you might be better served putting the data into a temp table with an index and then updating the date fields first before you join them; because, applying functions to join conditions will cause the optimizer to skip your indexes.
December 10, 2009 at 2:49 pm
Jay_Noob (12/10/2009)
If you only want to match the On_site to apptdate on the DAY then you need to scrub off the time from both the date time fields. If the record in one table was inserted at 2:18:21 PM and it's corresponding record was inserted into the other table at 2:18:46 PM then the join will fail because the milliseconds are different. So, in your join condition, you will have to remove time from the equality or otherwise scrub the fields to match your required granularity (day, minute, hour, etc).This is the fastest way to scrub off time that I have found:
SELECT CONVERT(DateTime, FLOOR( CONVERT( Float, GETDATE())))
If you are working with a huge dataset you might be better served putting the data into a temp table with an index and then updating the date fields first before you join them; because, applying functions to join conditions will cause the optimizer to skip your indexes.
With me working with a huge dataset, as can see from the sample dataset that was only from start of Dec, I need to go back to April 08.
How would I set a temp table up with an index?
do you mean updating the data fields in the way you suggested above?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply