Date range | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Date range

Hi,
I have to count the number of invoices between two dates (starting date and ending date).
I have a time dimension, and a another with the two dates.
I’ve created a measure : CREATE MEMBER [CTNAV].[Measures].[Nb]
AS count(
Filter
(
[Bon Vouchers].[N° Bon Vouchers].[N° Bon Vouchers].Members,
[Bon Vouchers].[Starting Date ].CurrentMember.MemberValue >=
[Time].[Date].CurrentMember.MemberValue
)
)
The problem is that if I display all the dates, each measure is at 0 but the total is ok (>0).
Sorry for my english. Thanks for your help.
Can you display or extract for each day? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Thanks for your help.
Actually I#%92ve tried those solutions:
WITH MEMBER [Measures].[Nb2]
AS count(
Filter
(
[Bon Vouchers].[N° Bon Vouchers].[N° Bon Vouchers].Members,
[Bon Vouchers].[Starting Date].CurrentMember.MemberValue >=
[Time].[Date].CurrentMember.MemberValue
)
)
Select
[Measures].[Nb2] On Columns,
[Time].[DateVal].[Months] On Rows
From [CTNAV]
Where [Time].[Years].&[2007-01-01T00:00:00]
The result is : Nb2
janvier 200726
février 200726
mars 200726
avril 200726
mai 200726
… If I replace >= by <= the result is the same. If I do the same test with days:
Select
[Measures].[Nb2] On Columns,
[Time].[DateVal].[Date] On Rows All the results are :
0 with >= [Time].[Date].CurrentMember.MemberValue
26 with <= [Time].[Date].CurrentMember.MemberValue I#%92ve also tried that: WITH MEMBER [Measures].[Nb2]
AS count(
Filter
(
[Bon Vouchers].[N° Bon Vouchers].[N° Bon Vouchers].Members,
[Bon Vouchers].[Starting Date].CurrentMember.MemberValue >=
Tail(Existing [Time].[Date].[Date]).item(0).item(0).MemberValue
)
)
Select
[Measures].[Nb2] On Columns,
[Time].[DateVal].Months On Rows
From [CTNAV]
Where [Time].[Years].&[2007-01-01T00:00:00]
In both cases (months and days), all the results are :
> : 0
< : 26 To finish, for information if I replace the dimension expression by [Bon Vouchers].[ Starting Date].CurrentMember.MemberValue >=
CDATE("2007-03-28") for example, the count is correct Nb2
janvier 20073
février 20073 …

Hi,
Try this:
–build a set of dates for which you need the count
with
set daterange as
[Time].[Day].&[2006-05-18T00:00:00]:[Time].[Day].&[2006-05-20T00:00:00] member measures.countvoucher as
count(nonempty([Bon Vouchers].[N° Bon Vouchers].[N° Bon Vouchers].Members,measures.[Voucheramount])) select daterange on 1,measures.countvoucher on 0 from
[cubename]
Thanks for your help.
Actually I’ve resolved my problem but I’ve another one.
I’ve created my own time table and I’ve declared the type of my start date as a "DateStart" … With Member [Measures].[NbC] As
Count
(
Filter
(
[Bon Vouchers].[Bon Vouchers].Members,
[Bon Vouchers].[Start Date].CurrentMember.MemberValue >=
Tail(Existing [DimDate].[Date].[Date]).item(0).item(0).MemberValue
)
)
Select [Measures].[NbC] On Columns,
[DimDate].[DateHier].[Date] On Rows
From [CTNAV] Now I want to create a named set to filter by dates.
There is no problem if it’s not dynamic :
Filter
(
[Bon Vouchers].[N° Bon Vouchers].[N° Bon Vouchers].Members,
[Bon Vouchers].[Start Date].CurrentMember.MemberValue >=
CDate(‘2006-11-11’)
Tail(Existing [DimDate].[Date].[Date]).item(0).item(0).MemberValue
) But I can’t consider the current date of my Time dimension.
Filter
(
[Bon Vouchers].[N° Bon Vouchers].[N° Bon Vouchers].Members,
[Bon Vouchers].[Start Date].CurrentMember.MemberValue >=
Tail(Existing [DimDate].[Date].[Date]).item(0).item(0).MemberValue
) Is it possible to do this ? Thanks for your help.
]]>