View of two linked tables, one to many relationship

1

I have one Item table of unique records, each with an ID, and then another table with communication history log entries, tied to the Item table by that ID. The communication logs have the associated Item ID, a communication date field, person's name, and text about the communication - I'm hoping to have a view that gets me the 3 most recent entries for each record in the original table. I'd like to get the fields from the 3 recent entries all joined together into one combined text field, formatted like {Date} ((Agent Name}): {Text} , then a carriage return, and the next two entries in the same formatting. So the view would have one concatenated field coming in from the Communication History table with all that, for each ID in the Item table.

Any ideas for how I'd go about this?
Thanks

2

Can you provide sample data and expected results? Sample data should be in the form of create statements and insert statements.

3 
CREATE TABLE [dbo].[Communications](
	[Comm_ID] [int] IDENTITY(1,1) NOT NULL,
	[Line_List] [nvarchar] NULL,
	[Agent] [nvarchar] NULL,
	[Comm_Date] [date] NULL,
	[Comm_Type] [nvarchar] NULL,
	[Comm_Entry] [nvarchar](max) NULL,
	[Project] [int] NULL,
	[DB] [int] NULL,
CONSTRAINT [PK_Communications] PRIMARY KEY CLUSTERED 
(
	[Comm_ID] ASC
)

CREATE TABLE [dbo].[LineLists](
	[Line_List_Num] [nvarchar] NOT NULL,
	[Project] [int] NOT NULL,
	[Site_Address] [nvarchar] NULL,
	[Owner_Name] [nvarchar] NULL,
	`....(Many more fields that aren't needed in view)`
 CONSTRAINT [PK_LL] PRIMARY KEY CLUSTERED 
(
	[Line_List_Num] ASC,
	[Project] ASC
)

In the view, I want each unique item in the LineLists table (Line_List_Num + Project for Primary Key), with the 3 most recent Communications table entries for that item, concatenated in descending order of Comm_Date, formatted as a longtext string that concatenates together the {Agent} and {Comm_Date}, and {Comm_Entry} text for those 3 entries, such as:
Comm_Date (Agent): Comm_Entry (carriage return)
Comm_Date (Agent): Comm_Entry (carriage return)
Comm_Date (Agent): Comm_Entry

4

Still need some sample data for the above tables - and expected results. Instead of real tables - it is much better to use temp tables (or table variables) and insert statements to insert test data into those tables.

Then - we can take those scripts and run them on our system to create a tested solution that works against the sample data.

If you are on SQL Server 2017 or later - lookup STRING_AGG. If on a prior version - then you would need FOR XML concatenation.

To get the TOP 3 you can use CROSS APPLY.

5

will this suffice? sample data from communications table, with the desired output coalescing the 3 most recent entries for an item (Line_List + Project)

2021-02-12 13_39_48-Book5 - Excel
2021-02-12 13_39_48-Book5 - Excel1872×636 42.8 KB

6

Sorry - I cannot take an image and run it on my SQL Server instance. It really needs to be something like:

Declare @testTable table (col1 int, col2 varchar(30), ...);
 Insert Into @testTable
 Values (1, '2')
      , (2, '3');

Then - using that sample data, what your expected results would be.

7

But use temp tables NOT table variables. Table variables disappear after the code runs and thus make it MUCH harder to debug and develop code.

CREATE TABLE #Communications (
    Comm_ID int ..., 
    ...
    )
INSERT INTO #Communications VALUES
    (..., ..., ...),
    (...
CREATE TABLE #LineLists (
    ...
INSERT INTO #LineLIsts VALUES
    (..., ...., ...),
    (...