March 17, 2015 at 4:20 pm
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
March 17, 2015 at 4:24 pm
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