Jun 22 2024 08:57 PM - edited Jun 27 2024 01:43 PM
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[People]') AND type in (N'U'))
DROP TABLE [People]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [People](
[PersonID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](16) NOT NULL,
[Married] [date] NULL,
CONSTRAINT [PK_People] PRIMARY KEY CLUSTERED
(
[PersonID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO
People ([Name], Married)
VALUES
('John Doe', '2000-02-20'),
('Sam Smith', '2000-03-03'),
('Jack Beanstalk', NULL),
('Mary Doe', '2000-02-20'),
('Harry Potter', NULL),
('Patty Smith', '2000-03-03');
Desired Output
+---------------------+------------+
| Names | Date |
+---------------------+------------+
| John and Mary Doe | 2000-02-20 |
| Sam and Patty Smith | 2000-03-03 |
+---------------------+------------+
Jun 23 2024 10:23 PM
Jun 27 2024 01:44 PM
@olafhelperI have rewritten the OP.
Jun 27 2024 09:30 PM
SolutionJun 27 2024 10:47 PM
@SezMe , so now you want to "guess" just by the marriage date the couples? Do you know, how many people marry per day? And which one first and second in the result?
DECLARE @People AS TABLE (
PersonID int IDENTITY(1,1) NOT NULL,
Name varchar(16) NOT NULL,
Married date NULL);
INSERT INTO
@People (Name, Married)
VALUES
('John Doe', '2000-02-20'),
('Sam Smith', '2000-03-03'),
('Jack Beanstalk', NULL),
('Mary Doe', '2000-02-20'),
('Harry Potter', NULL),
('Patty Smith', '2000-03-03');
SELECT *
FROM @People AS P1
INNER JOIN
@People AS P2
ON P1.Married = P2.Married
WHERE P1.Name <> P2.Name
Jun 29 2024 03:25 PM
@Arshad440Thanks. Just needed to add "having Married is not null"
Jun 29 2024 05:27 PM
@olafhelper No guessing required. A more precise question is: Do I know how many people marry per day who will ever be in my database? You will be happy to know I do know how to answer that question: 2.
If the order had mattered, I would have said so.
Jun 27 2024 09:30 PM
Solution