Query with 3 tables and MIN function

Question

question

robertrackl asked Nov 1, '23

Query with 3 tables and MIN function

I asked this question of Bard and Bing chat, but they gave me conflicting answers. Maybe they are both wrong. So here goes:

Write a SQL Server 2019 stored procedure for getting a list of invoices in table INVOICES which is ordered by the earliest date in OPERATIONS.DBegin, based on the following schema:

CREATE TABLE [dbo].[INVLINES]

( [ID] [int] IDENTITY(1,1) NOT NULL,

[iInvoice] [int] NOT NULL,

[DService] [datetimeoffset](0) NOT NULL,

[iOperation] [int] NOT NULL)

GO

CREATE TABLE [dbo].[INVOICES]

( [ID] [int] IDENTITY(1,1) NOT NULL,

[DInvoice] [datetimeoffset](0) NULL,

[mTotalAmt] [money] NULL)

GO

CREATE TABLE [dbo].[OPERATIONS]

( [ID] [int] IDENTITY(70001,1) NOT NULL,

[DBegin] [smalldatetime] NULL)

GO

ALTER TABLE [dbo].[INVLINES] WITH CHECK ADD CONSTRAINT [FK_INVLINES_INVOICES] FOREIGN KEY([iInvoice]) REFERENCES [dbo].[INVOICES] ([ID])

GO

ALTER TABLE [dbo].[INVLINES] CHECK CONSTRAINT [FK_INVLINES_INVOICES]

GO

ALTER TABLE [dbo].[INVLINES] WITH CHECK ADD CONSTRAINT [FK_INVLINES_OPERATIONS] FOREIGN KEY([iOperation]) REFERENCES [dbo].[OPERATIONS] ([ID])

GO

ALTER TABLE [dbo].[INVLINES] CHECK CONSTRAINT [FK_INVLINES_OPERATIONS]

GO

joins sql query

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2023-11-03T09:03:33Z

anthony.green answered Nov 3, '23

Join invoices to invlines

Join invlines to operations

Order by operations.dbegin

?????

select 
* 
from INVOICES inv
inner join INVLINES lin
on inv.id = lin.iInvoice
inner join OPERATIONS ope
on lin.iOperation = ope.ID
order by
ope.DBegin

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2023-11-05T17:56:32Z

robertrackl answered Nov 5, '23

@anthony.green 's answer lists each invoice as often as there are invoice lines. I asked Bing Chat again in a different way and got the idea of using a "derived table". I believe that the following query does the trick:

SELECT InvID
FROM (
SELECT TOP 100 PERCENT IV.ID AS InvID, MIN(OP.DBegin) AS DBegin
FROM dbo.INVLINES IL
INNER JOIN dbo.INVOICES IV ON IL.iInvoice = IV.ID
INNER JOIN dbo.OPERATIONS OP ON IL.iOperation = OP.ID
GROUP BY IV.ID
) AS derived_table
ORDER BY DBegin ASC

which includes the use of the MIN function.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2023-11-06T17:23:36Z

robertrackl answered Nov 6, '23

Since one invoice can have any number of invoice lines, and one invoice line is connected to one operation (where each operation has a different time tag), @anthony.green 's query results in each invoice being listed as often as there are invoice lines associated with it, albeit in the correct order of OPERATIONS.DBegin. I want each invoice to be listed just once. So, I tried to use the DISTINCT keyword and having in the SELECT list only the invoice ID although still having OPERATIONS.DBegin in the ORDER BY; this results in an error: the item in the ORDER BY must also appear in the SELECT list - but then the DISTINCT does not help me anymore.

I am sure this can be accomplished in a multi-step process. The challenge is to do it in one query. Here is a schema and data in case you feel like playing with this problem:

