A question of filling in missing value in SQL - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

A question of filling in missing value in SQL

A question of filling in missing value in SQL# Database - 数据库

g*n2008-10-23 07:10

1 楼

Hello,
I have a question on using SQL to fill in some missing values in a dataset?
Right now my dataset look like this
ID New_ID Date CV Value
3270001111111 098766 7/31/2007 check 40000
3270001111111 098766 9/30/2007 check 135000
3270001111111 098766 2/28/2008 check 75000
Note there is no date for 8/2007, 10/2007, 11/2007, 12/2007, and 1/2008. I
want to fill in those blanks with the data from previous month, making it
looks like the

B*g2008-10-23 07:10

2 楼

ORACLE
WITH
DATA_TABLE AS
(SELECT 3270001111111 ID,'098766' NEWID,TO_DATE('07/31/2007', 'mm/dd/yyyy')
A_DATE,'check' CV,40000 A_VALUE FROM DUAL
UNION ALL
SELECT 3270001111111,'098766',TO_DATE('09/30/2007', 'mm/dd/yyyy'),'check',
135000 FROM DUAL
UNION ALL
SELECT 3270001111111,'098766',TO_DATE('02/28/2008', 'mm/dd/yyyy'),'check',
75000 FROM DUAL
UNION ALL
SELECT 3270001111112,'098757',TO_DATE('10/31/2007', 'mm/dd/yyyy'),'mo' ,
6000 FROM DUAL),
DATE_LIST AS
(SELECT TO_DATE('07/31/2007', 'mm/

【在 g****n 的大作中提到】

: Hello,
: I have a question on using SQL to fill in some missing values in a dataset?
: Right now my dataset look like this
: ID New_ID Date CV Value
: 3270001111111 098766 7/31/2007 check 40000
: 3270001111111 098766 9/30/2007 check 135000
: 3270001111111 098766 2/28/2008 check 75000
: Note there is no date for 8/2007, 10/2007, 11/2007, 12/2007, and 1/2008. I
: want to fill in those blanks with the data from previous month, making it
: looks like the

n*62008-10-23 07:10

3 楼

/**************************
** MS SQL, USE CURSOR
** ADDDATE function need to be tested.
** Logic is not complete.
***************************/
DECLARE REC_cursor CURSOR STATIC FOR
SELECT id, newid, date, ...
FROM TABLE
OPEN REC_cursor
FETCH REC_cursor INTO @id, @newid, @date, ...
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF DATEADD(month, 1, @date) < '7/31/2007'
INSERT INTO TABLE
VALUES (@id, @newid, DATEADD(month, 1, @date), ...)
FETCH NEXT FROM REC_cursor INTO @id, @newid, @date,