TSQL query - adapt Part2 to include Part1

  • 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 2 months ago by  kevinyl.
    • This topic was modified 2 months ago by  kevinyl.
    • This topic was modified 2 months ago by  kevinyl.
    • This topic was modified 2 months ago by  kevinyl.
    • This topic was modified 2 months ago by  kevinyl.
  • Thanks for posting your issue and hopefully someone will answer soon.

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

  • 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.

  • 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)

  • 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.

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

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