TSQL query - adapt Part2 to include Part1

• kevinyl

  SSC Veteran

  Points: 274

  More actions

  September 19, 2024 at 1:00 pm

  #4459919

  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 to fetch data)

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

  -- Part2 (The below query  I want to add to Part1. 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 7 hours, 41 minutes ago by  kevinyl.
  • This topic was modified 7 hours, 12 minutes ago by  kevinyl.
  • This topic was modified 3 hours, 37 minutes ago by  kevinyl.
  • This topic was modified 3 hours, 34 minutes ago by  kevinyl.