0

How to dynamically compare x number of tables and generate dynamic messages?

I have the following schema:

  • Table1: customerID, startDate, endDate
  • Table2: customerID, startDate, endDate
  • Table3: customerID, startDate, endDate

And so on.

Customers in Table 1 could exist in Table 2 or 3 (or not at all). And same with customers in table 2 or 3, they may have common customers or not at all.

Expected Result:

table here

Basically a table with result (whether Customer is in Table 1/2/3/x), and result detail (Table 1 StartDate< Table 2 StartDate and so on). Each row contains a customer.

What I did:

  1. I created a giant case statement. It works, but it has many issues: it takes long to write, it is not dynamic, as you add more tables, you will end up with a lot more cases, and it never ends. The issue is also that it could add error and becomes impossible for more than 3 or 4 tables.
  2. I tried to separate them into columns then concat them, but still couldn't manage it.

My table structure:

CREATE TABLE [dbo].[Table_1](
[CustomerID] [varchar](15) NULL,
[StartDate] [date] NULL,
[EndDate] [date] NULL)



/****** Object:  Table [dbo].[Table_2]    Script Date: 2021-08-18 11:50:24 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_2](
    [CustomerID] [varchar](15) NULL,
    [StartDate] [date] NULL,
    [EndDate] [date] NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Table_3]    Script Date: 2021-08-18 11:50:24 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_3](
    [CustomerID] [varchar](15) NULL,
    [StartDate] [date] NULL,
    [EndDate] [date] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Table_1] ([CustomerID], [StartDate], [EndDate]) VALUES (N'0001', CAST(N'2020-01-20' AS Date), CAST(N'2020-01-25' AS Date))
GO
INSERT [dbo].[Table_1] ([CustomerID], [StartDate], [EndDate]) VALUES (N'0002', CAST(N'2020-01-21' AS Date), CAST(N'2020-01-26' AS Date))
GO
INSERT [dbo].[Table_1] ([CustomerID], [StartDate], [EndDate]) VALUES (N'0003', CAST(N'2020-01-22' AS Date), CAST(N'2020-01-27' AS Date))
GO
INSERT [dbo].[Table_1] ([CustomerID], [StartDate], [EndDate]) VALUES (N'0004', CAST(N'2020-01-23' AS Date), CAST(N'2020-01-28' AS Date))
GO
INSERT [dbo].[Table_1] ([CustomerID], [StartDate], [EndDate]) VALUES (N'0005', CAST(N'2020-01-24' AS Date), CAST(N'2020-01-29' AS Date))
GO
INSERT [dbo].[Table_1] ([CustomerID], [StartDate], [EndDate]) VALUES (N'0006', CAST(N'2020-03-15' AS Date), CAST(N'2020-03-28' AS Date))
GO
INSERT [dbo].[Table_2] ([CustomerID], [StartDate], [EndDate]) VALUES (N'0001', CAST(N'2020-01-20' AS Date), CAST(N'2020-01-25' AS Date))
GO
INSERT [dbo].[Table_2] ([CustomerID], [StartDate], [EndDate]) VALUES (N'0002', CAST(N'2020-01-20' AS Date), CAST(N'2020-01-26' AS Date))
GO
INSERT [dbo].[Table_2] ([CustomerID], [StartDate], [EndDate]) VALUES (N'0003', CAST(N'2020-01-22' AS Date), CAST(N'2020-01-25' AS Date))
GO
INSERT [dbo].[Table_2] ([CustomerID], [StartDate], [EndDate]) VALUES (N'0004', CAST(N'2020-01-20' AS Date), CAST(N'2020-01-21' AS Date))
GO
INSERT [dbo].[Table_2] ([CustomerID], [StartDate], [EndDate]) VALUES (N'0006', CAST(N'2020-01-15' AS Date), CAST(N'2020-01-28' AS Date))
GO
INSERT [dbo].[Table_3] ([CustomerID], [StartDate], [EndDate]) VALUES (N'0001', CAST(N'2020-01-20' AS Date), CAST(N'2020-01-25' AS Date))
GO
INSERT [dbo].[Table_3] ([CustomerID], [StartDate], [EndDate]) VALUES (N'0003', CAST(N'2020-01-22' AS Date), CAST(N'2020-01-25' AS Date))
GO
INSERT [dbo].[Table_3] ([CustomerID], [StartDate], [EndDate]) VALUES (N'0004', CAST(N'2020-01-23' AS Date), CAST(N'2020-01-28' AS Date))
GO
INSERT [dbo].[Table_3] ([CustomerID], [StartDate], [EndDate]) VALUES (N'0006', CAST(N'2020-01-19' AS Date), CAST(N'2020-01-28' AS Date))
GO
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Is_It_Broken
  • 1
  • 1
    I don't understand the dynamic messages you want to create. Can you be more explicit? – Gordon Linoff Aug 19 '21 at 11:46
  • @GordonLinoff sure: basically, the first field checks how many sources the customerID is in. That is: CustomerID is in Table A, B, but not in C and D. The second field, the more important field, compares the dates together. For example, in this example, the customerID is in A and B. So how do the start dates compare? – Is_It_Broken Aug 19 '21 at 12:24
  • @GordonLinoff if Customer ID is in tables A and B, and if start dates are the same, then no message. But if start dates are different, then it would give a message saying: "Start Date in A < Start Date than B". Basically, there are three fields, we are trying to compare all three (Start, End and Customer ID). If they are identical, we don't need a message, if they are not identical, how do they compare. Thank you – Is_It_Broken Aug 19 '21 at 12:25
  • What do you want for `ResultDetail` if `CustomerID` is present in all 3 tables? – Charlieface Aug 19 '21 at 13:29

0 Answers0