SSRS - comparing data over time

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

Analysis Server and Reporting Services (2008)

SSRS - comparing data over time

Author

Topic

DanielS
Starting Member

32 Posts

Posted - 2013-06-27 : 22:01:13

Hello. I have the following piece of code which gets price data across a few different points in time. It works perfectly in SQL Mgt Studio. I am having trouble figuring out how to 'parameterize' the date. So rather than @current = getdate()-1, if I use @current = @inputDate and then in the where clause use and FROM_DATE = @inputDate I don't get the results I'm expecting. How can I parameterize the FROM_DATE?

declare @current date,
@prior_week date,
@prior_eomth date

select @current = getdate()-1,
@prior_week = dateadd(week, -1, @current),
@prior_eomth = dateadd(month, datediff(month, 0, @current), -1)

select h.F_ISIN,
FROM_DATE = max(case when FROM_DATE = @current then FROM_DATE end),
PRICE = max(case when FROM_DATE = @current then CLEAN_PRICE end),
FROM_DATE_WEEK = max(case when FROM_DATE = @prior_week then FROM_DATE end),
PRICE_WEEK = max(case when FROM_DATE = @prior_week then CLEAN_PRICE end),
FROM_DATE_MONTH = max(case when FROM_DATE = @prior_eomth then FROM_DATE end),
PRICE_MONTH = max(case when FROM_DATE = @prior_eomth then CLEAN_PRICE end)
from HOLDINGS h

where h.FROM_DATE in (@current, @prior_week, @prior_eomth)
group by h.F_ISIN

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-06-27 : 22:45:03

can't help you with SSRS area as i am not familiar with that, but i can help you with the Query portion . ..

What is the data type of @inputDate ? Does it contains the time component ?

try
assigning @current = @inputDate
and use @current in your where clause instead of @inputDate

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-28 : 00:42:12

the problem is @current,@prior_week etc is based on getdate() which will also have time part. so comparison using IN will retrieve the data only if fields timepart coincides with the current timepart.

i think what you need is this


declare @current date,
@prior_week date,
@prior_eomth date

select @current = dateadd(dd,datediff(dd,0,getdate()),-1),
@prior_week = dateadd(week, -1, @current),
@prior_eomth = dateadd(month, -1, @current)

select h.F_ISIN,
FROM_DATE = max(FROM_DATE),
PRICE = max(CLEAN_PRICE),
FROM_DATE_WEEK = max(FROM_DATE_WEEK),
PRICE_WEEK = max(PRICE_WEEK),
FROM_DATE_MONTH = max(FROM_DATE_MONTH),
PRICE_MONTH = max(PRICE_MONTH)
from HOLDINGS h
CROSS APPLY (SELECT FROM_DATE_WEEK = max(FROM_DATE),
             PRICE_WEEK = max(FROM_DATE)
             FROM HOLDINGS
             WHERE F_ISIN = h.F_ISIN
             AND FROM_DATE >=@prior_week
             AND FROM_DATE < @current
            )wk
CROSS APPLY (SELECT FROM_DATE_WEEK = max(FROM_DATE),
             PRICE_WEEK = max(CLEAN_PRICE)
             FROM HOLDINGS
             WHERE F_ISIN = h.F_ISIN
             AND FROM_DATE >=@prior_month
             AND FROM_DATE < DATEADD(mm,DATEDIFF(mm,0,@current),0)
            )mn
where h.FROM_DATE >=@current
AND h.FROM_DATE < @current
group by h.F_ISIN

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-06-28 : 00:50:31

quote:
Originally posted by visakh16

the problem is @current,@prior_week etc is based on getdate() which will also have time part.

Not really, the data type for those variable is date not datetime

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-28 : 01:12:19

quote:
Originally posted by khtan

quote:
Originally posted by visakh16

the problem is @current,@prior_week etc is based on getdate() which will also have time part.

Not really, the data type for those variable is date not datetime

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

ah...missed that
thought it was datetime
also h.FROM_DATE in (@current, @prior_week, @prior_eomth)
will only retrieve the data for three days which i'm not sure is what OP is after

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-06-28 : 01:17:18

should be . . . it is continue from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=186436

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

DanielS
Starting Member

32 Posts

Posted - 2013-06-30 : 22:19:31

Hi, sorry for lack of reply over the weekend.
Yes, 3 day's worth of data is what I'm after.
Assigning @current = @inputDate in the select statement AND in the where clause seems to have done the trick.
Thanks all.

quote:
Originally posted by khtan

should be . . . it is continue from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=186436

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources