Showing posts with label datediff. Show all posts
Showing posts with label datediff. Show all posts

Tuesday, March 27, 2012

Calculated Measure using Datediff

Hello out there,

i am pretty new to SSAS and have to add a calculated measure that uses the Datediff function from T-SQL.

In detail I want to calculate the average Datediff between a selected Date and the Dates in the DB Table DimUsers.

For example:

UserID = 1 - FirstVisitDate = 05-01-2005
UserID = 2 - FirstVisitDate = 07-05-2005
UserID = 3 - FirstVisitDate = 07-19-2005
.
.
.

And now I want to display the average DateDiff in Days between those Datetimes and a selected Date (let's take 01-07-2006) for showing it in the Cube Browser.

I hope I explained it clear enough and someone please show me a detailled way to do that.
Thanks a lot in advance!
Greets
Claudio

Here's an Adventure Works query modified from an earlier thread - it computes the average difference of the Start Date of a Promotion from a given date (here it's 2005-01-01):

>>

With Member [Measures].[AvgDaysSinceStart] as

Avg(existing [Promotion].[Promotion].[Promotion],

DateDiff("d", [Promotion].[Start Date].MemberValue,

CDate("2005-01-01"))),

FORMAT_STRING = '#,#.00'

select {[Measures].[AvgDaysSinceStart]} on 0,

[Promotion].[Promotion Type].Members on 1

from [Adventure Works]

--

AvgDaysSinceStart
All Promotions 876.13
Discontinued Product 603.50
Excess Inventory 677.00
New Product 550.00
No Discount 1,310.00
Seasonal Discount 656.67
Volume Discount 1,280.00

>>

|||Hello Deepak,

wow thank you for your quick answer again!

It's almost what i need. How can I use a dynmaic value instead of CDate("2005-01-01")?

I use the Server Time Dimension and tried

AVG(existing [Dim User].[Dim User].[Dim User], DateDiff("d", [Dim User].[FIRST VISIT DATE].MemberValue, [Time].[Year - Half Year - Quarter - Month - Date].[Date].MemberValue))

that is not working.
|||

Well, it depends on how the dynamic value is defined. For example, if it should be the last date in the [Date] dimension:

With Member [Measures].[AvgDaysSinceStart] as

Avg(existing [Promotion].[Promotion].[Promotion],

DateDiff("d", [Promotion].[Start Date].MemberValue,

Tail([Date].[Date].[Date]).Item(0).MemberValue)),

FORMAT_STRING = '#,#.00'

select {[Measures].[AvgDaysSinceStart]} on 0,

[Promotion].[Promotion Type].Members on 1

from [Adventure Works]

|||Thats closer to what i am looking for. But how can I just use it full dynamic? I want to go to the Cube Browser and select a Date in the <select dimension> on top together with the calculated measure in the data area.

I guess (but i'm not sure) it's a similar approach as it is used in the Calculate measure "Growth in Customer Base" from Adventure Works

Case

When [Date].[Fiscal].CurrentMember.Level.Ordinal = 0
Then "NA"

When IsEmpty
(
(
[Date].[Fiscal].CurrentMember.PrevMember,
[Measures].[Customer Count]
)
)
Then Null

Else (
( [Date].[Fiscal].CurrentMember, [Measures].[Customer Count] )
-
( [Date].[Fiscal].PrevMember, [Measures].[Customer Count] )
)
/
( [Date].[Fiscal].PrevMember,[Measures].[Customer Count] )

End

... is it?|||

With Member [Measures].[AvgDaysSinceStart] as

Avg(existing [Promotion].[Promotion].[Promotion],

DateDiff("d", [Promotion].[Start Date].MemberValue,

[Date].[Date].MemberValue)),

FORMAT_STRING = '#,#.00'

select {[Measures].[AvgDaysSinceStart]} on 0,

[Promotion].[Promotion Type].Members on 1

from [Adventure Works]

where [Date].[Date].[July 1, 2004]

|||Thanks a lot Deepak! That works perfect!
What should I do without all your help? You are great!

Greets
Claudio

Monday, March 19, 2012

calculate date difference exclusding weekend

I want to calculate the day difference between 2 dates excluding wend.
I notice that DateDiff function, but it does not support option to excluding
wend.
Any information is great appreciated,
Souris,http://www.google.co.uk/groups?selm...br />
oups.com
David Portas
SQL Server MVP
--|||Look up the use of a calendar table for this kind of problem. This
way, you will get all the holidays.|||http://www.aspfaq.com/2519
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"souris" <soukkris@.viddotron.com> wrote in message
news:e2w1dwKLFHA.2136@.TK2MSFTNGP14.phx.gbl...
> I want to calculate the day difference between 2 dates excluding wend.
> I notice that DateDiff function, but it does not support option to
excluding
> wend.
> Any information is great appreciated,
> Souris,
>
>|||Thanks for the information,
I got it works.
It can calculate the date difference between 2 days in the table.
Is it possible to calculate the date difference in other table?
For example:
I have a table
Account_Number Opened_Date
1 03/10/2005
2 03/15/2005
Today is 03/30/2005.
I wanted calculate the days difference between today and Opened_date
excluding wend.
I tried using the table, but not lucky.
Any information is great appreciated,
Souris,
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1111255234.753472.313190@.l41g2000cwc.googlegroups.com...
> http://www.google.co.uk/groups?selm... />
groups.com
> --
> David Portas
> SQL Server MVP
> --
>|||On Wed, 30 Mar 2005 18:46:12 -0500, souris wrote:

>Thanks for the information,
>I got it works.
>It can calculate the date difference between 2 days in the table.
>Is it possible to calculate the date difference in other table?
>For example:
>I have a table
>Account_Number Opened_Date
> 1 03/10/2005
> 2 03/15/2005
>Today is 03/30/2005.
>I wanted calculate the days difference between today and Opened_date
>excluding wend.
>I tried using the table, but not lucky.
>Any information is great appreciated,
Hi Souris,
Try if this works:
SELECT a.Account_Number, a.Opened_Date.
(SELECT COUNT(*)
FROM Calendar AS c
WHERE c.caldate >= a.Opened_Date
AND c.caldate < CURRENT_TIMESTAMP + 1)
FROM YourTable AS a
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Calculate 30/360, 30/365 DateDiff

Has anyone come across the need for performing a day difference count betwee
n
2 dates adhering to a 30/360 day convention? Is there a System Function,
Custom Function or Proc which can achieve this ?If I remember correctly that's some sort of financial/bank calculation? If
you could refresh me on the specifics of how it's done by hand I might be
able to find something for you...
"D1p4k" <dipakb@.exchange.ml.com> wrote in message
news:6DE347B3-CBEB-4F23-AC94-DF5E7B9B9484@.microsoft.com...
> Has anyone come across the need for performing a day difference count
> between
> 2 dates adhering to a 30/360 day convention? Is there a System Function,
> Custom Function or Proc which can achieve this ?|||On Tue, 26 Jul 2005 12:30:02 -0700, D1p4k wrote:

> Has anyone come across the need for performing a day difference count betw
een
> 2 dates adhering to a 30/360 day convention? Is there a System Function,
> Custom Function or Proc which can achieve this ?
See a usenet thread from Feb 2004 on this topic: http://tinyurl.com/bwglz|||30/360 = The number of accrued days is calculated on the basis of a year of
360 days and a month of 30 days. However, if the first date of an accrual
period is not the 30th or 31st, and the last date of the period is the 31st
of the month then that month is considered to have 31 days. In addition, if
the last date of the period is the last day of February, the month of
February shall not be extended to a 30-day month.
Date1= 25-oct-1996 and Date2 = 31-dec-1996 the result would be 66 days.
An Actual DateDiff would give you 67 Days|||> Date1= 25-oct-1996 and Date2 = 31-dec-1996 the result would be 66 days.
6+30+31=67
"D1p4k" <dipakb@.exchange.ml.com> wrote in message
news:5FCA9DFC-15C4-452B-AA1E-9384C55F7756@.microsoft.com...
> 30/360 = The number of accrued days is calculated on the basis of a year
> of
> 360 days and a month of 30 days. However, if the first date of an accrual
> period is not the 30th or 31st, and the last date of the period is the
> 31st
> of the month then that month is considered to have 31 days. In addition,
> if
> the last date of the period is the last day of February, the month of
> February shall not be extended to a 30-day month.
> Date1= 25-oct-1996 and Date2 = 31-dec-1996 the result would be 66 days.
> An Actual DateDiff would give you 67 Days
>|||On Tue, 26 Jul 2005 13:30:03 -0700, D1p4k wrote:

> 30/360 = The number of accrued days is calculated on the basis of a year o
f
> 360 days and a month of 30 days. However, if the first date of an accrual
> period is not the 30th or 31st, and the last date of the period is the 31s
t
> of the month then that month is considered to have 31 days. In addition, if[/color
]
^^^^^^^^^^^^^
It's not clear what you mean by "that month": do you mean that the month of
the start of the period is considered to have 31, or that the month of the
end of the period is considered to have 31?
> the last date of the period is the last day of February, the month of
> February shall not be extended to a 30-day month.
> Date1= 25-oct-1996 and Date2 = 31-dec-1996 the result would be 66 days.
> An Actual DateDiff would give you 67 Days
Tell me if this works for you.
CREATE FUNCTION dbo.DayCount30_360Days(@.d1 DATETIME, @.d2 DATETIME)
RETURNS INT
AS
BEGIN
RETURN(
SELECT DATEDIFF(MONTH, @.d1, @.d2) * 30
+ DAY(@.d2)
- DAY(@.d1)
- case when day(@.d1)>=30 AND day(@.d2) = 31 then 1 else 0 end
)
END
GO
Select dbo.DayCount30_360Days('1996-10-25','1996-12-31')
-- result: 66
Select dbo.DayCount30_360Days('1996-10-26','1996-12-31')
-- result: 65
Select dbo.DayCount30_360Days('1996-10-27','1996-12-31')
-- result: 64
Select dbo.DayCount30_360Days('1996-10-28','1996-12-31')
-- result: 63
Select dbo.DayCount30_360Days('1996-10-29','1996-12-31')
-- result: 62
Select dbo.Daycount30_360Days('1996-10-30','1996-12-31')
-- result: 60