May 5, 2014 at 1:42 am
Hi Friends i have small doubt in mdx query.
table having data like below
table name : patieninformation
pn prcode dos ExpectedPayment
MS0000003PT0011/2/201329.72 0
MS0000003PT0011/21/201357.1 0
MS0000003PT0026/7/201326.69 0
MS0000003PT0026/7/201389.16 0
MS0000003PT0026/6/201357.1 0
MS0000003PT0026/7/201312.28 0
MS0000003PT0026/7/201326.69 26.69
MS0000003PT0026/7/201389.16 77.16
MS0000003PT0026/17/201357.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
MS0000003PT001 1/21/2013 19 86.82 0
MS0000003PT002 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
May 5, 2014 at 10:37 am
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.
May 5, 2014 at 11:07 am
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.
May 5, 2014 at 11:42 am
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.
May 7, 2014 at 1:23 am
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 ExpectedPayment
MS0000003PT0011/2/201329.72 0
MS0000003PT0011/21/201357.1 0
MS0000003PT0026/7/201326.69 0
MS0000003PT0026/7/201389.16 0
MS0000003PT0026/6/201357.1 0
MS0000003PT0026/7/201312.28 0
MS0000003PT0026/7/201326.69 26.69
MS0000003PT0026/7/201389.16 77.16
MS0000003PT0026/17/201357.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
MS0000003PT001 1/21/2013 19 86.82 0
MS0000003PT002 6/17/2013 11 358.18 160.98
Please tell me how to write mdx query based on this cube .
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply