I've got a report built and I'm trying to figure out how sorting and grouping works. I can group the report by Patient, Albumin and it groups as I would expect.
Patient Date Albumin
Adams, John 01/28/2007 4.1
Adams, John 12/30/2007 3.9
Adams, John 01/15/2007 3.2
Barker, Mark 01/18/2007 4.3
Barker, Mark 01/22/2007 4.1
Barker, Mark 01/05/2007 3.9
However, when I try to group by Albumin, Patient, it just sorts by Albumin.
Patient Date Albumin
Barker, Mark 01/18/2007 4.3
Adams, John 01/28/2007 4.1
Barker, Mark 01/22/2007 4.1
Adams, John 12/30/2007 3.9
Barker, Mark 01/05/2007 3.9
Adams, John 01/15/2007 3.2
What I'm looking for is this:
Patient Date Albumin
Barker, Mark 01/18/2007 4.3
Barker, Mark 01/22/2007 4.1
Barker, Mark 01/05/2007 3.9
Adams, John 01/28/2007 4.1
Adams, John 12/30/2007 3.9
Adams, John 01/15/2007 3.2
Is this something that can be done with grouping and sorting?
I am developing a database of soccer transfers. The transfer fee field contains numerical values but also 'u/d' to signify a transfer in which the fee has not been disclosed and 'loan' to signify a loan move. Is it possible to perform calculations on fields containing non numerical data? e.g. could I run a query that replaced u/d and loan with 0 for the purposes of performing calculations or should I remove all non numerical values from the fee field?
I am preparing the data prior to importing it into SQL but you can view the data structure here :
I have a report where I am giving the users a parameter so that they can select which field they would like to sort on.The report is also grouping by that field. I have a gruping section, where i have added code to group on the field I want based on this parameter, however I also would like to changing the sorting order but I checked around and I did not find any info.
So here is my example. I am showing sales order info.The user can sort and group by SalesPerson or Customer. Right now, I have code on my dataset to sort by SalesPerson Code and Order No.So far the grouping workds, however the sorting does not.
Is there a way to conditionally turn off a group? where would that be? I dont want it to group and sort the data in anyway? is there a way to force the grouping order (not as ascending/descending but your own stated order without doing that in the proc it calls? Thanks!
I have an interesting challenge. Imagine I own a pizza restaurant and I have delivery drivers (4). I also have a fixed number of delivery Routes for my restaurant and these routes have varying number of households on the them. I want to sort and assign the routes as evenly as I can to the drivers.
Drivers: ------------------------- 1 - Bob 2 - John 3 - Ricky Bobby 4 - Batman
What I'm TRYING to is write a Stored Proc to loop though the records (routes) and assign them to the drivers as evenly as possible based on households (staying with whole integers).
I've tried a couple of different concepts but keep hitting 'writers block'.
When the autogenerate property of datagrid is set to true, how to perform sorting on date fields.
Example:
SELECT DC_NO AS [Document No],CONVERT(CHAR(10),DC_DT,103) AS [Document Date], [Name] FROM XYZ
Here we convert the date field to the format "dd/mm/yyyy" so when sorting is done it is done in the format "dd/mm/yyyy". But instead sorting should be done in "yyyy/mm/dd" format.
I am having an issue which I'm guessing everyone has at one point. I just can't seem to find the answer. I might be misunderstanding something as I'm relatively new to SQL and data types etc.
Here is my issue.
I have a table with a column that has a nvarchar data type. The following is an example of field values in that column.
asystem1 zsystem19 zsystem9 bsystem23 bsystem3
I would like to sort that data alphabetically and then by number like so.
asystem1 bsystem3 bsystem23 zsystem9 zsystem19
A basic ORDER BY obviously will sort it alphabetically but will put 23 before 3. These system names are already named and can't be changed otherwise I would use "0" in front of single digit numbers.
I just can't figure out how to accomplish this. Is there a convert or cast (unfamiliar with those functions) that I can do or does it involve something more sophisticated?
I have a field in my database which stores product codes. This fieldis a varchar. Some users use characters in their codes but most justuse numbers. Whenever I go to sort the products by code the order ofthe numbers go something like: 1, 100, 101, 2, 210, 220, 2500, 3, ...I understand why this is sorted this way but is there anyway I canhave it sort the numbers in their proper numeric order (1,2,3,100,....) and just have them always appear before any codes that containcharacters? Right now I just have users put zeros in front of theirnumbers like: 0001, 0100, 0101, 0002, ...
What I require is to group the Codes and sum the Times for each code based on the text value of the code.
Code: CODE DATE1 HOURS2 DATE2 HOURS2 AAA 01.01.2015 1:25 02.01.2015 0:15 BBB 01.01.2015 0:55 02.01.2015 0:10 CCC 01.01.2015 1:15 02.01.2015 1:15 DDD 01.01.2015 0:00 02.01.2015 1:00 The query will alway be run for a week so DATE1-DATE7 and HOURS1-HOURS7 required.
AAA BBB CCC etc can be of multiple options so these can't be hard coded. eg there could be a ZZZ or ZAC etc.
hi!!!would appreciate if somebody could let me know the query/algoritham tosort the following values in the column either in asc or descthe values in the column originally11-1/21-1/41/2101214161822-1/22033/44568the sorted result should be as follows1/23/411-1/41-1/222-1/234568101214161820TIA, have a great dayregardsbala
I have the column of type string in the database Following is the data in that column 1 2 11 12 21 abc If i sort the table with the help of this column then the output come in the following manner 1 11 12 2 21 abc
can i improve the order actually i want the output like 1 2 11 12 21 abc
I am looking for an expression for a group in a matric. I am trying to figure out how to group by the a certain amount of letters in a string. For example if I have the followong fields I am grouping...
Bob001
Bob
Robert005
Doug053
Doug100
Douglas
Barney001
Frank
I want to group it up as...
Bob
Doug
Barney
Frank
And then be able to summarize the results in the matrix.
I have a sql server table column that can contain the following possible values. 1. 766/IT 2. 777/HR3. 890/EG4. 012/AS5. Trainee6. Contractor 7. Others I want to write an SQL query grouped by this column, Trainee should be one group, Contractor should be another different group, Others should be another different group and then every thing else ( 766/IT ,777/HR ,890/EG, 012/AS) should be grouped together as one group. Think of it in terms of a pie chart with those groups.
I have one of fileds Field1 one table Table1 which contains either numerical ID or alphabetic ID or both, how to write query to get max numerical ID? The simpler the query, the better. Thanks!
KRFeb 6, 1:48 pm show optionsNewsgroups: microsoft.public.access.formsFrom: "KR" <kra...@bastyr.edu> - Find messages by this authorDate: 6 Feb 2006 13:48:00 -0800Subject: Extract Number from Fields - SQLReply | Reply to Author | Forward | Print | Individual Message | Showoriginal | Remove | Report AbuseI am new to the SQL world, and I am trying to come up with a scriptthat will extract only the numerical data from a column of varchardata type . There is not a pattern to the data entered, except thatthe data thatI am looking to extract is a three digit number. If someone couldpoint me in the right direction that would be great.Thanks in advanceKR
SSRS 2012 - VS2010...The report compares two years with a sort order on a value that has been engineered based on text switched to int. When sorting A-Z this is the result in the horizontal axis is: 5th, K, 1st, 2nd, 3rd, 4th, 5th..When sorting Z-A the result in the horizontal axis is:5th, 4th, 3rd, 2nd, 1st, PreK..Z-A is correct but A-Z sorting shows 5th as the start and end. The magnitude of the PreK location is correct but the label is wrong on the A-Z sort order. The sorting is implemented using the Category Group sorting option.
I have a table with a startdatetime and an enddatetime column such as:StartDateTime EndDateTime what I want to see returnedis:01/29/2004 10:30AM 01/29/2004 1:30PM "1/29/2004 10:30AM - 1:30PM"01/29/2004 10:30AM 01/30/2004 1:30PM "1/29/2004 10:30AM - 1/30/20041:30PM"01/29/2004 10:30AM 01/30/2004 10:30AM "1/29/2004 10:30AM - 1/30/200410:30AM"Maybe someone has accomplished this aready in a stored procedure andhas an example of how to do it?lq
greetings!! the below trigger works fine in SQL 2000 and doesn't takeup much resources, it's a very simple solution to trim text fields atinput in SQL. I know a lot of folks will say to create input masks atthe UI level and that SQL is a restricted back-end DB, but to be quitehonest I don't trust those UI guys and as a DBA I will have to cleanthe mess when they forget to validate. Hope it can be usefull to anyof you too. ;)----------------------------------------------------------------------CREATE TRIGGER [TRIMMER_TGR] ON [dbo].[MyTable]AFTER INSERT, UPDATEASIF UPDATE (MyStringField)DECLARE @TRIMMEDFIELD NVARCHAR(50)DECLARE @MYID INTSELECT @TRIMMEDFIELD = MyStringField from InsertedSELECT @MYID = ID from InsertedBEGINUPDATE tblDocket SET MyStringField = RTRIM(LTRIM(@TRIMMEDFIELD))WHERE ID = @MYIDEND
Hi All,I'm familiar with the syntax that looks like this:SELECT * FROM Users WHERE Email LIKE '%aol%'Which would return all users that whose Email column contains "aol".However, if I wanted to do that sort of a search using a prepared statement, how would I do it?I can't simply doSELECT * FROM Users WHERE Email LIKE '%@MatchAgainst%'Can somebody clue me in?
I have a field that will look something like this. I need to read a file and when the first 3 positions are REM, the number following is the Order number, and the initials, are the rep. this is how they are tracking for the rep, if they sent reminders to customers and when.
REM 0482348 LV 04/13
I would like to do this in SQL and also join to the order header file that will give me the invoice total for that order. what is the best approach here via SQL for these first 2 fields' needed?
Hi I need to export data to CSV file from Sql Reporting Services. I am including single quotations in my view to display in SRS for string fields. After export if we open in Notepad, for string field it is adding multiple quotations.
How to add single quotations for string fields - CSV files,
Hello all,I'm at a loss on how to do this. We're using MS SQL 2000 Server and Ihave a list of fields I need to find the first and last entry for.Here's an example of the table:Number - VarChar(10)Jan - IntFeb - IntMar - IntApr - IntMay - IntJune - IntANd it'll look something like this:NumberJanFebMarAprMayJun12322001901922012032054432433322 4565423754694665And I need to create a table with this:NumberFirstLastDifference123220020554432433456235423754665-89I'm not sure if this'll copy over correctly, but I have gaps in thedata so I can't just say Jun-Jan, but I need tofind the first fieldwith data and last field with data, then find the difference of these.Suggestions? Is there a loop or something I can do in TSQL that'll dothis? I'd like to do this in Query Analyzer since it's just a one-timereport. Thanks --Alex
0 AS SalaryMin, 2088 AS SalaryMax, 2088 AS BillableHours, 'Month' AS SalaryPaidCode, 0 AS SalaryBreakdownHourly, 0 AS SalaryBreakdownDaily,
[Code] ...
While outputting to CSV.file
I got :0,2088,2088,"Month",0,0,0,0,0,0,0,"N/A","N/A","G","N/A","Exempt","Other",1
How can I remove all double quotes in the string fields? so that O can get the result as below while the output 0,2088,2088,Month,0,0,0,0,0,0,0,N/A,N/A,G,N/A,Exempt,Other,1
I have a trigger that writes changes to an audit table. In the case of Updates, I only want to write out the fields that have actually changed. My code for COLUMNS_UPDATED evaluates to true for all varchar fields, even when they haven't changed. All other scenarios appear to be working correctly.
WHILE @field < @maxfield BEGIN SELECT @field = min(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND ORDINAL_POSITION > @field SELECT @bit = (@field - 1 )% 8 + 1 SELECT @bit = power(2,@bit - 1) SELECT @char = ((@field - 1) / 8) + 1
IF ((@TriggerType = 'I') OR (@TriggerType = 'D') OR (@TriggerType = 'U' AND substring (COLUMNS_UPDATED(),@char, 1) & @bit > 0)) BEGIN..........
Im trying to determine the best way to store data gathered from a form that a user will fill out online. The form is dynamic and is customized at run time based on group-specific criteria. The end result is a form that might have 3 extra text boxes, 2 extra sets of radio buttons and a freeform textbox, whereas for another group, there might be a slightly different set of input fields. Now comes the issue of storing this data. Since the fields can be somewhat dynamic, it could get tricky to define table columns for each possible input field. So Im considering storing the data as xml. Has anyone else had to build custom forms and ended up storing the data as xml ?
I'm really stumped on this one. I'm a self taught SQL guy, so there is probobly something I'm overlooking.
I'm trying to get information like this in to a report:
WO# -WO Line # --(Details) --Work Order Line Detail #1 --Work Order Line Detail #2 --Work Order Line Detail #3 --Work Order Line Detail #etc --(Parts) --Work Order Line Parts #1 --Work Order Line Parts #2 --Work Order Line Detail #etc WO# -WO Line # --(Details) --Work Order Line Detail #1 --Work Order Line Detail #2 --Work Order Line Detail #3 --Work Order Line Detail #etc --(Parts) --Work Order Line Parts #1 --Work Order Line Parts #2 --Work Order Line Parts #etc
I'm unable to get the grouping right on this. Since the line details and line parts both are children of the line #, how do you do "parallel groups"?
There are 4 tables:
Work Order Header Work Order Line Work Order Line Details Work Order Line Requisitions
The Header has a unique PK. The Line uses the Header and a Line # as foreign keys that together are unique. The Detail and requisition tables use the header and line #'s in addition to their own line number foreign keys. My queries ends up looking like this:
It probobly isn't best practice, but I'm kinda new so I need some guidance. I'd really appreciate any help! Here's my query:
SELECT [Work Order Header].No_ AS WO_No, [Work Order Line].[Line No_] AS WOL_No, [Work Order Requisition].[Line No_] AS WOLR_No, [Work Order Line Detail].[Line No_] AS WOLD_No FROM [Work Order Header] LEFT OUTER JOIN [Work Order Line] ON [Work Order Header].No_ = [Work Order Line].[Work Order No_] LEFT OUTER JOIN [Work Order Line Detail] ON [Work Order Line].[Work Order No_] = [Work Order Line Detail].[Work Order No_] AND [Work Order Line].[Line No_] = [Work Order Line Detail].[Work Order Line No_] LEFT OUTER JOIN [Work Order Requisition] ON [Work Order Line].[Work Order No_] = [Work Order Requisition].[Work Order No_] AND [Work Order Line].[Line No_] = [Work Order Requisition].[Work Order Line No_]
Hi! Need help with this one: I have a column with a string composed by several data. After using REPLACE several times, I get something like the data below, which has (in most of cases) a value and a date.
378 9/05
388 9/05
4/05
1/06 606
1/06 646
76 5/05
100 1/05
118 8/05
129 8/05
9/05 342
05/3 123
1/07
4/06 164 The problem is that I need to get each value alone (to separate columns), in example: Value Date 378 09/2005 388 09/2005 0 04/2005 ... 606 01/2006
and so on... In addittion you can see that sometimes the Value come first or alone, and sometimes the Date come first or alone.
I will appreciate any good ideas, Thanks in advance, Aldo.
I have just started using SQL Server reporting services and am stuck with creating subreports.
I have a added a sub report to the main report. When I right click on the sub report, go to properties -> Parameters, and click on the dropdown for Parameter Value, I see all Sum and Count fields but not the data fields.
For example, In the dropdownlist for the Parameter value, I see Sum(Fields!TASK_ID.Value, "AppTest"), Count(Fields!TASK_NAME.Value, "CammpTest") but not Fields!TASK_NAME.Value, Fields!TASK_ID.Value which are the fields retrieved from the dataset assigned to the subreport.
When I manually change the parameter value to Fields!TASK_ID.Value, and try to preview the report, I get Error: Subreport could not be shown. I have no idea what the underlying issue is but am guessing that it's because the field - Fields!TASK_ID.Value is not in the dropdown but am trying to link the main report and sub report with this field.
Am I missing something here? Any help is appreciated.
