SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL to generate missing dates n prices from prev
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

deepsi1
Starting Member

1 Posts

Posted - 07/30/2013 :  09:15:41  Show Profile  Reply with Quote
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

547 Posts

Posted - 07/30/2013 :  10:46:27  Show Profile  Reply with Quote
If you have SQL2012 you can do this:


-- 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

Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 07/30/2013 :  17:33:17  Show Profile  Reply with Quote
Here is a better version, works for multiple securities and any number of holidays: (BTW you still need SQL2012):



-- 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


Edited by - MuMu88 on 07/30/2013 17:39:37
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/31/2013 :  01:50:41  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000