Grouping Numbers

Mar 4, 2007

I have a table which lists player names, teams played for and the
years they played there and my code looks like this

SELECT AlsoPlayedFor.playerID, AlsoPlayedFor.teamID,
AlsoPlayedFor.TeamName, Min([AlsoPlayedFor].[Year]) & "-" &
Max([AlsoPlayedFor].[Year]) AS [Year]
FROM AlsoPlayedFor
GROUP BY AlsoPlayedFor.playerID, AlsoPlayedFor.teamID,
AlsoPlayedFor.TeamName;

which takes the Min year and the Max Year and displays it like "Year-
Year"

But lets say for example the player played for 5 years so it 1990,
1991, 1992, 1993, 1995

It would display as 1990-1995 but I want it to display as 1990-1993,
1995, is this possiable??? Also I need it to gothe other wayso if the
years are 1990, 1992, 1993, 1994, 1995 I want that to display as 1990,
1992-1995.

PLEASE HELP

View 5 Replies


ADVERTISEMENT

Grouping Or SUM Of Numbers

Apr 12, 2006

Hi All,

After working out the basics and getting a report to give me back rows based upon a dropdown list, i know want to ask how do i show the sum of values for a customer.

So the out put would look something like this

Customer Total
0001 12234.56
0002 232.98
0003 456.78

I have two tables "customer" and "trades" i all ready have them linked and giving me all the individual items, oh and i have got it doing BETWEEN to dates, but i would really like to know how to show just the TOTAL trades for each cutomer on each line not every single trade for the customer.

Many thanks for any help with this

Dave C :confused:

View 1 Replies View Related

Formatting Numbers In A Mixed Column (numbers In Some Cells Strings In Other Cells) In Excel As Numbers

Feb 1, 2007

I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.

I already tried to set the value as CDbl which returns error for the cells containing a string.

The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.

Any suggestions?



View 1 Replies View Related

Query Analyzer Shows Negative Numbers As Positive Numbers

Jul 20, 2005

Why does M$ Query Analyzer display all numbers as positive, no matterwhether they are truly positive or negative ?I am having to cast each column to varchar to find out if there areany negative numbers being hidden from me :(I tried checking Tools/Options/Connections/Use Regional Settings bothon and off, stopping and restarting M$ Query Analyer in betwixt, butno improvement.Am I missing some other option somewhere ?

View 7 Replies View Related

I Need To Update A Table With Random Numbers Or Sequential Numbers

Mar 11, 2008



I have a table with a column ID of ContentID. The ID in that column is all NULLs. I need a way to change those nulls to a number. It does not matter what type of number it is as long as they are different. Can someone point me somewhere with a piece of T-SQL that I could use to do that. There are over 24000 rows so cursor change will not be very efficient.

Thanks for any help

View 6 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

Generate List Of All Numbers (numbers Not In Use)

Feb 21, 2007

I have an 'ID' column. I'm up to about ID number 40000, but not all are in use, so ID 4354 might not be in any row. I want a list of all numbers which aren't in use. I want to write something like this:

select [numbers from 0 to 40000] where <number> not in (select distinct id from mytable)


but don't know how. Any clues?

View 1 Replies View Related

Dataflow To Excel - Convert Numbers Stored As Text To Numbers Excel Cell Error

Mar 27, 2007

I'm trying to write data to excel from an ssis component to a excel destination.

Even thought I'm writing numerics, every cell gets this error with a green tag:

Convert numbers stored as text to numbers

Excel Cells were all pre-formated to accounting 2 decimal, and if i manually type the exact data Im sending it formats just fine.

I'm hearing this a common problem -

On another project I was able to find a workaround for the web based version of excel, by writing this to the top of the file:

<style>.text { mso-number-format:@; } </style>

is there anything I can pre-set in excel (cells are already formated) or write to my file so that numerics are seen as numerics and not text.

Maybe some setting in my write drivers - using sql servers excel destination.


So close.. Thanks for any help or information.

View 1 Replies View Related

Even And Odd Numbers

Mar 30, 2004

I need to figure out how to pull records if the id is even or odd.

ie

ID Name
1 bill
2 john
3 joe
4 jane

for example i need a idea of a select statement that will pull the even records 2 and 4.

is this possible?

View 2 Replies View Related

Numbers

Feb 25, 2008

Hello,

I have the following:

insert into dbo.Categories (CategoryID, [Name])
select
newid(),
'Category ' + right('000' + convert(varchar(3), n + 1), 3)
from @Numbers

Numbers is a table with 1000 numbers.
How can I create only 10 records in Categories?
I need to restrict the number of @Numbers used.

Thanks,
Miguel

View 7 Replies View Related

Numbers Less Than Zero

Mar 1, 2007

In a pretty standard select statement (as shown), i want to return 0 when "dbo.v_AgentOrderTotals.Total - dbo.v_AgentAmmountPaid.total - dbo.v_AgentCommClean.total AS amount_outstanding_commission" is less than 0.

SELECT     dbo.t_Agents.agent_code, dbo.v_CurrentParamPaymentTotal.ammount AS weekley_payment_total,
                      dbo.v_AgentNumberOfCustomers.count AS number_of_cust, dbo.v_AgentAmmountPaid.total AS total_paid,
                      dbo.v_AgentOrderTotals.Total AS ytd_order_total, dbo.v_AgentOrderTotals.Total - dbo.v_AgentAmmountPaid.total AS amount_outstanding,
                      ISNULL(dbo.v_AgentAmmountPaid.total / dbo.v_AgentOrderTotals.Total, 0) * 100 AS ytd_percentage,
                      dbo.v_AgentOrderTotals.Total - dbo.v_AgentAmmountPaid.total - dbo.v_AgentCommClean.total AS amount_outstanding_commission,
                      ISNULL(dbo.v_AgentOrderChange.amount, 0) AS net_weekly_order
FROM         dbo.t_Agents LEFT OUTER JOIN
                      dbo.v_AgentOrderChange ON dbo.t_Agents.AGENT_ID = dbo.v_AgentOrderChange.AGENT_ID LEFT OUTER JOIN
                      dbo.v_AgentCommClean ON dbo.t_Agents.AGENT_ID = dbo.v_AgentCommClean.AGENT_ID LEFT OUTER JOIN
                      dbo.v_AgentNumberOfCustomers ON dbo.t_Agents.AGENT_ID = dbo.v_AgentNumberOfCustomers.AGENT_ID LEFT OUTER JOIN
                      dbo.v_AgentOrderTotals ON dbo.t_Agents.AGENT_ID = dbo.v_AgentOrderTotals.AGENT_ID LEFT OUTER JOIN
                      dbo.v_AgentAmmountPaid ON dbo.t_Agents.AGENT_ID = dbo.v_AgentAmmountPaid.AGENT_ID LEFT OUTER JOIN
                      dbo.v_CurrentParamPaymentTotal ON dbo.t_Agents.AGENT_ID = dbo.v_CurrentParamPaymentTotal.AGENT_ID

 

Any ideas how i do this?

Cheers
Anthony Swift

View 3 Replies View Related

Row Numbers Etc.

Sep 24, 2007



I have a sales report that includes dollar amount, tonnage, and profit margin among other things. They are currently sorted by tonnage sold from highest to lowest. I'd like to be able to place a number in a column counting 1 up for tonnage ranking. I'd also like to get a number ranking for sales amount ranking along with profit margin ranking. The most tonnage sold might not have been the biggest sale nor had the highest profit margin.

Does this sound like something that can be done within SSRS?

I should ad I'm runing MDX queries against a cube so I can't use T-SQL for ranking.

View 5 Replies View Related

Expression.Like For Numbers?

Aug 26, 2007

Hi,
Is it possible to search in columns with a number datatype (I'm using an MS SQL database with bigint columns) with the ICriterion Expression.Like?
Normally the Expression.Like is used for varchar columns. However, if there's a bigint column with the value 167829 I want to search for example on %678%.
Cheers,
koekie

View 3 Replies View Related

Calculating ID Numbers

Mar 28, 2008

 I needed to come up with an algorithm to create unique user- friendly account numbers such as AC0000000001, AC0000000002, etc...Where they increment by 1. I created a SQL function that retrieves the previous number generated, adds 1 to it, inserts the new value into  the table, then returns the new value. I started thinking, what if the function is ran at the same time? What if function # 1 creates the new number, and function #2 creates a new number as well before function #1 inserts it? Is this a possible scenario? If so, how do I lock the process until the function completes to prevent this?  Thanks for any help you provide.

View 2 Replies View Related

Generating Numbers In SQL

Jan 19, 2004

hi,

I am developing a ASP.NET application using SQL Server as my database.
I want to create a number that is unique.

The number will have 8 digits.

first 2 digits correspond to year. ex:04 or 03

the next 6 digits start with 000001 and it should get added for each new entry of data.

i am not able to generate number in the way i said. I am relatively new to SQL. so any suggestions as how to go about solving the problem???. Are there any samples/codes available for this.


Any help would be highly appreciated.

thanks,
-sriram

View 7 Replies View Related

Again ! Serial Numbers

Apr 2, 2001

How to generate serial numbers ? I had already tired ident. i am getting the error. Can any people who is willing to write a syntax for me.

In sybase if we use Number * function. It will automatically generates the serial numbers from 1 to n. similarly i need the same function in SQL server 7.0 so that my problem will be solved...

I am converting sybase stored procedure into sql server stored procedure that is why i am asking about that. i am struggling hard to find an answer...

please help me in this...

urs
vj

View 1 Replies View Related

Rounding Numbers

Jun 6, 2000

Looking for a way to round numbers to a specified number of significant digits. The ROUND function rounds to a specific decimal place but does not take into account the level of significance of the remaining numbers. (i.e.
ROUND(7.12435,2)=7.12000) The type of function I need would round the number in the following manner: SigFigRound(7.12435,3)=7.12 or
SigFigRound(7.12345,1)=7.

Any solutions?

View 1 Replies View Related

Numbers Truncated

Mar 9, 2000

Hello,
I am trying to run a query
select logid, count(logid) from temp2
group by logid
order by logid
compute sum(count(logid))
when I get the result the numbers are being truncated
eg instead of 10471066 it shows 104710 so last two digits
get truncated. Any ideas or hints appreciated.
Thanks
HP

View 1 Replies View Related

Subselect With Numbers

Feb 18, 2002

I have the following table:
CREATE TABLE ITEMS ([ITEMID] int, [itRULE] varchar(1))
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (11, 3)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (12, 3)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (21, 2)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (22, 2)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (31, 1)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (32, 1)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (41, 0)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (42, 0)

-- Those works and gives me 11,12,21,22
SELECT ITEMID FROM ITEMS WHERE itRULE IN (2,3)
SELECT ITEMID FROM ITEMS WHERE itRULE IN ('2','3')

-- This doesn't works
declare @Rule varchar(10)
set @Rule='2,3'
SELECT ITEMID FROM ITEMS WHERE itRULE IN (@Rule)
Any idea?
I don't mind to change the data type if it works.

View 1 Replies View Related

Random Numbers.

Jun 22, 2001

I am trying to get random numbers to have a unique value for different processes, then I can identify each process. What happens is that I use rand() function without seed, so I got my random numbers, but after shutting down SQLServer and try to get again another random number after booting up, the same series of random numbers is given again and again. So if anyone knows how I can get unique values,even though reseting the server, and using random function or any other method which automatically provides unique values,I'll really appreciate it if you let me know it.

This is the function: select rand()

Alberto.

View 2 Replies View Related

Deleting Odd And Even Numbers

Aug 8, 2002

I have two identical databases. I need to delete the even customer numbers from the first database and the odd numbers from the other database. I know I can do this writing a delete statement for each table. However there are a total of 54 tables.

Does anyone know a faster way to write the script without writing 54 scripts?

I would appreciate your help.

As always you guys are the greatest.

Dianne

View 1 Replies View Related

Getting MAX From Char Numbers

Jul 28, 2001

I have a table which will recieve and interface file to be loaded. All the fields are there except an account_id field which is a char(12), but the data is a number 000000000001, 000000000002, etc. These numbers are to be incremented by one. I understand how to write the process using a cursor to run through all the records and to increment them before loading. My question is the best way to get the max number in the existing table which is the char(12) , and icrement the number by one, but retain the leading zeroes I will need. Anyone had the pleasure of doing this providing input greatly appreciated.

View 5 Replies View Related

Finding The Numbers

Jul 8, 2005

I have a column of data of type varchar. it contains a mix of text id's and numeric id's.
all i want is the highest purely numeric value
The closest i can get is to use a regex expression, but it don;t work and i just know there is a better way of doing it.

SELECT * FROM customers WHERE custref LIKE 'd*d';

I am using MS SQL 7

thanks so much for any help

View 4 Replies View Related

Formatting Numbers

Jan 18, 2005

I am using a MS Access ADP connected to SQL Server Data

In a view I have the following formula:

dbo.tblQuoteItem.Cost + dbo.tblQuoteItem.Markup * dbo.tblQuoteItem.Cost * .01

this calculates cost + markup

I cannot get it to format in Currency

Ex:

Cost is $1.75
Markup is 2.00 (2%)
Total shows - 1.785000

I want $1.78

Also - I am using this formula to calculate the Quoted price for the Qty Entered

dbo.tblQuoteItem.Qty * dbo.tblQuoteItem.Cost + dbo.tblQuoteItem.Markup * dbo.tblQuoteItem.Cost * .01

Using a Qty of 2 for above, I get 3.535000

I want $3.53


Any help is appreciated - AB

View 2 Replies View Related

Select Any 2 Numbers (was Help)

Dec 5, 2006

I have a table that have ID_Number,Telephone_Number,Contract_No and Telephone_Type. lets say one id_number has one contract_no with different telephone_numbers and Telephone_Type so now what i wanna know is how can i select any 2 numbers if i have more that 3 per id_number in my table bear in mind that my table has about 200 000 rows.
example:

ID_Number Tel_Number Contract_No Tel_Type
123 555-22 5 home
123 444-12 5 Business
123 546-12 5 Cell

View 4 Replies View Related

Formatting Numbers

May 20, 2008

I know this sounds like a basic question, but how do u add a leading 0 to a number, ie, 43 becomes 043

Thanks

View 6 Replies View Related

Port Numbers

Jun 2, 2008

Hi Gurus,

Iwant to know PORT number of B on A server.
Iam following the following process to get B PORT number.

1) Loging to server A.
2)Opened SQL Server Configuration Manager.
3)Opened SQL Server 2005 Network Configuration.
4)Opened TCPIP properties of Protocol for B.
5)On that opened IP Address TAB
6)Got PORT number from TCP Dynamic Ports

Am i going n right way.
If not please advice me the right way to get port numbers.


Thanks,
ServerTeam

View 8 Replies View Related

Numbers To Words

Aug 27, 2004

Sir,
is there is any method to convert numeric ecpression to words ie 110 to "one humdred ten"
Pawandeep Singh

View 2 Replies View Related

Truncating Numbers

Feb 20, 2006

Hi!

I have field1 decimal(11,0) containing number 1234567 and
I must get the last six digits to int-field, eg. I want field2 int containing 234567 as result. How should I do that with functions? And it should work regardless of the length of the field1, eg field1 = 123456789 -> field2 = 456789

Makkaramestari

View 20 Replies View Related

Wildcards And Numbers

Apr 18, 2007

How do Iput wildcards around a number in an sp ? If my user leaves BoxNo blank it will list all boxes


SELECT *
FROM tblFiles
WHERE
ConNo =@strRMUConsignmentNo
and FileRef like '%'+@strtxtFileRef+'%'
and Subject like '%'+@strtxtSubject+'%'
and FileDescription like '%'+@strtxtDescription+'%'
and BoxNo like %+@strBoxNo%

View 14 Replies View Related

Generating Numbers

May 12, 2007

A simple question! I would like to create a database that creates say serial numbers and saves them to a table. If poss I would like to create them on demend and not duplicate.....

I have blank page syndrome!!!!

Thanks in advance

View 2 Replies View Related

Line Numbers.

Oct 24, 2007

How to enable “line number� display in query editor (SSMS)?


------------------------
I think, therefore I am - Rene Descartes

View 3 Replies View Related

Approximating Numbers Using Sql

Jan 18, 2008

Hi All,

Is there a ready function in sql that approximate float numbers to the nearest tenth or hundredth?

Thanks

View 1 Replies View Related







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