compare data over different time periods

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

SQL Server 2008 Forums

Transact-SQL (2008)

compare data over different time periods

Author

Topic

DanielS
Starting Member

32 Posts

Posted - 2013-06-26 : 22:06:24

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)

17689 Posts

Posted - 2013-06-26 : 22:14:11

How does your data looks like ?

You have a PRICE record for everyday ?

KH
[spoiler]Time is always against us[/spoiler]

DanielS
Starting Member

32 Posts

Posted - 2013-06-26 : 23:07:16

Yes, data is daily.

quote:
Originally posted by khtan

How does your data looks like ?

You have a PRICE record for everyday ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-06-26 : 23:12:52

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
[spoiler]Time is always against us[/spoiler]

DanielS
Starting Member

32 Posts

Posted - 2013-06-26 : 23:18:25

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
[spoiler]Time is always against us[/spoiler]

DanielS
Starting Member

32 Posts

Posted - 2013-06-26 : 23:25:04

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
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-06-26 : 23:32:24

[code]
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[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-06-26 : 23:35:57

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
[spoiler]Time is always against us[/spoiler]

DanielS
Starting Member

32 Posts

Posted - 2013-06-27 : 00:08:02

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
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-06-27 : 00:12:09

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
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources