Gradually Add O Substract Numbers In A Column

Nov 22, 2007

how do I gradually add o substract numbers in a column for example if I have



total
deposito
test

120

120


80
40

77

117

7

124


4
120


i need in the test column:
if the number is in the total column the number in test is added
if the number is in the deposito column the number in test is substracted

i have this query:


SELECT gir_cantidad as total,null as deposito,'operation' as test

FROM giros inner join corresponsales on cor_corresponsal_id = gir_corresponsal_id where gir_fecha >= '11/21/2007' and gir_fecha <= '11/22/2007 23:59:59' and gir_fecha_anul is null and gir_agencia_id = 1

UNION

select null as total,paa_valor as deposito,'operation' as test from pagosagencia where paa_fecha >= '11/21/2007' and paa_fecha <= '11/22/2007 23:59:59' and paa_agencia_id= 1 order by gir_fecha

In column test i need the functionality to add and subtract the columns total and deposito

Please help

View 4 Replies


ADVERTISEMENT

Substract New Row From Old Row In Same Column?

Mar 12, 2008

Hi

I'm having a bit troubble creating a SQL-sentence which substract the newest row from the second newest row in the same column. The table looks like this:

Pricecalcid Date Price Itemid

2000 2006-12-12 3000 100

2488 2007-10-11 2800 100

3100 2008-08-07 2500 100

What I need is that the largest "Pricecalcid" that is 3100 equals "Price" 2500 and the second largest "Pricecalcid" eqauls 2800 results in a pricecalculation that substracts 2500 from 2800 for "Itemid" 100.

How do I do that?

Thanks

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

How To Substract New Rows From Old Rows In The Same Column?

Mar 7, 2008



Hi I'm having a bit troubble by creating a SQL-sentence which substract the newest row from the second newest row in the same column. The table looks like this:

Pricecalcid Date Price Itemid
2000 2006-12-12 3000 100
2488 2007-10-11 2800 100
3100 2008-08-07 2500 100



What I need is that the largest "Pricecalcid" that is 3100 equals "Price" 2500 and the second largest "Pricecalcid" eqauls 2800 results in a pricecalculation that substracts 2500 from 2800 for "Itemid" 100.

How do I do that?

Thanks

Morten

View 9 Replies View Related

Inserts Gradually Slow Down

Jul 23, 2005

I'm inserting 2 million+ records from a C# routine which starts outvery fast and gradually slows down. Each insert is through a storedprocedure with no transactions involved.If I stop and restart the process it immediately speeds up and thengradually slows down again. But closing and re-opening the connectionevery 10000 records didn't help.Stopping and restarting the process is obviously clearing up someresource on SQL Server (or DTC??), but what? How can I clean up thatresource manually?

View 9 Replies View Related

SQL Server Gradually Consumes All Memory And Brings Down NT

Mar 10, 2000

Hello,

After launching MS SQL Server 7.0 SP 1 on a Compaq Proliant 5500 with 1GB memory the sqlservr.exe process gradually consumes all available memory and brings down NT.
This happens when there's appearently no activity on SQL Server.

What's going on?

Stef

View 5 Replies View Related

Need Big Help : Sum And Substract

Sep 19, 2007

Hi,
to keep things short, I have a query like this : SELECT A AS A, SUM(B) AS B FROM CC WHERE A IN (1,2,3) GROUP BY A

I want to be able to SUM the field B when A is equal to 1, and substract the field B when A is equal to 2 or 3. So I know SUM is incorrect there, cuz sometimes the field B must be added, and sometimes it must be substracted. What's the easiest way to do it?

For example, if the fields B in the database has the values 3, 5, and 7, the query would go something like 3+5-7, giving 1 for B at the end.

Tanx for your help guyz!

View 3 Replies View Related

Substract Weekends

Oct 22, 2007



I am getting two dates each of which I calculate using a subquery (joining 2 or 3 tables)
Then I perform datediff on these two dates to get a interger values (No of Days)

I need to substract the weekends from this No of days
How do I accomplish this

Thanks
sowree

View 10 Replies View Related

Substract The Sum Of Two Doc Types In A Table

Jun 4, 2006

I need to subtract the values of the totals of two document types in a table. So far I have the following with the results as shown.

SELECT TOP 100 PERCENT { fn MONTHNAME(DOCDATE) } AS [MONTH], DOCID, SUM(DOCAMNT) AS TOTAL
FROM dbo.tblSales
WHERE (DOCID = 'RTD') OR
(DOCID = 'INV')
GROUP BY DOCID, { fn MONTHNAME(DOCDATE) }
ORDER BY DOCID DESC

This results in the following:
Month DOCID TOTAL
MAY RTD 165752.87
MAY INV 18149416.37

I need one value showing the difference of the totals.
Thanks.

View 6 Replies View Related

Multiply Numbers In A Column

Mar 21, 2002

Hi

I'm trying to find a decent way of multiplying a set of numbers in a column without using a cursor in T-SQL.

There is no 'Product' aggregate function that I'm aware of in SQL 7 or 2000. The workaround I'm currently using is this :

SELECT EXP(SUM(LOG(ColumnName))) FROM tblName

This works fine, except when negative numbers are introduced. The LOG function does not allow negative numbers and therefore returns a domain error and the negative number is eliminated from the aggregate.

I could use a cursor to do the multiplication, however, this is proving too slow for the bulk calculations involved.

If anyone has any ideas or suggestions, then that would be much appreciated.

thanks....Tom

View 1 Replies View Related

Sequential Numbers In Column

Oct 25, 2013

I have a table with 13,000 rows, in one column called Prioirty each row has a value of 1.

Is it possible to use SQL to replace all of these '1' values with a sequential list. Example 1,2,3,4,5,6,7....all the way to 13,000?

View 3 Replies View Related

Add New Table Column With Numbers

Jul 19, 2007

Hello.

Whats the easiest way to add a column with a numeration to my table?

I want the users to have an easier life finding on which row they are
talking.

I preffer of course doing it in reporting service and not adding a
column in my dataset.

Thanks.

View 1 Replies View Related

STUCH WITH SSN NUMBERS COLUMN....HW TO DO

Apr 23, 2008



OK I HAVE AN INTERESTING SITUATION HERE.

I HAVE AN SSN COLUMN.

SSN NUMBER HAS 9 NUMERIC DIGITS.

NOW IN THE SSN COLUMN I HAVE A LOT OF BAD SSN WHICH CONTAIN ONLY 3 OR ONLY 4 OR ONLY 5 AND SO ON DIGITS.

HOW CAN I FILTER THESE ONES OUT SO THAT I CAN MAKE AN EXCELL REPORT OF THIS DATA.

I AM THINKGING OF USING CONDITIONAL SPLIT.

BUT HOW SHOULD I GO ABOUT IT.

PLEASE LET ME KNOW THE COMMAND

View 1 Replies View Related

Removing Numbers From A Column

Oct 11, 2007



Hi,

I have the following tables :




Code Block
Create table #EmployeeList(empname nvarchar(20), emptype char(5))

Insert INTO #EmployeeList('Cary zzz',null);
Insert INTO #EmployeeList('01 Jack',null);
Insert INTO #EmployeeList('02 Tommy',null);
Insert INTO #EmployeeList('03 Ricardo',null);
Insert INTO #EmployeeList('04 Jack',null);
Insert INTO #EmployeeList('Les zzz',null);
Insert INTO #EmployeeList('05 Tim',null);

The final data looks like this :

Cary zzz NULL
01 Jack NULL
02 Tommy NULL
03 Ricardo NULL
04 Jack NULL
Les zzz NULL
05 Tim NULL






1. I want to delete all rows which have 'zzz' in it.
2. I want to remove the numbers from the empname column




Code Block
Expected Output :

Jack NULL
Tommy NULL
Ricardo NULL
Jack NULL
Tim NULL






Can anyone help me please with the query?

thanks.

View 4 Replies View Related

Convert Sql Smalldatetime Column Into Numbers

Aug 27, 2004

HI all;

I have a simple question to ask; I need to create a column with the data from my DOB column (which has the smalldatetime type attached to it). I know how to do that but I am not too sure how to convert the data from that column int normal character for example when I copy it into my newly created column and change the type to varchar I get this jan 16 1979 from this date 1979/01/16. But I actually want the data to look like this 19790116, so in effect I just want to take out the slashes.

Any help would be highly appreciated, thanks all.

View 1 Replies View Related

Ordering A Column In Descending With Numbers First

Oct 8, 2013

I have a Column which has both numeric values and Alphabets in them.I want to sort them in descending with numbers first and then Alphabets later.

Example:

