Split Out Number Into Groups
hi,
not sure if the title really expains what I want to achieve...
I have a table which is like :
name groups number
bob 1 160
bob 51 160
bob 101 160
What I need to do is split the 160 into the correct groups..
so the first 50 goes into group 1, then next 50 in group 51, then the next 60 in 101..
so the result would look like :
name groups number
bob 1 50
bob 51 50
bob 101 60
thank you for any advice.
View Complete Forum Thread with Replies
Related Forum Messages:
How To Count Number Of Groups In MS-SQL ?
The following SQL works on Access and Oracle to return the number of Groups(Rows) of the SQL. In MS-SQL this SQL is not valid. What is the equivalent in MS-SQL? Select Count(1) FROM (Select ShipRegion, Sum(FREIGHT) as [TotalFreight] from ORDERS WHERE OrderID < 123456 GROUP BY ShipRegion ) Thanks, Frankk
View Replies !
Counting Number Of Groups
Hi, i need to count the different orders (OrderID) used by employee (E_ID). Here are some records. E_IDOrderID 168472 168472 168424 168180 168472 168424 200372 200372 200373 200373 I used the folowing SQL sentence, but it didn't return what I want. Select E_ID, count(OrderID) From Orders group by E_ID; And here is what I want. E_IDCount 1683(472,424,180) 2002(372,373) Thanks
View Replies !
Counting The Number Of Groups. Please Help!!!!
Counting groups I have the following code that is grouped by name Mike G ( from 2000 €“ 2003) Address Raise Test 1 Test 1 Mike G ( from 2004 €“ 2007) Address Raise Test 2 1 Test 2 1 Mark G ( from 2004 €“ 2007) Address Raise Test 2 1 Test 2 1 Total 6 (I get this without any problem) The problem I am having is trying to find out how many groups we have to use the value for some other calculation. So I did this Count(Field .Name) wich will give me 6 instead of 3 . The reason is in my dataset it will be something like that Name add raise Period Mike G Test 1 2000-2003 Mike G Test 1 2000-2003 Mike G Test 1 2004-2004 Mike G Test 1 2004-2007 Mike G Test2 1 2004-2007 Mike G Test2 1 2004-2007 If I do CountDistinct(Field .Name) it will give me 2 which is also wrong . So how can I count many groups we have. I spend a lot of time on this please help! Thanks
View Replies !
Split Number Into New Row
I have the following: iddocidgroupidOption 48986111235 353973334 2314820112346 would like to see this result: iddocidgroupidOption 48986111 48986112 48986113 48986115 35397333 35397334 231482011 231482012 231482013 231482014 231482016 thanks you
View Replies !
How To Control Rendering Number Of Groups Per Page
Is there a way that i can render only 3 groups per page. Instead of the SSRS automatically rendering on its own is there a way i can decide how many groups are rendered in 1 page. I get output like this: when i dont keep pagebreak at end i get it like this displaying only half of the 4th group and the remianing half in next page. Texas abc pqr city1 ber cyh city2 illinois abc pqr city1 ber cyh city2 Denver abc pqr city1 ber cyh city2 Wiscon abc pqr city1 However i would need o/p like this Texas abc pqr city1 ber cyh city2 illinois abc pqr city1 ber cyh city2 Denver abc pqr city1 ber cyh city2 Pushing that 4th group to the next page... Any ideas.... Thanks in advance.. Pav
View Replies !
Split Data File Into Multiple File Groups..!
I have one of our production Accounting Databases starting from 2 GBnow grown into a 20 GB Database over the period of a few years...I have been getting timeouts when transactions are trying to updatedifferent tables in the database.. Most of the error I get are I/Orequests to the data file (Data file of the production dbAccounting_Data.MDF).I would like to implement the following to this Accounting database.I need to split the Data file into multiple files by placing some ofthe tables in different file groups. I have the server upgraded to beable to have different drives in different channels. I can place thesedata and log files in different drives so it will be less I/Oconflicts..I would like to have the following file groups..FileGroup 1 - which will have all database definitions (DDL).FileGroup 2 - I will have the AR Module tables under here..FileGroup 3 - I will have the GL module tables under here..FileGroup 4 - I will have the rest of the tables under hereFileGroup 5 - I will like to place the indexes under here....Also where will the associated transaction files go?I would like to get some help doing this. Is there any articles / helpavailable that I can refer to. Any suggestions / corrections/criticisms to what I have mentioned above is much appreciated...!Thanks in advance....
View Replies !
Problem With Matrix (in Subreport, Multiple Groups), Groups Repeating First Row Data
I have a new SQL 2005 (SP2) Reporting Services server to which I've just upgraded and deployed some SSRS 2000 reports. I have a subreport that contains a matrix with two groups. The report data seems to be inexplicably repeating the data for the first row in the group for all rows in the group. Example: ID1 ID2 DisplayData 1 1 A 1 2 B 1 3 C 2 1 A 2 2 B 2 3 C Parent group is on ID1, child group is on ID2, report would show: 1 1 A 2 A 3 A 2 1 A 2 A 3 A Is this a matrix bug in 2005 SP2, or do I need to do something differently? I can no longer pull a comparison version from an SSRS 2000 server to verify, but I believe it was working as expected before...
View Replies !
Split A Decimal Number Into The Integer Part And The Fraction Part
I have a table with a column named measurement decimal(18,1). If the value is 2.0, I want the stored proc to return 2 but if the value is 2.5 I want the stored proc to return 2.5. So if the value after the decimal point is 0, I only want the stored proc to return the integer portion. Is there a sql function that I can use to determine what the fraction part of the decimal value is? In c#, I can use dr["measurement "].ToString().Split(".".ToCharArray())[1] to see what the value after the decimal is.
View Replies !
Limitations In Term Of Number Of Tasks And Number Of Columns
Hi, I am currently designing a SSIS package to integrate data into a data warehouse fact table. This fact table has about 70 columns among which 17 are foreign keys for dimension tables. To insert data in that table, I have to make several transformations and lookups. Given the fact that the lookups I have to make are a little complicated, I have about 70 tasks in my Data Flow. I know it's a lot, but I can't find a way to make it simpler. It seems I really need all these tasks. Now, the problem is that every new action I try to make on the package takes a lot of time. At design time, everything is very slow. My processor is eavily loaded each time I change a single setting in one of the tasks, and executing the package in debug mode takes for ages. If I take a look at the size of my package file on disk, it's more than 3MB. Hence my question : Are there any limitations in terms of number of columns or number of tasks that can be processed within a Data Flow ? If not, then do you have any idea why it's so slow ? Thanks in advance for any answer.
View Replies !
NT Groups
Is there a way to check NT/2000 group memebership using T-SQL? I don't want to bother the network admins.
View Replies !
NT Groups
I need a bit of information about using NT groups on SQL 7.0. We've been using SQL for some time, and now we're testing a new security model for some access to the data. I've just discovered, however, that if a domain user is a member of a local group of the domain, SQL will not permit the user access. However, if I create the local group on the server itself, then SQL will grant access. Am I missing something? When I read through anything on integrated security, it says that you can use either built in users/groups or domain users/groups without a problem. Does anyone have any insight into this? Thanks for the help, Chris
View Replies !
The Number Of Requests For &&"XXXServerXXXUser&&" Has Exceeded The Maximum Number Allowed For A Single User
I have created a local user on Report Server Computer and the user has the administrative rights. When i try to connect Report Server (http://xxx.xxx.xxx.xxx/reportserver) with this user's credantials. (ReportServer directory security is set -only- to Basic Authentication. ). I get the following error. Reporting Services Error -------------------------------------------------------------------------------- The number of requests for "XXXServerXXXUser" has exceeded the maximum number allowed for a single user. -------------------------------------------------------------------------------- SQL Server Reporting Services Then i try to login using a different user with administrative rights on the machine, i can logon successfully. The system is up for a month but this problem occured today?!? What could be the problem?!?
View Replies !
Maximum Number Of Tables - Is 4^15 Big A Number?
Hello people,I might sound a little bit crazy, but is there any possibility that youcan incorporate 4^15 (1,073,741,824) tables into a SQL Database?I mean, is it possible at all? There might be a question of whereanyone would want so many tables, but i'm a bioinformatics guy and I'mtrying to deal with genomic sequences and was coming up with a newalgorithm, where the only limit is the number of tables I can put intoa Database.So, can you please advise if its possible to put in so many tables intoa SQL database? Or is the Bekerley DB better?
View Replies !
Counts By Groups
I expect to get a record below with a count of 0 (and I do), but when I take the comments out (--) of lines 1 & 6 I don't understand why I get no records at all. I need to be able to see all teams in EvalAnswers even if none of the records satisfies the where clause.1 select Count(*) as cnt--, TeamID 2 from EvalAnswers 3 where CoID=@CoID 4 and EvaluatorID=@EvaluatorID 5 and (Scr0=0 and Sugg0 is NULL) 6 --group by TeamID 7
View Replies !
File Groups
Hi folks, I have a database with only one data file with 3000 MB of size, Shows 1395 MB used and 1604 MB free in the Enterprise-Manager. Auto-grow is disabled. I am getting alert that the filegroup for the database is full(running spotlight). It shows "Unused%"=47 and "Free%"=7. What's the difference b/w "unused%" and "free%". Howdy!
View Replies !
SQL Server Groups
I am looking for the best solution in terms of how to implement SQL Server 7 logins using NT groups. It seems to me there are two ways to do this. 1. Create one local group (SQLUsers), and pull global groups in from other accounts domains. 2. Create one local group per database, which increases your NT administration. I would appreciate any advice that you have to offer. Thanks, Peter
View Replies !
Groups In SQLServer2000
Hi, I would like to create groups in SQLServer2000.Do i need to do this by roles or can i create groups for each database.I read some where that there where no groups for SQLServer7.0. Is this option available for the latest versions. Any help will be appreciated. Thanks in Advance. Ran.
View Replies !
File Groups
Hi everyone, When I do the following, did I put the files in Test1FG1 file group to the default file group(Primary) ? ALTER DATABASE Test1 MODIFY FILEGROUP Test1FG1 DEFAULT GO Thanks
View Replies !
File Groups
Hi everyone, While creating our database in only one disc(C or D), suppose that we create more than one file group in order to group our data files. However, in this situation; I wonder that whether it brings any benefit or advantage to us. Also, I wonder that why we always have to put our data file into separate file group if we use separate discs for data files. Is not it allowed to use only one file group even if we use separate dics ? Would you explain these to me ? Thanks
View Replies !
Conversation Groups
I'm having some troubles with conversation groups. I need to send two messages on the same conversation group so I have the following in my SP.... BEGIN DIALOG CONVERSATION @providerConversationHandle FROM SERVICE [ProviderDataService] TO SERVICE 'CalculatedDataService' ON CONTRACT [ProviderDataContract] WITH ENCRYPTION = OFF , LIFETIME = 600; BEGIN DIALOG CONVERSATION @curveConversationHandle FROM SERVICE [ProviderDataService] TO SERVICE 'CalculatedDataService' ON CONTRACT [ProviderDataContract] WITH RELATED_CONVERSATION = @providerConversationHandle , ENCRYPTION = OFF , LIFETIME = 600; SEND ON CONVERSATION @providerConversationHandle MESSAGE TYPE [ProviderDataMessage] ( @providerMessage ); SEND ON CONVERSATION @curveConversationHandle MESSAGE TYPE [ProviderCurveMessage] ( @curveMessage ); When I query the queue I see two messages, but they don't have the same conversation_group_id. Any ideas? Thanks.
View Replies !
Conversation Groups
I am thinking of updating my SQL monitoring application to use Service Broker. Right now I loop through my list of servers performing various checks on each server. Things like 'check last database backup', 'check for new databases', 'check for server restart'. I loop through, one server at a time, doing one check at a time. The more servers I have the longer it is taking. So, I want to multi-thread the servers, but single-thread the checks on each individual server. This way I can check say, 5 servers at a time, but on each server I will only do one check at a time. This way I won't flood an individual server with multiple checks. Is this possible? It looks like Conversation groups might be the way to go but I'm not sure.
View Replies !
Missing Groups
Server: Sql 2005 SP2 running on Win2003 Ent. SP1 I have a 3rd party app that needs me to add a user to the SQLServer2005SQLAgentUser group for the instance I placed the DB in. This group doesn't exist on my server for any of my instances. Is there a simple way to generate it or a document that can tell me how to manually create it?
View Replies !
SQL Using 2003 AD Groups
We have began to use Windows AUthentication for our SQL 2000 Server. When we llok atthe groups in AD we do not see any Domain Local groups? Is there a reason for that? Can I just add the group without browsing? I assume DLG will work with SQL for security. Thanks Brent
View Replies !
Security Groups
Hi all, Here's my problem: the company I work for is trying to allow certain groups of users to view reports. Right now it is set up so that only individual users can be given permission, one at a time. This gets tedious as dozens of people in different departments need the proper permissions to view certain reports; and, of course, people get hired, promoted, and terminated all the time, so I need a dynamic way of handling this. The current structure of our Active Directory does not facilitate this either€”there are only two extremely large groups in which all of the employees are located. Also, these groups cannot be changed. How can I create some sort of security groups, either in Reporting Services or elsewhere? I briefly read something about distribution groups in AD, does anyone know about this? The company does have Exchange groups set up for email, could this be a possible solution? Any other suggestions? Thanks.
View Replies !
Custom Groups
I'm wondering if there is any way in RS to create what Crystal reports calls Custom or Named Groups. This functionality allows you to define groups based on the values found in one of the fields on your report. For example, if I were grouping on a person's last name and I want 3 groups based on the first letter: A-G, H-P, Q-Z. I would want to keep it contained in one table.
View Replies !
Table Groups Keep Together
Is there any way to keep a table group together. MSFT says no but I can't believe this limitation exists. It's a basic reporting function in any reporting software I've used. I have one group in a table. I don't want the detail rows to be split when it gets to the bottom of the page.
View Replies !
Headers And Groups
How do I put information only at the top of a page and have it change whenever a group occurs. I want to do something like this. Have this on the top of every page: Country: current country State: current state City: current city There is a group on country, state and city. Whenever city changes the next page will display a new city. Whenever state changes the next page will display the new state in the header. I have already put Country, State, and City into their own group headers. However, if either of them change in the middle of the page then the group header repeats. I have group header repeat on so I can see it on each page. If I choose the Page break at start then it breaks but I don't want it to break. I want it to run on the same page if it passes a group and then have the next page display relevant information for the group. Putting it in the table header works as far as keeping them all on the top of the page but it doesn't change when a grouping ends. Please assist me in anyway you can. Thanks, Sarah
View Replies !
Top Count For Groups Possible
Hello, in a report in Reporting Services 2005 I want to create a top count 100 for groups. I have a report sheet with a table containing some groups. Under the groupings there are other groupings and so on. Now I want that only the top 100, ordered by a value on group level of the 1st group are contained in the report. Any ideas? Best regards, Stefoon
View Replies !
Totals By Groups
I am trying to group my data by department then by district. Then Total each group then total the groups together in one Table???
View Replies !
Totals By Groups
I have Reporting Services 2000. I have a SQL that return records that I want to group by Department then District. Then total the Departments Total. Then get a Total by District. Then total both groups together Michael Webb
View Replies !
Getting Sum Of Certain Groups Using Inscope
In my report I have 3 row groups and two column groups as follows, where measure is number of hours for a given project. 2008/05 2008/06 ........ Demand | Allocated Demand | Allocated - Project 1 1500 1000 - Department1 500 400 - Employee1 200 - Employee2 200 - Department2 1000 600 - Employee3 300 - Employee4 300 + Project 2 1200 1000 ------------------------------------------------------------------------------------------------------------------------- Grand Total 2700 2000 My question is, is this possible using a Matrix? I have used matrix in some of my reports but have always used automatic/generic SUM feature it provides. Note carefully that there are no values for Employees in Demand Column Group. Basically I would have to SUM Department groupings to get the Project level total and then SUM all Project Groupings to get the grand total. The Allocation column is okay since the Employees have values in that column. Can this be done using InScope( ) or some other function rather than simply doing the Generic SUM of Rows and Columns of the matrix which would probably throw error when trying to sum a blank value in demand column. Thanks in advance.
View Replies !
Ceilings On Groups
Hi All, I have a challenging report to create. I need to create a report where I'll have groups then I need to have a ceiling on the group to print only 10 records (if there are more than 10 records) then go to the next page and print the final records for that group. I can do the grouping and I can do the ceiling, but I am having a hard time doing both. I'm open for any suggestions. Thanks
View Replies !
Hiding Groups
I know how to hide groups using Expressions that use parameters that can be passed in, but can you hide groups that are inside other groups. Here is my situation. Our company has different organization levels. On reports the users want to select which organization levels they want to see the #s for. So I set up groups for each org level like so.... Org Level 1 Org Level 2 Org Level 3 The data results are like so Org Level 1 Org Level 2 Org Level 3 Category $ Amount USA Midwest Kansas 1 500.00 USA Northeast Maine 1 200.00 And I sum the $ Amount Per Category for each Org Level using grouping in the report. Then based on the selections they make it hides the org levels they don't need to see. The issue is that the report will not show org level 2 or org level 3 if org level 1 is not visible, and org level 3 can't be visible if org level 2 isn't visible, etc.....Anyone know a solution to this situation, I know this has had to have come up. Thanks, Adam
View Replies !
Filter Top N Groups
Using: SSRS 2005, SSAS 2005Question: Can we use Filter Top N on Groups? Detail:I have a table in RS with data sourced from an AS cube that has a structure like this: Category Date Quantity ======== ======== ======== + Cat1 2008-01-01 3,000 2008-01-02 5,000 2008-01-03 7,000 2008-01-04 9,000 2008-01-05 4,000 2008-01-06 2,000 2008-01-07 5,000 Cat1Total 35,000 + Cat2 2008-01-01 2,000 2008-01-02 5,000 2008-01-03 6,000 2008-01-04 8,000 2008-01-05 3,000 2008-01-06 1,000 2008-01-07 5,000 Cat2Total 30,000 + Cat n... I want to return only the top 10 categories by descending quantity. I can't get the filter to work on either the table or group level properties. FILTER:Expression: =sum(Fields!Quantity.Value)Operator: TopNValue: 10ERROR:Failed to evaluate the FilterValue Do the Top and Bottom functions only work on the detail level? Is there some other method I should be using? I've tried to do this with the query using the MDX AGGREGATE function but having trouble getting it to return proper results. (Details in this thread: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3203246&SiteID=17 ) So as a workaround to get the project delivered on time, I'm reluctantly bringing in the date level and trying to do the aggregations and filtering in the RS table. Any help would be appreciated. -Michael
View Replies !
Graph In Groups Of 10
I have some data that I want to represent in a bar chart but since there are some 100 different types I need to limit the bar chart to groups of say 10. this should give me some 10 different graphs, any suggestions kam
View Replies !
Groups Question
I have my first SSRS report going, after years on Crystal and Access. It has several groups, each nested within the next...I think. I'm having a little trouble with the groups...well, grouping the way I thought I had it set up. The groups come out looking like my primary group is second instead of first, although when I look at the groups in "edit groups" it appears I have them set up correctly. In Crystal, on the preview page you could see the groups idendified so if there was a problem in a report you could tell what group it was coming from. You could also select and change objects in preview, which made it nice to see the impact of changes. I cannot see either of these features in SSRS. I'm hoping that there's a setting I need to turn on, and also hoping that this product has these user friendly features. Any ideas on that? Also, in Crystal, when you set up a group or a formula, it became available in the field list within the formula builder, so you could use them by picking them instead of typing the whole thing in again, and that's not available either. Or I can't find it! Since I'm new to SSRS maybe there are features and settings I haven't found yet, but would appreciate any comments from more experienced users about the overall usability of this tool. Thanks!
View Replies !
Sorting Groups
I am having a bit of trouble with the sorting of grouped data. I have a data set that returns sales data for stock items between a user specified date range. As a result I have to group by the stock items (description or code depending on a parameter value) to prevent displaying duplicate stock items and to get aggregate sales data. However I also need to be able to sort this group by three other aggregates but have not been successful. I have done the following: The group expression is as follows: Function Group4(fields As Fields) As String Dim Group As String If( Report.Parameters!ReportSetting_ReportType.Value <> "1" AndAlso Report.Parameters!ReportSetting_DetailSummary.Value = "1" ) Then Group = fields!Group2Desc.Value Else If Report.Parameters!ReportSetting_SortType.Value = "1" Then Group = fields!StockDesc.Value Else Group = fields!StockCode.Value End If End If Return Group End Function The sort expression for this group is as follows: =Sum(Code.SortExpressionNumeric(Fields), "table1_Group4") where SortExpressionNumeric is as follows: Function SortExpressionNumeric(fields As Fields) As Decimal Select Case Report.Parameters!ReportSetting_SortType.Value Case "3" Return fields!TillSalesValue.Value Case "4" Return fields!TillSalesQty.Value Case "5" Return GrossProfit(fields) Case Else Return 0 End Select End Function where GrossProfit is another function that returns a decimal value. When I preview my report with ReportSetting_SortType = 1 or 2 everything displays as expected. However when I set ReportSetting_SortType = 3,4 or 5 the data does not sort how I need it to. Can anybody see what I might be doing wrong?
View Replies !
SQL Server Groups
Is it possible that i can create a SQL Server Group in sql 2005. Eg. in 2K when u right click (in Enterprise manager) on Microsoft Sql servers you can see "New Sql server Group". I am talking about this group. I have many servers in NYC and Dallas and in VA. I want to group them in the group names NYC DAL VA. is it possible.
View Replies !
File Groups
I am looking to find out when to use file groups when backing up. When should you use this, what's the benefit over just doing a full db backup? Is it better when you are dealing with large db's? Also this question has been on my mind for a while. Why shouldn't you shrink the db after every full backup? What is the negative in doing so? Thanks
View Replies !
Field LIKE Number,number,number
I am testing something in Visual Basic that talks to a database and I want to filter results by -> field1 like "###". However, that 'like' and '#' is VB syntax. How do you say that in SQL?
View Replies !
Counting And Displaying Groups
Lets suppose I have a db which looks like:District FundVAE AVAW AVAE BDC CDC ANYS BMD CVAW AI would like a query which displays the following dynamically. I don't know each unique member of the Fund field, they could be added by the end user. It might include M,J,S etc. I need the columns dynamically created. I can do it statically if I know the distinct members of fund. I would like to be able to do it dynamically. Any ideas? The static version with multiple queries is at the end.District Total A B CVAE 2 1 1 VAW 2 2 DC 3 2 1 NYS 1 1 MD 1 1 Query Name =GroupByDistSELECT district, COUNT(district) AS dcountFROM dbo.mydbGROUP BY districtQuery Name=DCountASELECT district, COUNT(district) AS dcountFROM dbo.mydbWHERE (fund = N'A')GROUP BY district Query Name=DCountBSELECT district, COUNT(district) AS dcountFROM dbo.mydbWHERE (fund = N'B')GROUP BY district Query Name=DCountCSELECT district, COUNT(district) AS dcountFROM dbo.mydbWHERE (fund = N'C')GROUP BY district SELECT GroupByDist.district, GroupByDist.dcount AS Total, DCountA.dcount AS A, DCountB.dcount AS B, DCountC.dcount AS C From GroupByDist LEFT OUTER JOIN DCountC ON GroupByDist.district = DCountC.district LEFT OUTER JOIN DCountB ON GroupByDist.district = DCountB.district LEFT OUTER JOIN DCountA ON GroupByDist.district = DCountA.district
View Replies !
Indexes And File Groups
Something strange.I have a database(SQL2000) with two file group(on seperate physicaldrives).One is meant for table data[PRIMARY] and one for indexes [INDEX].So i create a table on the [PRIMARY] file group, and fill indata.Next I build a clustered index on the table, on the [INDEX] filegroup.Once the index is built, the database now indicates that the filegroupfor the table [INDEX]! and not [PRIMARY] as i originally set it up for!My question it then: Has the table been moved or is this somehow anerror in SQL server?I would really appreciate any thought anyone might have on this?
View Replies !
|
TRACKER
