SQL Server 2012 :: Derived Table To Extract Value Of Account For Comparison
Nov 2, 2014
In my TSQL code i use a derived table to extract the value of account 321 to compare if they are the same that the SUM of my line invoice cost multiply by quantity line : Sum(fi.ecusto*qtt)
This is my script:
SELECT ft.ndoc [Doctype],ft.fno [Docnr] , Sum(fi.ecusto*qtt) [totalcostof my Invoiceline], xctb.conta [accountancy account],
sum(Case when ft.tipodoc = 1 then Xctb.ecre else Xctb.edeb end) as [Value of Cost of invoice in accountancy],
[DIF] = Sum(fi.ecusto*qtt) - Sum(Case when ft.tipodoc = 1 then xctb.ecre else xctb.edeb end)
[Code] ....
My problem is if i have more than on line on my invoice, for example 2 lines, the value of column [Value of Cost of invoice in accountancy] are duplicated, for 3 line invoice the value are multiply by 3.
View 7 Replies
ADVERTISEMENT
Aug 1, 2006
I am importing States into a table and need to change all NULL fields before I perform a lookup. I'm using a derived column to replace the state value. I'd like to find all States that are blank and set it to "--".
Ironically, this works:
State != "" ? State : "--"
but
State == "" ? State : "--"
Does not work
Can someone tell me why?
View 4 Replies
View Related
Jun 10, 2014
Is there a limit on the size of the strings on both sides of the '=' sign for string comparison? If I have two varchar(max) strings, will the comparison be done beyond 8,000 characters?
View 1 Replies
View Related
Aug 10, 2015
What i need is to create a function that compares 2 strings variables and if those 2 variables doesn't have at least 3 different characters then return failure , else return success.
View 9 Replies
View Related
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
Jun 3, 2014
I have a 2table like
create table test1
(col1 identity(1,1),
col2 varchar(20)
)
create table test2
(old int,
new int)
Existing record in test1 table
(1,'test')
Now I want to insert a new record into test1 and then capture the old and new identity columsn in test 2 table
i.e. after insert test1 table will be
1,test
2,test
and test2 table will be like
1,2 -- old and new identity columns are captured during insert and stored in test2 table.
If the repeat the process again test2 table will be like
1, test
2,test
3, test
and test2 table will be
2,3 --- test2 table will always have only one record. old records will be deleted.
Using output clause i can get the new identity value.. but how can i get the old identity value and insert that into test2 table..
I am not willing to do a self join after the record is inserted into test2. I wanted to get the old ident value during insert of new ident value.
View 2 Replies
View Related
Apr 14, 2015
how SQL 2012 would treat a literal string for a comparison similar to below. I want to ensure that the server isn't implicitly converting the value as it runs the SQL, so I'd rather change the data type in one of my tables, as unicode isn't required.
Declare @T Table (S varchar(2))
Declare @S nvarchar(255)
Insert into @T
Values ('AR'), ('AT'), ('AW')
Set @S = 'Auto Repairs'
Select *
from @T T
where case @S when 'Auto Repairs' then 'AR'
when 'Auto Target' then 'AT'
when 'Auto Wash' then 'AW' end = T.STo summarise
in the above would AR, AT and AW in the case statement be treated as a nvarchar, as that's the field the case is wrapped around, or would it be treated as a varchar, as that's what I'm comparing it to.
View 3 Replies
View Related
Oct 20, 2014
I have a field which contains something like prj(5616) .
I have been assigned to display the actual name and not the text with the number.
Example: if prj(8616) is called Soccer , then I want display Soccer instead of prj(8616).
View 9 Replies
View Related
Jul 14, 2014
I need to extract the .ispac file from the SSISDB. I can retrieve the stream with catalog.get_project sp. However, the file I end up with cannot be unzipped, giving an error message. My guess is that it is meta-data on the zip/ispac file that has a problem, because I can actually unzip it with Winrar, but not with any of those I (programmatically) need to unzip it with.
Below is the code for my stored procedure - My own suspicion is in the BCP usage to turn the stream into a file.
USE [SSISDB]
GO
ALTER PROCEDURE [dbo].[spGetIspacFile]
@project VARCHAR(255) ,
@environmentFolder VARCHAR(50) ,
@ispacTempFolder VARCHAR(100) ,
@ispacFilePath VARCHAR(200) OUTPUT
[code]....
View 5 Replies
View Related
Nov 19, 2014
We extract 10k tables every night and I have a table that keeps track of ETL tables that fail or succeed. I would like to know if a table fails during the night and nobody kicks off another job to fix it during the day.
The table structure looks like this:
| Table_Name | Time_Start | Status | Duration | Time_End |
Table_Name = varchar(20)
Time_Start = DateTime
Status varchar(7) = Success or Error
Duration = Number
Time_End = DateTime
Select Table_Name into #MyTempTable
From ETL.STATS_Table
Where Status = 'Error'
AND Cast(Time_Start as Date) = GetDate()
How do I take the table names from #MyTempTable and find out if they where successful for the same date? Duration time and Time_End fields aren't needed.
View 5 Replies
View Related
Dec 17, 2014
I have a database table that was designed by someone else, and I'm trying to see if I can normalize the pattern. Here's the dummy table:
CREATE TABLE [dbo].[BadTox](
[PatientID] [int] NULL,
[Cycle] [tinyint] NULL,
[ALOPECIA] [tinyint] NULL,
[Causality1] [tinyint] NULL,
[Code] ....
All the column names in upper case are actually symptom names, and in those columns are values {NULL, 1, 2, 3, 4, 5} and they belong in a column, so the normalized structure should be like this:
CREATE TABLE Symptom (
PatientID INT NOT NULL,
Cycle TINYINT NOT NULL,
SymptomName VARCHAR(20) NOT NULL, -- from the source column *name*
Grade TINYINT NOT NULL -- from the value in the column with the name in uppercase
PRIMARY KEY (PatientID, Cycle, SymptomName));
I can untwist the repeating groups with the code I borrowed from Kenneth Fisher's article [ here ], but the part I'm having a harder time with is grabbing the information that's still left in the column name and integrating it into the solution...
I can retrieve all the column names that are in uppercase using this:
DECLARE @db_id int;
DECLARE @object_id int;
SET @db_id = DB_ID(N'SCRIDB');
SET @object_id = OBJECT_ID(N'SCRIDB.dbo.BadTox');
SELECT name AS column_name
, column_id AS col_order
FROM sys.all_columns
WHERE name = UPPER(name) COLLATE SQL_Latin1_General_CP1_CS_AS
AND object_id = @object_id;
but I can't figure out how to work it into this (that I built by mimicking Kenneth Fisher's article...):
ALTER PROC [dbo].[UnpivotMaxGradeUsingCrossApply]
AS
SELECT PatientID
, Toxicity
, MAX(Grade) AS MaxGrade
[code]....
The problem is that I need to extract the column names (where ToxicityName[n] would be). I can do that by querying the sys.all_columns view, but I can't figure out how to integrate the two pieces. About the only thing I have even dreamed up is to build the VALUES(...) statements dynamically from the values returned by the system view.
So how do I get both the value from the ToxicityName[n] column and the column name into my final data query?
View 5 Replies
View Related
Jul 16, 2015
I am using following sql to extract locking information in database. It only work on current selected database, how can I tune to work on all databases and not only currently selected?
SELECT DISTINCT
ES.login_name AS LoginName,
L.request_session_id AS BlockedBy_SPID,
DATEDIFF(second,At.Transaction_begin_time, GETDATE()) AS Duration_Sec,
DB_NAME(L.resource_database_id) AS DatabaseName,
[Code] ....
View 3 Replies
View Related
Jul 21, 2014
I have a job that needs to execute with different account. I figured i need a proxy so i have created one.Now i need to configure a job that runs a store procedure using that proxy account.
View 3 Replies
View Related
Jul 17, 2015
I would like solving the following issue using the Patindex function i cannot retrieve or extract the single numeric value as an example in the the values below i would like retrieve the Value 2, but in my result set the value 22 also appears or it is completely omitted.
"2 8 7"
"2 8"
"2"
"22"
"3 2 8"
I have tried the following
Patindex('%[2]% [^0-9] [^1] [^3] [^4] [^5] [^6] [^7] [^8] [^9]' ,Replace(Replace(Marketing_Special_Attributes, '"',''),'^',' ')) as Col3,
Patindex('[2]' ,Replace(Replace(Marketing_Special_Attributes, '"',''),'^',' ')) as Col4,
Patindex('%[2][^0-9]%',Replace(Marketing_Special_Attributes,'^',' ')),
View 5 Replies
View Related
Aug 18, 2014
I am comparing two fields one from our legacy table and one in our new table structure that should have identical text data. The new field has an assortment of ANSI characters where the legacy data did not have these. Is there anything I can do that will ignore all ansi character differences? The only route I can think of is just do a replace on each ANSI type on the new column but there are quite a few character types.
View 4 Replies
View Related
Oct 7, 2015
I am using the following select statement to get the row count from SQL linked server table.
SELECT Count(*) FROM OPENQUERY (CMSPROD, 'Select * From MHDLIB.MHSERV0P')
MHDLIB is the library name in IBM DB2 database. The above query gives me only the row count of table MHSERV0P. However, I need to get the names, rowcounts, and sizes of all tables that exist in MHDLIB librray. Is it possible at all?
View 1 Replies
View Related
Aug 9, 2013
I'm using the following syntax to extract a list of all the table names on a linked server:
EXEC sp_tables_ex
@table_server = MY_SERVER_NAME
It outputs a list of tables into 4 columns in the result window.Is there a way an can use this as a 'SELECT * FROM ... " command so that I can organize records, insert into, etc etc ?
View 3 Replies
View Related
Dec 4, 2006
I have been running a script in SQL Server 2000 as sa also as a Active Directory user who has administrator rights (I tested both approaches SQL Server then Windows Authentication) in Query Analyser which grants execute rights to the stored procedures within the database instance and Query Analyser does not give any errors when I run the script. I have made sure that each transaction has a go after it. I then return to Enterprise Manager, check the rights (I apply them to roles so that when we create another SQL Server user we just grant him/her rights to the role) and discover that the role has not been granted the rights. I seems to be occurring only with 2 of the procedures. Is there a known bug that might be causing this?
yours sincerely
Craig Hoy
View 9 Replies
View Related
Feb 24, 2015
I run this code on several environments successfully but when on run it on a different environment it fails.
Its very strange as the user has database SA and OS administrator permissions.
Code...
declare @Command varchar (250)
set @Command = 'bcp "SELECT * FROM QAProcess.dbo.ReadMe_Table" queryout "THOMSONS-SQL01TempReadMe.txt" -c -T'
EXEC master..xp_cmdshell @Command
Error....
SQLState = 28000, NativeError = 18456
Error = [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user 'T-BXSQLServerAccount'.
NULL
View 2 Replies
View Related
Jun 30, 2015
I need to extract the information from the following Formula:
prj(10517)(a*w/100)+prj(10742)(a*w/100)+prj(10804)(a*w/100)+prj(10808)(a*w/100)+prj(10809)(a*w/100)+prj(10810)(a*w/100)
What would be the simplest and fastest way to do this...
I did write a fetch statement but i want to do something simpler as it is a bit hectic...
Basically i require the information from prj(10517) etc.
View 2 Replies
View Related
Jan 5, 2006
During install of SQL Server 2005, we can of course use a domain account or the built-in system account for running the services. I lean toward domain for obvious reaons but would like to know a +/- to each option and why I'd choose one over the other and what consequences or limitations one may encounter if I choose one over the other.
View 6 Replies
View Related
May 27, 2008
Parameter
Access 2000/XP
SQL Server 7.0
SQL Server 2000
MSDE 2000
Number of instances per server
n/a
n/a
16
16
Number of databases per instance / server
n/a
32,767
32,767
32,767
Number of objects per database
32,768
2,147,483,647
2,147,483,647
2,147,483,647
Number of users per database
n/a
16,379
16,379
16,379
Number of roles per database
n/a
16,367
16,367
16,367
Overall size of database (excluding logs)
2 GB
1,048,516 TB
1,048,516 TB
2 GB
Number of columns per table
255
1024
1024
1024
Number of rows per table
limited by storage
limited by storage
limited by storage
limited by storage
Number of bytes per row
(Excluding TEXT/MEMO/IMAGE/OLE)
2 KB
8 KB
8 KB
8 KB
Number of columns per query
255
4,096
4,096
4,096
Number of tables per query
32
256
256
256
Size of procedure / query
64 KB
250 MB
250 MB
250 MB
Number of input params per procedure / query
199
1,024
2,100
2,100
Size of SQL statement / batch
64 KB
64 KB
64 KB
64 KB
Depth of subquery nesting
50
32
32
32
Number of indexes per table
32
250 (1 clustered)
250 (1 clustered)
250 (1 clustered)
Number of columns per index
10
16
16
16
Number of characters per object name
64
128
128
128
Number of concurrent user connections
255
32,767
32,767
5
View 1 Replies
View Related
Nov 19, 2014
How would one add this as a derived column
CASE
WHEN Data IS NULL
THEN NULL
WHEN SUBSTRING(REPLICATE('0', 9 - LEN(Data)) + CAST(CYCLE_YYYYMM AS VARCHAR(9)), 4, 6) IS NULL
THEN 0
ELSE RTRIM(SUBSTRING(REPLICATE('0', 9 - LEN(Data)) + CAST(Data AS VARCHAR(9)), 4, 6))
END
From what I have seen the first option is not really required
View 1 Replies
View Related
Oct 3, 2000
Is there a way to compare two similar tables? I'm more interested in finding out if the data content is exactly the same or not between the two tables.
Thanks for your help.
View 1 Replies
View Related
May 15, 2008
Hi,
I have a table in sql server 2000.I delete some rows in the table.I would like to know the differences before & after deletion. Is it possible?
View 4 Replies
View Related
Apr 7, 2015
How can I create a SQL authentication account with read-write access to only 1 table in a SQL database.
View 1 Replies
View Related
Jan 14, 2015
I have a frustrating problem where I am using the Ola Hallengren jobs to backup to a network share. (This isn't something specific to his scripts).
For various reasons the SQL Server account can not be granted access to the share so I thought I would use a proxy account which does have access (this has been fully tested). I am using a CmdExec proxy.
The problem comes now that when I run the job it still thinks access is denied when running the xp_create_subdir command.
When I recreated this problem locally on my machine, as soon as I add the SQL Server account access to the share the backups work, so why isn't the job using the proxy account?
View 1 Replies
View Related
Jan 23, 2003
Hi,
Short version:
How would you accomplish comparing ALL the values in two tables wherein the PKs for each table are identical?
Long version:
SQL Server 2000 database with two tables
of identical structure having rows with some new data, some old data.* It's easy to determine which rows are 100% new by
looking for PKs not in the old data.
For the remaining rows, I need to make
certain no rows have been modified.
* Actually, there are hundreds of
pairs of tables as described.
The data comes from flat files, and I'd
rather use the database than Perl in this
case.
I can write code to generate the
list of columns for each table and then
query these as sets. Is there any
other method that might work better?
View 7 Replies
View Related
Dec 30, 2014
All is happened when a server crashed some weeks ago and it was removed from my network. After that, under my SQL Server 2012 I get an orphaned account which cannot be removed. This account is a computer account related to an old SCOM installation.
If I try to execute the command DROP USER [NETWORKSERVERNAME$] I get the following error message:
The database principal has granted or denied permissions to objects in the database and cannot be dropped. Msg 15284, Level 16, State 1, Line 1
The database principal has granted or denied permissions to objects in the database and cannot be dropped.But if I run the following command to know all permission granted to the account, I get an empty result:
select * from sys.database_permissions where grantee_principal_id = user_id ('NetworkSERVERNAME$');
Furthermore, following the instructions provided by the official KB for troubleshooting orphaned users, I get another error [URL].
sp_change_users_login 'update_one', 'NetworkSERVERNAME$', 'NetworkSERVERNAME$'
Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 114
Terminating this procedure. The User name 'NetworkSERVERNAME$' is absent or invalid.
The only thing I can retrieve is 15 permissions that this account granted to another account in the past:
select *
from sys.database_permissions
where grantor_principal_id = user_id ('NetworkSERVERNAME$'
--
class class_desc major_id minor_id grantee_principal_id grantor_principal_id type permission_name state state_desc
17 SERVICE 65538 0 5 19 SN SEND G GRANT
And more 14 rows…
resolve my issue and safe delete the account SERVERNAME$? I need this because I have to reinstall SCOM with the same computer name on another server, but installation fails due to this behavior.
View 8 Replies
View Related
Jan 25, 2007
Is there a transform available which allows you to specify two different tables (same primary key) and compare columns (you identify which column(s) values need to be compared in the transform) between those two tables?
thanks
View 11 Replies
View Related
Dec 15, 2007
Hi everybody,
I'm having trouble creating a seemingly simple Comparison report.
I want to be able to create a Table or Matrix that displays the number of items for the Current Year, the Previous Year, and the Difference. I was able to write a script that gives me the count for each item, for each year, as illustrated below:
Item
WhichYear
Count
Apples
Current Year
2
Apples
Previous Year
2
Mangos
Current Year
214
Mangos
Previous Year
204
Oranges
Current Year
13
Oranges
Previous Year
20
Pears
Current Year
19
Pears
Previous Year
50
Strawberries
Current Year
28
Strawberries
Previous Year
40
Ideally, the report Layout look like this, with a column for each year, and a separate column for the difference:
Item
Current
Previous
Difference
Apples
2
2
0
Mangos
214
204
10
Oranges
13
20
-7
Pears
19
50
-31
Strawberries
28
40
-12
Sounds simple enough to me. But when I put it in a Table, I can't get the counts for the Current and Previous Years on one line per item. They end up broken down into two lines (as illustrated in the first chart). When I try to add a grouping, it somehow holds onto the Current Year numbers and ignores the Previous Year numbers. When I put it in a Matrix, I can't seem to write a simple calculation, like finding the Difference between the two columns. Can I add a non-pivot row or column to the matrix?
I know this is a very general question... Any idea on whether I should go for a Table or a Matrix or another approach, like a summary table?
Thank you very much in advance,
- Trevor
View 2 Replies
View Related
Apr 22, 2015
As the title suggests we are looking to change the service account of a SQL mirror implementation. I will be using the same account on all 3 servers involved in the configuration.
I know each server requires the account of the other two adding but as this will be the same account I assume this doesn't apply?
Also for mirrored databases already set up would I need to reconfigure the security for each one?
Is there anything I am missing?
View 0 Replies
View Related
Nov 10, 2015
I upload data from a Txt File(Txt_Temp) where I have VinNumber with 6 digits. Another table name Resrve_Temp1 where I have Vinumber with 17 digit. Now I need to update the vinnumber 6 digit to 17 digit or to new column in Txt_temp.
Txt_Temp - Table
I tried this code with no succes and only one row is updating
update Txt_Temp Set Txt_Temp.Vinnumber=dbo.R_ResrvStock.Vin
from dbo.R_ResrvStock inner join Txt_Temp on Right (dbo.R_ResrvStock.Vin,6)=Txt_Temp.VinNumber
OR Add this code in view
Select dbo.R_ResrvStock.Vin,R_Txt_Temp.Vinnumber,R_Txt_Te mp.Model_Code
from dbo.R_ResrvStock inner join R_Txt_Temp on Right (dbo.R_ResrvStock.Vin,6)=R_Txt_Temp.VinNumber
Vin
123456
123123
123789
Resrve_Temp1 - Table
asddfghjklk123654
asddfghjklk123456
asddfghjklk321564
asddfghjklk123123
asddfghjklk123789
asddfghjklk654655
asddfghjklk456465
My Result can be in Txt_Temp table or new table or with one or two columns
asddfghjklk123456 123456
asddfghjklk123123 123123
asddfghjklk123789 123789
View 10 Replies
View Related