/****** Object: Table [dbo].[INVLINES] Script Date: 2023/11/04 12:03:47 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[INVLINES](

[ID] [int] IDENTITY(1,1) NOT NULL,

[InvoiceLineText] [nvarchar](120) NULL,

[mAmount] [money] NOT NULL,

[iOperation] [int] NOT NULL,

[iInvoice] [int] NOT NULL,

CONSTRAINT [PK_INVLINES] PRIMARY KEY CLUSTERED

(

[ID] 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

/****** Object: Table [dbo].[INVOICES] Script Date: 2023/11/04 12:03:47 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[INVOICES](

[ID] [int] IDENTITY(1,1) NOT NULL,

[InvoiceDescription] [nvarchar](120) NULL,

[mTotal] [money] NOT NULL,

[InvoiceNumber] [int] NOT NULL,

CONSTRAINT [PK_INVOICES] PRIMARY KEY CLUSTERED

(

[ID] 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

/****** Object: Table [dbo].[OPERATIONS] Script Date: 2023/11/04 12:03:47 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[OPERATIONS](

[ID] [int] IDENTITY(1,1) NOT NULL,

[DBegin] [datetime2](3) NOT NULL,

CONSTRAINT [PK_OPERATIONS] PRIMARY KEY CLUSTERED

(

[ID] 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

SET IDENTITY_INSERT [dbo].[INVLINES] ON

INSERT [dbo].[INVLINES] ([ID], [InvoiceLineText], [mAmount], [iOperation], [iInvoice]) VALUES (1, N'Op 20, Inv 1', 100.0000, 20, 1)

INSERT [dbo].[INVLINES] ([ID], [InvoiceLineText], [mAmount], [iOperation], [iInvoice]) VALUES (2, N'Op 19, Inv 1', 1901.0000, 19, 1)

INSERT [dbo].[INVLINES] ([ID], [InvoiceLineText], [mAmount], [iOperation], [iInvoice]) VALUES (3, N'Op 18, Inv 1', 1801.0000, 18, 1)

INSERT [dbo].[INVLINES] ([ID], [InvoiceLineText], [mAmount], [iOperation], [iInvoice]) VALUES (4, N'Op 17, Inv 2', 1702.0000, 17, 2)

INSERT [dbo].[INVLINES] ([ID], [InvoiceLineText], [mAmount], [iOperation], [iInvoice]) VALUES (5, N'Op 16, Inv 2', 1602.0000, 16, 2)

INSERT [dbo].[INVLINES] ([ID], [InvoiceLineText], [mAmount], [iOperation], [iInvoice]) VALUES (6, N'Op 15, Inv 2', 1502.0000, 15, 2)

INSERT [dbo].[INVLINES] ([ID], [InvoiceLineText], [mAmount], [iOperation], [iInvoice]) VALUES (7, N'Op 14, Inv 3', 1403.0000, 14, 3)

INSERT [dbo].[INVLINES] ([ID], [InvoiceLineText], [mAmount], [iOperation], [iInvoice]) VALUES (8, N'Op 13, Inv 3', 1303.0000, 13, 3)

INSERT [dbo].[INVLINES] ([ID], [InvoiceLineText], [mAmount], [iOperation], [iInvoice]) VALUES (9, N'Op 12, Inv 3', 1203.0000, 12, 3)

INSERT [dbo].[INVLINES] ([ID], [InvoiceLineText], [mAmount], [iOperation], [iInvoice]) VALUES (10, N'Op 11, Inv 4', 1104.0000, 11, 4)

INSERT [dbo].[INVLINES] ([ID], [InvoiceLineText], [mAmount], [iOperation], [iInvoice]) VALUES (11, N'Op 10, Inv 4', 1004.0000, 10, 4)

INSERT [dbo].[INVLINES] ([ID], [InvoiceLineText], [mAmount], [iOperation], [iInvoice]) VALUES (12, N'Op 9, Inv 4', 904.0000, 9, 4)

INSERT [dbo].[INVLINES] ([ID], [InvoiceLineText], [mAmount], [iOperation], [iInvoice]) VALUES (13, N'Op 8, Inv 5', 805.0000, 8, 5)

INSERT [dbo].[INVLINES] ([ID], [InvoiceLineText], [mAmount], [iOperation], [iInvoice]) VALUES (14, N'Op 7, Inv 5', 705.0000, 7, 5)

INSERT [dbo].[INVLINES] ([ID], [InvoiceLineText], [mAmount], [iOperation], [iInvoice]) VALUES (15, N'Op 6, Inv 5', 605.0000, 6, 5)

INSERT [dbo].[INVLINES] ([ID], [InvoiceLineText], [mAmount], [iOperation], [iInvoice]) VALUES (16, N'Op 5, Inv 6', 506.0000, 5, 6)

INSERT [dbo].[INVLINES] ([ID], [InvoiceLineText], [mAmount], [iOperation], [iInvoice]) VALUES (17, N'Op 4, Inv 6', 406.0000, 4, 6)

INSERT [dbo].[INVLINES] ([ID], [InvoiceLineText], [mAmount], [iOperation], [iInvoice]) VALUES (18, N'Op 3, Inv 6', 306.0000, 3, 6)

SET IDENTITY_INSERT [dbo].[INVLINES] OFF

GO

SET IDENTITY_INSERT [dbo].[INVOICES] ON

INSERT [dbo].[INVOICES] ([ID], [InvoiceDescription], [mTotal], [InvoiceNumber]) VALUES (1, N'one', 0.0000, 1)

INSERT [dbo].[INVOICES] ([ID], [InvoiceDescription], [mTotal], [InvoiceNumber]) VALUES (2, N'two', 0.0000, 2)

INSERT [dbo].[INVOICES] ([ID], [InvoiceDescription], [mTotal], [InvoiceNumber]) VALUES (3, N'three', 0.0000, 3)

INSERT [dbo].[INVOICES] ([ID], [InvoiceDescription], [mTotal], [InvoiceNumber]) VALUES (4, N'four', 0.0000, 4)

INSERT [dbo].[INVOICES] ([ID], [InvoiceDescription], [mTotal], [InvoiceNumber]) VALUES (5, N'five', 0.0000, 5)

INSERT [dbo].[INVOICES] ([ID], [InvoiceDescription], [mTotal], [InvoiceNumber]) VALUES (6, N'six', 0.0000, 6)

SET IDENTITY_INSERT [dbo].[INVOICES] OFF

GO

SET IDENTITY_INSERT [dbo].[OPERATIONS] ON

INSERT [dbo].[OPERATIONS] ([ID], [DBegin]) VALUES (1, CAST(N'2022-01-02T07:00:00.0000000' AS DateTime2))

INSERT [dbo].[OPERATIONS] ([ID], [DBegin]) VALUES (2, CAST(N'2022-01-02T09:00:00.0000000' AS DateTime2))

INSERT [dbo].[OPERATIONS] ([ID], [DBegin]) VALUES (3, CAST(N'2022-02-02T11:00:00.0000000' AS DateTime2))

INSERT [dbo].[OPERATIONS] ([ID], [DBegin]) VALUES (4, CAST(N'2022-02-02T13:00:00.0000000' AS DateTime2))

INSERT [dbo].[OPERATIONS] ([ID], [DBegin]) VALUES (5, CAST(N'2022-02-02T15:00:00.0000000' AS DateTime2))

INSERT [dbo].[OPERATIONS] ([ID], [DBegin]) VALUES (6, CAST(N'2022-03-03T07:00:00.0000000' AS DateTime2))

INSERT [dbo].[OPERATIONS] ([ID], [DBegin]) VALUES (7, CAST(N'2022-03-03T08:00:00.0000000' AS DateTime2))

INSERT [dbo].[OPERATIONS] ([ID], [DBegin]) VALUES (8, CAST(N'2022-03-03T09:00:00.0000000' AS DateTime2))

INSERT [dbo].[OPERATIONS] ([ID], [DBegin]) VALUES (9, CAST(N'2022-03-03T10:00:00.0000000' AS DateTime2))

INSERT [dbo].[OPERATIONS] ([ID], [DBegin]) VALUES (10, CAST(N'2022-03-03T11:00:00.0000000' AS DateTime2))

INSERT [dbo].[OPERATIONS] ([ID], [DBegin]) VALUES (11, CAST(N'2022-03-03T12:00:00.0000000' AS DateTime2))

INSERT [dbo].[OPERATIONS] ([ID], [DBegin]) VALUES (12, CAST(N'2022-04-01T01:00:00.0000000' AS DateTime2))

INSERT [dbo].[OPERATIONS] ([ID], [DBegin]) VALUES (13, CAST(N'2022-04-01T02:00:00.0000000' AS DateTime2))

INSERT [dbo].[OPERATIONS] ([ID], [DBegin]) VALUES (14, CAST(N'2022-04-01T03:00:00.0000000' AS DateTime2))

INSERT [dbo].[OPERATIONS] ([ID], [DBegin]) VALUES (15, CAST(N'2022-04-01T04:00:00.0000000' AS DateTime2))

INSERT [dbo].[OPERATIONS] ([ID], [DBegin]) VALUES (16, CAST(N'2022-04-01T05:00:00.0000000' AS DateTime2))

INSERT [dbo].[OPERATIONS] ([ID], [DBegin]) VALUES (17, CAST(N'2022-05-05T10:00:00.0000000' AS DateTime2))

INSERT [dbo].[OPERATIONS] ([ID], [DBegin]) VALUES (18, CAST(N'2022-05-05T11:00:00.0000000' AS DateTime2))

INSERT [dbo].[OPERATIONS] ([ID], [DBegin]) VALUES (19, CAST(N'2022-05-05T12:00:00.0000000' AS DateTime2))

INSERT [dbo].[OPERATIONS] ([ID], [DBegin]) VALUES (20, CAST(N'2022-05-05T13:00:00.0000000' AS DateTime2))

SET IDENTITY_INSERT [dbo].[OPERATIONS] OFF

GO

ALTER TABLE [dbo].[INVOICES] ADD CONSTRAINT [DF_INVOICES_mTotal] DEFAULT ((0)) FOR [mTotal]

GO

ALTER TABLE [dbo].[INVLINES] WITH CHECK ADD CONSTRAINT [FK_INVLINES_INVOICES] FOREIGN KEY([iInvoice])

REFERENCES [dbo].[INVOICES] ([ID])

GO

ALTER TABLE [dbo].[INVLINES] CHECK CONSTRAINT [FK_INVLINES_INVOICES]

GO

ALTER TABLE [dbo].[INVLINES] WITH CHECK ADD CONSTRAINT [FK_INVLINES_OPERATIONS] FOREIGN KEY([iOperation])

REFERENCES [dbo].[OPERATIONS] ([ID])

GO

ALTER TABLE [dbo].[INVLINES] CHECK CONSTRAINT [FK_INVLINES_OPERATIONS]

GO

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question