Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Get first non-null values for all customers

Answered Get first non-null values for all customers

  • Monday, February 04, 2013 2:51 PM
     
     
    Sign In to Vote
    0

    Hi,

    I am using SQL 2008R2, the Adventure Works database and I need to retrieve the first date with non-null Sales Amounts for each customer. The query is the following:

    SELECT
    {
         [Measures].[Sales Amount]
    } ON 0,
    NONEMPTY
    (
         [Due Date].[Date Key].&[20010701]
        :[Due Date].[Date Key].[All].LastChild
        ,{
             [Measures].[Sales Amount]
        }
    ).ITEM(0)
    ON 1
    FROM [Adventure Works]
    WHERE
    (
        [Customer].[Full Name].&[11000]
    )

    While this correctly returns the first date in the specified interval with non-null sales amounts for customer 11000, I would need to achieve this for all customers and this is where I get stuck. So I need to incorporate the Customer dimension in the SELECT statement somehow and remove it from the WHERE clause.

    Any help would be greatly appreciated!

    Thank you!

     

All Replies

  • Monday, February 04, 2013 4:02 PM
     
     Answered Has Code
    Sign In to Vote
    0

    There's a couple of ways to do it and none of them are all that pretty. One way that I've found that seems to be one of the more performant ways is to create a NonEmpty crossjoin of Customers and Dates, use the Rank function to determine the position of each tuple in that set, and create a calculated measure it that returns 1 if it's the first occurrence of the customer member. The resulting query looks like the following:

    WITH SET [Customer and Dates] AS 
	NonEmpty( 
		{ [Customer].[Customer].[Customer].Members }
		* { [Date].[Date].[Date].Members }
		, ( [Measures].[Internet Sales Amount] )
	)
MEMBER [Measures].[Position in Set] AS 
	Rank(
		(
			[Customer].[Customer].CurrentMember,
			[Date].[Date].CurrentMember
		),
		[Customer and Dates]
	)
MEMBER [Measures].[Is First Occurrence] AS
	IIf(
		[Customer].[Customer].CurrentMember IS [Customer and Dates].Item([Measures].[Position in Set]-2).Item(0),
		NULL,
		1
	)
SELECT	{
		[Measures].[Internet Sales Amount],
		[Measures].[Position in Set],
		[Measures].[Is First Occurrence]
	} ON COLUMNS,
	NonEmpty(
		[Customer and Dates],
		[Measures].[Is First Occurrence]
	) ON ROWS
FROM	[Adventure Works]

    Though even with Adventure Works at the Customer and Date grain, the query isn't all that performant.

    HTH, Martin

    http://martinmason.wordpress.com

    • Marked As Answer by AdinaF Friday, February 08, 2013 9:47 AM
    •  
     
  • Monday, February 04, 2013 5:51 PM
     
     Answered
    Sign In to Vote
    0

    Hi,

    while Martin already answered you question here is my bet:

    with member measures.FirstBuy as
    head(
    nonempty(
    [Date].[Date].[Date].Members
    ,([Measures].[Internet Sales Amount])
    )
    ,1).item(0).name

    SELECT {[Measures].[Internet Sales Amount],Measures.FirstBuy} ON 0
      ,[Customer].[Customer].[Customer].Members
      ON 1
    FROM [Adventure Works]

    HTH

    Jörg

    • Marked As Answer by AdinaF Friday, February 08, 2013 9:47 AM
    •  
     
  • Friday, February 08, 2013 9:47 AM
     
     
    Sign In to Vote
    0
    Both your answers solved my problem. Thank you!!