January 22, 2010 at 1:13 am
Hi, I originally posted this question in SQL2005 T-SQL forum, but it is actually a SQL 2000 problem. So I don't have the possibility to use row_number and partitioning to solve the 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!!
Regards,
Dagfinn
January 22, 2010 at 1:40 am
Hi , I dont have access to a 2000 box to test but i think this should would
Select t1.* into #t1 from TestTable t1
join (Select CustNo,ContractNo,RNo,MAX(DateFrom) as MaxDate
from TestTable t2
group by CustNo,ContractNo,RNo) as LastDate
on LastDate.MaxDate = t1.DateFrom
and LastDate.CustNo = t1.CustNo
and LastDate.ContractNo = t1.ContractNo
and LastDate.RNo = t1.Rno
go
Select * from #t1
union all
Select t3.* from #t1
join TestTable t3
on t3.CustNo = #t1.CustNo
and t3.ContractNo = #t1.ContractNo
and t3.RNo = #t1.Rno-1
and t3.Discount is not null
January 22, 2010 at 1:56 am
Thanks for your reply, Dave!
That gives me the following result - correct number of rows, but not the correct content...
CustNo ContractNo RNo DateFrom Discount
2 3 2 2010-01-01 00:00:00.000 NULL
1 1 3 2010-01-01 00:00:00.000 NULL
1 1 2 2010-01-01 00:00:00.000 NULL
1 1 1 2010-01-01 00:00:00.000 NULL
1 1 1 2008-01-01 00:00:00.000 50
1 1 2 2009-01-01 00:00:00.000 50
I think I must try to figure out what you have done with the joins :hehe:
regards,
Dagfinn
January 22, 2010 at 2:35 am
Try this, ive had to create a unique Id in the required order to get the 'previous' row
Drop table #Testtable
go
CREATE TABLE [#Testtable](
[TestTableId] integer identity,
[CustNo] [varchar](20) NOT NULL,
[ContractNo] [varchar](20) NOT NULL,
[RNo] [int] NOT NULL,
[DateFrom] [datetime] NOT NULL,
[Discount] [int] NULL
)
go
drop table #t1
go
insert into #TestTable
([CustNo], [ContractNo], [RNo], [DateFrom], [Discount])
Select t1.*
from TestTable t1
order by [CustNo],[ContractNo] ,[RNo],[DateFrom]
go
Select t1.* into #t1 from #TestTable t1
join (Select CustNo,ContractNo,RNo,MAX(DateFrom) as MaxDate
from #TestTable t2
group by CustNo,ContractNo,RNo) as LastDate
on LastDate.MaxDate = t1.DateFrom
and LastDate.CustNo = t1.CustNo
and LastDate.ContractNo = t1.ContractNo
and LastDate.RNo = t1.Rno
go
Select * from #t1
union all
Select t3.* from #t1
join #Testtable t3
on t3.CustNo = #t1.CustNo
and t3.ContractNo = #t1.ContractNo
and t3.RNo = #t1.Rno
and t3.TestTableId= #t1.TestTableId-1
and t3.Discount is not null
January 22, 2010 at 4:49 am
Thank's a lot, Dave.
It works, and the solution also works on my production DB (which involves a lot more fields).
So - thanks again !
Regards,
Dagfinn
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply