DATE RANGE ISSUE

Hi All,
I have below table structure:
ItemID Price FromDate
ToDate
-----
1 10.00 01/01/2005
12/31/2005
1 9.00 01/01/2004
12/31/2004
2 2.00 01/01/1900
01/01/9999
-----
Now I need to write a query to get price for all the items for the date
range 01/01/2005 to 12/31/2005
If I use FromDate >= 01/01/2005 and ToDate <= 01/01/2005 then I will get
only one record (ItemID=1). But ItemID =2 also lies in the same daterange,
ie, daterange specified lies between 1900 and 9999. But the query will not
give that result.
Please let me know how to get this record also.
Thanks in advance"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:628A35F2-9F1E-4E5A-B095-E5A45470CC84@.microsoft.com...
> Hi All,
> I have below table structure:
> ItemID Price FromDate
> ToDate
> -----
> 1 10.00 01/01/2005
> 12/31/2005
> 1 9.00 01/01/2004
> 12/31/2004
> 2 2.00 01/01/1900
> 01/01/9999
> -----
> Now I need to write a query to get price for all the items for the date
> range 01/01/2005 to 12/31/2005
>
> If I use FromDate >= 01/01/2005 and ToDate <= 01/01/2005 then I will get
> only one record (ItemID=1). But ItemID =2 also lies in the same daterange,
> ie, daterange specified lies between 1900 and 9999. But the query will not
> give that result.
>
The only records your query should return are ones with a date of
01/01/2005. I'm not sure how it's returning ItemID 1
ItemID=2 does not lay within that same daterange.
It's fromdate 01/01/1900 is less than 01/01/2005 and it's Todate, is greater
than 01/01/2005.
With the AND, you're asking for stuff BETWEEN 01/01/2005 and 01/01/2005.
ItemID=2 exceeds this range on both ends.
I suspect you want OR?
> Please let me know how to get this record also.
> Thanks in advance
>
--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||On Mar 14, 9:49 am, Ram <R...@.discussions.microsoft.com> wrote:
> Hi All,
> I have below table structure:
> ItemID Price FromDate = > ToDate
> ----=--=AD--
> 1 10.00 01/01/2005 = > 12/31/2005
> 1 9.00 01/01/2004 = > 12/31/2004
> 2 2.00 01/01/1900 = > 01/01/9999
> ----=--=AD--
> Now I need to write a query to get price for all the items for the date
> range 01/01/2005 to 12/31/2005
> If I use FromDate >=3D 01/01/2005 and ToDate <=3D 01/01/2005 then I will =get
> only one record (ItemID=3D1). But ItemID =3D2 also lies in the same dater=ange,
> ie, daterange specified lies between 1900 and 9999. But the query will not
> give that result.
> Please let me know how to get this record also.
> Thanks in advance
I think you are storing dummy values in fromdate and todate column
when it is unknown (NULL)
If that is the case
(FromDate >=3D 01/01/2005 and ToDate < '01/01/2006' )
OR
FromDate >=3D 01/01/1900 and ToDate <=3D '01/01/9999' )
Note: < 01/01/2006 instead of <=3D 12/31/2005' to take care of time
portion
M A Srinivas

Date Range for Report built against cube using MDX query

Hi,

I am trying to filter data within my report by a date range (FromDate - ToDate), which is using a cube as a datasource.

My Issue:

I have the filtering working ok but if i select a date which is outside the range of the data within my cube for example if i select the starting date for the range as 1/Jan/1965 but by data starts from 15/Jan/1965 then no data is returned.

Within the MDX query within the STRTOSET function i am using 'constrained' which is around the date parameter i.e. StartDate for Range.

My question is has anyone or is it possible to use date values outside of the range of the data within my cube and get a correct dataset returned. If so could you please explain how with an example.

Thanks

MDX sets do not really work like this, they are made up of sets of discrete members and while they are ordered and you can get ranges of members, the idea of a between predicate does not really map well. I usually try to constrain my report parameter so that users cannot pick values outside of the valid member range.

If you can't do this you could probably use the filter function, provided that you had the actual date value stored somewhere (the MemberValue property is ideal for this). Which would make your range statement something like this:

filter( <date dim>.<hierarchy>.<date level>.Members

, <date dim>.<hierarchy>.MemberValue >= <fromDate>

and <date dim>.<hierarchy>.MemberValue <= <toDate>))

But this is going to be a lot slower than directly specifying a start and end member directly.|||

Hi,

Thanks for your reply. I managed to solve this issue by creating a time dimension with all the possible date combinations for the next five years and previous year which could occur in my data. Performance was an issue that is why this solution implemented.

|||Yeah, that's what I do aswell. And 5 years is only 1500 members which is not big as dimensions go.

Date Range for Report built against a cube using MDX query

Hi,

I am trying to filter data within my report by a date range (FromDate - ToDate), which is using a cube as a datasource.

My Issue:

I have the filtering working ok but if i select a date which is outside the range of the data within my cube for example if i select the starting date for the range as 1/Jan/1965 but by data starts from 15/Jan/1965 then no data is returned.

Within the MDX query within the STRTOSET function i am using 'constrained' which is around the date parameter i.e. StartDate for Range.

My question is has anyone or is it possible to use date values outside of the range of the data within my cube and get a correct dataset returned. If so could you please explain how with an example.

Many Thanks

Absolutely it is possible. Have you tried removing the CONSTRAINED flag? Also do a search of this forum, i have posted several items regarding using dates and MDX within reports.

|||

Hi,

Thanks for your reply. I managed to solve this issue by creating a time dimension with all the possible date combinations for the next five years and previous year which could occur in my data. Performance was an issue that is why this solution implemented.

Date parameter

hi and thanx in advance
in a report, i am getting data from a stored proc which take to date parameter fromdate and todate which are passed from report.
user select dates in datepicker control
but problem is that it not returning any record
Can problem be in date format difference in report and sql server ?

If date format difference is true, it will give error at some point. So, the problem could be only with your stored procedure. Try executing the stored procedure from the management studio with the same parameter data and see if you get any records.

Shyam