I have a column that needs to display the number of Status hours between
Start_Date and either Stop_Date or Getdate() if StopDate is empty. I am usin
g
an Access project as my front end and SQL Server 2000 as my back end. I have
tried using the following as a row source in my function:
CASE WHEN STATUS_STOP_DATE IS NULL THEN datediff([HH] , STATUS_START_DATE +
STATUS_START_TIME , Getdate()) ELSE datediff([HH] , STATUS_START_DATE +
STATUS_START_TIME , STATUS_STOP_DATE + STATUS_STOP_TIME) END
I get an Invalid use of Getdate() in a function. Ok so I can't use getdate
like that. How can I display the status time on my form? I was thinking mayb
e
the text box record source could be a select statement but not sure how to
write it, any ideas?DateDiff(HH, STATUS_START_DATE + STATUS_START_TIME,
COALESCE(STATUS_STOP_DATE + STATUS_STOP_TIME,
GETDATE())
Roy
On Sat, 4 Mar 2006 14:08:27 -0800, AkAlan
<AkAlan@.discussions.microsoft.com> wrote:
>I have a column that needs to display the number of Status hours between
>Start_Date and either Stop_Date or Getdate() if StopDate is empty. I am usi
ng
>an Access project as my front end and SQL Server 2000 as my back end. I hav
e
>tried using the following as a row source in my function:
>CASE WHEN STATUS_STOP_DATE IS NULL THEN datediff([HH] , STATUS_START_DATE +
>STATUS_START_TIME , Getdate()) ELSE datediff([HH] , STATUS_START_DATE +
>STATUS_START_TIME , STATUS_STOP_DATE + STATUS_STOP_TIME) END
>I get an Invalid use of Getdate() in a function. Ok so I can't use getdate
>like that. How can I display the status time on my form? I was thinking may
be
>the text box record source could be a select statement but not sure how to
>write it, any ideas?|||Hi
CREATE FUNCTION dbo.Get_Getdate
(@.dt DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN @.dt
END
SELECT dbo.Get_Getdate (GETDATE())
SELECT dbo.Get_Getdate ('20050101')
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:30B29109-1B8E-4716-A506-EEDB943F4B64@.microsoft.com...
>I have a column that needs to display the number of Status hours between
> Start_Date and either Stop_Date or Getdate() if StopDate is empty. I am
> using
> an Access project as my front end and SQL Server 2000 as my back end. I
> have
> tried using the following as a row source in my function:
> CASE WHEN STATUS_STOP_DATE IS NULL THEN datediff([HH] , STATUS_START_DATE
> +
> STATUS_START_TIME , Getdate()) ELSE datediff([HH] , STATUS_START_DATE +
> STATUS_START_TIME , STATUS_STOP_DATE + STATUS_STOP_TIME) END
> I get an Invalid use of Getdate() in a function. Ok so I can't use getdate
> like that. How can I display the status time on my form? I was thinking
> maybe
> the text box record source could be a select statement but not sure how
> to
> write it, any ideas?sql
Showing posts with label empty. Show all posts
Showing posts with label empty. Show all posts
Friday, March 23, 2012
Friday, February 24, 2012
Get previous sale for a customer''s sale?
If I have the query:
select non empty [Ship Date].[Date].[Date] on 0
from (select {([Customer].[Customer].&[15566], [Ship Date].[Date].&[1082])} on 0 from [Adventure Works])
where ([Measures].[Internet Extended Amount])
How do I figure w/ mdx that the customer's previous sale was on January 15, 2004? (I don't see .PrevMember working in this case - there is no customer sales date hierarchy estiblished
For more date data, run this one:
select non empty [Ship Date].[Date].[Date] on 0
from [Adventure Works]
where ([Measures].[Internet Extended Amount], [Customer].[Customer].&[15566])
So, here is one (ugly) way to get what your looking for.
Code Snippet
select
[Measures].[Internet Extended Amount] on 0,
-- GET TOP 1 VALUE
HEAD(
-- SORT BASED ON DATE VALUE IN DESCENDING ORDER
ORDER(
FILTER(
-- SET OF SHIP DATES ASSOCIATED WITH THIS CUSTOMER
EXISTS(
[Ship Date].[Date].[Date].Members,
[Customer].[Customer].&[15566],
'Internet Sales'
),
-- LIMIT TO THOSE PRIOR TO THIS FIXED DATE
[Ship Date].[Date].CurrentMember.MemberValue <
[Ship Date].[Date].&[1082].MemberValue
),
[Ship Date].[Date].CurrentMember.MemberValue,
BDESC
),
1)
on 1
from [Adventure Works]
Did I mention this was ugly? :-)
Bryan
|||Thanks Bryan.
I'm also looking to tie it to the current customer/date used on the axis - in order to get an order growth, but I'm not sure how to reference the dates "currentmember" from Exists() compared to the axis "currentmember"
Code Snippet
with member x as
-- GET TOP 1 VALUE
HEAD(
-- SORT BASED ON DATE VALUE IN DESCENDING ORDER
ORDER(
FILTER(
-- SET OF SHIP DATES ASSOCIATED WITH THIS CUSTOMER
EXISTS(
[Ship Date].[Date].[Date].Members,
[Customer].[Customer].CurrentMember,
'Internet Sales'
),
-- LIMIT TO THOSE PRIOR TO THE CURRENT DATE
[Ship Date].[Date].CurrentMember.MemberValue? <
[Ship Date].[Date].CurrentMember.MemberValue?
),
[Ship Date].[Date].CurrentMember.MemberValue?,
BDESC
)
)
member growth as [Measures].[Internet Extended Amount] / x - 1
select
{[Measures].[Internet Extended Amount], x, growth} on 0,
[Ship Date].[Date].[Date] on 1
on 1
from [Adventure Works]
where ([Measures].[Internet Extended Amount], [Customer].[Customer].&[15566])
|||Sorry, but I'm afraid I don't follow what you are trying to do here. Could you provide a little more detail and/or provide a table of expected results?
Thanks,
Bryan
Get previous sale for a customer''s sale?
If I have the query:
select non empty [Ship Date].[Date].[Date] on 0
from (select {([Customer].[Customer].&[15566], [Ship Date].[Date].&[1082])} on 0 from [Adventure Works])
where ([Measures].[Internet Extended Amount])
How do I figure w/ mdx that the customer's previous sale was on January 15, 2004? (I don't see .PrevMember working in this case - there is no customer sales date hierarchy estiblished
For more date data, run this one:
select non empty [Ship Date].[Date].[Date] on 0
from [Adventure Works]
where ([Measures].[Internet Extended Amount], [Customer].[Customer].&[15566])
So, here is one (ugly) way to get what your looking for.
Code Snippet
select
[Measures].[Internet Extended Amount] on 0,
-- GET TOP 1 VALUE
HEAD(
-- SORT BASED ON DATE VALUE IN DESCENDING ORDER
ORDER(
FILTER(
-- SET OF SHIP DATES ASSOCIATED WITH THIS CUSTOMER
EXISTS(
[Ship Date].[Date].[Date].Members,
[Customer].[Customer].&[15566],
'Internet Sales'
),
-- LIMIT TO THOSE PRIOR TO THIS FIXED DATE
[Ship Date].[Date].CurrentMember.MemberValue <
[Ship Date].[Date].&[1082].MemberValue
),
[Ship Date].[Date].CurrentMember.MemberValue,
BDESC
),
1)
on 1
from [Adventure Works]
Did I mention this was ugly? :-)
Bryan
|||Thanks Bryan.
I'm also looking to tie it to the current customer/date used on the axis - in order to get an order growth, but I'm not sure how to reference the dates "currentmember" from Exists() compared to the axis "currentmember"
Code Snippet
with member x as
-- GET TOP 1 VALUE
HEAD(
-- SORT BASED ON DATE VALUE IN DESCENDING ORDER
ORDER(
FILTER(
-- SET OF SHIP DATES ASSOCIATED WITH THIS CUSTOMER
EXISTS(
[Ship Date].[Date].[Date].Members,
[Customer].[Customer].CurrentMember,
'Internet Sales'
),
-- LIMIT TO THOSE PRIOR TO THE CURRENT DATE
[Ship Date].[Date].CurrentMember.MemberValue? <
[Ship Date].[Date].CurrentMember.MemberValue?
),
[Ship Date].[Date].CurrentMember.MemberValue?,
BDESC
)
)
member growth as [Measures].[Internet Extended Amount] / x - 1
select
{[Measures].[Internet Extended Amount], x, growth} on 0,
[Ship Date].[Date].[Date] on 1
on 1
from [Adventure Works]
where ([Measures].[Internet Extended Amount], [Customer].[Customer].&[15566])
|||Sorry, but I'm afraid I don't follow what you are trying to do here. Could you provide a little more detail and/or provide a table of expected results?
Thanks,
Bryan
Get previous sale for a customer''s sale?
If I have the query:
select non empty [Ship Date].[Date].[Date] on 0
from (select {([Customer].[Customer].&[15566], [Ship Date].[Date].&[1082])} on 0 from [Adventure Works])
where ([Measures].[Internet Extended Amount])
How do I figure w/ mdx that the customer's previous sale was on January 15, 2004? (I don't see .PrevMember working in this case - there is no customer sales date hierarchy estiblished
For more date data, run this one:
select non empty [Ship Date].[Date].[Date] on 0
from [Adventure Works]
where ([Measures].[Internet Extended Amount], [Customer].[Customer].&[15566])
So, here is one (ugly) way to get what your looking for.
Code Snippet
select
[Measures].[Internet Extended Amount] on 0,
-- GET TOP 1 VALUE
HEAD(
-- SORT BASED ON DATE VALUE IN DESCENDING ORDER
ORDER(
FILTER(
-- SET OF SHIP DATES ASSOCIATED WITH THIS CUSTOMER
EXISTS(
[Ship Date].[Date].[Date].Members,
[Customer].[Customer].&[15566],
'Internet Sales'
),
-- LIMIT TO THOSE PRIOR TO THIS FIXED DATE
[Ship Date].[Date].CurrentMember.MemberValue <
[Ship Date].[Date].&[1082].MemberValue
),
[Ship Date].[Date].CurrentMember.MemberValue,
BDESC
),
1)
on 1
from [Adventure Works]
Did I mention this was ugly? :-)
Bryan
|||Thanks Bryan.
I'm also looking to tie it to the current customer/date used on the axis - in order to get an order growth, but I'm not sure how to reference the dates "currentmember" from Exists() compared to the axis "currentmember"
Code Snippet
with member x as
-- GET TOP 1 VALUE
HEAD(
-- SORT BASED ON DATE VALUE IN DESCENDING ORDER
ORDER(
FILTER(
-- SET OF SHIP DATES ASSOCIATED WITH THIS CUSTOMER
EXISTS(
[Ship Date].[Date].[Date].Members,
[Customer].[Customer].CurrentMember,
'Internet Sales'
),
-- LIMIT TO THOSE PRIOR TO THE CURRENT DATE
[Ship Date].[Date].CurrentMember.MemberValue? <
[Ship Date].[Date].CurrentMember.MemberValue?
),
[Ship Date].[Date].CurrentMember.MemberValue?,
BDESC
)
)
member growth as [Measures].[Internet Extended Amount] / x - 1
select
{[Measures].[Internet Extended Amount], x, growth} on 0,
[Ship Date].[Date].[Date] on 1
on 1
from [Adventure Works]
where ([Measures].[Internet Extended Amount], [Customer].[Customer].&[15566])
|||Sorry, but I'm afraid I don't follow what you are trying to do here. Could you provide a little more detail and/or provide a table of expected results?
Thanks,
Bryan
Get previous sale for a customer''s sale?
If I have the query:
select non empty [Ship Date].[Date].[Date] on 0
from (select {([Customer].[Customer].&[15566], [Ship Date].[Date].&[1082])} on 0 from [Adventure Works])
where ([Measures].[Internet Extended Amount])
How do I figure w/ mdx that the customer's previous sale was on January 15, 2004? (I don't see .PrevMember working in this case - there is no customer sales date hierarchy estiblished
For more date data, run this one:
select non empty [Ship Date].[Date].[Date] on 0
from [Adventure Works]
where ([Measures].[Internet Extended Amount], [Customer].[Customer].&[15566])
So, here is one (ugly) way to get what your looking for.
Code Snippet
select
[Measures].[Internet Extended Amount] on 0,
-- GET TOP 1 VALUE
HEAD(
-- SORT BASED ON DATE VALUE IN DESCENDING ORDER
ORDER(
FILTER(
-- SET OF SHIP DATES ASSOCIATED WITH THIS CUSTOMER
EXISTS(
[Ship Date].[Date].[Date].Members,
[Customer].[Customer].&[15566],
'Internet Sales'
),
-- LIMIT TO THOSE PRIOR TO THIS FIXED DATE
[Ship Date].[Date].CurrentMember.MemberValue <
[Ship Date].[Date].&[1082].MemberValue
),
[Ship Date].[Date].CurrentMember.MemberValue,
BDESC
),
1)
on 1
from [Adventure Works]
Did I mention this was ugly? :-)
Bryan
|||Thanks Bryan.
I'm also looking to tie it to the current customer/date used on the axis - in order to get an order growth, but I'm not sure how to reference the dates "currentmember" from Exists() compared to the axis "currentmember"
Code Snippet
with member x as
-- GET TOP 1 VALUE
HEAD(
-- SORT BASED ON DATE VALUE IN DESCENDING ORDER
ORDER(
FILTER(
-- SET OF SHIP DATES ASSOCIATED WITH THIS CUSTOMER
EXISTS(
[Ship Date].[Date].[Date].Members,
[Customer].[Customer].CurrentMember,
'Internet Sales'
),
-- LIMIT TO THOSE PRIOR TO THE CURRENT DATE
[Ship Date].[Date].CurrentMember.MemberValue? <
[Ship Date].[Date].CurrentMember.MemberValue?
),
[Ship Date].[Date].CurrentMember.MemberValue?,
BDESC
)
)
member growth as [Measures].[Internet Extended Amount] / x - 1
select
{[Measures].[Internet Extended Amount], x, growth} on 0,
[Ship Date].[Date].[Date] on 1
on 1
from [Adventure Works]
where ([Measures].[Internet Extended Amount], [Customer].[Customer].&[15566])
|||Sorry, but I'm afraid I don't follow what you are trying to do here. Could you provide a little more detail and/or provide a table of expected results?
Thanks,
Bryan
Subscribe to:
Posts (Atom)