date condition in where clause

Question

Hello ,

I need help with using a date field in where clause .

 

I need to get count where the date is yesterday , i dont have the date in dimension table but i need to calculate it on fly

Please help me with that

Answer 1

Are you needing it for your fact table in the dsv?

Select count(1) from [table] where Date = convert(datetime,convert(char(10),getdate(),101))

?

Or, if you're talking cube measures:  When you want a count of yesterday - yesterday's load?  yesterday's Orders?  yesterday's Sales?

Answer 2

hello,

sorry for not being clear.

i have a dimension table called date . i am trying to get sales amount for yesterday . the last member of the dimension table would be yesterday .

and i need anothe way also like dynamically find out the yesterday day. Please suggest .

thanks

 

Answer 3

And this is in SSAS so i need a mdx query

Answer 4

Hi,

If you need to dynamically construct yesterday date use VBA functions to get the key for yesterday member, assemble the members unique key and then use StrToMember() function.

For example if yesterday's attribute key is 20110205:

StrToMember("[Date].[Date].&[" + VBA!Format(Now()-1, "yyyyMMdd") + "]")

 

If the last member of the dimension table is yesterday use

Tail([Date].[Date].[Date].MEMBERS ,1).Item(0)

 

HTH,

Hrvoje Piasevoli

Answer 5

Thank You so much . I used the last one and it worked fine.

Thank You so much for your help

Answer 6

Hello ,

I need to use this in where condition like pick all inquiry counts for yesterday  and i have a date dimension. So how do i use it in where condition.

select

from

where

date.date.x.(yesterday)

 

can you please suggest something..

 

 

Answer 7

Hi,

if you need the last day member (you said it was yesterday) use this syntax:

select
Measures.DefaultMember on 0 
from [YourCube]
where
Tail([Date].[Calendar].[Date])

If you need to construct it dynamically use:

select
Measures.DefaultMember on 0 
from [YourCube]
where
StrToMember('[Date].[Calendar].[Date].' + VBA!Format(Now()-1, '&[yyyyMMdd]'))

HTH,

Hrvoje Piasevoli