Need help with a correlated query
-
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:
CustNoContractNoRNoDateFromDiscount
1112010-01-01 00:00:00.000NULL
1112009-01-01 00:00:00.000NULL
1112008-01-01 00:00:00.00050
1122010-01-01 00:00:00.000NULL
1122009-01-01 00:00:00.00050
1132010-01-01 00:00:00.000NULL
1132009-01-01 00:00:00.000NULL
2322010-01-01 00:00:00.000NULL
2322009-01-01 00:00:00.00050
2322008-01-01 00:00:00.00050
What I want from the query is the following:
CustNoContractNoRNoDateFromDiscount
1112010-01-01 00:00:00.000NULL
1122010-01-01 00:00:00.000NULL
1122009-01-01 00:00:00.00050
1132010-01-01 00:00:00.000NULL
2322010-01-01 00:00:00.000NULL
2322009-01-01 00:00:00.00050
If there is anyone who is able to see a solution I would be very grateful!!
-
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
-
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