SQL to generate missing dates n prices from prev

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

General SQL Server Forums

New to SQL Server Programming

SQL to generate missing dates n prices from prev

Author

Topic

deepsi1
Starting Member

1 Post

Posted - 2013-07-30 : 09:15:41

I have a table MKT which contains following fields value_date,stk_exch,security,mkt_price,source,currency,name of say 500 securities for each day (excluding Saturday and Sunday and other Market Holidays).
I need an sql to automatically generate missing dates and price from immediately previous date for missing date. So if Friday is 26.07.2013 and Saturday and Sunday are 27th and 28th, then date and prices for 27th and 28th would be missing from this table.
So while spooling the prices for entire month July 2013 I should get all dates and for missing dates eg: 27 and 28 the sql would take price of 26th.
I am using Oracle
value_date stk_exch security mkt_price
26/07/2013 BSE BANKBARODA 565.85
29/07/2013 BSE BANKBARODA 585.85
Now SQL should Return
value_date stk_exch security mkt_price
26/07/2013 BSE BANKBARODA 565.85
27/07/2013 BSE BANKBARODA 565.85
28/07/2013 BSE BANKBARODA 565.85
29/07/2013 BSE BANKBARODA 585.85

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-30 : 10:46:27

If you have SQL2012 you can do this:
[CODE]

-- TEST DATA
DECLARE @Temp1 TABLE(value_date DATE, stk_exch VARCHAR(6), security VARCHAR(20), mkt_price DECIMAL(18,2));
INSERT INTO @Temp1 VALUES
('2013-07-26', 'BSE', 'BANKBARODA', 565.85),
('2013-07-29', 'BSE', 'BANKBARODA', 565.86),
('2013-07-30', 'BSE', 'BANKBARODA', 565.87),
('2013-07-31', 'BSE', 'BANKBARODA', 565.88);

DECLARE @Startdate DATE = (SELECT DATEADD(dd, -1, MIN(value_date)) from @Temp1)
DECLARE @Enddate DATE = (SELECT MAX(value_date) from @Temp1)
;With CTE
AS
(
SELECT DATEADD(dd, 1, @startdate) AS value_date
UNION ALL
SELECT DATEADD(dd, 1, value_date) AS value_date
FROM CTE
WHERE DATEADD(dd, 1, value_date) <= @Enddate
)
SELECT T2.value_date,
COALESCE(stk_exch, LAG(stk_exch, 1, stk_exch) OVER(ORDER BY T2.value_date),
LAG(stk_exch, 2, stk_exch) OVER(ORDER BY T2.value_date)) as stk_exch,
COALESCE(security, LAG(security, 1, security) OVER(ORDER BY T2.value_date),
LAG(security, 2, security) OVER(ORDER BY T2.value_date)) as security,
COALESCE(mkt_price, LAG(mkt_price, 1, mkt_price) OVER(ORDER BY T2.value_date),
LAG(mkt_price, 2, mkt_price) OVER(ORDER BY T2.value_date)) as mkt_price
FROM
CTE T2 LEFT JOIN @Temp1 T1 ON T2.value_date = T1.value_date

[/CODE]

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-30 : 17:33:17

Here is a better version, works for multiple securities and any number of holidays: (BTW you still need SQL2012):

[CODE]

-- TEST CODE
DECLARE @Temp1 TABLE(value_date DATE, stk_exch VARCHAR(6), [security] VARCHAR(20), mkt_price DECIMAL(18,2));
INSERT INTO @Temp1 VALUES
('2013-06-21', 'BSE', 'BANKBARODA', 565.85),
('2013-06-24', 'BSE', 'BANKBARODA', 565.86),
('2013-06-25', 'BSE', 'BANKBARODA', 565.87),
('2013-06-26', 'BSE', 'BANKBARODA', 565.88),
('2013-06-27', 'BSE', 'BANKBARODA', 566.86),
('2013-07-01', 'BSE', 'BANKBARODA', 567.87),
('2013-07-02', 'BSE', 'BANKBARODA', 568.88),
('2013-07-03', 'BSE', 'BANKBARODA', 567.87),
('2013-07-05', 'BSE', 'BANKBARODA', 568.88),
('2013-07-08', 'BSE', 'BANKBARODA', 369.88),
('2013-07-09', 'BSE', 'BANKBARODA', 367.87),
('2013-07-10', 'BSE', 'BANKBARODA', 368.88),

('2013-06-21', 'ORCL', 'NYSE', 25.85),
('2013-06-24', 'ORCL', 'NYSE', 25.86),
('2013-06-25', 'ORCL', 'NYSE', 25.87),
('2013-06-26', 'ORCL', 'NYSE', 25.88),
('2013-06-27', 'ORCL', 'NYSE', 26.86),
('2013-07-01', 'ORCL', 'NYSE', 27.87),
('2013-07-02', 'ORCL', 'NYSE', 28.88),
('2013-07-03', 'ORCL', 'NYSE', 27.87),
('2013-07-05', 'ORCL', 'NYSE', 28.88),
('2013-07-08', 'ORCL', 'NYSE', 29.88),
('2013-07-09', 'ORCL', 'NYSE', 27.87),
('2013-07-10', 'ORCL', 'NYSE', 28.88),

('2013-06-21', 'IBM', 'BANKBARODA', 365.85),
('2013-06-24', 'IBM', 'BANKBARODA', 365.86),
('2013-06-25', 'IBM', 'BANKBARODA', 365.87),
('2013-06-26', 'IBM', 'BANKBARODA', 365.88),
('2013-06-27', 'IBM', 'BANKBARODA', 366.86),
('2013-07-01', 'IBM', 'BANKBARODA', 367.87),
('2013-07-02', 'IBM', 'BANKBARODA', 368.88),
('2013-07-03', 'IBM', 'BANKBARODA', 367.87),
('2013-07-05', 'IBM', 'BANKBARODA', 368.88),
('2013-07-08', 'IBM', 'BANKBARODA', 369.88),
('2013-07-09', 'IBM', 'BANKBARODA', 367.87),
('2013-07-10', 'IBM', 'BANKBARODA', 368.88);

; With CT AS
(SELECT [security], stk_exch, DATEADD(dd, -1, MIN(value_date)) as StartDate, MAX(value_date) as EndDate from @Temp1 GROUP BY [security], stk_exch),
CTE
AS
(
SELECT 1 as RN, [security], stk_exch, DATEADD(dd, 1, StartDate) AS value_date, EndDate FROM CT T
UNION ALL
SELECT RN+1 as RN, [security], stk_exch, DATEADD(dd, 1, value_date), EndDate AS value_date
FROM CTE
WHERE DATEADD(dd, 1, value_date) <= EndDate
),
CTE1 AS
(SELECT ROW_NUMBER() OVER(PARTITION BY stk_exch, [security] ORDER BY value_date desc) as RN1,
value_date, stk_exch, mkt_price,
security FROM @Temp1
),
CTE2 AS
(SELECT RN, RN1, ROW_NUMBER() OVER(PARTITION BY T2.stk_exch, T2.[security], (RN + RN1) ORDER BY T2.value_date) as GRP,
T2.value_date, T2.stk_exch, T2.security, mkt_price
FROM
CTE T2 LEFT JOIN CTE1 T1 ON T2.value_date = T1.value_date and T2.security = T1.Security and T2.stk_exch = T1.stk_exch
), -- SELECT * FROM CTE2 ORDER BY [security], stk_exch, value_date;
CTE3 AS
(SELECT ROW_NUMBER() OVER(PARTITION BY stk_exch, [security], (CASE WHEN RN1 IS NULL THEN (RN - GRP) ELSE (RN + RN1) END) ORDER BY value_date) as GROUP1, * FROM CTE2 )
SELECT value_date,
COALESCE(stk_exch, LAG(stk_exch, GROUP1, stk_exch) OVER(PARTITION BY [security], stk_exch ORDER BY value_date)) as stk_exch,
COALESCE([security], LAG([security], GROUP1, [security]) OVER(PARTITION BY [security], stk_exch ORDER BY value_date)) as security,
COALESCE(mkt_price, LAG(mkt_price, GROUP1, mkt_price) OVER(PARTITION BY [security], stk_exch ORDER BY value_date)) as mkt_price,
IIF(mkt_price is NULL, 'MKT CLOSED', 'MKT OPEN') as MarketStatus
FROM CTE3 ORDER BY stk_exch, [security], value_date

[/CODE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-31 : 01:50:41

I am using Oracle
Then you're in the wrong forum. This is MS SQL Server forum so people here dont have much expertise on Oracle. You may try the below solution, but if it doesnt work for you you may be better off posting this in some Oracle forum like www.orafaq.com


;With Dates
AS
(
SELECT MIN(value_date) AS dateval
FROM MKT
UNION ALL
SELECT dateval +1
FROM Dates
WHERE dateval +1 <= (SELECT MAX(value_date) FROM MKT)
)

SELECT *
FROM
(
SELECT dateval,stk_exch, security 
FROM Dates
CROSS JOIN (SELECT DISTINCT stk_exch, security FROM MKT) 
)m
CROSS APPLY( SELECT TOP 1 mkt_price
FROM MKT n
WHERE n.stk_exch = m.stk_exch
AND n.security = m.security 
AND n.value_date <= m.dateval
ORDER BY value_date DESC)m1

OPTION (MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources