TSQL query - adapt Part2 to include Part1

Question

TSQL query - adapt Part2 to include Part1

SSC Veteran

Points: 275

September 19, 2024 at 1:00 pm

CREATE TABLE [dbo].[zA](
[InvoiceNo] [nvarchar](255) NULL,
[Code] [nvarchar](255) NULL,
[Date] [datetime] NULL,
[Type] [float] NULL
) ON [PRIMARY]
GO

INSERT [dbo].[zA] ([InvoiceNo], [Code], [Date], [Type]) VALUES (N'A15020000005', N'R', CAST(N'2002-02-20T00:00:00.000' AS DateTime), 40)
GO
INSERT [dbo].[zA] ([InvoiceNo], [Code], [Date], [Type]) VALUES (N'P15010034980', N'R', CAST(N'2003-05-29T00:00:00.000' AS DateTime), 40)
GO
INSERT [dbo].[zA] ([InvoiceNo], [Code], [Date], [Type]) VALUES (N'P15020016655', N'R', CAST(N'2003-05-29T00:00:00.000' AS DateTime), 40)
GO
INSERT [dbo].[zA] ([InvoiceNo], [Code], [Date], [Type]) VALUES (N'P15020163126', N'R', CAST(N'2003-05-29T00:00:00.000' AS DateTime), 40)
GO
INSERT [dbo].[zA] ([InvoiceNo], [Code], [Date], [Type]) VALUES (N'P15020556622', N'R', CAST(N'2005-07-15T00:00:00.000' AS DateTime), 40)
GO
INSERT [dbo].[zA] ([InvoiceNo], [Code], [Date], [Type]) VALUES (N'P15021098038', N'R', CAST(N'2003-02-12T00:00:00.000' AS DateTime), 40)
GO
INSERT [dbo].[zA] ([InvoiceNo], [Code], [Date], [Type]) VALUES (N'P15022418128', N'R', CAST(N'2004-07-14T00:00:00.000' AS DateTime), 40)
GO
INSERT [dbo].[zA] ([InvoiceNo], [Code], [Date], [Type]) VALUES (N'P15022544125', N'R', CAST(N'2003-05-02T00:00:00.000' AS DateTime), 40)
GO
INSERT [dbo].[zA] ([InvoiceNo], [Code], [Date], [Type]) VALUES (N'P15030661590', N'R', CAST(N'2004-07-14T00:00:00.000' AS DateTime), 40)
GO
INSERT [dbo].[zA] ([InvoiceNo], [Code], [Date], [Type]) VALUES (N'T15020446874', N'R', CAST(N'2003-05-02T00:00:00.000' AS DateTime), 40)
GO
INSERT [dbo].[zA] ([InvoiceNo], [Code], [Date], [Type]) VALUES (N'P15041153078', N'R', CAST(N'2005-03-09T00:00:00.000' AS DateTime), 40)
GO
INSERT [dbo].[zA] ([InvoiceNo], [Code], [Date], [Type]) VALUES (N'P15041216539', N'R', CAST(N'2005-03-09T00:00:00.000' AS DateTime), 40)
GO
INSERT [dbo].[zA] ([InvoiceNo], [Code], [Date], [Type]) VALUES (N'P15062109550', N'R', CAST(N'2007-10-29T12:14:27.000' AS DateTime), 40)
GO
INSERT [dbo].[zA] ([InvoiceNo], [Code], [Date], [Type]) VALUES (N'P15062117901', N'R', CAST(N'2009-02-11T11:04:16.000' AS DateTime), 40)
GO
INSERT [dbo].[zA] ([InvoiceNo], [Code], [Date], [Type]) VALUES (N'T15230005794', N'R', CAST(N'2023-04-28T12:25:39.000' AS DateTime), 40)
GO
INSERT [dbo].[zA] ([InvoiceNo], [Code], [Date], [Type]) VALUES (N'T15230005794', N'R', CAST(N'2023-04-28T12:48:35.000' AS DateTime), 40)
GO
INSERT [dbo].[zA] ([InvoiceNo], [Code], [Date], [Type]) VALUES (N'T15230005794', N'R', CAST(N'2023-04-28T12:55:43.000' AS DateTime), 40)
GO
INSERT [dbo].[zA] ([InvoiceNo], [Code], [Date], [Type]) VALUES (N'T15230020621', N'W', CAST(N'2024-07-17T13:15:02.000' AS DateTime), 50)
GO
INSERT [dbo].[zA] ([InvoiceNo], [Code], [Date], [Type]) VALUES (N'T15230020645', N'W', CAST(N'2024-07-17T13:18:27.000' AS DateTime), 50)
GO
INSERT [dbo].[zA] ([InvoiceNo], [Code], [Date], [Type]) VALUES (N'T15230020645', N'R', CAST(N'2024-07-17T13:37:33.000' AS DateTime), 40)
GO
INSERT [dbo].[zA] ([InvoiceNo], [Code], [Date], [Type]) VALUES (N'T15230020621', N'R', CAST(N'2024-07-17T13:38:56.000' AS DateTime), 40)
GO
INSERT [dbo].[zA] ([InvoiceNo], [Code], [Date], [Type]) VALUES (N'T15230020621', N'W', CAST(N'2024-07-17T13:44:51.000' AS DateTime), 50)
GO
INSERT [dbo].[zA] ([InvoiceNo], [Code], [Date], [Type]) VALUES (N'T15230020645', N'W', CAST(N'2024-07-17T13:46:14.000' AS DateTime), 50)
GO
INSERT [dbo].[zA] ([InvoiceNo], [Code], [Date], [Type]) VALUES (N'T15230020645', N'R', CAST(N'2024-07-17T13:47:51.000' AS DateTime), 40)
GO

