How to get data for First Working Day Of The Week

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

How to get data for First Working Day Of The Week

Author

Topic

sateeshsangani
Starting Member

3 Posts

Posted - 2014-07-29 : 03:28:45

Hi Team,

Here i am providing reference site.I need to get the data same as it is
https://in.finance.yahoo.com/q/hp?s=AXISBANK.NS&a=05&b=01&c=2014&d=06&e=01&f=2014&g=w

here i am passing "from" and "to" dates to query based on this i have to get the only monday's data from the database in given dates.

Please help me, how to write sql query to solve this issue

awating for you kindly response
Thanks in advance

Thanks & Regards
sateesh

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-07-29 : 08:28:49

See the example below. If you have a Numbers table (Tally table) in your database, use that instead of the spt_values table.

DECLARE @start DATE = '20140101';
DECLARE @end DATE = '20140731';

SELECT
	DATEADD(dd, (number + DATEDIFF(dd,0,@start)/7) *7,0)
FROM master..spt_values
WHERE
	type = 'P'
	AND DATEADD(dd, (number + DATEDIFF(dd,0,@start)/7) *7,0)
		BETWEEN @start AND @end
ORDER BY 1

sateeshsangani
Starting Member

3 Posts

Posted - 2014-07-30 : 01:09:12

Thank you so much james, for your response.
I have tried your query, but its getting only the single date column,

select [Date],[symbol],High,Low from tblCompanyHistoricalPrices where Symbol = 'BAC' and Date between '2014-06-01' and '2014-06-15'

using the above query,it will get complete data from the given table with given date ranges.And its a Daily wise data.
output:
sno symbol date high low .
1 BAC 2014-06-01 1000 1500
2 BAC 2014-06-02 1000 1200
3 BAC 2014-06-03 1000 1300
4 BAC 2014-06-04 1000 2000
5 BAC 2014-06-05 1000 1500
6 BAC 2014-06-06 1000 1200
7 BAC 2014-06-07 1000 1300
8 BAC 2014-06-08 1000 2000
9 BAC 2014-06-09 1000 1500
10 BAC 2014-06-10 1000 1200
11 BAC 2014-06-11 1000 1300
12 BAC 2014-06-12 1000 2000
13 BAC 2014-06-13 1000 1500
14 BAC 2014-06-14 1000 1200
15 BAC 2014-06-15 1000 1300

In the above output its displays 2 weeks data by a given date ranges.
from this i need to get weekly wise data means only "monday" i.e(2014-06-02,2014-06-09) to be display

awating for you kindly response
Thanks in advance

Thanks & Regards
sateesh

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-07-30 : 09:40:21

Add another where clause using the query I posted above

select [Date],[symbol],High,Low from tblCompanyHistoricalPrices where Symbol = 'BAC' and Date between '2014-06-01' and '2014-06-15' 
AND [Date] IN
(
	SELECT
		DATEADD(dd, (number + DATEDIFF(dd,0,'2014-06-01')/7) *7,0)
	FROM master..spt_values
	WHERE
		type = 'P'
		AND DATEADD(dd, (number + DATEDIFF(dd,0,'2014-06-01')/7) *7,0)
			BETWEEN '2014-06-01' AND '2014-06-15' 
)

sateeshsangani
Starting Member

3 Posts

Posted - 2014-07-31 : 08:57:23

Thanks alot james i have solved my problem,thank you so much.

Thanks & Regards
sateesh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources