Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

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

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 compare data over different time periods
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DanielS
Starting Member

Australia
32 Posts

Posted - 06/26/2013 :  22:06:24  Show Profile  Reply with Quote
Hi there,

I am looking to get a few pieces of data and make a comparison.
I have a FROM_DATE and a PRICE field in a table called HOLDINGS.
I want to get the PRICE for last week and compare to today's PRICE, and I want to get the PRICE for the end of last month and compare to today's PRICE.

So, I have
SELECT DATEADD(day,-7,max(FROM_DATE)) FROM HOLDINGS
as last weeks date, and
SELECT DATEADD(day,-1,DATEADD(month, DATEDIFF(month,0,max(FROM_DATE)),0)) FROM HOLDINGS
as last months date.

What code do I need to get results which look something along the lines of;

FROM_DATE, PRICE, FROM_DATE_WEEK, PRICE_WEEK, PRICE_CHANGE_WEEK, FROM_DATE_MONTH, PRICE_MONTH, PRICE_CHANGE_MONTH

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 06/26/2013 :  22:14:11  Show Profile  Reply with Quote
How does your data looks like ?

You have a PRICE record for everyday ?


KH
Time is always against us

Go to Top of Page

DanielS
Starting Member

Australia
32 Posts

Posted - 06/26/2013 :  23:07:16  Show Profile  Reply with Quote
Yes, data is daily.

quote:
Originally posted by khtan

How does your data looks like ?

You have a PRICE record for everyday ?


KH
Time is always against us



Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 06/26/2013 :  23:12:52  Show Profile  Reply with Quote
so you are comparing TODAY with LAST WEEK THIS DAY and END OF LAST MONTH ?

can you post a sample of your data from the HOLDINGS table ?


KH
Time is always against us

Go to Top of Page

DanielS
Starting Member

Australia
32 Posts

Posted - 06/26/2013 :  23:18:25  Show Profile  Reply with Quote
Here is a cut of the data:

FROM_DATE F_ISIN PRICE
30/05/2013 AU3FN0014916 104.349998
31/05/2013 AU3FN0014916 104.348
1/06/2013 AU3FN0014916 104.348
2/06/2013 AU3FN0014916 104.348
3/06/2013 AU3FN0014916 104.346001
4/06/2013 AU3FN0014916 104.304001
5/06/2013 AU3FN0014916 104.228996
6/06/2013 AU3FN0014916 104.163002
7/06/2013 AU3FN0014916 104.198997
8/06/2013 AU3FN0014916 104.198997
9/06/2013 AU3FN0014916 104.198997
10/06/2013 AU3FN0014916 104.198997
11/06/2013 AU3FN0014916 104.181
12/06/2013 AU3FN0014916 104.174004
13/06/2013 AU3FN0014916 103.959999
14/06/2013 AU3FN0014916 103.852997
15/06/2013 AU3FN0014916 103.852997
16/06/2013 AU3FN0014916 103.852997
17/06/2013 AU3FN0014916 103.777
18/06/2013 AU3FN0014916 103.738998
19/06/2013 AU3FN0014916 103.772003
20/06/2013 AU3FN0014916 103.724998
21/06/2013 AU3FN0014916 103.574997
22/06/2013 AU3FN0014916 103.574997
23/06/2013 AU3FN0014916 103.574997
24/06/2013 AU3FN0014916 103.453003
25/06/2013 AU3FN0014916 103.456001
26/06/2013 AU3FN0014916 103.656998

quote:
Originally posted by khtan

so you are comparing TODAY with LAST WEEK THIS DAY and END OF LAST MONTH ?

can you post a sample of your data from the HOLDINGS table ?


KH
Time is always against us



Go to Top of Page

DanielS
Starting Member

Australia
32 Posts

Posted - 06/26/2013 :  23:25:04  Show Profile  Reply with Quote
And yes, compare today to last week and end of last month.
So compare 26/7 price to 19/7 amd 26/7 to 31/5.

quote:
Originally posted by DanielS

Here is a cut of the data:

FROM_DATE F_ISIN PRICE
30/05/2013 AU3FN0014916 104.349998
31/05/2013 AU3FN0014916 104.348
1/06/2013 AU3FN0014916 104.348
2/06/2013 AU3FN0014916 104.348
3/06/2013 AU3FN0014916 104.346001
4/06/2013 AU3FN0014916 104.304001
5/06/2013 AU3FN0014916 104.228996
6/06/2013 AU3FN0014916 104.163002
7/06/2013 AU3FN0014916 104.198997
8/06/2013 AU3FN0014916 104.198997
9/06/2013 AU3FN0014916 104.198997
10/06/2013 AU3FN0014916 104.198997
11/06/2013 AU3FN0014916 104.181
12/06/2013 AU3FN0014916 104.174004
13/06/2013 AU3FN0014916 103.959999
14/06/2013 AU3FN0014916 103.852997
15/06/2013 AU3FN0014916 103.852997
16/06/2013 AU3FN0014916 103.852997
17/06/2013 AU3FN0014916 103.777
18/06/2013 AU3FN0014916 103.738998
19/06/2013 AU3FN0014916 103.772003
20/06/2013 AU3FN0014916 103.724998
21/06/2013 AU3FN0014916 103.574997
22/06/2013 AU3FN0014916 103.574997
23/06/2013 AU3FN0014916 103.574997
24/06/2013 AU3FN0014916 103.453003
25/06/2013 AU3FN0014916 103.456001
26/06/2013 AU3FN0014916 103.656998

quote:
Originally posted by khtan

so you are comparing TODAY with LAST WEEK THIS DAY and END OF LAST MONTH ?

can you post a sample of your data from the HOLDINGS table ?


KH
Time is always against us





Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 06/26/2013 :  23:32:24  Show Profile  Reply with Quote

declare	@today			date,
	@last_week_today	date,
	@end_of_last_mth	date

select	@today			= getdate(),
	@last_week_today	= dateadd(week, -1, getdate()),
	@end_of_last_mth	= dateadd(month, datediff(month, 0, getdate()), -1)

select	h.F_ISIN,
	FROM_DATE	= max(case when FROM_DATE = @today then FROM_DATE end),
	PRICE		= max(case when FROM_DATE = @today then PRICE end),
	FROM_DATE_WEEK	= max(case when FROM_DATE = @last_week_today then FROM_DATE end),
	PRICE_WEEK	= max(case when FROM_DATE = @last_week_today then PRICE end),
	FROM_DATE_MONTH	= max(case when FROM_DATE = @end_of_last_mth then FROM_DATE end),
	PRICE_MONTH	= max(case when FROM_DATE = @end_of_last_mth then PRICE end)
from	HOLDINGS h
where	h.FROM_DATE	in (@today, @last_week_today, @end_of_last_mth)
group by h.F_ISIN



KH
Time is always against us

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 06/26/2013 :  23:35:57  Show Profile  Reply with Quote
if today's data is not available yet, change to yesterday

select	@today			= getdate() - 1,
	@last_week_today	= dateadd(week, -1, getdate() - 1),
	@end_of_last_mth	= dateadd(month, datediff(month, 0, getdate() - 1), -1)



KH
Time is always against us

Go to Top of Page

DanielS
Starting Member

Australia
32 Posts

Posted - 06/27/2013 :  00:08:02  Show Profile  Reply with Quote
Thanks, this looks good.

Now what if I wanted to 'paramaterize' the FROM_DATE, ie I will be putting this code into SSRS and then building a report with FROM_DATE as the parameter. So the user should be able to select any date in time, eg 13/7/12 and get the prices for 6/7/12 (week prior) and 30/6/12 (prior month end).


quote:
Originally posted by khtan

if today's data is not available yet, change to yesterday

select	@today			= getdate() - 1,
	@last_week_today	= dateadd(week, -1, getdate() - 1),
	@end_of_last_mth	= dateadd(month, datediff(month, 0, getdate() - 1), -1)



KH
Time is always against us



Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 06/27/2013 :  00:12:09  Show Profile  Reply with Quote
i am not familiar with SSRS, but you can change the following, the @last_week_today & @end_of_last_mth will be based on the @today to calculate the date
select	@today			= getdate() - 1

select  @last_week_today	= dateadd(week, -1, @today - 1),
	@end_of_last_mth	= dateadd(month, datediff(month, 0, @today - 1), -1)



KH
Time is always against us

Go to Top of Page
  Previous Topic Topic Next 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.09 seconds. Powered By: Snitz Forums 2000