Source column:
2008
2005
2010
2013
All-Year
Month-year

Required Output:

Source Column:
2013
2010
2008
2005
Month-Year
All-year

View 4 Replies View Related

Reuse Numbers In Identity Column

Mar 4, 2008

Hello,

I have an ID column which is an Identity column, and it counts from 1 upwards.

If I have 10 records, I have ID's number 1 to 10.
If I then add another record, it will give the ID number 11.

Imagine I then delete ID number 9
Is it possible to get the next record I create to take number 9 instead of 12 ?

View 5 Replies View Related

Create Sequential Numbers In A Column

Jul 20, 2005

I have a temp table that's populated with an insert query in as toredprocedure. The temp table has a uniqueID as the primary key.In that table I have a column SortOrder.What I want to do is to create a sequential number in SortOrder butonly for records matching a WHERE statement, for example:(pardon the shorthand...)Insert *.tblPermanent into tblTempIf myField = 1 thenSortOrder = 1(2,3,4,5,.....etc.)elseSortOrder = 0Thankslq

View 1 Replies View Related

Identity Column Jumped 1.8 Billion Numbers

Aug 19, 1999

After moving our database between servers, one table had a hiccup in it's
identity column. The number jumped from 761 to 1886863475 on the next
insert. This is a production server and I didn't catch it until yesterday.
So I have several days worth of numbers in a central table. I can't clean
them out, but I wondered if I could do the following:

DBCC CHECKIDENT ('table_name', RESEED, 800)

I tested it on our development server and it doesn't seem to cause any
problems. I know that when we get to 1.8 billion on the identity column
again we'll get an error, but I'm willing to risk it.

A few questions. 1) Am I insane for trying this? 2) Has anyone else ran into
a similar problem and what did you do to fix it? 3) If I do this, is there
anything in particular to watch out for? 4) What caused the jump in the
first place?

My other option is to change the datatype from int to decimal(19) or
something along those lines. This will upset our programmers and I'll have
to change all my foreign keys (not a big deal, just a pain).

Any input at all here would be appreciated.

Thanks,

Grant

View 1 Replies View Related

SQL 2012 :: Create A Column Of Numbers That Increment By 1?

Feb 18, 2014

I'm trying to create a column of numbers that increment by one.

I'm not able to use a #temptable in the application I'm using so I cannot use IDENTITY(int,1,1).

I want to add an Id column to this query:

Select distinct sd.name,ic.TABLE_SCHEMA,ic.TABLE_NAME from sys.databases sd
cross join INFORMATION_SCHEMA.COLUMNS ic
where sd.name = 'ODS1stage'
order by TABLE_SCHEMA,TABLE_NAME

How can I accomplish this without creating a temp table? I would just alter the table and insert the numbers but there are 2000 rows.

View 7 Replies View Related

Generate Serial Numbers And Update Column?

Jul 17, 2013

I have two tables. One table has empty column (ID) and I want to generate serial numbers in that column based two tables columns(LoanNum) condition.

Table A

ID LoanNum
Null 1234
Null 2345
Null 3456
Null 4567
Null 5678
Null 6789

Table B

LoanNum
1234
2345
3456
4567
5678
6789
2324
4352
4235

Id is not primary key, but should not duplicates keys, it is just a column want to generate serial number (1,2,3,4...etc) How to generate?

View 5 Replies View Related

Masking Table Of ID Numbers - Populating A Column

Aug 29, 2013

I have a table of id numbers that I wish to mask. My thought was to create a new column for this new id number and populate it with a unique sequential value - start at 1 and go as high as needed. My problem is that I cannot recall how to populate that column with a number...

View 4 Replies View Related

Generate Column Numbers Using Dynamic Pivot

Jul 1, 2014

I have the following SQL which i want to convert to a stored procedure having dynamic SQL to generate column numbers (1 to 52) for Sale_Week.Also, I want to call this stored procedure from Excel using VBA, passing 2 parameters to stored procedure in SQL Server
e.g,

DECLARE @KPI nvarchar(MAX) = 'Sales Value with Innovation' DECLARE @Country nvarchar(MAX) = 'UK'

I want to grab the resultant pivoted table back into excel. how to do it?

USE [Database_ABC]
GO
DECLARE @KPI nvarchar(MAX) = 'Sales Value with Innovation'
DECLARE @Country nvarchar(MAX) = 'UK'

[code]...

View 1 Replies View Related

Transact SQL :: How To Remove Numbers Zero From The Left Only In Column

Jul 1, 2015

example:

column: ID  numbercar      before         ID    numbercar         after

        1      00122011                      1        122011                                           
       2      00042010                       2         42010
       3      03102012                          3       3102012                                          

View 5 Replies View Related

Update Column With Increasing Sequential Numbers

Feb 18, 2008

Here is my problem. I have a table with 4 columns id1,id2,id3,boxnum. Here is some sample data.

id1 id2 id3 boxnum
1 1 1

1 1 1
1 2 1
1 2 1
1 2 1
2 2 2
2 3 4

What I need is to be able to update boxnum with sequential numbers based on the unique set of values from the id columns. So my output would be this.


id1 id2 id3 boxnum
1 1 1 1

1 1 1 2
1 2 1 1
1 2 1 2
1 2 1 3
2 2 2 1
2 3 4 1

View 6 Replies View Related

Updating Table Column With Cumulative Numbers

Nov 13, 2007

Hello,

One more question about this Custom Calendar table I'm creating. I have a column called "IsWorkdays" which indicates if the day represented by a row is a workday or not. For our purposes, I also need to create a row that accumulates those numbers by month. So, if it is the 3rd workday of the month, this column would have a 3. This is beyond my current T-SQL ability. Does anyone know how to do this?

Thanks a lot,
Andy

SQL version: 2005, Standard edition.

View 1 Replies View Related

Inputting Random Numbers To Table Column From Web Form

Feb 28, 2007

I'm grappling with this issue which I thought was basic VB programming; I'm trying to insert a random number (between 100 and 999) into a SQL table column (=Status_ID). This is input as part of a user submitting helpdesk requests via a APS.Net Web Form. The 'Status_ID' field is obviously not visible to the user but will help reference this Helpdesk request on the database.Here is the code:Protected Sub submitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submitButton.Click        If Page.IsValid Then            ' Define data objects            Dim conn As SqlConnection            Dim comm As SqlCommand            ' Read the connection string from web.config            Dim connectionString As String = _            ConfigurationManager.ConnectionStrings("ITNet_Students").ConnectionString            ' Initialize connection            conn = New SqlConnection(connectionString)            ' Create command            comm = New SqlCommand( _            "INSERT INTO HelpDesk (First_Name, Last_Name, StudentID, PersonalEmail," & _            "CategoryID, SubjectID, Description, StatusID) " & _            "VALUES (@First_Name, @Last_Name, @StudentID, @PersonalEmail, " & _            "@CategoryID, @SubjectID, @Description, @StatusID)", conn)            ' Use randomize            Randomize()            Dim randomvalue As Integer            ' Generate random value between 999 and 100.            randomvalue = Int((900 * Rnd()) + 100)            ' Add command parameters            comm.Parameters.Add("@First_Name", System.Data.SqlDbType.NVarChar, 50)            comm.Parameters("@First_Name").Value = fnameTextBox.Text            .            .            .            comm.Parameters.Add("@StatusID", System.Data.SqlDbType.Int)            comm.Parameters("@StatusID").Value = randomvalue            'Enclose database code in Try-Catch-Finally            Try                ' Open connection                conn.Open()                ' Execute the command                comm.ExecuteNonQuery()                ' Reload page if the query executed successfully                Response.Redirect("HelpDesk.aspx")            Catch                ' Display error message                dbErrorMessage.Text = _                    "Error submitting the help desk request! Please try again later, and/or change the entered data!"            Finally                'close connection                conn.Close()            End Try        End If    End Sub----------------------------------------------------------------------------------------------------------------------I keep getting the error message under 'Catch'  and the page 'HelpDesk.aspx' is not reloading; the 'comm.ExecuteNonQuery()' is not executing.Can anyone spot any inconsistencies in the declaration of the 'randomvalue' variable?P.S: this code works fine if you replace 'randomvalue' with any integer in 'comm.Parameters("@StatusID").Value = randomvalue' 

View 2 Replies View Related

Search A Column Of Comma Delimited Numbers (was Help With Sql Query)

Nov 30, 2005

Hi, I need to search a column in my database (varchar:50) that contains a comma delimited string of numbers (i.e. 1, 2, 3, 4, 5, 10). Currently, I am doing the following SQL query:

SELECT * FROM people WHERE clubs_belongs_to LIKE '%1%'

Where 1 is the number I'm searching for. The problem with the query above is that it returns records that contain 1, 10, 11, 12, 13, etc. in the clubs_belongs_to field. I want the query to only return those people who belong to club number 1, not 10, 11, 12, etc. Please help. Thanks in advance. I have tried using IN instead of LIKE, but that didn't seem to get the results I wanted either.

View 4 Replies View Related

Simple Query To Create Column Of Sequential Numbers

May 2, 2006

Hi,
Can anyone tell me offhand the simplest/most elegant way of updating an integer column to a sequential column of numbers with a query?

e.g given

intval | Description| Cost
0 | Descvalue0| 4.32
2 | Descvalue2| 4.33
3 | Descvalue3| 4.34
8 | Descvalue8| 4.35

change it to:

intval | Description| Cost
0 | Descvalue0| 4.32
1 | Descvalue2| 4.33
2 | Descvalue3| 4.34
3 | Descvalue8| 4.35

I think it might need a stored proc..

Many Thanks
greg

View 2 Replies View Related

SQL 2012 :: Update A Column With A Sequence Of Numbers Starting From 1?

Oct 7, 2015

If Exists ( Select c.name from sys.columns c where object_id = object_id('HH835HP') and C.name = 'ID_1' )
Begin
UPDATE HH835HP
SET ID_1 =
( select ROW_NUMBER() OVER(ORDER BY CHKDTS ASC) AS ID_1 FROM HH835HP ) ;
End;

Obviously... The stuff inside the IF is wrong syntax...I mean

UPDATE HH835HP
SET ID_1 =
( select ROW_NUMBER() OVER(ORDER BY CHKDTS ASC) AS ID_1 FROM HH835HP ) ;

View 4 Replies View Related

Unable To Format Excel Destination Column For Numbers

Oct 31, 2006

Hi,

I have an excel export with numeric values. When the SSIS package writes into the excel it treats all data types as strings and attaches an apostrophe in the beginning. I tried formatting the excel sheet with the number data type and saving it . It doesnt work.

Other strange thing is that when I go into the advanced editor for the excel destination and look into the properties of the external columns all of them have the data type as Unicode String[DT_WSTR] irrespective of what the data type is from the input.The UI allows me to change it to numeric for numeric columns without any error but the value is not saved at all which is very frustrating. I also tried changing it in the xml file of the package, it some how seems to overwrite it after validation. It would be really nice if it threw an error saying that it cannot be changed.

Anyways there seems to be no way of changing the destination data type if it s an existing file and if I create a new excel sheet there is no way of formatting data. Is there any way out?

Thanks

View 1 Replies View Related

SQL Server 2014 :: Generate Column Numbers Using Pivot Command

Jul 1, 2014

I have the following SQL which i want to convert to a stored procedure having dynamic SQL to generate column numbers (1 to 52) for Sale_Week. Also, I want to call this stored procedure from Excel using VBA, passing 2 parameters to stored procedure in SQL Server
e.g,

DECLARE @KPI nvarchar(MAX) = 'Sales Value with Innovation' DECLARE @Country nvarchar(MAX) = 'UK'

I want to grab the resultant pivoted table back into excel. how to do it?

USE [Database_ABC]
GO

DECLARE @KPI nvarchar(MAX) = 'Sales Value with Innovation'
DECLARE @Country nvarchar(MAX) = 'UK'

SELECT [sCHAR],[sCOUNTRY],[Category],[Manufacturer],[Brand],[Description],[1],[2],
[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],

[Code] ....

View 9 Replies View Related

SQL 2012 :: Populate Int Column With A Sequence Of Numbers But Sorted By Another Field

Oct 8, 2015

The following works just fine. The table tmpMHPCLMDET does have a column ADMTDT ( varchar(8) ).

While I am adding the sequence of numbers I like it to be sorted based on ADMTDT column.

What that means is the row with the earliest ( smallest ) ADMTDT will get 1 and the next 2 and so on.

Declare @ID int
If Exists ( Select c.name from sys.columns c where object_id = object_id('tmpMHPCLMDET') and C.name = 'ServiceLineID' )
Begin
--Adding a sequence of numbers to the ServiceLineID column.
SET @id = 0
UPDATE tmpMHPCLMDET
SET @id = ServiceLineID = @id + 1;
End;

View 2 Replies View Related







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