-- Part1 (The below query fetches data from source - collected from several tables and inserted into ([dbo].[zA]) - approx. 1 million rows)

SELECT
InvoiceNo,Code,[Date],[Type]
FROM [dbo].[zA]

-- Part2 (Instead of pulling data from source for the 3 instances in Part2 ([dbo].[zA]), which will slow down the stored procedure), I just pull the data from source once (Part1). Thus, the below query - Part2, I want to add to Part1, so it's part of the same stored procedure.

I'm using an old database that does not have windows functions like CTE, ROWNUMBER, etc. , also can't use INSERT INTO table or INSERT INTO # table. Hope someone can assist. Thanks.)

SELECT
InvoiceNo,Code,[Date],[Type]
FROM [dbo].[zA]
WHERE Type = 50
AND InvoiceNo IN ('T15230020645','T15230020621')

UNION ALL

SELECT
s.InvoiceNo,Code,[Date],[Type]
FROM [dbo].[zA] s
INNER JOIN (
SELECT InvoiceNo, min([Date]) as MinDate
FROM [dbo].[zA]
WHERE Type = 40 AND Code = 'R'
GROUP BY InvoiceNo
) mins
ON s.InvoiceNo = mins.InvoiceNo and s.[Date] = mins.MinDate
WHERE s.[Type] = 40 AND s.Code = 'R'
AND s.InvoiceNo IN ('T15230020645','T15230020621')

This topic was modified 1 month, 3 weeks ago by kevinyl.
This topic was modified 1 month, 3 weeks ago by kevinyl.
This topic was modified 1 month, 3 weeks ago by kevinyl.
This topic was modified 1 month, 3 weeks ago by kevinyl.
This topic was modified 1 month, 2 weeks ago by kevinyl.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Ed B Hall of Fame Points: 3664 More actions · Answer 2

I don't understand what you need to do. I presume that you don't want to union all query 1 with query 2. Do you need to left outer join parts 1 and 2?

Can you explain the requirements? it appears that you wish to suppress "duplicates" under certain conditions by selecting the earliest row based on date. Alternatively if you can write the query using cte and/or row_number someone could recreate the logic using sql 2000 syntax.

Jeff Moden SSC Guru Points: 1004292 More actions · Answer 3

Ed B wrote:

I don't understand what you need to do. I presume that you don't want to union all query 1 with query 2. Do you need to left outer join parts 1 and 2?
Can you explain the requirements? it appears that you wish to suppress "duplicates" under certain conditions by selecting the earliest row based on date. Alternatively if you can write the query using cte and/or row_number someone could recreate the logic using sql 2000 syntax.

That pretty much covers everything I was going to ask about this question.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kevinyl SSC Veteran Points: 275 More actions · Answer 4

Ed B / Jeff Moden

-- Part1 (The below query fetches data from source - collected from several tables and inserted into ([dbo].[zA]) - approx. 1 million rows).

.....................

-- Part2 (Instead of pulling data from source for the 3 instances in Part2 ([dbo].[zA]), which will slow down the stored procedure), I just pull the data from source once (Part1). Thus, the below query - Part2, I want to add to Part1, so it's part of the same stored procedure. I'm using an old database that does not have windows functions like CTE, ROWNUMBER, etc. , also can't use INSERT INTO table or INSERT INTO # table.

.......................

SELECT InvoiceNo, min([Date]) as MinDate

Some InvoiceNo have many dates, I just need the first/earliest date for that InvoiceNo

Every InvoiceNo can have many (Type = 50 , Code = 'W') , but only 1 (Type = 40 , Code = 'R')

......................

Hope above explanation helps.