How to join 2 tables considering record sequence order?

  • Hi,

    I am facing a very tricky issue and I don't find any solution...

    I have 2 tables:

    tbl_ACTIVITY is used to record all activities that took place for a certain incident.

    tbl_ASSIGNMENTis used to record all assignee changes.

    CREATE TABLE [dbo].[tbl_ASSIGNMENT](

    [PM_NUMBER] [varchar](50) NULL,

    [ASSIGNMENT] [varchar](50) NULL,

    [TIME_MODIFIED] [datetime] NULL

    ) ON [PRIMARY]

    CREATE TABLE [dbo].[tbl_ACTIVITY](

    [THENUMBER] [uniqueidentifier] NULL,

    [NUMBER] [varchar](50) NULL,

    [TYPE] [varchar](50) NULL,

    [DATESTAMP] [datetime] NULL

    ) ON [PRIMARY]

    Link between both tables:

    tbl_ACTIVITY.NUMBER <==> tbl_ASSIGNMENT.PM_NUMBER this corresponds to the Incident ID

    In tbl_ACTIVITY, I only consider the records for which [TYPE] = "Reassignment" and I need to link these records to tbl_ASSIGNMENT based on the following:

    1) There is no relationship (in terms of sequence) between both tables

    2) Timestamps are not synchronized, meaning tbl_ACTIVITY.DATESTAMP might not be identical to tbl_ASSIGNMENT.TIME_MODIFIED (sometimes, there is 1 second difference).

    3) Each time there is a modification of assignment, a record is added to the tbl_ASSIGNMENT table

    4) Several "actions" might be recorded in tbl_ACTIVITY without any reassignment takes place.

    So, my question:

    Would there exist any "kind of join" that would solve this

    For each record in tbl_ACTIVITY where TYPE = 'Reassignment', take tbl_ASSIGNMENT.ASSIGNMENT in sequence (based on TIME_MODIFIED).

    So, for the first matching record (TYPE='Reassignment' and 'NUMBER' = @in_incidentId) in tbl_ACTIVITY, consider the first record from tbl_ASSIGNMENT (where PM_NUMBER = @in_incidentId),

    for the second matching record in tbl_ACTIVITY, take the second record from tbl_ASSIGNMENT and so on...

    Many thanks in advance,

  • It would help if you could provide sample data in the form of INSERT statements, together with what results you would expect to see based on that data. Also, do either of the tableas have any PRIMARY KEY, UNIQUE or FOREIGN KEY constraints? Please post scripts for those if they do.

    Thanks

    John

  • Here is the requested script:

    USE [TEST_DB]

    GO

    /****** Object: Table [dbo].[tbl_ASSIGNMENT] Script Date: 05/25/2011 13:42:43 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[tbl_ASSIGNMENT](

    [PM_NUMBER] [varchar](50) NULL,

    [ASSIGNMENT] [varchar](50) NULL,

    [TIME_MODIFIED] [datetime] NULL

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    INSERT [dbo].[tbl_ASSIGNMENT] ([PM_NUMBER], [ASSIGNMENT], [TIME_MODIFIED]) VALUES (N'IM001', N'Assignee1', CAST(0x00009EEE0083D72C AS DateTime))

    INSERT [dbo].[tbl_ASSIGNMENT] ([PM_NUMBER], [ASSIGNMENT], [TIME_MODIFIED]) VALUES (N'IM001', N'Assignee2', CAST(0x00009EEE0095F7CC AS DateTime))

    INSERT [dbo].[tbl_ASSIGNMENT] ([PM_NUMBER], [ASSIGNMENT], [TIME_MODIFIED]) VALUES (N'IM001', N'Assignee1', CAST(0x00009EEE00984090 AS DateTime))

    INSERT [dbo].[tbl_ASSIGNMENT] ([PM_NUMBER], [ASSIGNMENT], [TIME_MODIFIED]) VALUES (N'IM001', N'Assignee3', CAST(0x00009EEE00A5594C AS DateTime))

    INSERT [dbo].[tbl_ASSIGNMENT] ([PM_NUMBER], [ASSIGNMENT], [TIME_MODIFIED]) VALUES (N'IM002', N'Assignee4', CAST(0x00009EEE00735C6C AS DateTime))

    INSERT [dbo].[tbl_ASSIGNMENT] ([PM_NUMBER], [ASSIGNMENT], [TIME_MODIFIED]) VALUES (N'IM003', N'Assignee1', CAST(0x00009EEE007FC68C AS DateTime))

    INSERT [dbo].[tbl_ASSIGNMENT] ([PM_NUMBER], [ASSIGNMENT], [TIME_MODIFIED]) VALUES (N'IM002', N'Assignee2', CAST(0x00009EEE008CA000 AS DateTime))

    /****** Object: Table [dbo].[tbl_ACTIVITY] Script Date: 05/25/2011 13:42:43 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[tbl_ACTIVITY](

    [THENUMBER] [bigint] IDENTITY(1,1) NOT NULL,

    [NUMBER] [varchar](50) NULL,

    [TYPE] [varchar](50) NULL,

    [DATESTAMP] [datetime] NULL

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    SET IDENTITY_INSERT [dbo].[tbl_ACTIVITY] ON

    INSERT [dbo].[tbl_ACTIVITY] ([THENUMBER], [NUMBER], [TYPE], [DATESTAMP]) VALUES (1, N'IM001', N'Open', CAST(0x00009EEE0083D600 AS DateTime))

    INSERT [dbo].[tbl_ACTIVITY] ([THENUMBER], [NUMBER], [TYPE], [DATESTAMP]) VALUES (2, N'IM001', N'Notification', CAST(0x00009EEE008463CC AS DateTime))

    INSERT [dbo].[tbl_ACTIVITY] ([THENUMBER], [NUMBER], [TYPE], [DATESTAMP]) VALUES (3, N'IM001', N'Reassignment', CAST(0x00009EEE0095D120 AS DateTime))

    INSERT [dbo].[tbl_ACTIVITY] ([THENUMBER], [NUMBER], [TYPE], [DATESTAMP]) VALUES (4, N'IM001', N'Notification', CAST(0x00009EEE0095F6A0 AS DateTime))

    INSERT [dbo].[tbl_ACTIVITY] ([THENUMBER], [NUMBER], [TYPE], [DATESTAMP]) VALUES (5, N'IM001', N'Notification', CAST(0x00009EEE009693A8 AS DateTime))

    INSERT [dbo].[tbl_ACTIVITY] ([THENUMBER], [NUMBER], [TYPE], [DATESTAMP]) VALUES (9, N'IM001', N'Reassignment', CAST(0x00009EEE00983E38 AS DateTime))

    INSERT [dbo].[tbl_ACTIVITY] ([THENUMBER], [NUMBER], [TYPE], [DATESTAMP]) VALUES (10, N'IM001', N'Reassignment', CAST(0x00009EEE00A5594C AS DateTime))

    INSERT [dbo].[tbl_ACTIVITY] ([THENUMBER], [NUMBER], [TYPE], [DATESTAMP]) VALUES (11, N'IM002', N'Open', CAST(0x00009EEE0072CEA0 AS DateTime))

    INSERT [dbo].[tbl_ACTIVITY] ([THENUMBER], [NUMBER], [TYPE], [DATESTAMP]) VALUES (12, N'IM002', N'Reassignment', CAST(0x00009EEE008C9B50 AS DateTime))

    INSERT [dbo].[tbl_ACTIVITY] ([THENUMBER], [NUMBER], [TYPE], [DATESTAMP]) VALUES (13, N'IM003', N'Open', CAST(0x00009EEE007FC68C AS DateTime))

    SET IDENTITY_INSERT [dbo].[tbl_ACTIVITY] OFF

    Outcome that I need to obtain:

    IM001 Open 2011-05-25 08:00:00.000 Assignee1

    IM001 Reassignment 2011-05-25 09:05:28.000 Assignee2

    IM001 Reassignment 2011-05-25 09:14:18.000 Assignee1

    IM001 Reassignment 2011-05-25 10:02:01.000 Assignee3

    IM002 Open 2011-05-25 06:58:00.000 Assignee4

    IM002 Reassignment 2011-05-25 08:31:56.000 Assignee2

    IM003 Open 2011-05-25 07:45:13.000 Assignee 1

    The first 3 columns correspond to tbl_ACTIVITY (NUMBER, TYPE, DATESTAMP) and the last column to tbl_ASSIGNMENT (ASSIGNMENT).

    As regard the notion of Foreign Keys, Primary keys, Indexes, it does not matter for the moment.

    Hope this is the information you wanted.

  • In a properly normalised database, you'd usually model a many-to-many relationship with a junction table. If you don't have any control over the structure of your database, then you can use the ROW_NUMBER() function, partitioning by NUMBER or PM_NUMBER. That will give you a unique combination that you can use to join the two tables. Have a go at that and post back if you have any problems.

    John

  • Unfortunately, I don't have any control on the database structure, since it is part of an HP product.

    As regard your sentence: "then you can use the ROW_NUMBER() function, partitioning by NUMBER or PM_NUMBER", I am sorry to ask but could you please be a bit more explicit, I don't understand the second part "partitioning..."'?

  • SELECT a.NUMBER, a.[TYPE], a.DATESTAMP, b.ASSIGNMENT

    FROM (SELECT ROW_NUMBER() OVER (PARTITION BY act.NUMBER ORDER BY act.THENUMBER ASC) AS [RowNum],

    act.THENUMBER, act.NUMBER, act.[TYPE], act.DATESTAMP

    FROM #tbl_ACTIVITY act WHERE act.[TYPE] <> 'Notification') a

    JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY at.PM_NUMBER ORDER BY at.PM_NUMBER,at.TIME_MODIFIED ASC) AS [RowNum],

    at.PM_number, at.ASSIGNMENT

    FROM #tbl_ASSIGNMENT at) b

    ON b.PM_NUMBER=a.NUMBER AND b.RowNum=a.RowNum

    ORDER BY a.THENUMBER ASC

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Look up the syntax for ROW_NUMBER - it has an optional PARTITION BY clause. You can use this to assign a 1,2,3,etc to each value of NUMBER in one table and PM_NUMBER in the other. If you build a Common Table Expression (CTE) like this for each table, then you can join the two CTEs to obtain the results that you require.

    John

  • Thanks so much to both of you. It's working perfectly.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply