Add A Minus To Two Column Values If Criteria Met
Mar 7, 2008
Hi I was hoping you could help,
I have a query that pulls back a customer€™s account statement, however the credits are coming back as positives as well, So I am getting wrong figures, how can I correct the code below so that VAT, Goods Value and Total are negative if the data in column "trans reference" begins with a C for example C123456789 a invoice would be 123456789
Thanks
Code Snippet
SELECT
SLCUSA as [Compny Number],
SLCUSB as [Customer Suffix],
SLDELN as [Deliber To Number],
SLCNAM as [Customer Name],
SLDATE as Date,
SLTREF as [Trans Reference],
SLGDSV as [Goods Value],
SLVATV as [VAT],
SLTOTV as Total
FROM LIVEAS400.S65C422B.WRFDTA.SQLSLDGR
WHERE (SLCUSA = 2) AND (SLCUSB = 1007)
END
View 11 Replies
ADVERTISEMENT
Aug 22, 2007
Hi,
I have multiple columns in a Single Table and i want to search values in different columns. My table structure is
col1 (identity PK)
col2 (varchar(max))
col3 (varchar(max))
I have created a single FULLTEXT on col2 & col3.
suppose i want to search col2='engine' and col3='toyota' i write query as
SELECT
TBL.col2,TBL.col3
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col2,'engine') TBL1
ON
TBL.col1=TBL1.[key]
INNER JOIN
CONTAINSTABLE(TBL,col3,'toyota') TBL2
ON
TBL.col1=TBL2.[key]
Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.
I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information.
for e.g.;
i was thinking to concatinate both fields like
col4= ABengineBA + ABBToyotaBBA
and in search i use
SELECT
TBL.col4
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1
ON
TBL.col1=TBL1.[key]
Result = 1 row
But it don't work in following scenario
col4= ABengineBA + ABBCorola ToyotaBBA
SELECT
TBL.col4
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1
ON
TBL.col1=TBL1.[key]
Result=0 Row
Any idea how i can write second query to get result?
View 1 Replies
View Related
Sep 7, 2015
We have SharePoint list which has, say, two columns. Column A and Column B.
Column A can have three values - red, blue & green.
Column B can have four values - pen, marker, pencil & highlighter.
A typical view of list can be:
Column A - Column B
red  - pen
red - pencil
red - highlighter
blue - marker
blue - pencil
green - pen
green - highlighter
red  - pen
blue - pencil
blue - highlighter
blue - pencil
We are looking to create a report from SharePoint List using SSRS which has following view:
          red   blue  green
  pen       2    0    1
  marker    0    1    0
  pencil      1    3    0
  highlighter  1    1    1Â
We tried Sum but not able to display in single row.
View 2 Replies
View Related
Sep 3, 2015
I have an SSIS package that imports data from an Excel file, replaces any value in Excel that reads "NULL" to "", then writes the data to a couple of databases.
What I have discovered today, is I have two columns of dates, an admit date and discharge date column, and what I need to do is anywhere I have a null value in the discharge date column, I have to replace it with the value in the admit date column.Â
I have searched around online and tried a few things using the Replace funtion in Derived columns but no dice so far.Â
View 3 Replies
View Related
Feb 12, 2014
I want to add $ symbol to column values and convert the column values to western number system
Column values
Dollar
4255
25454
467834
Expected Output:
$ 4,255
$ 25,454
$ 467,834
My Query:
select ID, MAX(Date) Date, SUM(Cost) Dollars, MAX(Funded) Funding from Application
COST is the int datatype and needs to be changed.
View 2 Replies
View Related
Mar 31, 2008
Let's say you had a User table and one of the fields was called Deceased. It's a simple closed-ended question, so a bit value could be used to satisfy the field, if the person is dead or alive. Let's say another field is called EyeColor. A person can have only one eye color and thus one answer should be stored in this value, so this is easy as well.
Now, let's say I want to store all the languages that a specific user can speak. This isn't as easy as the previous examples since it's not a yes or no or a single-value answer. I haven't had much experience with working with databases so I've come up with two possible ways with my crude knowledge hehe.
In terms of inputting the multi-answer values, I suppose I could use a multiple-selection listbox, cascading dropdowns, etc. Now, here are the 2 solutions that came to mind.....
1) Make a field called LanguagesSpoken in the User table. When I process the selections the user makes on the languages he knows, I can then insert into the LanguagesSpoken field a string "English, Spanish, Czech" or IDs corresponding to the languages like "1, 5, 12" (these IDs would be referenced from a separate table I guess). I would use commas so that later on, when I need to display a user's profile and show the user's languages, I can retrieve that long string from the LanguagesSpoken field, and parse the languages with the commas I've used. Using commas would just be a convention I use so I would know how to parse (I could have used "." or "|" or anything else I guess) the data.
2) Forget about the LanguagesSpoken field in the User table altogether, and just make a LanguagesSpoken table. A simple implementation would have 3 fields (primary key, userId, languageId). A row would associate a user with a language. So I would issue a query like "SELECT * FROM LanguagesSpoken WHERE userId=5" (where userId=5 is some user). Using this method would free me from having to store a string with delimited values into the User table and then to parse data when I need them. However, I'm not sure how efficient this method would be if the LanguagesSpoken table grows really large since the userIds would NOT be contiguous, the search might take a long time. I guess I would index the userId field in the LanguagesSpoken table for quicker access?
OR, I may be going about this the wrong way and I'm way out on left field with these 2 solutions. Is there a better way other than those 2 methods?
I haven't work extensively with databases and I'm just familiar with the basics. I'm just trying to find out the best-practice implementation for this type of situation. I'm sure in the real world, situations like this is very common and I wonder how the professionals code this.
Thanks in advance.
View 3 Replies
View Related
Jan 30, 2008
Hi.
I have this method in a class, it produces a string value based on the value of another property in the object (which represents a field in the database). I would like to turn this into a computed column in SQL server... but need help converting the formula if this is even possible. Thanks in advance.public string GetVendorEvalRating(int vendorevaltotal)
{
string vendorevalrating = "";if (vendorevaltotal >= 26)
{vendorevalrating = "Critical";
}else if ((vendorevaltotal >= 10) && (vendorevaltotal <= 25))
{vendorevalrating = "Material";
}else if ((vendorevaltotal >= 0) && (vendorevaltotal <= 9))
{vendorevalrating = "Minor";
}return vendorevalrating;
}
View 16 Replies
View Related
Sep 21, 2005
Currently I'm trying to add a column based on certain criteria based on the following data:
CallID GroupName CustomerPending
------ ----------- ------------
00500588FollowupN
00500588FollowupN
00500588Server N
00500588Service DeskN
00500588Service DeskN
Basically I'm trying to add an extra column, so that whenever the GroupName is "Followup", then a 'Y' will appear in the CustomerPending column for all instances of that CallID. I tried with the following, but it only provides a 'Y' in the rows (not the CallId's) where "Followup" is found.
--------------------------------------
UPDATE dbo.Asgnmnt
SET CustomerPending = 'Y'
FROM dbo.Asgnmnt
WHERE dbo.Asgnmnt.GroupName IN ('SD Followup')
ALTER Table Asgnmnt
ALTER column CustomerPending varchar(1)
UPDATE dbo.Asgnmnt
SET CustomerPending = 'N'
FROM dbo.Asgnmnt
WHERE dbo.Asgnmnt.GroupName NOT IN ('SD Followup')
ALTER Table Asgnmnt
ALTER column CustomerPending varchar(1)
---------------------------------------
Any assistance appreciated.
View 4 Replies
View Related
Feb 13, 2008
I have a report with a date type parameter. Depending on the value return by this date type parameter the dataset will return either the credit, deposit or process date. How do I go about coding it so that it will dynamically select the right column in my query for my dataset?
Sincerely appreciate all the help I can get.
Thanks in advance.
View 11 Replies
View Related
May 24, 2004
For example, consider the following queries:
DECLARE @SomeParam INT
SET @SomeParam = 44
SELECT *
FROM TableA A
JOIN TableB B ON A.PrimaryKeyID = B.ForeignKeyID
WHERE B.SomeParamColumn = @SomeParam
SELECT *
FROM TableA A
JOIN TableB B ON A.PrimaryKeyID = B.ForeignKeyID AND B.SomeParamColumn = @SomeParam
Both of these queries return the same result set, but the first query filters the results in the WHERE clause whereas the the second query filters the results in the JOIN criteria. Once upon a time a DBA told me that I should always use the syntax of the first query (WHERE clause). Is there any truth to this, and if so, why?
Thanks.
View 3 Replies
View Related
Dec 9, 2013
I have SQL Server 2012 SSIS. I have Excel source and OLE DB Destination.I have problem with importing CustomerSales column.CustomerSales values like 1000.00,2000.10,3000.30,NotAvailable.So I have decimal values and nvarchar mixed in on Excel column. This is requirement for solution.However SSIS reads only numeric values correctly and nvarchar values are set as Null. Why?
CREATE TABLE [dbo].[Import_CustomerSales](
 [CustomerId] [nvarchar](50) NULL,
 [CustomeName] [nvarchar](50) NULL,
 [CustomerSales] [nvarchar](50) NULL
) ON [PRIMARY]
View 5 Replies
View Related
Jun 18, 2015
Bitmask fields! I am capturing row changes manually via a high frequency ETL task. Â It works effectively however i am capturing the movement of multiple fields. Â A simple example, for Order lines, i have a price, a discount and a date. Â I am capturing a 001, 010, 100 respectively for each change. Â
I would like my users to be able to select from a dimension which has the 3 members in it and they can select one, multiples, or all values (i.e. only want to see rows that have had the date and price changed).Â
Obviously if i only had 3 columns i would use bit's and be done with it, i have many different values (currently around 24 and growing).
View 2 Replies
View Related
Aug 3, 2015
How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?
View 5 Replies
View Related
Jul 9, 2015
I have a table with 2 columns and my source data looks like this..
PolicyNum  InsCode   Â
1ABC12Â Â Â Â Â Â Â Â 1001Â Â Â Â Â Â Â Â
1ABC12Â Â Â Â Â Â Â Â Â 1002Â Â Â Â Â Â Â Â
1ABC12Â Â Â Â Â Â Â Â Â 1003Â Â Â Â Â Â Â
1ABC12Â Â Â Â Â Â Â Â Â 1004Â Â Â Â Â Â Â
1ABC12Â Â Â Â Â Â Â Â Â 1005Â Â Â Â Â Â Â Â
[Code] ....
My output should look like this..I need T-sql to get below output.
PolicyNum  InsCode1  InsCode2   Â
1ABC12Â Â Â Â Â Â Â Â Â Â Â 1001Â Â Â Â Â Â 1005Â Â Â Â Â Â Â
1ABC12Â Â Â Â Â Â Â Â Â Â Â Â 1002Â Â Â Â Â Â 1006Â Â Â Â Â Â Â Â
1ABC12Â Â Â Â Â Â Â Â Â Â Â 1003Â Â Â Â Â Â 1004Â Â Â Â Â Â Â
1ABC20Â Â Â Â Â Â Â Â Â Â Â Â 1001Â Â Â Â Â Â 1005Â Â Â Â Â Â Â Â
[Code] ...
Basically it's converting certain row values to new column. Every PloicyNum will have 1001 to 1006 Fixed InsCode values as a group.
Rule-1: InsCode value 1001 should always mapped to 1005Â Â Â
          InsCode value 1002 should always mapped to 1006
           InsCode value 1003 should always mapped to 1004Â
Rule-2: For a policyNum, If any Inscode value is missed from the group values 1001 to 1006, still need to mapped with corresponding values as shown in Rule-1
In the above sample data..
for PolicyNum - 1ABC20 , group values 1003,1006 are missing
for PolicyNum - 1ABC25 , group values 1002,1003,1004,1005,1006 are missing
Create Table sampleDate (PolicyNum varchar(10) not null, InsCode Varchar(4) not null)
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1001')Â Â Â Â Â Â Â Â
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1002')Â Â Â Â Â Â Â
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1003')Â Â Â Â Â Â
[Code] ....
View 14 Replies
View Related
Sep 14, 2007
I'm working on a social network where I store my friend GUIDs in a table with the following structure:user1_guid user2_guidI am trying to write a query to return a single list of all a users' friends in a single column. Depending on who initiates the friendship, a users' guid value can be in either of the two columns. Here is the crazy sql I have come up with to give what I want, but I'm sure there's a better way... Any ideas?SELECT DISTINCT UserIdFROM espace_ProfilePropertyWHERE (UserId IN
(SELECT CAST(REPLACE(CAST(user1_guid AS VarChar(36)) + CAST(user2_guid AS VarChar(36)), @userGuid, '') AS uniqueidentifier) AS UserId FROM espace_UserConnection WHERE (user1_guid = @userGuid) OR
(user2_guid = @userGuid))) AND (UserId IN
(SELECT UserId FROM espace_ProfileProperty))
View 1 Replies
View Related
Sep 16, 2004
This is a report I'm trying to build in SQL Reporting Services. I can do it in a hacky way adding two data sets and showing two tables, but I'm sure there is a better way.
TheTable
Order# Customer Status
STATUS has valid values of PROCESSED and INPROGRESS
The query I'm trying to build is Count of Processed and INProgress orders for a given Customer.
I can get them one at a time with something like this in two different datasets and showing two tables, but how do I achieve the same in one query?
Select Customer, Count (*) As Status1
FROM TheTable
Where (Status = N'Shipped')
Group By Customer
View 2 Replies
View Related
Dec 21, 2007
Can anyone show how to alter the value in a column using DerivedColumn component when creating an SSIS package programatically.
View 4 Replies
View Related
Apr 28, 2008
I need to create the following table in reporting services
PRODUCT April March Feb
2008 2007 2008 2007 2008 2007
chair 8 9 7 4 4 4
table 3 4 5 6 4 6
My problem is the month names are a column in the dataset, but I dont know how to get it to fill as column headers???
Thanks in advance!!!
View 1 Replies
View Related
Jan 18, 2005
If I was asked "How many days passed" the query is (below) and the answer is 33. But, then the curve ball is "can you subtract out the weekends?" and I said....well, I said I think so, but not sure how. So far, I have had no luck. Any advise?
SELECT
DATEDIFF ( dd , dbo.table_case.creation_time , dbo.table_close_case.close_date ) AS no_of_days,
dbo.table_case.id_number,
dbo.table_case.creation_time CreateTime,
dbo.table_close_case.close_date CloseDate
FROM
dbo.table_case,
dbo.table_close_case
WHERE
dbo.table_close_case.last_close2case=dbo.table_case.objid AND
dbo.table_case.id_number = '969382'
ORDER BY no_of_days ASC
View 2 Replies
View Related
Jun 2, 2007
Hello:
I need to change minus brackets () with - sign in SSRS. How i can do this?
Thanks
Amit
View 11 Replies
View Related
Jan 7, 2008
Good afternoon,
I am trying to figure out a way to use a columns default value when using a stored procedure to insert a new row into a table. I know you are thinking "that is what the default value is for", but bare with me on this.
Take the following table and subsequent stored procedure. In the table below, I have four columns, one of which is NOT NULL and has a default value set for that column.
CREATE TABLE [dbo].[TestTable](
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[SSN] [nvarchar](15) NULL,
[IsGeek] [bit] NOT NULL CONSTRAINT [DF_TestTable_IsGeek] DEFAULT ((1))
) ON [PRIMARY]
I then created the following stored procedure:
CREATE PROCEDURE TestTable_Insert
@FirstName nvarchar(50),
@LastName nvarchar(50),
@SSN nvarchar(15),
@geek bit = NULL
AS
BEGIN
INSERT INTO TestTable (FirstName, LastName, SSN, IsGeek)
VALUEs (@FirstName, @LastName, @SSN, @geek)
END
GO
and executed it as follows (without passing the @geek parameter value)
EXEC TestTable_Insert 'scott', 'klein', '555-55-5555'
The error I got back (and somewhat expected) is the following:
Cannot insert the value NULL into column 'IsGeek', table 'ScottTest.dbo.TestTable'; column does not allow nulls. INSERT fails.
What I would like to happen is for the table to use the columns default value and not the NULL value if I don't pass a parameter for @geek. OR, it would be really cool to be able to do something like this:
INSERT INTO TestTable (FirstName, LastName, SSN, IsGeek)
VALUEs (@FirstName, @LastName, @SSN, ISNULL(@geek, DEFAULT))
Does this make sense?
View 9 Replies
View Related
Mar 3, 2008
Can anyone assist me with a script that adds a new column to a table then inserts new values into the new column based on the Table below. i have included an explanation of what the script should do.
Column from
Parts Table Column from
MiniParts New Column in
(Table 1 ) (Table 2 ) MiniParts (Table2)
PartsNum
MiniPartsCL
NewMiniPartsCL
1
K
DK
1
K
K
1
Q
Q
0
L
L
0
L
LC
0
D
G
0
S
S
I have 2 tables in a database. Table 1 is Parts and Table 2 is MiniParts. I need a script that adds a new column in the MiniParts table. and then populate the new column (NewMinipartsCL) based on Values that exist in the PartsNum column in the Parts Table, and MiniPartsCL column in the MiniParts columns.
The new column is NewMiniPartsCL. The table above shows the values that the new column (NewMiniPartsCL) should contain.
For Example
Anytime you have "1" in the PartsNum column of the Parts Table and the MiniPartsCL column of the MiniParts Table has a "K" , the NewMiniPartsCL column in the MiniParts Table should be populated with "DK" ( as shown in the table above).
Anytime you have "1" in the PartsNum column of the Parts Table and the MiniPartsCL column of the MiniParts Table has a "K" , the NewMiniPartsCL column in the MiniParts Table should be populated with "K" ( as shown in the table above). etc..
View 3 Replies
View Related
Feb 25, 2008
Hi, how are you?
I'm having a problem and I don't know if it can be solved with a derived column expression. This is the problem:
We are looking data in a a sql database.
We are writting the SQL result in a flat file.
We need to transform data in one of the columns.
For example: we can have 3 digits as value in a column but that column must be 10 digit length. So we have to complete all the missing digits with a zero. So, that column will have the original 3 digits and 7 zeros. How we can do that tranformation? We must do it from de the flat file or it can be a previous step?
Thanks for any help you can give me.
Regards,
Beli
View 10 Replies
View Related
Jul 26, 2007
Hi,
I was wondering if anyone knows a command that will select all fields in a table except one?
Thanks!
Lee
View 6 Replies
View Related
Jul 20, 2005
Hi,I've used the minus functionality which is available in Oracle andi would like to use it in SQL server, but i don't know how to. Thefolllowing is how it works in OracleSelect symbols from symbol_tableminusselect tsymbols from tradeIt returns a list of all the symbols from symbol_table which are notpresent in trade.Similarly, the intersect will return only those which are common toboth.I was wondering if someone throw some light on this problem for me.Thanks in advance,Sumanth
View 1 Replies
View Related
Mar 23, 2006
Hi,
I've to translate this SQL-Statement from ORACLE to SQL-Server. But I'm missing the Minus-Statement on SQL-Server.
select table_name, column_name from user_tab_columns
where table_name not in ( select table_name from user_tab_columns
minus
select tab_name from data_dic
)
minus
select tab_name, col_name from data_dic
what can I do to run it on SQL-Server.
Thanks in advance
Raimund
View 4 Replies
View Related
Apr 22, 2003
Hi,
Is there something equivalent to the MINUS in ORacle ?
Or a workaround ?
thanks
View 11 Replies
View Related
Jul 25, 2005
I would like to get a feed back from people who using Express edition.
What are the Plus and Minus points which you can list agenst Express Edition of MsSQL !
Please list them from your experiance rather than the documentation!
NB:- Specify : OS - Development Platforms - If any installation problems found in your Desk.
Thank you.
Thank you.
View 5 Replies
View Related
Feb 20, 2008
Hi
I am currently trying to import data from a table in 1 database into a table of the same name in another database. This in it's self is simple, however to add a twist to the proceedings there is data that exists in both tables. I just want to import the data that doesn't exist in the table I am importing into.
Please can you advise as to the best method to use
Many thanks
Paul
View 5 Replies
View Related
Aug 25, 2015
The charindex can run this
LEFT([Description], CHARINDEX('(', [Description]) + 1) Â as NewDesc,
But I can't run
LEFT([Description], CHARINDEX('(', [Description]) - 1) Â as NewDesc,
View 5 Replies
View Related
Nov 25, 2014
I got a table where i need to add certain rows and minus the valu with a row.
IDC1C2C3C4C5C6
1551557775
266201452
345222266
441727582
532556951
6022022
7182344142
8
Result in the 8 Row
Sum(id(2,3,4)-sum(id(6,7))
View 2 Replies
View Related
Mar 22, 2006
I have two tables CarType & Cars. Table CarType has a column CarTypeId which is the primary key (int, identity). Table Cars includes a column CarTypeId (int) which is a foreign key into the CarType table.Now I am trying to build a list of all those CarTypeId entries (from the CarType table) for which there are no Cars (i.e. there is no corresponding entry in the Cars table).I read that there is an SQL "MINUS" keyword that you can use like this:
SELECT CarTypeId FROM CarTypeMINUSSELECT CarTypeId FROM Cars..So if CarType contains records with the keys {1, 2, 3, 4, 5} and Cars contains records with the foreign keys {2, 4} (using set notation), your result should be the set {1, 3, 5}. (Note: I have tried alternate versions of this where I selected the DISTINCT CarTypeId entries from Cars, and where I sorted them; results same in both cases).I tried it in SQL Server 2005 Express. The result is just {1, 2, 3, 4, 5}.
Is the MINUS capability supported by SQL Server? I know that there are all sorts of different SQL dialects & implementations and that SQL has evolved & transmutated over the years; perhaps they opted to leave this out of SQL Server..
Here's a reference to the website where I initially found out about MINUS: [URL] ....
View 19 Replies
View Related
Feb 6, 2015
Have the following in my SELECT Stataement
CASE WHEN com.completion_date IS NOT NULL AND dim.DayName <> 'Saturday'
THEN DATEDIFF(d, com.current_task_target_date,com.completion_date) - non1.NoWorkDays
WHEN com.completion_date IS NOT NULL AND dim.DayName = 'Saturday'
THEN DATEDIFF(d, com.current_task_target_date,com.completion_date)
ELSE NULL
END AS 'DaysOverTarget'
Some of the figures coming back are minus figures. How could I get the minus figures reported to be 0.00?
Below is the full TSQL
SELECT DISTINCT com.comm_reference AS 'Referance'
,com.crt_date AS 'CreatedDate'
,com.current_task_target_date AS 'TargetDate'
,com.completion_date AS 'CompletionDate'
,CASE WHEN com.completion_date IS NOT NULL AND dim.DayName <> 'Saturday'
THEN DATEDIFF(d, com.crt_date,com.completion_date) - non.NoWorkDays
[Code] .....
View 4 Replies
View Related
