SQL Server 2012 :: How To Accumulate Additional Values To Existing Column

Nov 2, 2015

I have some transactions with the same card number that needs to add value amount to its existing balance. For example:

Card Number Balance Amount Issue Date Issue Branch.
4000111122223333 $100.00 10/1/2015 123 <= This is an existing row in Card Number SQL table.

Now, the same card number with additional $50 dollars that I want to add to this card number to make the total to become $150. This additional $50 is from another transaction table. On the contrary, I will have -$20 from the same card number in different transaction that I will need to deduce $150-$20 to become $130. How can I update the card number table with debit/credit transactions to keep the outstanding balance?

View 4 Replies


ADVERTISEMENT

SQL 2012 :: How To Check Existing Values In A Column For Duplicates Before Inserting

Aug 12, 2014

I have the following objective:

1. I want to check a column to see if there are values (to Eliminate dups)
2. Once checked the values in a column, if not found insert the new value

Here is my code for this:

ALTER TRIGGER DUPLICATES ON AMGR_User_Fields_Tbl
-- When inserting or updating
AFTER INSERT, UPDATE AS

-- Declare the variables
DECLARE @AN varchar(200)

[Code] ....

View 1 Replies View Related

SQL Server 2012 :: Update Table Based On Existing Values In Multiple Rows?

Oct 1, 2015

The objective is to identify orders where an order fee has been applied incorrectly. I have multiple orders per customer, my table contains an orderID and a customerID. Currently if the customer places additional orders before the previous orders have been closed/cancelled, then additional fees are being applied.

Let's say I'm comparing order #1 to order #2. I need to identify these rows where the following is true:-

The CustID is the same.

Order #2 has a more recent order date.

Order #2 has a FeeDate Before the CancelledDate of Order #1 (or Order #1 has no cancellation date).

So in the table the orderID:2835692 of CustID: 24643 has a valid order fee. But all the subsequently placed orders have fees which were applied before the first order was cancelled and so I want to update the FeeInvalid column with a 'Y'. The first fee will always be valid.

I think I understand why the code I am trying doesn't achieve the result I want but I can't figure out how to write it correctly. Below is one example of code I've tried and also code to create the table and insert some test data.

update t1
SET FeeInvalid = 'Y'
FROM MockData t1 Join MockData t2 on t1.CustID = t2.CustID
WHERE t1.CustID = t2.CustID
AND t2.OrderDate > t1.OrderDate
AND t2.FeeDate > t1.CancelledDate
CREATE TABLE [dbo].[MockData](
[OrderID] [float] NULL,

[code]....

View 4 Replies View Related

SQL Server 2008 :: Prerequisites For Additional Instance On Existing Cluster?

Sep 4, 2015

We have a SQL 2008 Instance existing on active/passive cluster with 2 nodes running on Windows server 2008 R2 Ent. Edn.

Now we need to install another SQL instance on this cluster. So what are the prerequisites apart from new IP address? Do we need shared disks or can the existing disks can be utilized?

View 3 Replies View Related

Setup And Upgrade :: How To Add Additional Server Instance In Existing Clustered Environment

May 14, 2015

How can i add additional SQL server instance to existing clustered environment on SQL 2005.

View 7 Replies View Related

SQL Server 2012 :: How To Add A Primary Key For Existing Column In The Table

Oct 19, 2015

How to add a primary key for existing column in the table

View 8 Replies View Related

SQL Server 2012 :: New Column In Existing Table And Uploading Data

Jan 13, 2015

Need to change the datatype of existing column which has huge data.

I'm performing below steps

1. Create new column with correct datatype in the same table
2. copy data into new column
3. drop indexes on column
4. <<<>>>
now the existing column also has many SP dependent and I do not wish to drop them.
5. rename existing column to xxx
6. rename new column to correct column
7. drop old column
8. make required indexes

View 9 Replies View Related

SQL Server 2012 :: Splitting Column Value While Keeping Existing Data?

Jun 22, 2015

Currently I have a column with multiple postcodes in one value which are split with the “/” character along with the corresponding location data. What I need to do is split these postcode values into separate rows while keeping their corresponding location data.

For example:

PostCodeLatitudeLongitude
66000/6610042.6965952.899370
20251/2027042.1964719.404951

Would become

PostCodeLatitudeLongitude
6600042.6965952.899370
6610042.6965952.899370
2025142.1964719.404951
2027042.1964719.404951

how this can be done?

View 2 Replies View Related

Query That Accumulate Detail Values

May 24, 2008

How would be a query that read detailed sales from several dates and accumulated them in a table that has months as fields

MSSQL 2005

Table : DETAIL
Customer_Date___________Value
1________01-Jan-2008____1,234.00
2________01-Feb-2008____3,456.00
3________01-Mar-2008______500.00
4________01-Apr-2008____1,567.00
1________01-Apr-2008____2,123.00

Table : TOTAL
Year__Customer_Jan______Feb______Mar_____Apr ...
2008__1________1,234.00_0________0_______2,123.00
2008__2________0________3,456.00_0_______0
2008__3________0________0________500.00__0
2008__4________0________0________0_______1,567.00

Thanks for your help

JG

View 5 Replies View Related

Transact SQL :: Accumulate Values Based On Time For Each Day

Jun 10, 2015

I have a table Mytable which has two fields (Date, Value) and record is based on every hour like this:

Date Value
1999-01-01 00:00:00 10
1999-01-01 01:00:00 8
1999-01-01 02:00:00 6
...
1999-01-01 23:00:00 12
1999-01-02 00:00:00 9
1999-01-02 01:00:00 5
...
1999-01-02 23:00:00 2

How can I use a query to get accumulate value for the day? the result should look like this:

Date Value Accumulated_Value
1999-01-01 00:00:00 10 10
1999-01-01 01:00:00 8 18
1999-01-01 02:00:00 6  24...
1999-01-01 23:00:00 12 36
1999-01-02 00:00:00 9 9
1999-01-02 01:00:00 5 14...
1999-01-02 23:00:00 2 16

The accumulated value should be based on every hour for the day and then repeat for the second day and so on.

What is the best way to do this? I am using SQL 2008

View 2 Replies View Related

How To Accumulate A Column

Aug 9, 2014

I would like to accumulate the 'diferencia' column by 'codigo'.

DECLARE @stock_prices TABLE
(codigo char(7)
,fecha date
, precio decimal(10,3)
)
INSERT INTO @Stock_prices

[Code] ...

View 7 Replies View Related

SQL Server 2012 :: Rounding UP Values In Column

Nov 28, 2014

I need to round UP values but they should never be rounded down, below is my expected output in RoundVal column.

SELECT 89 AS Val, 100 AS RoundVal UNION ALL
SELECT 329, 1000 UNION ALL
SELECT 6329, 10000 UNION ALL
SELECT 43299, 100000 UNION ALL
SELECT 155329, 1000000

View 1 Replies View Related

SQL Server 2012 :: Reducing Duplicate Row Because Of Different Column Values?

Oct 14, 2014

With the data example below I am trying to consolidate the duplicate rows by flattening the dealer and billcode, or putting those values in each of the columns instead of creating separate rows...

74 MARTHA PATNE RIPLEY 1 23,327,76 ROTTINGDAM AAC SPRINGFIELD 3052 USA MPATRIP@AMERICANALARM.COM,MPATRIP@COMCAST.NET,PRIPLEY@ONECOMMUNICATIONS.COM

[table]
arnumbercustomernamedealerbillcodeaddl_addraddr1addr2branchcityzipcodecountryemaildefaultEmail
39SUSAN THALKER 2271BOTTOMWOOD RDAACHAMMOND02180-2703USASUWS3@COMCAST.NET1
56ANN REBELLO 123SHERIDAN AVEAACMEDFORD2155USANULLNULL
58DARRELL/PATTI SANDERS 6020DOTY AVENUEAACDANVERS1923USANULLNULL
74MARTHA PATNE RIPLEY 123ROTTINGDAM DRAACSPRINGFIELD3052USAMPATRIP@AMERICANALARM.COM1
74MARTHA PATNE RIPLEY 1327ROTTINGDAM DRAACSPRINGFIELD3052USAMPATRIP@COMCAST.NET1
74MARTHA PATNE RIPLEY 176ROTTINGDAM DRAACSPRINGFIELD3052USAPRIPLEY@ONECOMMUNICATIONS.COM1
[/table]

View 1 Replies View Related

SQL Server 2012 :: Merge Column Values Into Rows?

Aug 12, 2015

I need to merge column values (#Status.Status) based on OrderID onto #Orders.NewStausCombined field separated by commas .

CREATE TABLE #Status
(
ID INT IDENTITY (1,1) PRIMARY KEY,
OrderID INT,
Status VARCHAR(20)
)
INSERT INTO #Status ( OrderID, Status )

[code].....

View 3 Replies View Related

SQL Server 2012 :: Add Where Condition On ID Column With Values Coming From Variable

Jun 26, 2015

I am trying to add a where condition on an ID column(type - INT) with values coming from a variable (type - STRING). i am using cast to cast the ID as Varchar and then apply the condition, but i am not getting any results back. following is an example of what i am trying to do.using temp table in the example , so you can copy the t-sql and run as is.

CREATE TABLE #TABLE1(ID INT)
INSERT INTO #TABLE1 VALUES (1), (2) , (3) , (4)
DECLARE @ID varchar(8000) = '2,4'

[code]....

View 4 Replies View Related

SQL Server 2012 :: Splitting Column Values In Multiple Columns And Assigning It To Row

Dec 11, 2013

How do I write a query using the split function for the following requirement.I have a table in the following way

Identity Name Col1 Col2 Col3
1 Test1 1,2,3 200,300,400 3,4,6
2 Test2 3,4,5 300,455,600 2,3,8

I want an output in the following format

Identity Name Col1 Col2 Col3
1 Test1 1 200 3
1 Test1 2 300 4
1 Test1 3 400 6
2 Test2 3 300 2
2 Test2 4 455 3
2 Test2 5 600 8

If you see the data, first element in col1 is matched to first element in col2 and 3 after splitting the string.

View 2 Replies View Related

SQL Server 2012 :: Add Column Names As Total And Subtotal For NULL Values?

Jan 21, 2014

How do I add column names as Total and SubTotal for NULL values.

SELECT DISTINCT
--[Group]
[Month]
,[Market]
,[Environment]
,[type]
, COUNT(*)

[code]....

View 9 Replies View Related

SQL Server 2012 :: Normalize Values From Column Split Using Tally Table?

Apr 23, 2014

I have a table that has the following structure:

EntryID int,
Categories varchar(200)

values look like:

541,'A,B,C'
345,'B,C'
234,'A,C'
657,'D,E'
435,'D'

what I want to do is extract the Categories column to a normalized separate table:

541,'A'
541,'B'
541,'C'
345,'B' ....

I found the split using the tally table useful to split one-by-one, but how can it be applied when you are referring to a table?

View 2 Replies View Related

SQL 2012 :: How To Prepend Value To Existing Value While Creating A Temp Column

Dec 5, 2014

I am looking for a way to create a temp column who's value would take the value of another column and prepend a value like this to it "domain". This is the Select statement I currently have:

SELECT Nalphakey,[Last Name],[First Name],[User Name],[E-mail Address],[User Name]
FROM SkywardUserProfiles

I understand how to create an Alias for an existing column, but not sure how to do what I am wanting. I also understand that the following will do the concatenation that I need, but I have only used it in an UPDATE query, and I'm not sure how to use it within a Select statement or if that's even possible:

domainName=CONCAT('domain',User Name);

View 2 Replies View Related

SQL Server 2012 :: Joining Table To Itself To Get Most Recent Date As Well As Additional Fields

Mar 11, 2014

I am querying a table log file in an attempt to get the most recent status of an item. The items can have a variety of different statuses:

(A = Active, R = Repeat, L = liquidation......)

Here is a sample of the data I am trying to report off of:

CREATE TABLE [dbo].[item_status](
[item_number] [varchar](20) NULL,
[sku] [varchar](100) NULL,
[Field_Name] [varchar](50) NULL,
[Old_Value] [varchar](150) NULL,
[New_Value] [varchar](150) NULL,
[Change_Date] [smalldatetime] NULL
) ON [PRIMARY]

[code]...

I have tried join to the same table - but I am still unable to get it to work.

View 1 Replies View Related

SQL 2012 :: Partition Existing Table On Foreign Key (datetime) Column?

May 28, 2015

Is it possible to partition an existing table on a foreign key (datetime) column? Also would partition switching work?

View 0 Replies View Related

SQL Server 2008 :: Pivot Function - Additional Final Column

Mar 11, 2015

I've managed to use the pivot function using the code below which gives me the following output:

Location_Code Gt 0-1 Gt 1-2 Gt 2-3 Gt 3-4
North 10 0 3 5
West 6 3 2 0
South 4 2 8 2

This is exactly what I want but I would like an additional final column that will total the columns by location_code and weekband.

[Location_Code] AS 'Location Code'
,[Gt 0-1], [Gt 1-2], [Gt 2-3], [Gt 3-4]
from (select [WeekBand]
,[Location_Code]
, count(*) as CountOf

[Code] ....

View 7 Replies View Related

SQL Server 2012 :: Delete / Recreate Identity Column / Fetch Newly Created Values In Update Statement?

Jul 25, 2015

I have a four tables called plandescription, plandetail and analysisdetail. The table plandescription has the columns DetailQuestionID which is the primary and identity column and a QuestionDescription column.

The table plandetail consists of the column PlanDetailID which the primary and identity column, DetailQuestionID which is the foreign key attribute of plandescription table and a planID column.

The third table analysisdetail consists of a analysisID which the primary and identity column, PlanDetailID which is the foreign key attribute of plandetail table and a scenario.

Below is the schema of the three tables

I have a two web form that will insert, update and delete data into these three tables in a two transaction. One web form will perform CRUD operations in plandescription and plandetail table. When the user inserts QuestionDescription and planid in this web form, I will insert the QuestionDescription Value in the plandescription table and will generate a DetailQuestionID value and this value is fed to the plandetail table with the planid. Here I will generate a PlanDetailID.

Once this transaction is done, I will show the second web form in which the user enters the scenario and this will be mapped with the plandescription using the PlanDetailID.

This schema cannot be changes as this is the client requirement. When I insert values I don’t have any problem. However when I update existing data, I need to delete existing PlanDetailID in the plandetail table and recreate PlanDetailID data for that DetailQuestionID and planID. This is because, the user will be adding or deleting a planID associated with the QuestionDescription.

Once I recreate PlanDetailID for that DetailQuestionID and planID, I need to update the old PlanDetailID with the new PlanDetailID in the third table analysisdetail for the associated analysisID.

I created a #Temp table called #DetailTable to insert the values analysisID, planid and old PlanDetailID and new PlanDetailID so that I can have them in update statement once I delete the data from plandetail table for that PlanDetailID.

Then I deleted the plandetailid from the plandetail table and recreate PlanDetailID for that DetailQuestionID. During my recreation I fetched the new PlanDetailID’s created into another temp table called #InsertedRows

After this I am running a while loop to update the temp table #DetailTable with the newly created PlanDetailID for the appropriate planID’s. The problem is here. When I have the same number of planID’s for example 2 planID’s 1,2 I will have only two old PlanDetailID and new PlanDetailID for that planID and analysisID.But When I add a new PlanID or remove a existing planID I am getting null value for that newly added or deleted planID. This is affecting my update statement of analysisdetail table as PlanDetailID cannot be null.

I tried to remove the Null value from the #DetailTable by running the update statement of analysis detail in a while loop however its not working.

DECLARE @categoryid INT = 8
DECLARE @DetailQuestionID INT = 1380
/*------- I need the query to run for the below three data.
Here i'm updating my planids that already exists in my database*/
DECLARE @planids VARCHAR(MAX) = '2,4,5'

[code].....

View 2 Replies View Related

SQL Server 2k And VWD - Cant Edit Existing Db Values

Mar 14, 2006

I have SQL 2000 server machine on our network.
I can connect to our existing SQL database and display data from tables etc, but when I try and edit the data using the detailsView it wont let me. No error msg, it just does not update the field.
However if I create a new record using the detailsView, I can edit it.
Its just older records that were created previously that it wont let me edit.
Is this a bug?, A compatibility issue?, Any Ideas?
O_F

View 1 Replies View Related

Analysis :: Best Way To Add Additional Parent Child Attribute Values

Apr 16, 2015

I have a parent Child attribute in my dimension.  I am currently displaying the correct ID value as the business wants.  So now they can see the rollup of the ID(intOrgNodeID ) values.They would also like to see the same rollup of the Name (vcharOrgNodeName)  for this ID.However they do not want it concatenated.  They want to be able to see them separate.You cannot create two parent child attibutes in one dimension so not sure if there is some simple trick to make this work? It seems like there should be some simple trick for this. 

My dimension table looks something like this
intdimOrgNodeID int Key (surreget key)
intOrgNodeID int (Actual ID)
intDimParentOrgNodeID
vcharOrgNodeName
In the Propertys I have set this below.
KeyColumns  = tbldimOrgNode.intDimParentOrgNodeID
NameColumn = tbldimOrgNode.intOrgNodeID

View 8 Replies View Related

SQL 2012 :: Replacing Column Names With Values

Nov 4, 2015

I have a field in a table that contains a different formula (varchar(1000)) for each record. It's along the lines of something like this, although each formula is different: ([ColumnA] - [ColumnB])/([ColumnC] - [ColumnD]). I plug that into a dynamic SQL statement so that it can get executed in a select statement.

Due to the variations of the formulas, checking for Divide by Zero, etc, we want to move this to a .NET method. We'd like to replace "ColumnA" and "ColumnB", etc., with the actual values so that we're passing something like (5-3)/(6-2). I haven't been able to figure out a way to do this without actually executing it. We don't want to pass the solution, but the equation filled with the actual values rather than the column names.

View 3 Replies View Related

Reporting Services :: Report Builder 3.0 - Additional Columns Sorted After Values

May 12, 2014

I'm somewhat new to report builder and have been trying to recreate a report previously created in an Excel Pivot Table. I'm encountering an issue arranging the data the way it's arranged in Excel. 

Specifically, I would like the values column to precede an additional column.  

Until I can post pictures I'll have to try and mock it:

COLUMNS
Values
Results (my data I want as a 2nd column)

I can't figure out how to get report builder to do it the same way. Whenever I add the 'Result' data as a column it always appears on top. I'm guessing what I need to do is somehow get result set as a child of the first Static group, but I'm unsure how to do that.

View 4 Replies View Related

SQL 2012 :: Removing Additional Unwanted Log File?

Jul 6, 2015

I wanted to remove an extra transaction log file that was no longer required, and ran the following against the database...

DBCC Shrinkfile (DB_Name_log2, Emptyfile);
go
alter database [Db_Name]
remove file DB_Name_log2;
go

I got a successful removal message. But if I go into the properties of the database, and click on files, it still shows up. Why is this and how can I get rid of it?

It shows up in sys.master_files as offline.

View 4 Replies View Related

Additional 0 To Column Data

Jul 6, 2006

Hi

I have a column in the db with serial number data, on a export that I am doing the data has to be 10 digits long the problem is not all of them are eg

12234

122334343

1234234567

how can i get it to look like this adding an 0 to the front to make the row 10 digits

0000012234

0122334343

1234234567

Thanks

View 6 Replies View Related

Adding Additional Column

Feb 29, 2008

Hi All,

I have developed a report using report viewer in asp.net web form. The report is generated based on the selection of Department. User have an option to multi select the departments so the department column grows. Lets say user select Business category first and generate the report then user decides to add another department say Engineering. No the report column headers will have addional column "Engineering". I have used the Matrix since I had to group by Name and dates. Everything is good so far.
Now I have to add an additional column "Notes" at the end of all the columns. If there is ony department is chosen then Notes column appears after that and if mulitple departments are chosen the Notes column should also appear after all the department column (at the very end).
I am really having hard time to accomplish this. Is there any suggestion or solution to finish this? I really appreciate your responses.

Thank you.

View 1 Replies View Related

SQL Server 2012 :: Replace All Values In String With Values From Look Up Table

Mar 19, 2014

I have a table that lists math Calculations with "User Friendly Names" that look like the following:

([Sales Units]*[AUR])
([Comp Sales Units]*[Comp AUR])

I need to replace all the "User Friendly Names" with "System Names" in the calculations, i.e., I need "Sales Units" to be replaced with "cSalesUnits", "AUR" replaced with "cAUR", "Comp Sales Units" with "cCompSalesUnits", and "Comp AUR" with "cCompAUR". (It isn't always as easy as removing spaces and added 'c' to the beginning of the string...)

The new formulas need to look like the following:

([cSalesUnits]*[cAUR])
([cCompSalesUnits]*[cCompAUR])

I have created a CTE of all the "Look-up" values, and have tried all kinds of joins, and other functions to achieve this, but so far nothing has quite worked.

How can I accomplish this?

Here is some SQL for set up. There are over 500 formulas that need updating with over 400 different "look up" possibilities, so hard coding something isn't really an option.

DECLARE @Synonyms TABLE
(
UserFriendlyName VARCHAR(128)
, SystemNames VARCHAR(128)
)
INSERT INTO @Synonyms
( UserFriendlyName, SystemNames )

[Code] .....

View 3 Replies View Related

SQL 2012 :: Store Attributes / Values In XML Column - Table Design

Oct 29, 2014

I'd like to create a table that will store different order items. Several order items make up one single order. Order items can have 0 or more children (max depth will never be deeper than one). Order items can have up to 150 attributes/values. The way I think this should be done is using XML column instead of the EAV type of model. My table structure currently looks like this:

* child_order_item_id (PK)
* parent_order_item_id (FK to child_order_item_id)
* order_id (FK to Order table)
* product_id (FK to Product table)
* price
* attribute_XML

How my attribute_XML should look like or how to validate the xml.

View 2 Replies View Related

SQL 2012 :: Select Multiple Values From Same Column And Make Them To Show In A Row

Jun 10, 2015

I created a query that got the following result. But I expect to get the structure like, care_nbr, cust_nbr,legal_name, address_type=physical address, addr_line_1, addr_line_2, address_type-primary address, ddr_line_1, addr_line_2. That means I only need primary and physical address, and expect them to show in a row to each care_nbr. How to perform that?

CARE_Nbr||Cust_Nbr||Legal_Name||||||| Address_Tpye |||Addr_Line_1 ||||||||||||||||Addr_Line_2
99000001||004554||Mac Marketing, LLC||Billing Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Mailing Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Primary Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Physical Address||210 Parktowne Blvd Suite 1||NULL

My script is here, and the sample result is attached. How should I modify this query to get my expected result?

select a.CARE_Number,
a.Customer_Nbr_Txt,
a.Customer_Type_Txt,
a.Legal_Name_Txt,
c.Address_Type_Txt,c.Address_Line_1_Txt,c.Address_Line_2_Txt,
a.Customer_Status_Txt,
a.Legal_Entity_Type_Txt,
a.Business_Unit_Txt
FROM dw_mart.dbo.DimCustomer a

[code]....

View 1 Replies View Related







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