Post reply

Need help with a correlated query

  • January 21, 2010 at 5:51 am

    #386155

    Hi, I need help with a problem. I have a table with some columns (see below).

    I need to select the last row for each customer, contract and rno based on column DateFrom. BUT if the previous row has a discount, I need to select this one, too.

    Here is a script to generate the testdata:

    CREATE TABLE [TestTable](

    [CustNo] [varchar](20) NOT NULL,

    [ContractNo] [varchar](20) NOT NULL,

    [RNo] [int] NOT NULL,

    [DateFrom] [datetime] NOT NULL,

    [Discount] [int] NULL

    )

    INSERT INTO [TestTable]([CustNo], [ContractNo], [RNo], [DateFrom], [Discount])

    SELECT N'1', N'1', 1, '20100101 00:00:00.000', NULL UNION ALL

    SELECT N'1', N'1', 1, '20090101 00:00:00.000', NULL UNION ALL

    SELECT N'1', N'1', 1, '20080101 00:00:00.000', 50 UNION ALL

    SELECT N'1', N'1', 2, '20100101 00:00:00.000', NULL UNION ALL

    SELECT N'1', N'1', 2, '20090101 00:00:00.000', 50 UNION ALL

    SELECT N'1', N'1', 3, '20100101 00:00:00.000', NULL UNION ALL

    SELECT N'1', N'1', 3, '20090101 00:00:00.000', NULL UNION ALL

    SELECT N'2', N'3', 2, '20100101 00:00:00.000', NULL UNION ALL

    SELECT N'2', N'3', 2, '20090101 00:00:00.000', 50 UNION ALL

    SELECT N'2', N'3', 2, '20080101 00:00:00.000', 50

    This gives me the following data:

    CustNo ContractNo RNo DateFrom Discount

    1 1 1 2010-01-01 00:00:00.000 NULL

    1 1 1 2009-01-01 00:00:00.000 NULL

    1 1 1 2008-01-01 00:00:00.000 50

    1 1 2 2010-01-01 00:00:00.000 NULL

    1 1 2 2009-01-01 00:00:00.000 50

    1 1 3 2010-01-01 00:00:00.000 NULL

    1 1 3 2009-01-01 00:00:00.000 NULL

    2 3 2 2010-01-01 00:00:00.000 NULL

    2 3 2 2009-01-01 00:00:00.000 50

    2 3 2 2008-01-01 00:00:00.000 50

    What I want from the query is the following:

    CustNo ContractNo RNo DateFrom Discount

    1 1 1 2010-01-01 00:00:00.000 NULL

    1 1 2 2010-01-01 00:00:00.000 NULL

    1 1 2 2009-01-01 00:00:00.000 50

    1 1 3 2010-01-01 00:00:00.000 NULL

    2 3 2 2010-01-01 00:00:00.000 NULL

    2 3 2 2009-01-01 00:00:00.000 50

    If there is anyone who is able to see a solution I would be very grateful!!

  • January 21, 2010 at 6:08 am

    #1106628

    WITH CTE AS (

    SELECT [CustNo], [ContractNo], [RNo], [DateFrom], [Discount],

    ROW_NUMBER() OVER(PARTITION BY [CustNo], [ContractNo], [RNo] ORDER BY [DateFrom] DESC) AS rn

    FROM TestTable)

    SELECT [CustNo], [ContractNo], [RNo], [DateFrom], [Discount]

    FROM CTE

    WHERE rn=1 OR (rn=2 AND [Discount] IS NOT NULL)

    ORDER BY [CustNo], [ContractNo], [RNo], [Discount], [DateFrom]

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • January 21, 2010 at 6:24 am

    #1106635

    That works perfectly on my SQL2008 :-), but I forgot to mention that the server in question is a SQL Server 2000.

    Sorry about that...

Viewing 3 posts - 1 through 2 (of 2 total)

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