Latest value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Latest value

Author

Topic

Corobori
Posting Yak Master

105 Posts

Posted - 2005-06-09 : 15:17:26

I guess it's so obvious that I can't make it work !

I have got 2 tables tblOp and tblSecurity. I need to group all operation by Op_Sec_Id and retrieve the Sec_Price the closest to the Op_Val_Date. In this example the operation with the 10/jan/2005 should get the 31/dec/2004 value, the 5/feb/2005 and 10/feb/2005 the 1/feb/2005 value.

SET DATEFORMAT 'dmy'
SET NOCOUNT ON

CREATE TABLE [dbo].[#tblOp] (
[Od_Id] [int] NOT NULL ,
[Op_Sec_Id] [int] NOT NULL ,
[Op_Val_Date] [datetime] NOT NULL
) ON [PRIMARY]

GO
insert into #tblOp VALUES(1,1,'10/1/2005')
insert into #tblOp VALUES(2,1,'5/2/2005')
insert into #tblOp VALUES(3,1,'10/2/2005')

CREATE TABLE [dbo].[#tblSecurity] (
[Sec_Id] [int] NOT NULL ,
[Sec_Date] [datetime] NOT NULL ,
[Sec_Price] [decimal](18, 0) NOT NULL
) ON [PRIMARY]
GO

insert into #tblSecurity VALUES(1,'31/12/2004',100)
insert into #tblSecurity VALUES(1,'15/1/2005',150)
insert into #tblSecurity VALUES(1,'1/2/2005',150)

jean-luc
www.corobori.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-09 : 15:41:01

I believe that 10/jan/2005 should get the 15/jan/2005 value as January 15th is closer to January 10th than December 31st is.

Here ya go:



SET NOCOUNT ON
SET DATEFORMAT 'dmy'
SET NOCOUNT ON

CREATE TABLE [dbo].[#tblOp] (
[Od_Id] [int] NOT NULL ,
[Op_Sec_Id] [int] NOT NULL ,
[Op_Val_Date] [datetime] NOT NULL
) ON [PRIMARY]

GO
insert into #tblOp VALUES(1,1,'10/1/2005')
insert into #tblOp VALUES(2,1,'5/2/2005')
insert into #tblOp VALUES(3,1,'10/2/2005')

CREATE TABLE [dbo].[#tblSecurity] (
[Sec_Id] [int] NOT NULL ,
[Sec_Date] [datetime] NOT NULL ,
[Sec_Price] [decimal](18, 0) NOT NULL
) ON [PRIMARY]
GO

insert into #tblSecurity VALUES(1,'31/12/2004',100)
insert into #tblSecurity VALUES(1,'15/1/2005',150)
insert into #tblSecurity VALUES(1,'1/2/2005',250)

SELECT b.Op_Val_Date, c.Sec_Date, c.Sec_Price
FROM
(
	SELECT Op_Val_Date, MIN(DateDifference) AS DateDifference
	FROM 
	(
		SELECT Op_Val_Date, ABS(DATEDIFF(d, Op_Val_Date, Sec_Date)) AS DateDifference, Sec_Date, Sec_Price
		FROM #tblOp o
		INNER JOIN #tblSecurity s
		ON o.Op_Sec_Id = s.Sec_Id
	) a
	GROUP BY Op_Val_Date
) b
INNER JOIN 
(
	SELECT Op_Val_Date, ABS(DATEDIFF(d, Op_Val_Date, Sec_Date)) AS DateDifference, Sec_Date, Sec_Price
	FROM #tblOp o
	INNER JOIN #tblSecurity s
	ON o.Op_Sec_Id = s.Sec_Id
) c
ON b.Op_Val_Date = c.Op_Val_Date AND b.DateDifference = c.DateDifference

DROP TABLE #tblOp, #tblSecurity

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-09 : 15:54:56

Well here you go if December 31st is the answer for January 10th, which means you only want the closest date that is less than Op_Val_Date instead of just the closest date as was described in your post.



SELECT b.Op_Val_Date, c.Sec_Date, c.Sec_Price
FROM
(
	SELECT Op_Val_Date, MAX(DateDifference) AS DateDifference
	FROM 
	(
		SELECT Op_Val_Date, DATEDIFF(d, Op_Val_Date, Sec_Date) AS DateDifference, Sec_Date, Sec_Price
		FROM #tblOp o
		INNER JOIN #tblSecurity s
		ON o.Op_Sec_Id = s.Sec_Id
	) a
	WHERE DateDifference <= 0
	GROUP BY Op_Val_Date
) b
INNER JOIN 
(
	SELECT Op_Val_Date, DATEDIFF(d, Op_Val_Date, Sec_Date) AS DateDifference, Sec_Date, Sec_Price
	FROM #tblOp o
	INNER JOIN #tblSecurity s
	ON o.Op_Sec_Id = s.Sec_Id
) c
ON b.Op_Val_Date = c.Op_Val_Date AND b.DateDifference = c.DateDifference

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources