How to get table records position in comparison to other records based on numeric field?

Hi,

Let's say I have 1000 registered users in database table and each of them has numeric ranking value.

How can I get the position of each user in comparison to other users ranking value?

If you're using SQL Server 2005, you can use the row_number() function. Are you?

Don

|||Hi,|||

Hi,

Yes, I am. Can you post some example code?

|||maybe you can post your table DDL, some sample data and the result that you want ?|||

Hi,

Solution found! Here's the code:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString%>"

SelectCommand="SELECT [usernickname], ROW_NUMBER() OVER(ORDER BY userrankavg DESC) AS 'position' FROM [dbusers]"
</asp:SqlDataSource>

|||

Hi,

That doesn't actually solve my problem.

The code does display position for each record when all records are selected from table, but doesn't when WHERE attribute is specified.

How can I get position for a specific record that I specify with WHERE attribute?

How to get months

I need to get all the data based on the user selection of the months. So, I will provide a dropdown will all list of the months. When user pick the month, for example January, then I will show all the invoice on January. How to get the month of the year in the where clause in T-SQL?

TIA

...WHERE MONTH(yourDateColumn) = @.selectedvaluefromDL|||

Thank you Dinakar,

But I want to take the specific month in particular year. So, if I say month(o.InvoiceDT) = 6; it will take only June 2006.

TIA

|||Can you provide an example of what the user input will be and some sample data in your table and the expected result from the sql query?|||

The dropdown would be January - December which has value 1-12. Then, my table is Order table which has InvoiceDT = mm/dd/yyyy (ex. 11/30/2006).

Then, I want to write in my query such as: WHERE InvoiceDT = @.Month ??

Since, I only get the input from the user as Month, how can I Get the month of the current year.

Hope this helps

|||

hi ,

ideally you should also provide list of years but you can try following

select * from table where month(datefield) = dropdown.selectedvalue and year(datefield) = year(getdate())

regards,

satish.

|||Thank you satish|||

no problems,

cheers

satish

How to get MDX query that BI Studio generates in the Cube''s Browser

Hi,

I have been trying to write an MDX query which restricts the data based on a date range on the time dimension.

I am able to frame this query on the GUI interface of the Business Intelligence Studio (Using the cube browser) But I want the actual MDX for it.

To be specific, I am trying to view how the "Range (Inclusive") operator is implemented in the "Browser" Tab of the Cube Editor in the Business Intelligence Studio.

I have a Time dimension which has Year, Quarter, Month, Day Hierachy and I need to form the query such that I have a couple of pre-defined CALCULATED measures on the Column axis and Another dimension on the Row axis. I want to limit the records to a Range (Say Jan 1, 2007 to Aug 5, 2007).

My MDX works when it is for a single value of the Time Dimension.But for a range, I get nulls in the calculated measures.

My MDX is :

WITH

MEMBER [Time-By Calendar].[Time-By Calendar].[Selected Time] AS '

AGGREGATE({{[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 1].[January].[1]:[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 1].[January].[1].parent.parent.lastChild.lastChild}

+ {[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 1].[January].[1].parent.parent.nextMember:[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 3].[August].[5].parent.parent.prevMember}

+ {[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 3].[August].[5].parent.parent.firstChild.firstChild:[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 3].[August].[5]}})

', SOLVE_ORDER = 5001, SCOPE_ISOLATION=CUBE

SELECT DISTINCT( {[Measures].[Available Hours], [Measures].[Agency] ) ON AXIS(0) ,

DISTINCT( {[Resource-By Pool].[Resource Name].members} ) on AXIS(1)

FROM [RESOURCE SUMMARY]

WHERE [Time-By Calendar].[Time-By Calendar].[Selected Time]

=====================================

Where the calculated MEASURE.Agency is pre-defined as:

CREATE MEMBER CURRENTCUBE.Measures.[Agency] AS

'IIF(NOT([Resource-By Pool].[Resource-By Pool].CurrentMember IS NULL) AND IsLeaf([Resource-By Pool].[Resource-By Pool].CurrentMember),

IIF(NONEMPTYCROSSJOIN({[Agency-By Pool].[Agency Pool].&[].&[0]},{[Resource-By Pool].[Resource-By Pool].CurrentMember}).count > 0,

"",NONEMPTYCROSSJOIN({[Agency-By Pool].[Agency Name].members},{[Resource-By Pool].[Resource-By Pool].CurrentMember}).item(0).item(0).name),"")', SOLVE_ORDER = 5000;

Any help is greatly appreciated.

Regards,

Mehernosh

I'm not entirely sure, but if I read your statement correctly all you are trying to achieve is to get the aggregate of the dates from Jan 1 to Aug 5. In which case you should be able to do the following.

SELECT {[Measures].[Available Hours], [Measures].[Agency] } ON AXIS(0) ,

{[Resource-By Pool].[Resource Name].members} on AXIS(1)

FROM [RESOURCE SUMMARY]

WHERE {[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 1].[January].[1]:[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 3].[August].[5]}

And if you are after a YTD amount it could be expressed even more concisely as:

SELECT {[Measures].[Available Hours], [Measures].[Agency] } ON AXIS(0) ,

{[Resource-By Pool].[Resource Name].members} on AXIS(1)

FROM [RESOURCE SUMMARY]

WHERE {YTD([Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 3].[August].[5])}

I'm not sure why you are using a measure for the agency name and not just crossjoining in the Agency dimension:

SELECT {[Measures].[Available Hours] } ON AXIS(0) ,

NON EMPTY {[Resource-By Pool].[Resource Name].members} *

{Agency-By Pool].[Agency Name].members} on AXIS(1)

FROM [RESOURCE SUMMARY]

WHERE {[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 1].[January].[1]:[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 3].[August].[5]}

There are a couple of issues with your calculated measure. This statement "NOT ([Resource-By Pool].[Resource-By Pool].CurrentMember IS NULL)" will always return true as the IS operator does a member comparison not a value comparison and CurrentMember will always return a valid member. I think what you probably meant was "NOT IsEmpty(Resource-By Pool].[Resource-By Pool].CurrentMember)". But I would have thought including the Agency dimension in the row axis would have done a similar thing (unless agency &[0] has some other label)

|||

Hello Darren,

Thank you for your response. You are correct that I want the aggregate of the Hours for a Resource, but also want the value of the Agency dimension to be displayed along, BUT as a measure.

The MDX you provided, (with the cross join on Row Axis), certainly works. But alas I need the AGENCY to be a measure on the Column axis.

The reason is that our enterprise application has a customizable Portfolio management component (that uses Analysis Server) wherein Users can add/remove a set of predefined measures to their view. Very much like how the Cube browser does, but with limited functionality such that only predefined measures can be dropped on to the Column axis. And the ROWS are limited by Time dimension. The application generates the MDX based on the set of measures provided. Hence we need each measure to be declared separately as 'CREATE MEMBER CURRENTCUBE.Measures'.

Also the switch to using "NOT IsEmpty" instead of "NOT null" check in the definition of the Agency measure did not make any difference. The measure value still returns as null for each row.

The funny thing is that this query WORKs in AS2000. But as we were porting our application to use AS2005 we ran into this issue.

But I see that when I do a similar thing with the "Cube Browser", we get the correct result. (Agency is declared as a calculated measure on the cube)

Hence I wanted to see the MDX query that is generated by the Cube browser. Should I listen on any particular port to get the MDX that is sent to the Analysis Server. Is the traffic encoded

Any help will be highly appreciated as the porting of the application to AS2005 has stalled because of this issue

Regards,

Mehernosh

|||

Mehernosh Vadiwala wrote:

The funny thing is that this query WORKs in AS2000. But as we were porting our application to use AS2005 we ran into this issue.

You did not mention that this used to run under AS2000, on double checking the calc I noticed that you are referencing the [Resource-By Pool].[Resource-By Pool] attribute hierarchy. At the attribute hierarchy level there is usually a default "All" member so the count will always be 1. If you add an extra [Resouce-By Pool] reference to specify just the level in the attribute hierarchy with the actual members.

CREATE MEMBER CURRENTCUBE.Measures.[Agency] AS

'IIF(NOT([Resource-By Pool].[Resource-By Pool].CurrentMember IS NULL) AND IsLeaf([Resource-By Pool].[Resource-By Pool].CurrentMember),

IIF(NONEMPTYCROSSJOIN({[Agency-By Pool].[Agency Pool].&[].&[0]},{[Resource-By Pool].[Resource-By Pool].[Resource-By Pool].CurrentMember}).count > 0,

"",NONEMPTYCROSSJOIN({[Agency-By Pool].[Agency Name].members},{[Resource-By Pool].[Resource-By Pool].CurrentMember}).item(0).item(0).name),"")', SOLVE_ORDER = 5000;

Mehernosh Vadiwala wrote:

Hence I wanted to see the MDX query that is generated by the Cube browser. Should I listen on any particular port to get the MDX that is sent to the Analysis Server. Is the traffic encoded

Yes, the traffic is encoded, but you can use SQL Profiler to connect to SSAS in 2005 and see the begin and end query events which have most of the MDX, some of the cube browser controls create named sets within the session, so you might need to assemble the MDX from a couple of trace statements.

|||

Darren,

This query used to work in AS2000, but I had already ported it for AS2005. i.e. I had already added the additional hierarchy attribute. (If you notice, I already have [Resource-By Pool].[Resource-By Pool].currentMember in the query I gave above. The SQL2000 version had only [Resource-By Pool].currentMember )

But that was the only change I made to the Query.

Anyway I found the solution. All I had to do was not declare the slicer axis (WHERE clause) in place, instead of with a separate Member.

======= Not Working =================================

WITH

MEMBER [Time-By Calendar].[Time-By Calendar].[Selected Time] AS '

AGGREGATE({[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 1].[January].[1]:[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 3].[August].[5})

', SOLVE_ORDER = 5000, SCOPE_ISOLATION=CUBE

SELECT DISTINCT( {[Measures].[Available Hours], [Measures].[Agency] ) ON AXIS(0) ,

DISTINCT( {[Resource-By Pool].[Resource Name].members} ) on AXIS(1)

FROM [RESOURCE SUMMARY]

WHERE [Time-By Calendar].[Time-By Calendar].[Selected Time]

======= WORKING =================================

SELECT DISTINCT( {[Measures].[Available Hours], [Measures].[Agency] ) ON AXIS(0) ,

DISTINCT( {[Resource-By Pool].[Resource Name].members} ) on AXIS(1)

FROM [RESOURCE SUMMARY]

WHERE ({[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 1].[January].[1]:[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 3].[August].[5})

=================================================

Any insight on why this works and not the other ?

Also I have a bigger issue coming up with "LookupCube" function. None of our calculated Measures that have the LookupCube function in them return any results now. In the documentation, there is just one line, stating that the implementation of LookupCube has changed. There is no additional information.

Could this be the reason? Is there some documentation on this ?

Mehernosh

|||

Mehernosh Vadiwala wrote:

This query used to work in AS2000, but I had already ported it for AS2005. i.e. I had already added the additional hierarchy attribute. (If you notice, I already have [Resource-By Pool].[Resource-By Pool].currentMember in the query I gave above. The SQL2000 version had only [Resource-By Pool].currentMember )

Sorry, you're right the formula has already been adjusted correctly.

Mehernosh Vadiwala wrote:

Anyway I found the solution. All I had to do was not declare the slicer axis (WHERE clause) in place, instead of with a separate Member.

Yeah, that was what I suggested in my first response.

Mehernosh Vadiwala wrote:

Any insight on why this works and not the other ?

The only reason I could think that this might have issues would be if you [available hours] measure is calculated and relies on having a [Time-by Calendar] context. A cacluated aggregate in the query will not supply such a context and the formula would calculate as if it were at the all level. The Aggregate function is meant to calculate early in the solve order, but your setting of the solve order may be overriding this.

Mehernosh Vadiwala wrote:

Also I have a bigger issue coming up with "LookupCube" function. None of our calculated Measures that have the LookupCube function in them return any results now. In the documentation, there is just one line, stating that the implementation of LookupCube has changed. There is no additional information.

I don't use LookupCube any more I use multiple measure groups, possibly linking them in if they are shared across more than one cube.

How to get last payment and amt of all

This will get me what I need based on an entered client id, but what is I want it to return the last payment date and amt for all loans? I tried removing the two where clauses and it only returned the last payment entered but not for all loans.

SELECT dbo.tblLoan.Client_ID,MAX(dbo.tblPayments.PaymentDate)AS [Last Pay Date],SUM(dbo.tblPayments.AmountPaid)AS [Last Pay Amt]FROM dbo.tblLoanINNERJOIN dbo.tblPaymentsON dbo.tblLoan.Loan_ID = dbo.tblPayments.Loan_IDWHERE (dbo.tblLoan.Client_ID = @.Client_ID)AND dbo.tblPayments.PaymentDate = (SELECT TOP 1 p.PaymentDateFROM dbo.tblPayments pINNERJOIN dbo.tblLoan lON l.Loan_ID = p.Loan_IDWHERE l.Client_ID = @.Client_IDORDER BY p.PaymentDateDESC)GROUP BY dbo.tblLoan.Client_ID

Can you give the exact design of the two tables, which fileds are what ...i mean primary key etc?

|||

Considering the Loan_ID will be unique in tblLoan

SELECT l.Client_ID,MAX(p.PaymentDate)AS'Last Pay Date',SUM (p.AmountPaid)AS'Total Amount Paid'FROM tblPayments pINNERJOIN tblLoan lON p.Loan_ID = l.Loan_IDWHERE l.Client_ID = 1GROUP BY l.Client_ID

|||

Addie,

I tried your suggestion but I need to see the last pay date and amt for all clients. This only shows if there is a clientid of 1.

I also tried removing the Where clause, that gave me the last pay date but it gave me a total sum of payments and I need just the amount of the last payment and date.

Then I remved the Sum but that made the query return all payments for all clients.

How can I make it show each client and there last pay date and last pay amt?

|||

Post some sample data from each of the tables in question and the expected output. Sometimes it is easier to see what you are trying to do just by looking at the data than your explanation.

|||

ndinakar,

I'm not able to provide data, however, below is an example of what I need:

ClientID Last Pay Date Last Pay Amt10010001 2/2/2007 $10010002001 3/2/2007 $200Eachof the clients may have made several paymentsover time but I needto seeonly these three fieldsfor each client,for their LAST payment made.

|||

Try this:

I have added Loan_ID to the results row; it seems possible one client might have two loans?

If you are absolutely sure that cannot happen, then simply remove Loan_ID from the first and last lines.

If you happen to have two or more payments for the same loan on the same day, you'll get the total. 

SELECT dbo.tblLoan.Client_ID, dbo.tblLoan.Loan_ID, max(dbo.tblPayments.PaymentDate)AS [Last Pay Date], sum(dbo.tblPayments.AmountPaid)AS [Last Pay Amt]FROM dbo.tblLoanINNERJOIN dbo.tblPaymentsON dbo.tblLoan.Loan_ID = dbo.tblPayments.Loan_IDWHERE dbo.tblPayments.PaymentDate = (SELECT max( p.PaymentDate)FROM dbo.tblPayments pWHERE p.Loan_ID = dbo.tblLoan.Loan_ID)

Group by dbo.tblLoan.Client_ID, dbo.tblLoan.LoanID

|||

I think we are making progress, but that is not it yet. I'm going to make it easier for us.

Clients make payments on loans, they are inserted into tblPayments. I need to return a list of the LAST PAYMENT made by each client. I need to see clientID, PaymentDate, and PaymentAmount.

client1 2/2/2007 $100 < this is the last payment they made, they may have made other payments but this was the last.

client2 3/2/2007 $50 < this is the last payment they made, they may have made other payments but this was the last.

client3 4/1/2007 $1000 < thi si the last payment they made, they may have made other payments but this was the last.

etc...

|||

Without providing any table structure or sample data to work on your query it will only be a guessing game. Its just a matter of luck as to who takes the best guess. From your initial query it looks like the two tables are related by LoanId. But what is the relation between LoanId and ClientId?

|||

Did you look at my last post? It shows a simplified version of what I need. I have taken the loan table out of the query, it is not needed.

I cannot beleive that this is so hard to do in SLQ Server. I just want a list of the last payment made by each client that is in tblPayments.

tblPayments

ClientID, PaymentDate, PaymentAmount

|||

Yes I did look at your last post. Stating what you want can help if you also provide the source data from which you intend to achieve the result. As I mentioned after making the assumptions based on the info you provided here's a sample:

Declare @.paymentstable (PaymentIdint, Paymentdatedatetime , amountdecimal(10,2), ClientIdint)Insert into @.paymentsSelect 1,'01/01/2007', 100.00,10010001unionallSelect 2,'02/01/2007', 125.00,10010001unionallSelect 10,'02/01/2007', 125.00,10002001unionallSelect 11,'03/01/2007', 125.00,10002001unionallSelect 12,'04/01/2007', 255.00,10002001select P.*from @.payments PJoin (select Clientid,Max(PaymentDate)as MaxDatefrom @.paymentsGroup by Clientid) P2ON P.ClientId = P2.clientidAND P.Paymentdate = P2.maxdate

And, what you are trying to do is very simple, if you provided all the required info.

|||I think I may have worded my post in a way that you did not like, for that I apologize. That is never my intention.|||

SELECT dbo.tblLoan.Client_ID, dbo.tblPayments.PaymentDateAS [Last Pay Date], dbo.tblPayments.AmountPaidAS [Last Pay Amt]FROM dbo.tblLoanINNERJOIN dbo.tblPaymentsON dbo.tblLoan.Loan_ID = dbo.tblPayments.Loan_IDWHERE dbo.tblPayments.PaymentDate = (SELECT max( p.PaymentDate)FROM dbo.tblPayments pWHERE p.Loan_ID = dbo.tblLoan.Loan_ID)

order by dbo.tblLoan.Client_ID

|||

I mis understood the question, below is what you are perhaps looking for:

SELECT dbo.tblLoan.Client_ID,dbo.tblLoan.Loan_ID,MAX(dbo.tblPayments.PaymentDate)AS [Last Pay Date],SUM(dbo.tblPayments.AmountPaid)AS [Last Pay Amt]FROM dbo.tblLoanINNERJOIN dbo.tblPaymentsON dbo.tblLoan.Loan_ID = dbo.tblPayments.Loan_IDWHERE dbo.tblPayments.PaymentDate = (SELECT MAX( p.PaymentDate)FROM dbo.tblPayments pWHERE p.Loan_ID = dbo.tblLoan.Loan_ID)GROUP BY dbo.tblLoan.Client_ID, dbo.tblLoan.Loan_IDORDER BY Client_ID

How to get hierarchical xml based of multiple tables xml columns

Hi,
I am currently working in SQL Server 2005.
we have three tables all these tables have an xml type columns.The XML in
these XML columns are related to each other.
EX.
Table "PLY" contains Rows As
<dsplaylist>
<ply id="f277f633-fa5d-4d98-8a30-d8d857d65343" slug="ply1">
<ply_grp_info grp_ref_id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" />
</ply>
</dsplaylist>
<dsplaylist>
<ply id="c9835a5e-5dd0-47cd-8d14-a59ae00abda6" slug="ply2">
<ply_grp_info grp_ref_id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" />
<ply_grp_info grp_ref_id="3de9cf11-34C8-53fc-6ae8-3335f2cd58fe" />
</ply>
</dsplaylist>
Table "GRP" Contains Rows As
<dsgrp>
<grp id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" slug="grp4">
<grp_inst_info inst_ref_id="d7a02503-8186-4380-a9d3-16aeedb7fa08" />
<grp_inst_info inst_ref_id="dca02503-8186-3480-a9d3-16aeedb7fa23" />
<grp_inst_info inst_ref_id="aba02503-8186-4380-a9d3-16aeedb7fa23" />
</grp>
</dsgrp>
<dsgrp>
<grp id="c9835a5e-5dd0-47cd-8d14-a59ae00abda6" slug="grp3">
<grp_inst_info inst_ref_id="d7a02503-8186-4380-a9d3-16aeedb7fa08" />
</grp>
</dsgrp>
Table "INSTANCE" Contains Rows As
<instance id="d7a02503-8186-4380-a9d3-16aeedb7fa08" slug="inst1" />
<instance id="dca02503-8186-3480-a9d3-16aeedb7fa23" slug="inst4" />
<instance id="aba02503-8186-4380-a9d3-16aeedb7fa23" slug="inst3" />
i want to get a hierachical relational xml out of these TABLES columns XML.
In the Following XML FORMAT
<ply id="f277f633-fa5d-4d98-8a30-d8d857d65343" slug="ply1">
<grp id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" slug="grp4">
<instance id="d7a02503-8186-4380-a9d3-16aeedb7fa08" slug="inst1" />
<instance id="dca02503-8186-3480-a9d3-16aeedb7fa23" slug="inst4" />
<instance id="aba02503-8186-4380-a9d3-16aeedb7fa23" slug="inst3" />
</grp>
</ply>
<ply id="c9835a5e-5dd0-47cd-8d14-a59ae00abda6" slug="ply2">
<grp id="c9835a5e-5dd0-47cd-8d14-a59ae00abda6" slug="grp3">
<instance id="d7a02503-8186-4380-a9d3-16aeedb7fa08" slug="inst1" />
</grp>
</ply>
How can i achieve this using FLWOR Expression or any other way.
Thanks,
CarolI have done this for one table
try this
create table ply (ply_col xml)
insert into ply values ('<dsplaylist>
<ply id="f277f633-fa5d-4d98-8a30-d8d857d65343" slug="ply1">
<ply_grp_info grp_ref_id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" />
</ply>
</dsplaylist>')
insert into ply values ('<dsplaylist>
<ply id="c9835a5e-5dd0-47cd-8d14-a59ae00abda6" slug="ply2">
<ply_grp_info grp_ref_id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" />
<ply_grp_info grp_ref_id="3de9cf11-34C8-53fc-6ae8-3335f2cd58fe" />
</ply>
</dsplaylist>')
This is the query
with CTE_PLY as
(
SELECT T1.ply_id.query('.') as ply_id
FROM ply
CROSS APPLY ply_col.nodes('/dsplaylist/ply') as T1(ply_id)
)
select ply_id.value('/ply[1]/@.id[1]','varchar(100)') as [id],
T2.ply_GRP.value('@.grp_ref_id','varchar(100)') as ply_grp_ref_id
FROM CTE_PLY
CROSS APPLY PLY_ID.nodes('/ply/ply_grp_info') as T2(ply_GRP)
If you can apply the same logic on all three tables then its a normal join
query :)
Hope this helps..