CurrentMember and a set

I have an excel sheet that people are using with pivot tables. The users want to be able to select a set of dates, and get a current year measure, along with the corresponding last year measures on a day to day comparison (not day of year, or date vs. date, but a "monday this year vs. monday last year", which is 364 days ago). They want to be able to pick and choose which days go into the set, for instance they want 1/1/cy, 2/1/cy, 2/5/cy and the sum of a measure vs. 1/2/ly, 2/2/ly, and 2/6/ly same measure summed up.

I can do this for a single selection (using ([Calendar].[Date].CurrentMember.Lag.(364), [Measure]), but I don't know how to access a list of items in the calcluated measure when they select a set.

If I could write this in MDX it would be much easier, but they would like to have control over it and do it through Excel (who can blame them?).

Does anyone have any suggestions?

thank you in advance,

John Hennesey

Assuming that you're using AS 2005, which version of Excel is involved (2003 issues different multi-select MDX queries than 2007)?|||

Sorry I left that detail out. I am using Excel 2003.

Thanks in advance,

John

|||

Hi John,

With Excel 2003, you should be able to use the "Existing" approach, discussed in Mosha's blog:

http://sqljunkies.com/WebLog/mosha/archive/2005/11/18/multiselect_friendly_mdx.aspx

>>

Writing multiselect friendly MDX calculations

...

But obviously we wanted AS2005 to work well with existing client tools too. Therefore, AS's query engine recognizes the shape of the queries where there is query calculated member doing Aggregate over constant single grain set, and this calculated member (or members if there are multiple multiselects in different hierarchies) is in the WHERE clause. And when AS detects this situation, it replaces the calculated member in the WHERE clause with the corresponding set.

...

>>

So, in your scenario, the calculated measure MDX expression could be like:

Aggregate(Generate(Existing [Calendar].[Date].[Date], {[Calendar].[Date].Lag(364)}), [Measure])

|||

Awesome - thank you very much for the response. I will give it a shot. One question - when it iterates through the currently selected set, how will it know which ones are selected? Should the first [Calendar].[Date].[Date] be [Calendar].[Date].CurrentMember? If this is a dumb question, please forgive me - I'm still fairly new to MDX.

Once again, thanks!

John

|||

"when it iterates through the currently selected set, how will it know which ones are selected?" - Mosha's blog entry above explains this better than I could.

"Should the first [Calendar].[Date].[Date] be [Calendar].[Date].CurrentMember" - no, this is the [Date] level of the [Calendar].[Date] hierarchy, and is shorthand for [Calendar].[Date].[Date].Members, which you can use instead. This all applies if you're using AS 2005, of course.

|||Cool - I will dig into Mosha's blog. Thank you for your quick response!

CurrentDate within a report?

Hi all, im having difficulty using dates within my report. Any advice
welcome.
Basically I want to restrict my query by date, however, I want my start and
enddate parameters to have universal generic values.
I.e.
startdate = CurrentDate - 31
enddate = CurrentDate
I cannot figure out how todo this within RS 2005, I have attempted to use
some of the .NET datetime functions but I cannot get them to work (i.e.
"Today() - 31" etc)
What should I be doing?
Kind regards
TazTry the following expressions:
=Today() -- for StartDate
=DateAdd(DateInterval.Day, -31, Today()) -- for EndDate
HTH
--
Magendo_man
Freelance SQL Reporting Services developer
Stirling, Scotland
"Tarun Mistry" wrote:
> Hi all, im having difficulty using dates within my report. Any advice
> welcome.
> Basically I want to restrict my query by date, however, I want my start and
> enddate parameters to have universal generic values.
> I.e.
> startdate = CurrentDate - 31
> enddate = CurrentDate
> I cannot figure out how todo this within RS 2005, I have attempted to use
> some of the .NET datetime functions but I cannot get them to work (i.e.
> "Today() - 31" etc)
> What should I be doing?
> Kind regards
> Taz
>
>|||Many thanks for the reply, this has helped alot.
However, a new problem has surfaced. There is an inconsistency between the
date format in my data fields and the format within the database. I need to
force an American format, how can I do this?
Alternatively, is there a way to convert UK dates into American dates?
Currently, each time I run my report, within the parameter window, the
values are being alternated (which is VERY strange).
I.e.
12/01/2006 -> Press "View Report"
01/12/2006 -> Press "View Report"
12/01/2006 -> Press "View Report"
etc etc
Thanks for your help
Taz
"magendo_man" <sql@.kappa.co.uk.(donotspam)> wrote in message
news:BFD2C7D1-0EF2-45F2-81AE-57504A839891@.microsoft.com...
> Try the following expressions:
> =Today() -- for StartDate
> =DateAdd(DateInterval.Day, -31, Today()) -- for EndDate
> HTH
> --
> Magendo_man
> Freelance SQL Reporting Services developer
> Stirling, Scotland
>
> "Tarun Mistry" wrote:
>> Hi all, im having difficulty using dates within my report. Any advice
>> welcome.
>> Basically I want to restrict my query by date, however, I want my start
>> and
>> enddate parameters to have universal generic values.
>> I.e.
>> startdate = CurrentDate - 31
>> enddate = CurrentDate
>> I cannot figure out how todo this within RS 2005, I have attempted to use
>> some of the .NET datetime functions but I cannot get them to work (i.e.
>> "Today() - 31" etc)
>> What should I be doing?
>> Kind regards
>> Taz
>>|||did you put that date formula into an expression or did you put it in the
data tab.
"Tarun Mistry" <nospam@.nospam.com> wrote in message
news:ezuOx5n0GHA.1568@.TK2MSFTNGP03.phx.gbl...
> Many thanks for the reply, this has helped alot.
> However, a new problem has surfaced. There is an inconsistency between the
> date format in my data fields and the format within the database. I need
> to force an American format, how can I do this?
> Alternatively, is there a way to convert UK dates into American dates?
> Currently, each time I run my report, within the parameter window, the
> values are being alternated (which is VERY strange).
> I.e.
> 12/01/2006 -> Press "View Report"
> 01/12/2006 -> Press "View Report"
> 12/01/2006 -> Press "View Report"
> etc etc
> Thanks for your help
> Taz
> "magendo_man" <sql@.kappa.co.uk.(donotspam)> wrote in message
> news:BFD2C7D1-0EF2-45F2-81AE-57504A839891@.microsoft.com...
>> Try the following expressions:
>> =Today() -- for StartDate
>> =DateAdd(DateInterval.Day, -31, Today()) -- for EndDate
>> HTH
>> --
>> Magendo_man
>> Freelance SQL Reporting Services developer
>> Stirling, Scotland
>>
>> "Tarun Mistry" wrote:
>> Hi all, im having difficulty using dates within my report. Any advice
>> welcome.
>> Basically I want to restrict my query by date, however, I want my start
>> and
>> enddate parameters to have universal generic values.
>> I.e.
>> startdate = CurrentDate - 31
>> enddate = CurrentDate
>> I cannot figure out how todo this within RS 2005, I have attempted to
>> use
>> some of the .NET datetime functions but I cannot get them to work (i.e.
>> "Today() - 31" etc)
>> What should I be doing?
>> Kind regards
>> Taz
>>
>|||I have put those formulas into the Default values within the "Report
Parameters" setup.
Taz
"Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
news:eE5m$8n0GHA.1288@.TK2MSFTNGP03.phx.gbl...
> did you put that date formula into an expression or did you put it in the
> data tab.
> "Tarun Mistry" <nospam@.nospam.com> wrote in message
> news:ezuOx5n0GHA.1568@.TK2MSFTNGP03.phx.gbl...
>> Many thanks for the reply, this has helped alot.
>> However, a new problem has surfaced. There is an inconsistency between
>> the date format in my data fields and the format within the database. I
>> need to force an American format, how can I do this?
>> Alternatively, is there a way to convert UK dates into American dates?
>> Currently, each time I run my report, within the parameter window, the
>> values are being alternated (which is VERY strange).
>> I.e.
>> 12/01/2006 -> Press "View Report"
>> 01/12/2006 -> Press "View Report"
>> 12/01/2006 -> Press "View Report"
>> etc etc
>> Thanks for your help
>> Taz
>> "magendo_man" <sql@.kappa.co.uk.(donotspam)> wrote in message
>> news:BFD2C7D1-0EF2-45F2-81AE-57504A839891@.microsoft.com...
>> Try the following expressions:
>> =Today() -- for StartDate
>> =DateAdd(DateInterval.Day, -31, Today()) -- for EndDate
>> HTH
>> --
>> Magendo_man
>> Freelance SQL Reporting Services developer
>> Stirling, Scotland
>>
>> "Tarun Mistry" wrote:
>> Hi all, im having difficulty using dates within my report. Any advice
>> welcome.
>> Basically I want to restrict my query by date, however, I want my start
>> and
>> enddate parameters to have universal generic values.
>> I.e.
>> startdate = CurrentDate - 31
>> enddate = CurrentDate
>> I cannot figure out how todo this within RS 2005, I have attempted to
>> use
>> some of the .NET datetime functions but I cannot get them to work (i.e.
>> "Today() - 31" etc)
>> What should I be doing?
>> Kind regards
>> Taz
>>
>>
>|||try this: =switch(Fields!YOURFIELD.Value = "US","M/d/yy")
"Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
news:eE5m$8n0GHA.1288@.TK2MSFTNGP03.phx.gbl...
> did you put that date formula into an expression or did you put it in the
> data tab.
> "Tarun Mistry" <nospam@.nospam.com> wrote in message
> news:ezuOx5n0GHA.1568@.TK2MSFTNGP03.phx.gbl...
>> Many thanks for the reply, this has helped alot.
>> However, a new problem has surfaced. There is an inconsistency between
>> the date format in my data fields and the format within the database. I
>> need to force an American format, how can I do this?
>> Alternatively, is there a way to convert UK dates into American dates?
>> Currently, each time I run my report, within the parameter window, the
>> values are being alternated (which is VERY strange).
>> I.e.
>> 12/01/2006 -> Press "View Report"
>> 01/12/2006 -> Press "View Report"
>> 12/01/2006 -> Press "View Report"
>> etc etc
>> Thanks for your help
>> Taz
>> "magendo_man" <sql@.kappa.co.uk.(donotspam)> wrote in message
>> news:BFD2C7D1-0EF2-45F2-81AE-57504A839891@.microsoft.com...
>> Try the following expressions:
>> =Today() -- for StartDate
>> =DateAdd(DateInterval.Day, -31, Today()) -- for EndDate
>> HTH
>> --
>> Magendo_man
>> Freelance SQL Reporting Services developer
>> Stirling, Scotland
>>
>> "Tarun Mistry" wrote:
>> Hi all, im having difficulty using dates within my report. Any advice
>> welcome.
>> Basically I want to restrict my query by date, however, I want my start
>> and
>> enddate parameters to have universal generic values.
>> I.e.
>> startdate = CurrentDate - 31
>> enddate = CurrentDate
>> I cannot figure out how todo this within RS 2005, I have attempted to
>> use
>> some of the .NET datetime functions but I cannot get them to work (i.e.
>> "Today() - 31" etc)
>> What should I be doing?
>> Kind regards
>> Taz
>>
>>
>|||I'm not sure where you need to force an American date format.
If you are referencing columns in your SQL Server database which have a
datatype of DATETIME then you shouldn't have a problem. If you are running
RS2000 and haven't applied Service Pack two you can get inconsistencies in
presentation of dates when entering report parameters - apply the service
pack.
Also, you can force a UK format in your reports by using the format property
of a given textbox, making it something like dd-MM-yy.
If none of these pointers helps you then please clarify what the exact
problem is with date formats.
--
Magendo_man
Freelance SQL Reporting Services developer
Stirling, Scotland
"Tarun Mistry" wrote:
> Many thanks for the reply, this has helped alot.
> However, a new problem has surfaced. There is an inconsistency between the
> date format in my data fields and the format within the database. I need to
> force an American format, how can I do this?
> Alternatively, is there a way to convert UK dates into American dates?
> Currently, each time I run my report, within the parameter window, the
> values are being alternated (which is VERY strange).
> I.e.
> 12/01/2006 -> Press "View Report"
> 01/12/2006 -> Press "View Report"
> 12/01/2006 -> Press "View Report"
> etc etc
> Thanks for your help
> Taz
> "magendo_man" <sql@.kappa.co.uk.(donotspam)> wrote in message
> news:BFD2C7D1-0EF2-45F2-81AE-57504A839891@.microsoft.com...
> > Try the following expressions:
> >
> > =Today() -- for StartDate
> >
> > =DateAdd(DateInterval.Day, -31, Today()) -- for EndDate
> >
> > HTH
> >
> > --
> > Magendo_man
> >
> > Freelance SQL Reporting Services developer
> > Stirling, Scotland
> >
> >
> > "Tarun Mistry" wrote:
> >
> >> Hi all, im having difficulty using dates within my report. Any advice
> >> welcome.
> >>
> >> Basically I want to restrict my query by date, however, I want my start
> >> and
> >> enddate parameters to have universal generic values.
> >>
> >> I.e.
> >>
> >> startdate = CurrentDate - 31
> >> enddate = CurrentDate
> >>
> >> I cannot figure out how todo this within RS 2005, I have attempted to use
> >> some of the .NET datetime functions but I cannot get them to work (i.e.
> >> "Today() - 31" etc)
> >>
> >> What should I be doing?
> >>
> >> Kind regards
> >> Taz
> >>
> >>
> >>
>
>|||and if my last post doesnt help then try changing up your formula some.
=dateadd("d",-31,today). leave off the () and use "d" instead of
dateinterval.day. I have never had my dates go crazy like that.
"Tarun Mistry" <nospam@.nospam.com> wrote in message
news:eE1DXHo0GHA.4972@.TK2MSFTNGP03.phx.gbl...
>I have put those formulas into the Default values within the "Report
>Parameters" setup.
> Taz
> "Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
> news:eE5m$8n0GHA.1288@.TK2MSFTNGP03.phx.gbl...
>> did you put that date formula into an expression or did you put it in the
>> data tab.
>> "Tarun Mistry" <nospam@.nospam.com> wrote in message
>> news:ezuOx5n0GHA.1568@.TK2MSFTNGP03.phx.gbl...
>> Many thanks for the reply, this has helped alot.
>> However, a new problem has surfaced. There is an inconsistency between
>> the date format in my data fields and the format within the database. I
>> need to force an American format, how can I do this?
>> Alternatively, is there a way to convert UK dates into American dates?
>> Currently, each time I run my report, within the parameter window, the
>> values are being alternated (which is VERY strange).
>> I.e.
>> 12/01/2006 -> Press "View Report"
>> 01/12/2006 -> Press "View Report"
>> 12/01/2006 -> Press "View Report"
>> etc etc
>> Thanks for your help
>> Taz
>> "magendo_man" <sql@.kappa.co.uk.(donotspam)> wrote in message
>> news:BFD2C7D1-0EF2-45F2-81AE-57504A839891@.microsoft.com...
>> Try the following expressions:
>> =Today() -- for StartDate
>> =DateAdd(DateInterval.Day, -31, Today()) -- for EndDate
>> HTH
>> --
>> Magendo_man
>> Freelance SQL Reporting Services developer
>> Stirling, Scotland
>>
>> "Tarun Mistry" wrote:
>> Hi all, im having difficulty using dates within my report. Any advice
>> welcome.
>> Basically I want to restrict my query by date, however, I want my
>> start and
>> enddate parameters to have universal generic values.
>> I.e.
>> startdate = CurrentDate - 31
>> enddate = CurrentDate
>> I cannot figure out how todo this within RS 2005, I have attempted to
>> use
>> some of the .NET datetime functions but I cannot get them to work
>> (i.e.
>> "Today() - 31" etc)
>> What should I be doing?
>> Kind regards
>> Taz
>>
>>
>>
>|||Guys,
I really don't understand what was going on. The "flipping" date issue was
only happening within RS and not in the webservice. It seems to be fine now.
Thanks for all the help and the magic formulaes!!
Taz
"Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
news:OdnS4Ko0GHA.3752@.TK2MSFTNGP02.phx.gbl...
> and if my last post doesnt help then try changing up your formula some.
> =dateadd("d",-31,today). leave off the () and use "d" instead of
> dateinterval.day. I have never had my dates go crazy like that.
> "Tarun Mistry" <nospam@.nospam.com> wrote in message
> news:eE1DXHo0GHA.4972@.TK2MSFTNGP03.phx.gbl...
>>I have put those formulas into the Default values within the "Report
>>Parameters" setup.
>> Taz
>> "Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
>> news:eE5m$8n0GHA.1288@.TK2MSFTNGP03.phx.gbl...
>> did you put that date formula into an expression or did you put it in
>> the data tab.
>> "Tarun Mistry" <nospam@.nospam.com> wrote in message
>> news:ezuOx5n0GHA.1568@.TK2MSFTNGP03.phx.gbl...
>> Many thanks for the reply, this has helped alot.
>> However, a new problem has surfaced. There is an inconsistency between
>> the date format in my data fields and the format within the database. I
>> need to force an American format, how can I do this?
>> Alternatively, is there a way to convert UK dates into American dates?
>> Currently, each time I run my report, within the parameter window, the
>> values are being alternated (which is VERY strange).
>> I.e.
>> 12/01/2006 -> Press "View Report"
>> 01/12/2006 -> Press "View Report"
>> 12/01/2006 -> Press "View Report"
>> etc etc
>> Thanks for your help
>> Taz
>> "magendo_man" <sql@.kappa.co.uk.(donotspam)> wrote in message
>> news:BFD2C7D1-0EF2-45F2-81AE-57504A839891@.microsoft.com...
>> Try the following expressions:
>> =Today() -- for StartDate
>> =DateAdd(DateInterval.Day, -31, Today()) -- for EndDate
>> HTH
>> --
>> Magendo_man
>> Freelance SQL Reporting Services developer
>> Stirling, Scotland
>>
>> "Tarun Mistry" wrote:
>> Hi all, im having difficulty using dates within my report. Any advice
>> welcome.
>> Basically I want to restrict my query by date, however, I want my
>> start and
>> enddate parameters to have universal generic values.
>> I.e.
>> startdate = CurrentDate - 31
>> enddate = CurrentDate
>> I cannot figure out how todo this within RS 2005, I have attempted to
>> use
>> some of the .NET datetime functions but I cannot get them to work
>> (i.e.
>> "Today() - 31" etc)
>> What should I be doing?
>> Kind regards
>> Taz
>>
>>
>>
>>
>|||It seems i posted in success a little too early,
My default parameter is correctly appearing in UK format, however when I
pass it to another report, it is being sent in american format, or being
interpretted in american format. The chart that sends the data is set to UK,
as is the main report.
what could be afoot here? furter, my reports may be deployed on a number of
different systems, will this behaviour happen differently on different
servers depending on its language setting'
Thanks
Taz|||Tarun Mistry wrote:
> It seems i posted in success a little too early,
> My default parameter is correctly appearing in UK format, however when I
> pass it to another report, it is being sent in american format, or being
> interpretted in american format. The chart that sends the data is set to UK,
> as is the main report.
> what could be afoot here? furter, my reports may be deployed on a number of
> different systems, will this behaviour happen differently on different
> servers depending on its language setting'
> Thanks
> Taz
If you are running SQL 2005 make sure you have installed servicepack 1.
The thing you are describing looks a bit like this post.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=255248&SiteID=1&PageID=1|||> If you are running SQL 2005 make sure you have installed servicepack 1.
> The thing you are describing looks a bit like this post.
> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=255248&SiteID=1&PageID=1
>
Many thanks Peter, it does indeed look like this is the problem.
I googled the issue but couldn't find anything, i really appreciate your
help.
Downloading SP1 now, fingers crossed.
Kind regards
Taz

Current month minus current day

Beginners question:

I am using a calendar table joined to my data table so that it will give me all the dates regardless wether there is data for that day or not. Now, I want it to give me all the data from the current month minus the current day. Right now I am using (MONTH(c.dt) = MONTH(GETDATE())) AND (YEAR(c.dt) = YEAR(GETDATE())) but that also gives me data for the current day which I do not want.

Any way I can make this happen?

Thanks

Try:

(MONTH(c.dt) = MONTH(GETDATE())) AND (YEAR(c.dt) = YEAR(GETDATE()) AND (DAY(c.dt) != DAY(GETDATE()))

Chris

|||

Thank you Chris.

That will indeed not give me the current day but it also does not show me the current day with a null value. I still need to see the current day just not with any data in it. I dont even know if it's possible.

|||

I am still not sure if I get the aim; is this way off?

declare @.calendar table
( dt datetime
)
insert into @.calendar
select convert(datetime, '1/31/7') + number
from master.dbo.spt_values (nolock)
where name is null
and number <= 29

declare @.dataTable table
( dataDt datetime,
dataValue integer
)

insert into @.dataTable
select convert(datetime, '2/1/7 7:00') + number,
number
from master.dbo.spt_values (nolock)
where name is null
and number <= 27

select convert (varchar(11), c.dt, 102) as [Date],
d.dataValue
from @.calendar c
left join @.dataTable d
on d.dataDt >= c.dt
and d.dataDt < c.dt + 1
and d.dataDt < convert (datetime, convert(varchar(10), getdate(), 101))
where c.dt >= convert(datetime,left(convert(varchar(10),getdate(),112),6)+'01')
and c.dt < dateadd(mm,1, convert(datetime,left(convert(varchar(10),getdate(),112),6)+'01'))

Date dataValue
-- --
2007.02.01 0
2007.02.02 1
2007.02.03 2
...
2007.02.20 19
2007.02.21 20
2007.02.22 NULL
2007.02.23 NULL
...

This select does not enclose the datetime columns within a function and therefore may be able to utilize a datetime index (if one is available). Enclosing c.dt within a function will inhibit to use any index that might be available on this column.

|||

OK, slight change of plan - you can do this if you include the DAY condition as part of a LEFT JOIN rather than in the WHERE clause.

SELECT c.dt,

ot.SomeOtherData

FROM CalendarTable c

LEFT JOIN SomeOtherTable ot ON c.dt = ot.dt AND (DAY(c.dt) != DAY(GETDATE()))

WHERE (MONTH(c.dt) = MONTH(GETDATE())) AND (YEAR(c.dt) = YEAR(GETDATE()))

or alternatively if your dates include times other than 00:00:00.000 :

SELECT c.dt,

ot.SomeOtherData

FROM CalendarTable c

LEFT JOIN SomeOtherTable ot ON CONVERT(VARCHAR(11), c.dt, 102) = CONVERT(VARCHAR(11), ot.dt, 102) AND (DAY(c.dt) != DAY(GETDATE()))

WHERE (MONTH(c.dt) = MONTH(GETDATE())) AND (YEAR(c.dt) = YEAR(GETDATE()))

I'm taking a bit of a guess as to how you're joining your tables, but hopefully this should be closer to what you need.

Chris

|||Awesome. Thank you very much Chris.

Current month minus current day

Beginners question:

I am using a calendar table joined to my data table so that it will give me all the dates regardless wether there is data for that day or not. Now, I want it to give me all the data from the current month minus the current day. Right now I am using (MONTH(c.dt) = MONTH(GETDATE())) AND (YEAR(c.dt) = YEAR(GETDATE())) but that also gives me data for the current day which I do not want.

Any way I can make this happen?

Thanks

Try:

(MONTH(c.dt) = MONTH(GETDATE())) AND (YEAR(c.dt) = YEAR(GETDATE()) AND (DAY(c.dt) != DAY(GETDATE()))

Chris

|||

Thank you Chris.

That will indeed not give me the current day but it also does not show me the current day with a null value. I still need to see the current day just not with any data in it. I dont even know if it's possible.

|||

I am still not sure if I get the aim; is this way off?

declare @.calendar table
( dt datetime
)
insert into @.calendar
select convert(datetime, '1/31/7') + number
from master.dbo.spt_values (nolock)
where name is null
and number <= 29

declare @.dataTable table
( dataDt datetime,
dataValue integer
)

insert into @.dataTable
select convert(datetime, '2/1/7 7:00') + number,
number
from master.dbo.spt_values (nolock)
where name is null
and number <= 27

select convert (varchar(11), c.dt, 102) as [Date],
d.dataValue
from @.calendar c
left join @.dataTable d
on d.dataDt >= c.dt
and d.dataDt < c.dt + 1
and d.dataDt < convert (datetime, convert(varchar(10), getdate(), 101))
where c.dt >= convert(datetime,left(convert(varchar(10),getdate(),112),6)+'01')
and c.dt < dateadd(mm,1, convert(datetime,left(convert(varchar(10),getdate(),112),6)+'01'))

Date dataValue
-- --
2007.02.01 0
2007.02.02 1
2007.02.03 2
...
2007.02.20 19
2007.02.21 20
2007.02.22 NULL
2007.02.23 NULL
...

This select does not enclose the datetime columns within a function and therefore may be able to utilize a datetime index (if one is available). Enclosing c.dt within a function will inhibit to use any index that might be available on this column.

|||

OK, slight change of plan - you can do this if you include the DAY condition as part of a LEFT JOIN rather than in the WHERE clause.

SELECT c.dt,

ot.SomeOtherData

FROM CalendarTable c

LEFT JOIN SomeOtherTable ot ON c.dt = ot.dt AND (DAY(c.dt) != DAY(GETDATE()))

WHERE (MONTH(c.dt) = MONTH(GETDATE())) AND (YEAR(c.dt) = YEAR(GETDATE()))

or alternatively if your dates include times other than 00:00:00.000 :

SELECT c.dt,

ot.SomeOtherData

FROM CalendarTable c

LEFT JOIN SomeOtherTable ot ON CONVERT(VARCHAR(11), c.dt, 102) = CONVERT(VARCHAR(11), ot.dt, 102) AND (DAY(c.dt) != DAY(GETDATE()))

WHERE (MONTH(c.dt) = MONTH(GETDATE())) AND (YEAR(c.dt) = YEAR(GETDATE()))

I'm taking a bit of a guess as to how you're joining your tables, but hopefully this should be closer to what you need.

Chris

|||Awesome. Thank you very much Chris.

Current month date query

Hi,

I have a simple table called events, which lists the start and end dates of events. I'm using a calendar control that queries the db for events, but at the moment it does a check for every day by passing in the day. (Very inefficient)

What I'd like to do is pass in the current month, and get a set of rows that have an event which is in that month. It sounds easy, but I'm a little confused about what to do if the event starts current month -1 and ends currentmonth +1 I obviously need to return results like these also.

Do you just need something like this:

SELECT *FROM EventTableWHERE EventStartDate <= @.EndOfMonthOR EventEndDate >= @.StartOfMonth

You'll need to pass in the Start and End of Month values, of course. HTH

|||

I assume that you would like to return all events which started or finished in selected month. Maybe you can use Query like this if year is not important.

DECLARE @.tcMonth int

SET @.tcMonth=10 -- events for October

Select *

from Events

where MONTH(StartDate)=@.tcMonth

OR MONTH(EndDate)=@.tcMonth

Thanks

JPazgier

|||

That's great.

I changed the Or to an And though. :-)

|||

If you change OR to AND in this query you will receive only events which started and ended in selected month, with OR you should receive also events which were started earlier and finished in selected month and events which were started in selected month and finished after it.

Thanks

JPazgier

Cubes Migration problem

Hello,
I have 3 cubes with 3 shared dimensions (dates, ids, factors) in SQL Server 2000 environment. After the cubes migration to SSAS 2005
using Analysis Services Migration Wizard I got individual dimensions for
each cube : dates, dates1, dates2, ids,ids1, ids2, factors, factors1,
factors2. This makes the problem for my cubes MDX queries and based on it applications.
The question is how to preserve shared dimensions during cubes migration? And another qustion: is it possible to have in the database dimensions with the same name

(like dates and dates)?

Thanks,
GB

Migration is not going to create a best possible stucture for your cube. It operates in generic manner trying to cover all possible cube designs.

After you've migrated you need to go over your cube design and improve it. You should try to re-structure your cubes so you have a single version of database dimension ( you probably dont neet dates1, dates2, ids1, ids2 ...).

You should also take a look if possible to create a single cube with several measure groups. Looks like this might be the case. If you have multiple dimensions shared between several cubes, chances you can model the situation by creating multiple measure groups.

Take a look at sample Adventure Works project, see the stucture of Adventure Works cube. This should give you clues for having better cube design.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Cubes Migration problem

Hello,
I have 3 cubes with 3 shared dimensions (dates, ids, factors) in SQL Server 2000 environment. After the cubes migration to SSAS 2005
using Analysis Services Migration Wizard I got individual dimensions for
each cube : dates, dates1, dates2, ids,ids1, ids2, factors, factors1,
factors2. This makes the problem for my cubes MDX queries and based on it applications.
The question is how to preserve shared dimensions during cubes migration? And another qustion: is it possible to have in the database dimensions with the same name

(like dates and dates)?

Thanks,
GB

Migration is not going to create a best possible stucture for your cube. It operates in generic manner trying to cover all possible cube designs.

After you've migrated you need to go over your cube design and improve it. You should try to re-structure your cubes so you have a single version of database dimension ( you probably dont neet dates1, dates2, ids1, ids2 ...).

You should also take a look if possible to create a single cube with several measure groups. Looks like this might be the case. If you have multiple dimensions shared between several cubes, chances you can model the situation by creating multiple measure groups.

Take a look at sample Adventure Works project, see the stucture of Adventure Works cube. This should give you clues for having better cube design.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

CTP Expiration Dates

Is there a document that lists the expiration dates of the CTP releases? We've a test box still on CTP15 and I was wondering when the CTP will stop working.
There is no paper that lists them all but I think in 15's case it will be soon, its almost a year since it was released.|||The agreed licence statement in the setup tells you that the CTP will loose it licence when the version is pubslihed, so you should either consider to remove or upgrade your current system.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
-