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
 Analysis Server and Reporting Services (2008)
 SSRS - comparing data over time
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DanielS
Starting Member

Australia
32 Posts

Posted - 06/27/2013 :  22:01:13  Show Profile  Reply with Quote
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)

Singapore
17626 Posts

Posted - 06/27/2013 :  22:45:03  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/28/2013 :  00:42:12  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17626 Posts

Posted - 06/28/2013 :  00:50:31  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/28/2013 :  01:12:19  Show Profile  Reply with Quote
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
Time is always against us




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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17626 Posts

Posted - 06/28/2013 :  01:17:18  Show Profile  Reply with Quote
should be . . . it is continue from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=186436


KH
Time is always against us

Go to Top of Page

DanielS
Starting Member

Australia
32 Posts

Posted - 06/30/2013 :  22:19:31  Show Profile  Reply with Quote
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
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