今有如下資料date_s (日期起) date_e(日期迄) inp_date (建立日) price
----------------------- ----------------------- ----------------------- -------
2009-11-10 00:00:00.000 2010-06-12 00:00:00.000 2009-07-07 00:00:00.000 1000
2009-11-07 00:00:00.000 2009-12-07 00:00:00.000 2009-10-28 00:00:00.000 850
2009-11-07 00:00:00.000 2009-11-16 00:00:00.000 2009-10-29 00:00:00.000 700
2009-11-09 00:00:00.000 2009-11-15 00:00:00.000 2009-10-29 00:00:00.000 450
2009-11-09 00:00:00.000 2009-11-15 00:00:00.000 2009-10-30 00:00:00.000 500可否求出
date_s date_e price
----------------------- ----------------------- -------
2009-11-07 00:00:00.000 2009-11-08 00:00:00.000 700
2009-11-09 00:00:00.000 2009-11-15 00:00:00.000 500
2009-11-16 00:00:00.000 2009-11-16 00:00:00.000 700
2009-11-17 00:00:00.000 2010-06-12 00:00:00.000 1000我想求出 在特定日期期間,價格調整的節點以最後輸入日為正確資料的資料,
請各位幫忙!!!
----------------------- ----------------------- ----------------------- -------
2009-11-10 00:00:00.000 2010-06-12 00:00:00.000 2009-07-07 00:00:00.000 1000
2009-11-07 00:00:00.000 2009-12-07 00:00:00.000 2009-10-28 00:00:00.000 850
2009-11-07 00:00:00.000 2009-11-16 00:00:00.000 2009-10-29 00:00:00.000 700
2009-11-09 00:00:00.000 2009-11-15 00:00:00.000 2009-10-29 00:00:00.000 450
2009-11-09 00:00:00.000 2009-11-15 00:00:00.000 2009-10-30 00:00:00.000 500可否求出
date_s date_e price
----------------------- ----------------------- -------
2009-11-07 00:00:00.000 2009-11-08 00:00:00.000 700
2009-11-09 00:00:00.000 2009-11-15 00:00:00.000 500
2009-11-16 00:00:00.000 2009-11-16 00:00:00.000 700
2009-11-17 00:00:00.000 2010-06-12 00:00:00.000 1000我想求出 在特定日期期間,價格調整的節點以最後輸入日為正確資料的資料,
請各位幫忙!!!
--1. 表結構
CREATE TABLE [dbo].[test](
[date_s] [datetime] NOT NULL,
[date_e] [datetime] NOT NULL,
[inp_date] [datetime] NOT NULL,
[sprice] [numeric](9, 3) NULL
) ON [PRIMARY]
--2. 測試數據
INSERT INTO dbo.test([date_s],[date_e],[inp_date],[sprice])VALUES (CONVERT(DATETIME, 0x00009cbd00000000), CONVERT(DATETIME, 0x00009d9300000000), CONVERT(DATETIME, 0x00009c3f00000000), 1000.000);
INSERT INTO dbo.test([date_s],[date_e],[inp_date],[sprice])VALUES (CONVERT(DATETIME, 0x00009cba00000000), CONVERT(DATETIME, 0x00009cd800000000), CONVERT(DATETIME, 0x00009cb000000000), 850.000);
INSERT INTO dbo.test([date_s],[date_e],[inp_date],[sprice])VALUES (CONVERT(DATETIME, 0x00009cba00000000), CONVERT(DATETIME, 0x00009cc300000000), CONVERT(DATETIME, 0x00009cb000000000), 700.000);
INSERT INTO dbo.test([date_s],[date_e],[inp_date],[sprice])VALUES (CONVERT(DATETIME, 0x00009cbc00000000), CONVERT(DATETIME, 0x00009cc200000000), CONVERT(DATETIME, 0x00009cb000000000), 450.000);
INSERT INTO dbo.test([date_s],[date_e],[inp_date],[sprice])VALUES (CONVERT(DATETIME, 0x00009cbc00000000), CONVERT(DATETIME, 0x00009cc200000000), CONVERT(DATETIME, 0x00009cb100000000), 500.000);--3. 計算方法 ??
尚未找到快速的方法--4. 正確結果date_s date_e price
----------------------- ----------------------- -------
2009-11-07 00:00:00.000 2009-11-08 00:00:00.000 700
2009-11-09 00:00:00.000 2009-11-15 00:00:00.000 500
2009-11-16 00:00:00.000 2009-11-16 00:00:00.000 700
2009-11-17 00:00:00.000 2010-06-12 00:00:00.000 1000 因資料龐大, 需進行大批比對, 故請求有最佳化的SQL語法得出結果