SQL Server 2012 :: If Minus Figure Pull Back 0

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


ADVERTISEMENT

SQL 2012 :: Join Tables And Only Pull Back Certain Values?

Oct 22, 2014

I am having problems joining these two tables and returning the correct values. The issue is that i have a work order table and a revenue table. I only want to return the sum of the revenue when the revenue comes after the work order date. That is simple enough, but it gets tricky when there are multiple work orders for the same ID. for those instances, we only want the sum of the revenue if it shows up post the work order date and if it is before any other work order date. So ID 312187014 should only have the 9-5 revenue from below, not the 7/7 or 8/6 revenue because the 8/7 work order date is after those revenue dates and thus will not have any revenue tied to it because there is a 9/3 work order that ties to the 9/5 revenue. Additionally the 412100368 ID has a 7/7 work order that ties to the 7/26 revenue, and the 8/7 work order will tie to the 8/23 and 9/20 revenue

--===== Create the test table with

CREATE TABLE #workorder
(
Id varchar(20),
wo varchar(10),
wodate datetime,
amount float
)
GO
CREATE TABLE #revenue

[code].....

View 2 Replies View Related

SQL Server 2012 :: Adding Certain Rows And Minus Value With A Row

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

Pull Back Data Using A Dataset

Feb 19, 2008

Hello, I am trying to pull run this sql statement but it's bombing out at the comm.ExecuteNonQuery();. ..
 
Could someone help me figure this out..
 Ex.System.Int32 bum = System.Convert.ToInt32(Request.QueryString["dum"]);
SqlConnection conn = new SqlConnection("Data Source=**********************");SqlCommand comm = new SqlCommand();
comm.Connection = conn;SqlDataAdapter myadapter = new SqlDataAdapter(comm);DataSet myset = new DataSet();
conn.Open();
 comm.CommandText = "Select * from Order_Forms where (Order_Num = " + Session["dum1"] + " ) ";
 
comm.ExecuteNonQuery();myadapter.Fill(myset, "Order_Forms");
myset.AcceptChanges();
 
Can yo usee the problem???
 
 
 

View 7 Replies View Related

Correct Syntax To Pull Back Duplicate Vendors Based On 6 Fields From Two Different Tables

Feb 20, 2015

I'm looking for the correct syntax to pull back duplicate vendors based on 6 fields from two different tables. I want to actually see the duplicate vendor information (not just a count). I am able to pull this for one of the tables, something like below:

select *
from VendTable1 a
join ( select firstname, lastname
from VendTable1
group by firstname, lastname
having count(*) > 1 ) b
on a.firstname = b.firstname
and a.lastname = b.lastname

I'm running into issues when trying to add the other table with the 4 other fields.

View 5 Replies View Related

SQL Server 2012 :: Pull Expected Results When Using Strings With Comparison Operators?

Mar 1, 2015

We can use comparison operators with strings as well. Hence, I tried to use the following query on a SQL Server 2012 instance with the sample AdventureWorks2012 database (the collation of the database and of the column is the default:

SQL_Latin1_General_CP1_CI_AS):

USE AdventureWorks2012 ;
GO

--Returns 5 records
SELECT pp.Name
FROM Production.Product AS pp
WHERE pp.Name >= N'Short' AND pp.Name <= N'Sport' ;
GO

The query only returns 5 records. This despite the fact that the search is an inclusive search and the Production.Product table contains records that begin with "Sport".

Now, when I replace "Sport" with "Sporu" (just moving one character up in the alphabet to verify whether characters after the word have any impact on the search) gives me 8 records.

USE AdventureWorks2012 ;
GO

--Returns 8 records
SELECT pp.Name
FROM Production.Product AS pp
WHERE pp.Name >= N'Short' AND pp.Name <= N'Sporu' ;
GO

What's going on inside of SQL Server that allows it to fetch "Short-Sleeve Classic Jersey" for the starting word "Short" but prevents it from fetching "Sport-100 Helmet" for the ending word "Sport" despite the search being an inclusive search?

View 3 Replies View Related

Real Figure Instead Of Exponential Figure Needed

May 29, 2008

Hello,

I use OPENROWSET to read values from Excel and store them in a SQL Server table. In the Excel file I have a row having format 'Number' with two decimal places.

Example: 1225000.00

When I select this value using SSMS I get the correct value:

1225000

Strange enough, I cannot see the decimals anymore. However, when I now store this value into my table and then select it from there I get: (the datatype in the table is VARCHAR(max))

1.225e+006

I would not care if I could convert this back to a numeric datatype but this seems not to work: CAST('1.225e+006' as INT) throws an exception. Obviously OPENROWSET sends the data strictly as a character string. Storing this into varchar(max) works for small figures but it starts to use exp values for big figures.

Does anybody has an idea how to bring huge Excel based figures safely into a MS SQL Table ?

Thanks: Peter

View 5 Replies View Related

SQL Server 2012 :: Using Xquery To Get Event Data Back From Extended Events?

Feb 25, 2014

I am trying to use xquery to get event data back from extended events. I am trying to use some sample data from Grant Fritchey but I am getting null records back. Below is the xml - I just want to retrieve a distinct list of the client_hostname and client_app_name.

<event name="login" package="sqlserver" timestamp="2014-02-19T23:53:54.299Z">
<data name="is_cached"><value>true</value></data><data name="is_dac">
<value>false</value></data><data name="database_id"><value>7</value>
</data><data name="packet_size"><value>8000</value></data><data name="options">

[Code] ....

The query I have that doesn't work is :

WITH xEvents AS
(SELECT object_name AS xEventName,
CAST (event_data AS xml) AS xEventData
FROM sys.fn_xe_file_target_read_file
('C:LoginTraceShared_0*.xel', NULL, NULL, NULL))
SELECT distinct top 1000 xEventName,
xEventData.value('(/event/data[@action_name=''Client_APP_Name'']/value)[1]','varchar') Client_APP_Name,
xEventData.value('(/event/data[@action_name=''Client_Host_Name'']/value)[1]','varchar') Client_Host_Name
FROM xEvents

View 3 Replies View Related

Including MTD And YTD Figure With A Daily Figure

Feb 4, 2008

Hello,
I have a query that returns a daily revenue figure. The query is as follows:

SELECT top 1000
ds.AcctCode,
ds.TxnDate,
SUM(isnull(ds.FuelFee,0)) + SUM(isnull(ds.CashFee,0)) + SUM(isnull(ds.ScFee,0)) AS TotalDailyRevenue,
--"MTD" = ?,
--"YDT" = ?,
ps.TC,
CASE
WHEN ps.Proj = 100 THEN 'New Account'
WHEN ps.Proj = 200 THEN 'Current Account'
END AS ProjStatus,
ps.FSR,
ps.SubmitRep1

FROM
TxnRptg.dbo.tbl_DailySummary ds
INNER JOIN SalesData.dbo.tbl_CYProcessedSales ps
ON ds.AcctCode = ps.Acct

WHERE
MONTH(ds.TxnDate) = 1
AND
Proj IN (100,200)
AND TC = 'HV'

GROUP BY
ds.AcctCode, ds.TxnDate, ps.TC, ps.Proj, ps.FSR, ps.SubmitRep1

ORDER BY
ds.AcctCode, ds.TxnDate

--*********************************

TxnDate represents a single day of the month. How can I include MTD so that the dates for the revenue total are from DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) (beginning of current month) to TxnDate, and YTD so that the revenue totals are from DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) (beginning of the current year) to TxnDate?


Thank you for your help!


cdun2

View 5 Replies View Related

SQL Server 2012 :: How To Pull Value Of Query And Not Value Of Variable When Query Using Select Top 1 Value From Table

Jun 26, 2015

how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...

create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120))
insert into #temptable
SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10
--column name
declare @cname varchar(30)

[code]...

View 4 Replies View Related

SQL Server 2012 :: View To Concatenate File / Path Names Back To Root Directory?

Sep 25, 2014

We have a hierarchical table of some 2-3k of rows that grows slowly at only 3-5 rows a month, and is never likely to be above 5k, holding file/directory names with an IdParent int value pointing at that items immediate parent or NULL if root

Below is a sample:

Id Name IdParent
1 C: NULL
2 D: NULL
3 ProgramFiles 1
4 DataFiles 2
5 Excel.EXE 3
6 MyDataList.CSV 4

iterative code required to achieve the result I am looking for is best handled in the Application layer, but in this case the design brief is that we must get our results back as a View. Speed of execution and code clarity are secondary 'icing on the cake' issues.

I have tried recursive CTE's and XML FOR solutions but without success, and while I feel the latter is probably the best all rounder the searches I have made and the examples I have read do not seem to solve this particular issue.

Even to find the starting point of the search tree (i.e. the .EXE and the .CSV items) was difficult because the final slash is optional in some of the rows (.e. row 4)

The end result of my view on the above data should return:

Id FilePath
5 C:ProgramFilesExcel.EXE
6 D:DataFilesMyDataList.CSV (note the adding of the missing delimiter between DataFiles and MyDataList)

View 5 Replies View Related

Oracle MINUS Equivalent In Sql Server

Apr 22, 2003

Hi,
Is there something equivalent to the MINUS in ORacle ?
Or a workaround ?
thanks

View 11 Replies View Related

SQL Server Express Edition - Plus And Minus

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

Rolling Back SQL Server 2005 Databases Back To SQL Server 2000

Sep 22, 2006

Does anybody know of a way to rollback SQL Server 2005
databases back to SQL Server 2000? Is there a way of doing it without
resorting to Copy Database Wizard? I love to find a way of attaching a SS 2005 database
to a SS 2000 instance without any issues.



I recently upgraded to SS 2005 and I am very unhappy with the SS 2005 and I
want to rollback to SS 2000, which was a lot more stable. I am having
several major issues that are affecting my whole company's day-to-day
operations and the managers are not happy. Some of the issues include
night time batch running very sluggish for no apparent reason. This is a
biggest problem because it only occurs once or so a week and causes a disturbance
with the daily activities when the night time processing isn€™t completed on
time. The rest of the time, the batch processing runs great, even a little better then on SS 2000. I
don't believe it is a matter of my application needing to be retuned because if
that was the case, then why isn't it running sluggish every night? Also,
it's never the same day that the sluggish behavior occurs. If it was occurring
on the same night, then I would have something to investigate within our
application, but it doesn't. Another issue that I am having involves a
night time job that restores a copy of the production database to the Data
Warehouse server to be used for updating the data warehouse. Again, most
of the time it runs great (~2 1/2 hours), but once or twice a week, it goes
stupid and takes 6 1/2 hours for no apparent reason. Again, it is not happening
the same day either, which could give me something to invesigate. On SS 2000, this same job ran flawlessly. Never I did I run into situation that the
database restoration took that long to run. Even another issue involves a SQL Server Agent Job that was put into suspended
state. What's a suspended state and how can I get it out of suspended
state? I can find no information about suspended state in BOL. I
did a Google and nothing came up. If this suspended state was put
in for security reasons, great, but then tell me how I can remove the suspended
state. I am also not happy with the
fact that I can't get accurate information about the queries that are actively
running at that particular moment. In SS 2000, when I noticed high CPU
usage on the server, I would run the sp_who2 active stored proc and it would
show me all the active thread and how much CPU it was consuming. I would
then find the running threads with the highest CPU numbers and investigate the
query and see if we could improve it. Now in SS 2005, I get in the same
situation and run the sp_who2 stored proc, and there is no smoking gun.
All of the active threads are showing very little CPU usage, which I am very
suspect of. What the heck happen to sp_who2? I looked at some of
the other ways of looking at running processes (i.e... sys.sysprocesses) and
they don't appear to be giving the information that I need.



I am very unhappy and I just want to roll back to SS 2000 and wait a couple of
years before I upgrade to SS 2005.


Dave Brown

View 1 Replies View Related

Transact SQL :: Does Server Support MINUS Keyword

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

SQL 2012 :: Pull String Inside Stored Procedure Definition

May 5, 2014

In the below procedure definition, i need to find a way parse the definition and get the list of places where the where clause is being used.

SELECT DISTINCT
FC.CASE_ID,
UPPER(FC.CASE_NUMBER) AS CASE_NUMBER,
UPPER(REPLACE(FC.CASE_SHORT_TITLE,CHAR(13)+CHAR(10),'')) AS CASE_SHORT_TITLE,
FC.CASE_STATUS_DESCR,
FC.CASE_TYP_DESC, FC.CASE_SUB_TYP_DESC,

[Code] ....

In the above query there are more than one places and the where clause may not have the same string the where clause it can be with a space between the "=" and the value in single quotes.

Result set should be in the below format:

TABLE NAME Column Name VALUE
CFG_ELEMENTS ELEMENT_NAME REPORT_CONSOLIDATEDCASES_CASERELATEDTYPID

View 9 Replies View Related

SQL 2012 :: Database Project Schema Compare Fails To Pull In CDC Tables

Jul 11, 2014

I have a database project where objects have been pulled in from the database using schema compare.

Unfortunately CDC tables which are referenced in stored procedures on the database have not been pulled in by the schema compare & hence I cannot build the project and deploy changes back to the database.

How to get these tables included in the project .

View 1 Replies View Related

RDA Pull Problem: Command=PULL Hr=80040E4D Login Failed For User 'test'

Apr 25, 2007

Hi all,

I have following problem:

I'm developing a Windows Mobile application, which is using RDA Pull for retrieving data from SQL Server 2005 database to PDA. Please, see the example:






Code Snippet

using (SqlCeEngine engine = new SqlCeEngine(connStr))

{

engine.CreateDatabase();

}

serverConnStr="Provider=SQLOLEDB;Data Source=.;User ID=sa;Initial Catalog=Demo;Password=xxx";

using (SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess(

Configuration.Default.SyncServerAddress, "", "", connStr))

{

rda.Pull("MyTable", "SELECT * FROM mytable", serverConnStr, RdaTrackOption.TrackingOffWithIndexes, "ErrorTable");

}





Everythink works fine, when I use 'sa' user account in serverConnStr.

But, when I change conn string to:

"Provider=SQLOLEDB;Data Source=.;User ID=test;Initial Catalog=Demo;Password=test"

the sqlcesa30.dll cannot connect to SQL Server database.

In the sqlcesa30.log then I found following line:




Code Snippet

2007/04/17 10:43:31 Thread=1EE30 RSCB=16 Command=PULL Hr=80040E4D Login failed for user 'test'. 18456



The user 'test' is member of db_owner, db_datareader and public roles for the Demo database and in SQL Server Management Studio I'm able to login to the Demo database with using the 'test' users credentials and I'm able to run the select command on 'mytable'.



So, what's wrong? Why the sqlcesa30.dll process cannot login to the Demo database, and from another application with using the SAME connection string it works?



Please help.



Thank you.

Fipil.



View 10 Replies View Related

How Do I Figure Out The Database_Host And Database_Name For My Sharepoint SQL Server?

Dec 8, 2007

I'm trying to access my sharepoint sql server through php, and normally I would set the connection like this:

define('DB_USER', 'user');
define('DB_PASSWORD', 'pass');
define('DB_HOST', 'database host');
define('DB_NAME', 'database name');

$dbc = mssql_connect(DB_HOST, DB_USER, DB_PASSWORD) or die('coudlnt connect to mssql server' .mssql_error());
mssql_select_db(DB_NAME) or die('couldnt connect to that database:' .mssql_error());

How do I figure out what the DB_HOST and DB_NAME is?

And I'm assuming my normal username and password to the server would work, right?

View 5 Replies View Related

SQL 2012 :: AlwaysOn Async Replica Back To Sync

Aug 19, 2014

After failing over to the DR replica. All databases are out of sync. DR replicas were setup as async the other 2 are set up as sync. Is this by design. No data has been updated to any of these as they are test dbs. So all dbs should be the same, no data loss.

View 0 Replies View Related

Still Trying To Figure Out Identification Of SQL Server Stored Proc. Permissions Via VB

Jul 20, 2005

I've looked through many suggestions and partial examples all overthis newsgroup and still am not coming up with anything that doesspecifically what I'm wanting to accomplish.I'm writing a VB 6.0 application which uses SQL Server as theback-end.Here's an example of what I'm wanting to do...A user accessing the VB GUI attempts to open a certain form. Coderuns behind the scenes in VB that checks the user's "Execute"permissions on the stored procedure that retrives the data into theform. If the user is determined to have "Execute" permissions to thatstored procedure, the user is allowed to proceed with opening theform. If they don't have permissions, they are informed of this andaren't allowed to open the form and view the data.Let's say that we initially determine through the VB code that theuser has the ability to "Execute" the stored procedure that allows forviewing of the data. We go ahead and let them open the form. Oncethe form is being opened though, we run VB code to check for theirpermissions on other stored procedure that can be used in UPDATING /INSERTING / DELETING the records being displayed in the form. If theydon't have "Execute" permissions on these particularUpdate/Insert/Delete stored procedures, then I'll have a text box onthe form show as visible to advise them while they're looking at thisdata that they can't do anything to this data other than view it...they can't insert new records, they can't delete any records, and theycan't update any records.I have yet to find specific code that tells me how to checkpermissions on a storedc procedure for the user that's logged-in, whatthe resulting codes (i.e. 12291, etc.) mean as far as identificationof their permissions, etc.If anyone can help me out here, I'd greatly appreciate it. I'd hateto just have to resort to trying to allow the user to run the storedprocedures and just trap the error codes that may arise once eachstored procedure is executed and inform the user on the tail-end ofthe process that they can't run the stored procedure. I'd like toidentify their permissions to the stored procedures on the front-endof the process either before each form is opened or just as it'sopening to advise them early as to what they can/can't do with thedata being displayed in the form.Thanks in advance for any examples/information!Sincerely,Brad H. McCollumJoin Bytes!

View 1 Replies View Related

SQL 2012 :: Data Migration - Pull Data From All DBs To Input Into DW Table

Jul 15, 2014

I have a cluster hosting multiple GP databases and a second for my data-warehouse I am playing around with (personal project).

I have scripts that pull data from all the DB's to input into the DW's tables(Customers,Reps,Hub....)

An example of my branch script :

select interidas BranchID,
cmpnynamas BranchDesc,
address1as BranchAddressLine1,
address2as BranchAddressLine2,
address3as BranchAddressLine3,
zipcodeas PostalCode
from dynamics..SY01500

Where interid in ('comp1', 'comp2', 'comp4', 'comp5')

what would be the best way to using these scripts pull the data to my testDW and not have duplicate data issues?

I was thinking of using a staging DB on the GP cluster and then building an import data package to run nightly. the issue i had was how do i avoid duplicate data ?

View 0 Replies View Related

Minus The Weekend

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

Minus Sign ()

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

Having Difficulty Setting Back Up To Back Up File Wihout Datetime Stamp SQL 2K

Apr 24, 2007

Hello,I'm trying to create a simple back up in the SQL Maintenance Plan that willmake a single back up copy of all database every night at 10 pm. I'd likethe previous nights file to be overwritten, so there will be only a singleback up file for each database (tape back up runs every night, so each daysback up will be saved on tape).Every night the maintenance plan makes a back up of all the databases to anew file with a datetime stamp, meaning the previous nights file stillexists. Even when I check "Remove files older than 22 hours" the previousnights file still exists. Is there any way to create a back up file withoutthe date time stamp so it overwrites the previous nights file?Thanks!Rick

View 5 Replies View Related

Mirroring :: Principal Database Get Role Back After Being Back On Line

May 14, 2015

New to Database Mirroring and I have a question about the Principal database server. I have a Database Mirroring setup configured for High-safety with automatic fail over mode using a witness.

When a fail over occurs because of a lost of communication between the principal and mirror, the mirror server takes on the roll of Principal. When communication is returned to the Principal server, at some point does the database that was the previous Principal database automatically go back to being the Principal server?

View 2 Replies View Related

Reporting Services :: Run Two Reports Back To Back Without Page Eject?

Jun 9, 2015

I need to run two reports each of A5 Size to run back to page and print on single A4 paper means in 1st half Sale bill will be printed and in second half Gate Pass Will Be Printed both report will be on same page and size and shape should be maintained. How to do it.

View 4 Replies View Related

SELECT * Minus A Field

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

Minus And Intersect Functionality

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

How Can I Replace The Minus-Statement

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

Import Data Minus Duplicates

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

Transact SQL :: Can Add 1 To Charindex Function But Can't Minus 1

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

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







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