Grouping Sorting String And Numerical Fields

Feb 6, 2007

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?

Thanks,
Chad

View 1 Replies


ADVERTISEMENT

Calculations On Fields Containing Non-Numerical Data?

Feb 29, 2012

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 :

Premier League Transfers Winter 2012

View 14 Replies View Related

Sorting And Grouping Question By Allowing Users To Select The Sorting Field

Feb 11, 2007

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.



Any suggestions would help.


Thanks

View 1 Replies View Related

Check String Or Numerical

Feb 7, 2008

When I load data from excel file, how I can check is data string or numerical ?

View 1 Replies View Related

Grouping/sorting

Oct 11, 2007



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!

View 2 Replies View Related

SQL 2012 :: Grouping And Sorting Challenge

Aug 15, 2014

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.

My route data my look like this:

[RouteID]_[RouteName]_[HouseHolds]___[DriverID]
-----------------------------------------------------
1________Route01_____700__________0
2________Route02_____210__________0
3________Route03_____111__________0
4________Route04_____452__________0
5________Route05_____236__________0
6________Route06_____111__________0
7________Route07_____300__________0
8________Route08_____421__________0
9________Route09_____1200_________0
10_______Route10______525_________0

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'.

View 9 Replies View Related

Sorting Of Date Fields

Dec 27, 2004

hi,

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.

View 6 Replies View Related

Sorting Nvarchar Fields

Jun 6, 2007

Hi all,

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 appreciate any help in advance.

Israel

View 6 Replies View Related

Sorting Mixed Type Fields...

Jul 20, 2005

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, ...

View 4 Replies View Related

Grouping Of Fields

Jan 26, 2015

I have a 3rd Party Table with the following fields

Code:
date Code1 Time1 Code2 Time2 Code3 Time3 Code4 Time4 Code5 Time5.
01.01.2015 AAA 0:15 CCC 1:00 AAA 0:10 BBB 0:15
01.01.2015 BBB 0:10 AAA 1:00 EEE 0:10 BBB 0:30 CCC 0:15
02.01.2015 CCC 0:15 CCC 1:00 BBB 0:10 DDD 1:00 AAA 0:15

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.

View 3 Replies View Related

Summing And Grouping Numeric Fields

Nov 6, 2006

Hello again

How do I sum up and group numeric values into one output column in SSIS?

I have data that looks like this:

Refno date credit amount

11675 1987-04-23 7.03 13501.27


and I want to add the credit and amount columns together and output them as one column, In T-SQL, i'd do this:

SELECT Refno, SUM(Credit + Amount)

FROM Transfer

GROUP BY Refno, Date

I could add this SQL into my SSIS package easily enough but I wan't to learn how to do it the SSIS way







View 4 Replies View Related

Pulling Data From Multiple Fields And Grouping

Jun 3, 2014

I have a table of Projects which have multiple Resources.

PROJ_ID, PROJ_NAME,RESOURCE1,RESOURCE2,RESOURCE3
01 Project1 001 005 088
02 Project2 002 004 005

How can I pull out a list of resources with the projects associated with them?

i.e. the above would return

001 01
002 02
004 02
005 01
005 02
008 01

or

001 01
002 02
004 02
005 01,02
008 01

View 10 Replies View Related

Sorting String

Jul 20, 2005

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

View 4 Replies View Related

Sorting Of A String Column

Mar 7, 2008

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

View 2 Replies View Related

Grouping By Partial String

Jun 12, 2007

Hello All,



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.



Thanks in advance for any help

-Clint

View 13 Replies View Related

Conditionally Grouping Different String Values Together

Jan 29, 2008

 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. 

View 4 Replies View Related

How To Just Get Max Numerical ID For This Case?

Jan 18, 2006

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!
 

View 1 Replies View Related

Searching/Estracting Numerical Data

Feb 6, 2006

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

View 5 Replies View Related

Reporting Services :: Horizontal Axis Show Last Value In First And Last Space When Sorting A-z But Shows Correctly When Sorting Z-a

Jul 10, 2015

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.

View 6 Replies View Related

Finding Fields In String

Apr 28, 2006

I have data looks like below

23-4c-4f-3rd-2
23-5c-4ad-3-2

THis is all in one field. I need to use sql to take out the data between the 1st and third hyphens to return

4c-3rd
5c-4ad

View 2 Replies View Related

Search And Update All Fields That Contains String

Jan 8, 2008

I hava at large database and I need to search all tables for the string 'NaN' and replace it to null to enable conversion.

Is there any way to do this so that i do not need to write an update query for every column?

Would appretiate an example if anyone knows how to manage this.

Thanks!

View 2 Replies View Related

Creating A String From Date Fields

Jul 20, 2005

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

View 2 Replies View Related

SQL Trigger For Trimming String Fields

Jul 20, 2005

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

View 6 Replies View Related

How To Search For Fields Containing A Specific Text String

May 26, 2006

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?

View 1 Replies View Related

Read Parts Of Unformatted Fields String

Apr 23, 2014

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?

View 1 Replies View Related

CSV Export With Single Quotations For String Fields

Sep 20, 2007

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,

Please let me know if anybody knows.

Thx
Vijji

View 4 Replies View Related

Help With SQL Statement To Find First And Last Populated Field In String Of Fields

Jun 21, 2006

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

View 2 Replies View Related

SQL Server 2008 :: Remove Double Quote In String Fields

Feb 23, 2015

I have a SQL select syntax as below

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

View 7 Replies View Related

Update Trigger: COLUMNS_UPDATED Evaluates To True For All String Fields

Jul 14, 2006

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..........

END

END

Ideas? Thanks!

View 1 Replies View Related

Contact Form - Variable Number Of Input Fields, Store Data As Xml String ?

Jul 13, 2007

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 ?

View 2 Replies View Related

Query Or Grouping Problem (some Kind Of Parallel Grouping?)

Nov 26, 2007

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:

WO WOL WOLR WOLD
226952 10000 10000 10000
226952 10000 10000 20000
226952 10000 10000 30000
226952 10000 10000 40000
226952 10000 20000 10000
226952 10000 20000 20000
226952 10000 20000 30000
226952 10000 20000 40000
399999 10000 NULL 10000
375654 10000 10000 NULL
etc


Hierarchy:
WO > WOL > WOLD
WO > WOL > WOLR

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_]

View 1 Replies View Related

TSQL - Trim String Containing Both Data And Value Into 2 Separate Data Fields

Aug 20, 2007

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.


View 3 Replies View Related

Subreports: Parameter Value Dropdown Shows Sum And Count Fields But Not The Actual Data Fields.

Jan 28, 2008


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.

Thanks,
Sirisha

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved