May 25, 2011 at 3:54 am
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,
May 25, 2011 at 4:18 am
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
May 25, 2011 at 5:49 am
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.
May 25, 2011 at 6:53 am
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
May 25, 2011 at 7:04 am
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..."'?
May 25, 2011 at 7:08 am
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.
May 25, 2011 at 7:08 am
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
May 25, 2011 at 8:40 am
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