Converting Daily data to Weekly data Sql Server 2005
-
Hi All,
I have a table of stock details for NSE holiding Daily data.
Here is the structure of the table:
Table Name: NseData
symbolid bigint
symbol varchar(200)
stimestamp datetime -- Stores everyday date - Values are updated End of Day
sopen numeric(18, 2)
shigh numeric(18, 2)
slow numeric(18, 2)
sclose numeric(18, 2)
tottrdqty bigint
Sample data looks like: (Daily Data)
1 20MICRONS 2011-02-01 42.00 42.55 41.50 42.00 5137
2 3IINFOTECH 2011-02-01 49.45 50.20 47.20 47.65 915994
3 3MINDIA 2011-02-01 3495.00 3500.00 3402.05 3494.35 989
4 A2ZMES 2011-02-01 268.00 299.85 265.10 266.25 290144
5 AARTIDRUGS 2011-02-01 139.80 140.00 134.00 138.80 3359
6 AARTIIND EQ 2011-02-01 54.85 50.75 51.30 51.30 126181
Requirement:
The above given data is daily data and there are a total of 1500 symbols everyday that gets open, high, low, close values and the data stretches for over an year with approximately 5 Lakh records. I need all these records to be converted to Weekly data.
Daily Data to Weekly Data Procedure:
When converting daily data to weekly data, following is the procedure that is followed:
Week starts from Monday and ends on Friday a total of 5 days
Week Date is taken as date of Monday
Open value is taken as the open value (sopen) of Monday
High Value is taken as Highest of High column (shigh) in the entire week of 5 days.
Low Value is taken as Lowest of Low column (slow) in the entire week of 5 days.
Close value is taken as close value (sclose) of Friday
For every week there is only one record generated per symbol. So approximately 52 records per symbol are generated in an year.
I want an sql view that can generate these weekly records based on the procedure given above by generating weekly data for each symbol and for all the weeks in an year and for all the symbols.
This is my first Post in this forum. So kindly guide me through if I have made any mistake posting things here.
Looking forward for your response and thanks for your help in advance.
-
CREATE TABLE NseData(
symbolid bigint,
symbol varchar(200),
stimestamp datetime,
sopen numeric(18, 2),
shigh numeric(18, 2),
slow numeric(18, 2),
sclose numeric(18, 2),
tottrdqty bigint
)
INSERT INTO NseData
VALUES
(1,'Microsoft','Jul, 23 2012',42.00,43.5,38.25,43,10),
(1,'Microsoft','Jul, 24 2012',43.25,44.75,39.50,44.25,20),
(1,'Microsoft','Jul, 25 2012',44.50,46.00,40.75,45.50,30),
(1,'Microsoft','Jul, 26 2012',45.75,47.25,42.00,46.75,10),
(1,'Microsoft','Jul, 27 2012',47.00,48.50,43.25,48.00,20),
(2,'Infosys','Jul, 23 2012',48.25,49.75,44.50,49.25,30),
(2,'Infosys','Jul, 24 2012',49.50,51.00,45.75,50.50,10),
(2,'Infosys','Jul, 25 2012',50.75,52.25,47.00,51.75,20),
(2,'Infosys','Jul, 26 2012',52.00,53.50,48.25,53.00,30),
(2,'Infosys','Jul, 27 2012',53.25,54.75,49.50,54.25,10);
DECLARE @today datetime = CONVERT(DATETIME,CONVERT(DATE,getdate())) -- TODAY's DateDECLARE @start_date DATETIME
DECLARE @end_date DATETIME
SELECT @start_date=DATEADD(d,-1*(DATEPART(dw,@today-1)-2),@today-1)
SELECT @end_date = DATEADD(s,-1,DATEADD(d,5,@start_date))
SELECT symbolid,symbol,@start_date as WeekDate,MAX(shigh) sHigh,
MIN(sLow) sLow,
MAX(CASE CONVERT(DATETIME,CONVERT(DATE,stimestamp))
WHEN @start_date THEN sOpen END) AS sOpen,
MAX(CASE CONVERT(DATETIME,CONVERT(DATE,stimestamp))
WHEN CONVERT(DATETIME,CONVERT(DATE,@end_date)) THEN sClose END) AS sClose
FROM NseData
WHERE stimestamp BETWEEN @start_date AND @end_date
GROUP BY symbolid,symbol
Run this query on any date (say x), It will give you the data from Monday to Friday for the week in which x lies.
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
As you are new to this forum try to know the best practices in order to get quicker answers on T-SQL questions, Follow this link
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Cheers
Lokesh
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Hi Lokesh,
Thanks a lot for your guidance.
Your query fetches me results between the start date and end date. If the start date is 01-JAN-2011'
end date is 31-DEC-2011 (1 Year), it is just giving me one row between these two dates for every symbol.
what I am expecting:
There are 52 weeks in a year. Every week i want a row to be generated for every symbol taking the values between Monday through Friday to compute open, high, low, close as described in the procedure of my post.
So for a symbol 'MICROSOFT' there are 52 records generated one for each week of an year
Here is procedure once again for each record generated in a week
sOpen value of Moday,
Highest of sHigh value between Monday to Friday
Lowest of sLow value between Monday to Friday
sClose value of Friday
I want a view to do this. I hope i could make it clear.
Looking forward for your response once again.
Regards,
Reuben
-
Here you go....
I have modified the Data Script to add one more week for both Microsoft and Infosys
CREATE TABLE NseData(
symbolid bigint,
symbol varchar(200),
stimestamp datetime,
sopen numeric(18, 2),
shigh numeric(18, 2),
slow numeric(18, 2),
sclose numeric(18, 2),
tottrdqty bigint
)
INSERT INTO NseData
VALUES
(1,'Microsoft','Jul, 23 2012',42.00,43.5,38.25,43,10),
(1,'Microsoft','Jul, 24 2012',43.25,44.75,39.5,44.25,20),
(1,'Microsoft','Jul, 25 2012',44.5,46,40.75,45.5,30),
(1,'Microsoft','Jul, 26 2012',45.75,47.25,42,46.75,10),
(1,'Microsoft','Jul, 27 2012',47,48.5,43.25,48,20),
(2,'Infosys','Jul, 23 2012',48.25,49.75,44.5,49.25,30),
(2,'Infosys','Jul, 24 2012',49.5,51,45.75,50.5,10),
(2,'Infosys','Jul, 25 2012',50.75,52.25,47,51.75,20),
(2,'Infosys','Jul, 26 2012',52,53.5,48.25,53,30),
(2,'Infosys','Jul, 27 2012',53.25,54.75,49.5,54.25,10),
(1,'Microsoft','Jul, 02 2012',54.5,56,50.75,55.5,10),
(1,'Microsoft','Jul, 03 2012',55.75,57.25,52,56.75,20),
(1,'Microsoft','Jul, 04 2012',57,58.5,53.25,58,30),
(1,'Microsoft','Jul, 05 2012',58.25,59.75,54.5,59.25,10),
(1,'Microsoft','Jul, 06 2012',59.5,61,55.75,60.5,20),
(2,'Infosys','Jul, 02 2012',60.75,62.25,57,61.75,30),
(2,'Infosys','Jul, 03 2012',62,63.5,58.25,63,10),
(2,'Infosys','Jul, 04 2012',63.25,64.75,59.5,64.25,20),
(2,'Infosys','Jul, 05 2012',64.5,66,60.75,65.5,30),
(2,'Infosys','Jul, 06 2012',65.75,67.25,62,66.75,10);
Here is the query which will give you 2 records both for Microsoft and Infosys...Once you apply this on complete data set, it will give you 52 records for each symbol.
DECLARE @year_start DATETIME = '02-JAN-2012'DECLARE @year_end DATETIME = '31-DEC-2012'
;WITH Date_range
AS
(
SELECT @year_start Start_date, DATEADD(s,-1,@year_start+5) End_date
UNION ALL
SELECT Start_date + 7, DATEADD(s,-1,Start_date+7+5)
FROM Date_range
WHERE Start_date + 1 <=@year_end
AND DATEADD(s,-1,Start_date+7+5) <=@year_end
AND DATEPART(dw,Start_date)=2
)
SELECT symbolid,symbol,Start_date as WeekDate,MAX(shigh) sHigh,
MIN(sLow) sLow,
MAX(CASE CONVERT(DATETIME,CONVERT(DATE,stimestamp))
WHEN Start_date THEN sOpen END) AS sOpen,
MAX(CASE CONVERT(DATETIME,CONVERT(DATE,stimestamp))
WHEN CONVERT(DATETIME,CONVERT(DATE,End_date)) THEN sClose END) AS sClose
FROM NseData n,Date_range d
WHERE n.stimestamp BETWEEN d.Start_date AND d.End_date
GROUP BY symbolid,symbol,Start_date
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
One more thing, i missed above
DECLARE @year_start DATETIME = '02-JAN-2012' --First Monday of the yearDECLARE @year_end DATETIME = '31-DEC-2012'
@Year_start is the first Monday of the year. End date can be any thing. Furthermore, you can apply this to any date span - like 1 yr,2 yr and so on.
Check the results 🙂
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Hi Lokesh,
Thanks for it. 90% of the query works like charm!..
Now the problem is with sClose values.
I am executing the query with startdate = '03-JAN-2011' (MONDAY)
end date='31-DEC-2011' (SATURDAY)
sOpen, sHigh, sLow gets the values as per a week. But not sClose. If i'm not wrong, it is taking the value of the Last day in the year which actually should be a friday in the same week where sOpen, sHigh, sLow values are being generated.
I am attaching a picture for your information. I have made few changes to code to work as per my requirement but you please follow the code which you have written. I'll modify it as per my requirement. Just concentrate on sClose.
Thanks for all your help mate. You are excellent. This piece of code I have been trying for the past 3 days.. Help me through.
Regards,
Reuben.
-
Try putting Year end date as first friday of next year and check the results.
And I am not able to see your screen attachment, Please post again.
Thanks
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Thanks for all your help mate. You are excellent. This piece of code I have been trying for the past 3 days..
Glad to know that was helpful. I am not able to view your screen shot. Kindly attached it in the post [check Attachements tab at the end of Post reply box]
I will look into and get back to you.
-Lokesh
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
https://www.sqlservercentral.com/Forums/Uploads/image-unavailable.png
visit the above link and you should be able to view the image.
thanks
-
sClose should not be NULL. Not sure why this is happening. On my machine with this sample data. It is showing the correct value.
| SYMBOLID | SYMBOL | WEEKDATE | SHIGH | SLOW | SOPEN | SCLOSE |
---------------------------------------------------------------------------------------
| 1 | Microsoft | July, 02 2012 00:00:00-0700 | 61 | 50.75 | 54.5 | 60.5 |
| 1 | Microsoft | July, 23 2012 00:00:00-0700 | 48.5 | 38.25 | 42 | 48 |
| 2 | Infosys | July, 02 2012 00:00:00-0700 | 67.25 | 57 | 60.75 | 66.75 |
| 2 | Infosys | July, 23 2012 00:00:00-0700 | 54.75 | 44.5 | 48.25 | 54.25 |
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
symbolid is an autonumber. You don't need to group that.
In the logic which you wrote sClose is taking the final value may be the end of the year or any other final end date which we take.
Could you please check and make sure sClose takes the final friday value as sOpen is taking the Monday value of the week.
-
Took a second look. With the sample data, here is the result which I am getting. Is sClose correct? | SYMBOL | WEEKDATE | SHIGH | SLOW | SOPEN | SCLOSE |
----------------------------------------------------------------------------
| Infosys | July, 02 2012 00:00:00-0700 | 67.25 | 57 | 60.75 | 66.75 |
| Microsoft | July, 02 2012 00:00:00-0700 | 61 | 50.75 | 54.5 | 60.5 |
| Infosys | July, 23 2012 00:00:00-0700 | 54.75 | 44.5 | 48.25 | 54.25 |
| Microsoft | July, 23 2012 00:00:00-0700 | 48.5 | 38.25 | 42 | 48 |[/code]
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
The values are correct.
Here is the complete code on how I'm using it taking your code and modifying it as per my needs.
DECLARE @year_start DATETIME
SELECT @year_start= '03-JAN-2011'
DECLARE @year_end DATETIME
SELECT @year_end= '31-DEC-2011'
;WITH Date_range
AS
(
SELECT @year_start Start_date, DATEADD(s,-1,@year_start+5) End_date
UNION ALL
SELECT Start_date + 7, DATEADD(s,-1,Start_date+7+5)
FROM Date_range
WHERE Start_date + 1 <=@year_end
AND DATEADD(s,-1,Start_date+7+5) <=@year_end
AND DATEPART(dw,Start_date)=2
)
SELECT symbol,Start_date as WeekDate,MAX(shigh) sHigh,
MIN(sLow) sLow,
Min(CASE CONVERT(DATETIME,CONVERT(DATETIME,stimestamp))
WHEN Start_date THEN sOpen END) AS sOpen,
MAX(CASE CONVERT(DATETIME,CONVERT(DATETIME,stimestamp))
WHEN CONVERT(DATETIME,CONVERT(DATETIME,End_date)) THEN sClose END) AS sClose
FROM usrstocks.NseData n,Date_range d
WHERE n.stimestamp BETWEEN d.Start_date AND d.End_date
GROUP BY symbol,Start_date
order by symbol,Start_date
-
Made some changes in your code. Please check now.
DECLARE @year_start DATETIMESELECT @year_start= '03-JAN-2011'
DECLARE @year_end DATETIME
SELECT @year_end= '31-DEC-2011'
;WITH Date_range
AS
(
SELECT @year_start Start_date, DATEADD(s,-1,@year_start+5) End_date
UNION ALL
SELECT Start_date + 7, DATEADD(s,-1,Start_date+7+5)
FROM Date_range
WHERE Start_date + 1 <=@year_end
AND DATEADD(s,-1,Start_date+7+5) <=@year_end
AND DATEPART(dw,Start_date)=2
)
SELECT symbol,Start_date as WeekDate,MAX(shigh) sHigh,
MIN(sLow) sLow,
Min(CASE CONVERT(DATETIME,CONVERT(DATETIME,stimestamp))
WHEN Start_date THEN sOpen END) AS sOpen,
MIN(CASE CONVERT(DATETIME,CONVERT(DATETIME,stimestamp))
WHEN CONVERT(DATETIME,CONVERT(DATE,End_date)) THEN sClose END) AS sClose
FROM NseData n,Date_range d
WHERE n.stimestamp BETWEEN d.Start_date AND d.End_date
GROUP BY symbol,Start_date
order by symbol,Start_date
I am confident, it will work 🙂
- Lokesh
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply