June 9, 2005 at 12:07 pm
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
June 9, 2005 at 3:13 pm
SELECT AAA.OD_ID,Op_Sec_ID,Op_Val_Date,Sec_Date,Sec_Price
FROM
#tblOP AAA INNER JOIN
(SELECT OD_ID,MIN(MyNmb) MyDiff
FROM
(SELECT A.Od_Id,A.Op_Sec_Id,A.Op_Val_Date,B.Sec_ID,B.Sec_Date,B.Sec_Price, CASE WHEN A.Op_Val_Date>B.Sec_Date THEN CAST(A.Op_Val_Date as int)-CAST(B.Sec_Date as int) ELSE CAST(B.Sec_Date as int)-CAST(A.Op_Val_Date as int) END MyNmb
FROM #tblOP A inner join #tblSecurity B
ON A.Op_Sec_ID=B.Sec_ID) AA
WHERE MyNmb>=0
GROUP BY AA.OD_ID, AA.Op_Sec_Id) BBB
on AAA.Od_Id=BBB.Od_ID INNER JOIN
#tblSecurity CCC ON
AAA.Op_Sec_ID=CCC.Sec_ID and cast(AAA.Op_Val_Date as int)-MyDiff=cast(CCC.Sec_Date as int)
Vasc
June 9, 2005 at 3:16 pm
>>retrieve the Sec_Price the closest to the Op_Val_Date.
What are the requirements in the case of a tie ? What if there's a Sec_Price 5 days before & 5 days after an Op_Val_Date, which one is "closest" ?
June 9, 2005 at 4:31 pm
Assumption: "Closest", in your definition, means the closest previous (same day or earlier) record.
Assumption: There will be no duplicate date/Sec_ID records
With this:
SELECT op_id, sec_id, op_val_date, sec_date, Sec_Price
FROM #tblOP o
INNER JOIN #tblSecurity s ON o.Op_Sec_ID = s.Sec_ID
WHERE Sec_Date =
(SELECT MAX(Sec_Date) From #tblSecurity S1
WHERE Sec_Date <= op_Val_Date)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply