Converting Daily data to Weekly data Sql Server 2005
-
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
-
Sorry friend..
I guess My computer is little just too hard to convince..
Check this code. I'm generating Friday date and could you please check sClose is able to get the value on this date?
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,
MAX(CASE CONVERT(DATETIME,CONVERT(DATETIME,stimestamp))
WHEN Start_date THEN sOpen END) AS sOpen,
MAX(CASE CONVERT(DATETIME,CONVERT(DATETIME,stimestamp))
WHEN End_date THEN sClose END) AS sClose,
max(End_date) as FridayDate
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
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply