filter data by row number

SELECT *
FROM (SELECT [Patient Identifier], [Operator Index], Date, Time, ROW_NUMBER() OVER (PARTITION BY [Patient Identifier], Date
ORDER BY [Patient Identifier], Date, Time) AS RowNum
FROM Complete
WHERE [Operator Index] <= 89) AS a
WHERE RowNum <= 4
UNION
SELECT *
FROM (SELECT [Patient Identifier], [Operator Index], Date, Time, ROW_NUMBER() OVER (PARTITION BY [Patient Identifier], Date
ORDER BY [Patient Identifier], Date, Time) AS RowNum
FROM Complete
WHERE [Operator Index] >= 90) AS a
WHERE RowNum <= 2

This query returns values above 90 (I need 2 of them) or values between 80 and 89 (data is already filtered for only greater >=80) and I need 4 values above 80. I only need either 2 above 90 or 4 above 80, not both, and this query returns 2 above 90, but also the values between 80 and 89. If there are already 2 above 90, I do not want any values between 80 and 89. If there are 4 above 80, I do not need any additional values. If the are two above 80 and 1 above 90, I will take all of them (max I will ever take is 4).Can you give me sample data to work on?|||Patient IdentifierPatient InitialsDateTimeOperator Index
0517_00003GHV18-Oct-0611:4891
0517_00003GHV18-Oct-0611:50100
0517_00004JMH17-Oct-0611:4189
0517_00004JMH17-Oct-0611:5093
0517_00004JMH17-Oct-0611:5291
0517_00004JMH17-Oct-0612:0093
0534_00003JS21-Nov-0612:35100
0534_00003JS21-Nov-0612:46100
0534_00004ChM20-Nov-0610:49100
0534_00004ChM20-Nov-0610:51100
0534_00006JK4-Dec-069:38100
0534_00006JK4-Dec-069:4784
0534_00006JK4-Dec-069:5093
0534_00007TL29-Nov-069:2298
0534_00007TL29-Nov-069:34100
0539_00001PGL9-Oct-069:39100
0539_00001PGL9-Oct-069:4395
0539_00002DWR27-Oct-0610:0491
0539_00002DWR31-Oct-0611:4092
0539_00002DWR31-Oct-0611:4196
0539_00002DWR31-Oct-0611:4292
0539_00003JmL30-Nov-069:1496
0539_00003JmL30-Nov-069:1897|||I figured it out! Thanks!|||Here is the code I wrote and it is not correct although it appears to be correct at first. I was validating my data and discovered on several instances a value of 80 (something) is there instead of 90 (something).

SELECT [Patient Identifier], Date, [Operator Index], Time

FROM (SELECT ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]) AS [Patient Identifier], ISNULL(t9.Date, t8.Date) AS Date, ISNULL(t9.Rows, t8.Rows)
AS Rows, c.[Operator Index], c.Time, ROW_NUMBER() OVER (PARTITION BY ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]),
ISNULL(t9.Date, t8.Date)
ORDER BY c.Time) AS RowNum
FROM (SELECT [Patient Identifier], Date, 2 AS [Rows]
FROM [First Step]
WHERE [Operator Index] >= 90
GROUP BY [Patient Identifier], Date
HAVING COUNT(*) >= 2) AS t9 FULL JOIN
(SELECT [Patient Identifier], Date, 4 AS [Rows]
FROM [First Step]
WHERE [Operator Index] BETWEEN 80 AND 89
GROUP BY [Patient Identifier], Date
HAVING COUNT(*) >= 4) AS t8 ON t8.[Patient Identifier] = t9.[Patient Identifier] AND t8.Date = t9.Date INNER JOIN
[First Step] AS c ON c.[Patient Identifier] = ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]) AND c.Date = ISNULL(t9.Date, t8.Date)) AS d
WHERE d .RowNum <= d .[Rows]

Filter data back to publisher

Hi all,

We have merge replication set up between 2 instances of SQL 2005 through web synchronization. I was wondering is it possible if the subscriber adds data at their end can we selectively edit what data gets uploaded back to the publisher? Will this work if I add a filter to the appropriate article at the publisher or will that only limit what goes down to the subscriber rather than vice-versa?

Also, could we set up web sychronization if the publisher had no direct access to the IIS server (ie. was not on the same network, available only through port 80, 443)?

Thanks,

Iain

Hey lain,

You can't selectively edit what data gets uploaded back to the publisher. There's a parameter that you can use in sp_addmergearticle @.subscriber_upload_options which you can set to (0,1,2) NO restrictions, changes are allowed at sub but not uploaded to the publisher , and last option doesn't allow any changed at the subscriber respectively. So you can either upload all the changes or none of the changes, but you can't filter out the data to be uploaded.

There's a workaround but that's not recommended. You can enable the insert triggers on the data you want to upload to the publisher, and then disable the insert triggers on the data you want to filter out. By doing that merge agent won’t detect those changes and they won't be uploaded to the publisher.

hope that helps

bishoyy

|||

Ok, thanks. Looks like if they want to implement their requirement they will just have to become the publisher. I think messing the the triggers will just lead to all sorts of issues and problems that I could well do without!

Thanks for the help.

Filter data back to publisher

Hi all,

We have merge replication set up between 2 instances of SQL 2005 through web synchronization. I was wondering is it possible if the subscriber adds data at their end can we selectively edit what data gets uploaded back to the publisher? Will this work if I add a filter to the appropriate article at the publisher or will that only limit what goes down to the subscriber rather than vice-versa?

Also, could we set up web sychronization if the publisher had no direct access to the IIS server (ie. was not on the same network, available only through port 80, 443)?

Thanks,

Iain

Hey lain,

You can't selectively edit what data gets uploaded back to the publisher. There's a parameter that you can use in sp_addmergearticle @.subscriber_upload_options which you can set to (0,1,2) NO restrictions, changes are allowed at sub but not uploaded to the publisher , and last option doesn't allow any changed at the subscriber respectively. So you can either upload all the changes or none of the changes, but you can't filter out the data to be uploaded.

There's a workaround but that's not recommended. You can enable the insert triggers on the data you want to upload to the publisher, and then disable the insert triggers on the data you want to filter out. By doing that merge agent won’t detect those changes and they won't be uploaded to the publisher.

hope that helps

bishoyy

|||

Ok, thanks. Looks like if they want to implement their requirement they will just have to become the publisher. I think messing the the triggers will just lead to all sorts of issues and problems that I could well do without!

Thanks for the help.

Filter Cursor With Dynamic SQL

I should know this, but having a brain cramp.

I am trying to filter the data a cursor has to go through based on dynamic sql, but can't seem to get the syntax correctly.

Basically

'tmp is filter clause brought into the stored proc.

Declare @.tmp varchar(2000)

Declare my CURSOR FAST_FORWARD

FOR

Select * from table where + @.tmp

OPEN my

...

...

...

Any ideas?

declare a table variable to hold the data
declare @.SqlStr varchar(8000)

set @.SQLStr = 'Select * from table where '+ @.tmp

insert into @.tableVariable (Col1, Col2,...)
exec (@.SqlStr)

declare my CURSOR FAST_FORWARD
FOR
select * from @.tableVariable

rest of your should work...

|||

Also, be sure to declare the cursor as LOCAL unless you need it to be global...

declare my CURSOR LOCAL FAST_FORWARD

Better yet, consider not using a cursor if possible Tell us more about what you were doing and perhaps we can help.

|||

Here's the skinny.

I created a view to consolidate a bunch of data across tables. Once this table is consolidated I need to perform calculations on a row by row basis as a previous row's calculation is used in future rows based upon business rules.

I have written this logic using an inline view, TSQL Stored Proc (utilizing cursors), CLR Stored Proc, and traditional .NET business logic. I found that of the four options TSQL Stored Proc and CLR Stored Proc perform the best when running remotely. I now am trying to filter down the result set of the TSQL Stored Proc based upon parameters a user will provide. After following Bashan's advice I am able to filter it down and the speed is great; however, if I run it without filtering down the results then the logic doubled the processing time of the stored proc.

I would love to create this logic without using cursors but not sure how to perform row by row complex logic inside the database without using them.

Thanks for your help. I look forward to receiving any advice.

|||

The question is always "what is the complex logic"? Usually, the complex logic can be turned into where clauses, subqueries, and occasionally a function or two. It is also possible that the cursor is the best way do things if you need ordered data: http://blogs.sqlservercentral.com/blogs/amachanic/archive/2006/02/28/508.aspx

Using row_number() you can often do the kind of rollups based on ordering and previous values, but it can also be hard to do. So without much more work/knowledge of your system, if the cursor works I would go with that. But if you want some more advice/ideas, give us the gist of the problem and we can take a look and see what you might could change.

sql

Filter Cursor With Dynamic SQL

I should know this, but having a brain cramp.

I am trying to filter the data a cursor has to go through based on dynamic sql, but can't seem to get the syntax correctly.

Basically

'tmp is filter clause brought into the stored proc.

Declare @.tmp varchar(2000)

Declare my CURSOR FAST_FORWARD

FOR

Select * from table where + @.tmp

OPEN my

...

...

...

Any ideas?

declare a table variable to hold the data
declare @.SqlStr varchar(8000)

set @.SQLStr = 'Select * from table where '+ @.tmp

insert into @.tableVariable (Col1, Col2,...)
exec (@.SqlStr)

declare my CURSOR FAST_FORWARD
FOR
select * from @.tableVariable

rest of your should work...

|||

Also, be sure to declare the cursor as LOCAL unless you need it to be global...

declare my CURSOR LOCAL FAST_FORWARD

Better yet, consider not using a cursor if possible Tell us more about what you were doing and perhaps we can help.

|||

Here's the skinny.

I created a view to consolidate a bunch of data across tables. Once this table is consolidated I need to perform calculations on a row by row basis as a previous row's calculation is used in future rows based upon business rules.

I have written this logic using an inline view, TSQL Stored Proc (utilizing cursors), CLR Stored Proc, and traditional .NET business logic. I found that of the four options TSQL Stored Proc and CLR Stored Proc perform the best when running remotely. I now am trying to filter down the result set of the TSQL Stored Proc based upon parameters a user will provide. After following Bashan's advice I am able to filter it down and the speed is great; however, if I run it without filtering down the results then the logic doubled the processing time of the stored proc.

I would love to create this logic without using cursors but not sure how to perform row by row complex logic inside the database without using them.

Thanks for your help. I look forward to receiving any advice.

|||

The question is always "what is the complex logic"? Usually, the complex logic can be turned into where clauses, subqueries, and occasionally a function or two. It is also possible that the cursor is the best way do things if you need ordered data: http://blogs.sqlservercentral.com/blogs/amachanic/archive/2006/02/28/508.aspx

Using row_number() you can often do the kind of rollups based on ordering and previous values, but it can also be hard to do. So without much more work/knowledge of your system, if the cursor works I would go with that. But if you want some more advice/ideas, give us the gist of the problem and we can take a look and see what you might could change.

Filter Cube Data

Hi

i am using pivot table 11 to display the data from SSAS Cube. the problem is

1- pivot table fetches all the data and display it according to the design. but what i need is

a- the user could only see the data which was posted by himself.

b. He must be unaware of the data posted by other users.

in database table there is a field named userId which is also which is part of dimenssion in myCube.

i dont want to use filteraxes for this purpose actually i want the user to be unaware of other users of system also

any single helping word will be appriciated

Regards

Salman Bashir

Hi Salman,

You can define custom dimension security rules for the "UserID" attribute - the MDX function: UserName (which returns "domain\user") is typically used for this purpose:

http://msdn2.microsoft.com/en-us/library/ms175366.aspx

>>

SQL Server 2005 Books Online

Granting Custom Access to Dimension Data

...

Understanding the IsAllowed Property

The IsAllowed property determines whether the database role can access attribute members. By default, a database role that has access to a dimension cannot access attribute hierarchies.

Understanding the AllowedSet Property

The AllowedSet property uses a Multidimensional Expressions (MDX) expression to determine which attribute members can be viewed by the database role (the allowed set). The allowed set can include no (default), all, or some attribute members. If you allow access to an attribute and do not define any members of the allowed set, access to all members is granted. If you allow access to an attribute and define a specific set of attribute members, only the specifically allowed members are visible. Specifically defining an allowed set may limit the visibility of attribute members added after the allowed set is defined.

...

Understanding the VisualTotals Property

The VisualTotals property indicates whether the aggregated cell values that are displayed are calculated according to all cell values or only according to the cell values that are visible to the database role.

By default, the VisualTotals property is disabled (set to False). This default setting maximizes performance because Analysis Services can quickly calculate the total of all cell values, instead of having to spend time selecting which cells values to calculate.

However, having the VisualTotals property disabled could create a security issue if a user can use the aggregated cell values to deduce values for attribute members to which the user's database role does not have access. For example, Analysis Services uses the values for three attribute members to calculate an aggregated cell value. The database role has access to view two of these three attribute members. Using the aggregated cell value, a member of this database role would be able to deduce the value for the third attribute member.

If a user can deduce values for attribute members to which the user's database role does not have access, security best practice dictates that you enable (set to True) the VisualTotals property for the attribute. When you enable the VisualTotals property, a database role can only view aggregated totals for dimension members to which the role has permission. For example, enabling the VisualTotals property means that the database role will see an aggregated total that includes only those states (that is, members of the State attribute) that are visible to the role. The aggregated total will not include the values for all states.

Understanding the DefaultMember Property

The DefaultMember property determines the data set that is returned to a client when an attribute is not explicitly included in a query. When the attribute is not explicitly included, Analysis Services uses one of the following default members for the attribute:

If the database role defines a default member for the attribute, Analysis Services uses this default member.
If the database role does not define a default member for the attribute, Analysis Services uses the default member that is defined for the attribute itself. The default member for an attribute, unless you specify otherwise, is the All member (unless the attribute is defined as non-aggregatable).
|||

Dear sir

i am really thankful to you for your such a quick reply. it is no doubt very much helpful but my requirments are a bit different

i have custom role management system and i dont use the roles and user accounts in operating system but there are roles which are defined in my personal database and there are user accounts under those roles.

whenever a user inserts a record in database it leaves a refference tag (His LoginName (which is from a table named users in the same database and have no relation with os or domain users) with that record) which identify that which user is owner of that record.

when user sign in with my application (basically with my own role management system) a global reference to his LoginName (which is from User table in my role management system) is created when his session starts. now from this application he opens a form on which a pivot table is placed which is getting data from the cube (whose datasource is the database i defined in 2nd paragraph) now i want pivot table to send a LoginNameas parameter and cube send back the data with this username and all calculations must be performed on this filter data

|||

Actually, dynamic dimension data security as described above should still work - if you set the SSAS "CustomData" connection property to the custom "LoginName" managed by your app. Then you would access the user "LoginName" via the "CustomData" MDX function:

http://msdn2.microsoft.com/en-us/library/ms145582.aspx

>>

SQL Server 2005 Books Online

CustomData (MDX)

Updated: 17 July 2006

Returns the value of the CustomData connection string property if defined; otherwise, null.

...

For example, this function can be used in a dynamic security expression to select the allowed/denied set members for the string value in the CustomData connection string property.

>>

|||

Hi Salman,

were you able to figure out the solution. if yes could you please post you example details.

Thanks

Milind

|||

I am also having the same problem of filtering the cube data based on users defined in a database. were you able to find a solution for this?

Thanks,

Maximus11

Filter Cube Data

Hi

i am using pivot table 11 to display the data from SSAS Cube. the problem is

1- pivot table fetches all the data and display it according to the design. but what i need is

a- the user could only see the data which was posted by himself.

b. He must be unaware of the data posted by other users.

in database table there is a field named userId which is also which is part of dimenssion in myCube.

i dont want to use filteraxes for this purpose actually i want the user to be unaware of other users of system also

any single helping word will be appriciated

Regards

Salman Bashir

Hi Salman,

You can define custom dimension security rules for the "UserID" attribute - the MDX function: UserName (which returns "domain\user") is typically used for this purpose:

http://msdn2.microsoft.com/en-us/library/ms175366.aspx

>>

SQL Server 2005 Books Online

Granting Custom Access to Dimension Data

...

Understanding the IsAllowed Property

The IsAllowed property determines whether the database role can access attribute members. By default, a database role that has access to a dimension cannot access attribute hierarchies.

Understanding the AllowedSet Property

The AllowedSet property uses a Multidimensional Expressions (MDX) expression to determine which attribute members can be viewed by the database role (the allowed set). The allowed set can include no (default), all, or some attribute members. If you allow access to an attribute and do not define any members of the allowed set, access to all members is granted. If you allow access to an attribute and define a specific set of attribute members, only the specifically allowed members are visible. Specifically defining an allowed set may limit the visibility of attribute members added after the allowed set is defined.

...

Understanding the VisualTotals Property

The VisualTotals property indicates whether the aggregated cell values that are displayed are calculated according to all cell values or only according to the cell values that are visible to the database role.

By default, the VisualTotals property is disabled (set to False). This default setting maximizes performance because Analysis Services can quickly calculate the total of all cell values, instead of having to spend time selecting which cells values to calculate.

However, having the VisualTotals property disabled could create a security issue if a user can use the aggregated cell values to deduce values for attribute members to which the user's database role does not have access. For example, Analysis Services uses the values for three attribute members to calculate an aggregated cell value. The database role has access to view two of these three attribute members. Using the aggregated cell value, a member of this database role would be able to deduce the value for the third attribute member.

If a user can deduce values for attribute members to which the user's database role does not have access, security best practice dictates that you enable (set to True) the VisualTotals property for the attribute. When you enable the VisualTotals property, a database role can only view aggregated totals for dimension members to which the role has permission. For example, enabling the VisualTotals property means that the database role will see an aggregated total that includes only those states (that is, members of the State attribute) that are visible to the role. The aggregated total will not include the values for all states.

Understanding the DefaultMember Property

The DefaultMember property determines the data set that is returned to a client when an attribute is not explicitly included in a query. When the attribute is not explicitly included, Analysis Services uses one of the following default members for the attribute:

If the database role defines a default member for the attribute, Analysis Services uses this default member.

If the database role does not define a default member for the attribute, Analysis Services uses the default member that is defined for the attribute itself. The default member for an attribute, unless you specify otherwise, is the All member (unless the attribute is defined as non-aggregatable).
|||

Dear sir

i am really thankful to you for your such a quick reply. it is no doubt very much helpful but my requirments are a bit different

i have custom role management system and i dont use the roles and user accounts in operating system but there are roles which are defined in my personal database and there are user accounts under those roles.

whenever a user inserts a record in database it leaves a refference tag (His LoginName (which is from a table named users in the same database and have no relation with os or domain users) with that record) which identify that which user is owner of that record.

when user sign in with my application (basically with my own role management system) a global reference to his LoginName (which is from User table in my role management system) is created when his session starts. now from this application he opens a form on which a pivot table is placed which is getting data from the cube (whose datasource is the database i defined in 2nd paragraph) now i want pivot table to send a LoginNameas parameter and cube send back the data with this username and all calculations must be performed on this filter data

|||

Actually, dynamic dimension data security as described above should still work - if you set the SSAS "CustomData" connection property to the custom "LoginName" managed by your app. Then you would access the user "LoginName" via the "CustomData" MDX function:

http://msdn2.microsoft.com/en-us/library/ms145582.aspx

>>

SQL Server 2005 Books Online

CustomData (MDX)

Updated: 17 July 2006

Returns the value of the CustomData connection string property if defined; otherwise, null.

...

For example, this function can be used in a dynamic security expression to select the allowed/denied set members for the string value in the CustomData connection string property.

>>

|||

Hi Salman,

were you able to figure out the solution. if yes could you please post you example details.

Thanks

Milind

|||

I am also having the same problem of filtering the cube data based on users defined in a database. were you able to find a solution for this?

Thanks,

Maximus11