How to SLQ query to Mdx Query

  • I have :

    CREATE TABLE [dbo].[Fact](

    [Id_Fact] [int] IDENTITY(1,1) NOT NULL,

    [Id_Elem] [int] NULL,

    [Id_Store] [int] NULL,

    CONSTRAINT [PK_H_Movimientos] PRIMARY KEY CLUSTERED

    (

    [Id_Mov] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    CREATE TABLE [dbo].[Dim_Store](

    [Id_Store] [int] IDENTITY(1,1) NOT NULL,

    [Name] [varchar](50) NULL,

    CONSTRAINT [PK_Dim_Store] PRIMARY KEY CLUSTERED

    (

    [Id_Store] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    CREATE TABLE [dbo].[Dim_Elem](

    [Id_Elem] [int] IDENTITY(1,1) NOT NULL,

    [Name] [varchar](50) NULL,

    CONSTRAINT [PK_Dim_Elem] PRIMARY KEY CLUSTERED

    (

    [Id_Elem] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    A Fact table an two dimensions,

    the data in dimensions is:

    INSERT INTO [dbo].[Dim_Elem]

    ([Name])

    VALUES

    ('Elem 1'),

    ('Elem 2'),

    ('Elem 3')

    Go

    INSERT INTO [dbo].[Dim_Store]

    ([Name])

    VALUES

    ('AI'),

    ('AII'),

    ('AIII')

    Go

    insert into [dbo].[Fact]([Id_Elem], [Id_Store])

    values

    (1, 2),

    (2, 1),

    (1, NULL),

    (3, 2),

    (3, 3)

    how to make this SQL Query:

    select a.Id_Store, count(distinct a.Id_Elem)

    from Fact a inner join (

    select Id_Elem, max(Id_Fact) as Id_Fact from Fact

    group by Id_Elem) x on

    a.Id_Elem = x.Id_Elem and

    a.Id_Fact = x.Id_Fact and

    a.Id_Store is not null

    group by a.Id_Store

    in MDX Query

    Thanks

  • Please don't post duplicate questions/threads. Rather update the original with more information.

    Original thread is here: http://www.sqlservercentral.com/Forums/Topic1669062-17-1.aspx#bm1669295

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply