g****n
发帖数: 18 | 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*****g
发帖数: 34098 | 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********6
发帖数: 1511 | 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, |
|
