Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

group by clasue in mdx Expand / Collapse
Author
Message
Posted Monday, May 5, 2014 1:42 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 12, 2014 2:11 AM
Points: 211, Visits: 4,540
Hi Friends i have small doubt in mdx query.
table having data like below
table name : patieninformation


pn prcode dos Expected Payment
MS0000003 PT001 1/2/2013 29.72 0
MS0000003 PT001 1/21/2013 57.1 0
MS0000003 PT002 6/7/2013 26.69 0
MS0000003 PT002 6/7/2013 89.16 0
MS0000003 PT002 6/6/2013 57.1 0
MS0000003 PT002 6/7/2013 12.28 0
MS0000003 PT002 6/7/2013 26.69 26.69
MS0000003 PT002 6/7/2013 89.16 77.16
MS0000003 PT002 6/17/2013 57.1 57.1

Based on this table data i need to output like below




pn prcode maxdos list of servicedays(max of dos-min of dos) Expected Payment
MS0000003 PT001 1/21/2013 19 86.82 0
MS0000003 PT002 6/17/2013 11 358.18 160.98



maxdos means highest date based on prcode wise
list of service days : highet date from dos-least date from dos based on prcode wise.


i tried in sql query
select pn,prcode ,max(dos) as maxdos, max(dos)-min(dos) as list of servicedays frOM [patientinformation] group by pn,prcode

same way how to implement in mdx query.please tell me how to write mdx query to solve this issue
Post #1567428
Posted Monday, May 5, 2014 10:37 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Yesterday @ 7:18 AM
Points: 1,836, Visits: 3,544
asranantha,

MDX is used against OLAP sources (ie 'cubes'). You've provided no details of your cube at all, only a DB table.

Have you built your cube yet? If so, maybe if you outline the fact and dimension tables (ie the source) and then anything you may have done with the creation of the dims and measure groups, including what your measures are, *then* you might have more luck getting a response form someone who simply writes the mdx for you.




Steve.
Post #1567616
Posted Monday, May 5, 2014 11:07 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 5:53 PM
Points: 143, Visits: 316
Is this what you are looking for? I didn't find any cube related information in your question, so I did this in straight SQL..
DECLARE @Input TABLE
(
pn VARCHAR(10),
prcode VARCHAR(10),
dos DATE,
Expected DECIMAL(10,2),
Payment DECIMAL(10,2)
)

INSERT INTO @Input VALUES('MS0000003', 'PT001', '1/2/2013', 29.72, 0), ('MS0000003', 'PT001', '1/21/2013', 57.1, 0)
,('MS0000003', 'PT002', '6/7/2013', 26.69, 0),
('MS0000003', 'PT002', '6/7/2013', 89.16, 0),
('MS0000003', 'PT002', '6/6/2013', 57.1, 0),
('MS0000003', 'PT002', '6/7/2013', 12.28, 0),
('MS0000003', 'PT002', '6/7/2013', 26.69, 26.69),
('MS0000003', 'PT002', '6/7/2013', 89.16, 77.16),
('MS0000003', 'PT002', '6/17/2013', 57.1, 57.1)

;WITH CTE AS
(
SELECT pn, prcode, MAX(dos) AS Max_dos, MIN(dos) AS min_dos, SUM(expected) as expected, SUM(payment) AS payment
FROM @Input
GROUP BY pn, prcode
)
SELECT a.pn, a.prcode, Max_dos, DATEDIFF(d, min_dos, max_dos) AS differnce, a.Expected, a.Payment
FROM CTE a



Good Luck :) .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
Post #1567632
Posted Monday, May 5, 2014 11:42 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Yesterday @ 7:18 AM
Points: 1,836, Visits: 3,544
a4apple - i think he had provided his own tsql, but is looking for an 'equivalent' in MDX. But as stated previously, he's given no details on the dims/measures that he has implemented (assuming he has implemented them) so is asking us how long is that piece of string


Steve.
Post #1567646
Posted Wednesday, May 7, 2014 1:23 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 12, 2014 2:11 AM
Points: 211, Visits: 4,540
Hi all, applogy to given some wrong information. Here Cube Name :Testcube....> its having measures expected,payment.
dimensions: dimpatient: its having attribute is pn
Dimprcode: its having attribute is prcode
Dimdate: its having attribute is dos
based on this attributes and measures for one pn having sample data like below
pn prcode dos Expected Payment
MS0000003 PT001 1/2/2013 29.72 0
MS0000003 PT001 1/21/2013 57.1 0
MS0000003 PT002 6/7/2013 26.69 0
MS0000003 PT002 6/7/2013 89.16 0
MS0000003 PT002 6/6/2013 57.1 0
MS0000003 PT002 6/7/2013 12.28 0
MS0000003 PT002 6/7/2013 26.69 26.69
MS0000003 PT002 6/7/2013 89.16 77.16
MS0000003 PT002 6/17/2013 57.1 57.1
Based on this data i required output like below
pn prcode maxdos list of servicedays(max of dos-min of dos) Expected Payment
MS0000003 PT001 1/21/2013 19 86.82 0
MS0000003 PT002 6/17/2013 11 358.18 160.98

Please tell me how to write mdx query based on this cube .



Post #1568305
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse