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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 compare data over different time periods
 New Topic  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
17681 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
17681 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
17681 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
17681 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
17681 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  
 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