Transact SQL :: If Exists In Query
Nov 18, 2015
I am using SQL Server 2008 - and what I want to do is set my variable @dh. If the @startDate and @endDate falls into the criteria for my if exists statement, I want to set @dh equal to datediff(h, logontime, logofftime) BUT if that criteria is not true, I want to set @dh = 24. How can I do that?
Declare @startDate date, @endDate date, @employeeID varchar(100), @userid varchar(100), @dh int
Set @startDate = '11/09/2015'
Set @endDate = '11/14/2015'
Set @employeeID = 'ab12345'
Set @userid = '162489'
[Code] .....
View 3 Replies
ADVERTISEMENT
Aug 4, 2015
When i am running below snippet execution plan is showing constant scan instead of referring subquery table.
I want to know how this query working. and why in execution plan there is no scan /seek which will basically indicate that particular table is getting referred.
select count(*) from A where exists (select count(1) from B where A.a=B.a)
execution plan has to show scan or seek for subquery. Surprisingly, output is coming as expected.
View 8 Replies
View Related
Jul 23, 2015
When I execute the below queries it works perfectly where as my expectation is, it should break.
Select * from ChildDepartment C where C.ParentId IN (Select Id from TestDepartment where DeptId = 1)
In TestDepartment table, I do not have ID column. However the select in sub query works as ID column exists in ChildDepartment. If I do change the query to something below then definately it will break -
Select * from ChildDepartment C where C.ParentId IN (Select D.Id from TestDepartment D where D.DeptId = 1)
Shouldn't the default behavior be otherwise? It should throw error if column doesnt exists in sub query table and force me to define the correct source table or alias name.
create table TestDepartment
(
DeptId int identity(1,1) primary key,
name varchar(50)
)
create table ChildDepartment
(
Id int identity(1,1) primary key,
[Code] ....
View 3 Replies
View Related
Oct 2, 2015
This is my query, from my nonono table I need to return the most recent date from attempted (column name) How could I modify this to do such
Select *
FROM [nonono] td
WHERE (NOT EXISTS
(SELECT [First], [second]
FROM [yesyesyes] AS d
WHERE ([First] = td.[First]) AND ([second] = td.[second]) AND ([Worksite] = td.[Worksite])))
View 3 Replies
View Related
Oct 23, 2015
I am trying to bulk update about 50,000 rows in a SQL table. The values that the table MUST contain are:
1-3 days
4-7 days
8-10 days
Some of the rows contain a space between the number and the hyphen like:
1 - 3 days
4 - 7 days
8 - 10 days
What would be my best methodology of removing the space between numerics only? I have seen multiple examples of how to remove ALL whitespace, but I only want to remove the space between the numbers and the hyphen *IF* it exists. And the field type is varchar(200)
View 8 Replies
View Related
Feb 18, 2010
I am trying to determine the existence of at least one row in my Detail table using EXISTS in my SELECT list from my Main table.SELECT M.ID,EXISTS(SELECT 1 FROM Detail D WHERE D.ID = M.ID) as HasDataFROM Main MCan this be done this way?I was hoping that using EXISTS would find a row and move on thus increasing performance.
View 15 Replies
View Related
May 25, 2015
I have a table which has 2 columns and the data is like below
API_Number Group_Name
1234 Group A
3241 Group A
1234 Group B
4567 Group C
7896 Group D
3241 Group E
I wanted to find the API numbers which are repeating in different groups. In the output I want
API_Number Group_Name
1234 Group A,Group B
3241 Group A,Group E
View 5 Replies
View Related
Jan 21, 2010
What is the best way to drop a temp table if it exists? I am looking something similar to this: if exists (select top 1 * from #TableName) then drop #TableName else end
View 5 Replies
View Related
May 6, 2015
I have been asked to create PK on many tables using a query on all tables where we do not have clustered indexes. Some of the tables contains PK but non-clustered. If in a table there are no PK, then how to decide on which column PK can be created? can we do it with the query without data loss and without human intervention?
View 5 Replies
View Related
Jul 31, 2015
I have a table that has for each shop a value that can change over time.For example
BK_POS 1 --> Segment A
BK_POS 1 --> Segment /
What I would like to achieve is to get all distinct Shops (BK_POS) from the table above, but if for that specific pos a row exists where the segment = "/" then I do not want to take this BK_POS in my select query.More concrete, the for example above I do not want to select BK_POS 1 because he has one row where the segment = "/".
View 3 Replies
View Related
Oct 29, 2015
Usual way to check if file exists
DECLARE @File_Exists INT
EXEC Master.dbo.xp_fileexist 'serverBSQL_Backupfile.bak', @File_Exists OUT
print @File_Exists
1
And if check folder, can use "nul", but it doesn't work for UNC path
DECLARE @File_Exists INT
EXEC Master.dbo.xp_fileexist 'serverBSQL_Backup
ul', @File_Exists OUT
print @File_Exists
0
If use xp_subdirs like:
EXEC master.dbo.xp_subdirs 'serverBSQL_Backups'
If the folder doesn't exists,
Msg 22006, Level 16, State 1, Line 3
xp_subdirs could not access 'ServerBSQL_Backups*.*': FindFirstFile() returned error 67, 'The network name cannot be found.'
How to check if UNC folder exists in Backup? in my code I want to check if the unc folder exists before doing backup, the unc path is retrieved from other table or backup history.
View 8 Replies
View Related
Nov 16, 2015
I have scenario where i have to pick one particular value from where condition. Here is the example:A store can have different types i-e A or B , A and B or either A or B.
Store Type Sales
11 A 1000
23 A 1980
23 B 50
5 B 560
I want to filter the store in "where clause" where
1)- if the store has type A and B, then assign only A
2)- if the store has type A associated with it then assign A
3)- if the store has type B associated with it, then assign B.
Select Store, sum(sales), Type
from table1
where (TYPE]= (case when [TYPE] in ('A','B') then 'A'
when [TYPE]='A' then 'A' else 'B'end))
GROUP BY [store], [TYPE]
The above statement is not working for when store has only Type B associated with it.
View 7 Replies
View Related
Jul 20, 2005
I am having trouble with what will surely be a simple query for you experts.I have 2 tables with inventory data.IMITMIDX contains the master item infoIMINVLOC contains location specific data such as quantity on hand at thatlocation.These tables have 2 commons fields, ITEM_NO and LOCI need to search the IMINVLOC table for any records where ITEM_NO and LOC donot match that in the IMITMIDX table.The following query give me zero records even though I can manually findsome records:SELECT *FROM IMINVLOC_SQL INNER JOINIMITMIDX_SQL ON IMITMIDX_SQL.item_no = IMINVLOC_SQL.item_nowhere not exists (select loc from iminvloc_sql where IMITMIDX_SQL.loc =IMINVLOC_SQL.loc)Any ideas?Thanks.
View 5 Replies
View Related
Dec 6, 2007
I have a NOT EXISTS Query that works, but I have been giving a new requirement from MGMT. They initially wanted to know if a record existed in the system. Now they want to know if one exists over certain time periods (i.e. last 24 hours, 48 hours, etc...)
The System:
This is an ASP.NET application that uses table1 and table2 as list items in a drop down list. Once a record is created in the system it puts a time date stamp on the record along with other fields. For reporting I only care about reporting on two fields. This will be used to show activity in the system. MGMT expects at bare minimum 3 entries per 24 hour period.
SELECT table1.Department, Table2.field2
FROM table1 INNER JOIN
Table2 ON Table1.DeptID = Table2.DeptID
WHERE (NOT EXISTS
(SELECT Table3.Department, Table3.Field2
FROM Table3
WHERE Table1.Department = Table3.Department AND Table2.Field2 = Table3.Field2))
The above returns records where there are no entries in Table3 for table1.Department AND Table2.field2. I now have the requirement to do the same thing, but also to include records where table3.insert_date was in the last 24 hours, 48 hours, etc...
I have tried adding:
Table3.insert_date >= GetDate() - 1)
but it still only returns records with no entry at all.
SELECT table1.Department, Table2.field2
FROM table1 INNER JOIN
Table2 ON Table1.DeptID = Table2.DeptID
WHERE (NOT EXISTS
(SELECT Table3.Department, Table3.Field2
FROM Table3
WHERE Table1.Department = Table3.Department AND Table2.Field2 = Table3.Field2 AND Table3.insert_date >= GetDate() - 1))
I have moved it all over the place in this query changed AND to OR and cannot get the desired result.
View 4 Replies
View Related
Nov 9, 2015
I have two tables Costtable (Id,ResourceId, Amount,Date) and ResourceTable (ResourceId,Name) which shows output as below.
I want to show 0 amount for rest of the name in case of September. For e.g. if rest of the Resources does not appear in cost table they should appear 0 in amount
My Desired output
My current query
SELECT
RG.Id AS Id,
RG.Name AS Name,
ISNULL(SUM(AC.Amount), 0) AS Amount,
RIGHT(CONVERT(varchar(10), AC.[Date], 105), 7) AS [YearMonth]
[Code] ....
View 6 Replies
View Related
May 9, 2007
Hello,
i hope that somebody can help me. I have a problem with ms sql query.
i have two tabels(wtmenSends and T1) in a database. i need pick up all email addresses from table wtmenSends but without those email addresses which are in table T1.
I have written this sql query to get these Emails, but i get nothing by this query. Can somebody tell me, where is the problem? Thanks.
select wtmenSends.Email
from wtmenSends
where not exists(
select T1.*
from(
SELECT wtmenSends.Id, wtmenSends.Email, wtmenSends.IDMailing, wtmenSends.Title, wtmenSends.Firstname, wtmenSends.Lastname, wtmenSends.IDUser,
wtmenSends.IdStatus, wtmenSends.IsSent, wtmenSends.DateSent, wtmenSends.wtobjIDClass, wtmenSends.wtobjDateCreated,
wtmenSends.wtobjDateChanged, wtmenSends.wtobjUserCreated, wtmenSends.wtobjUserChanged
FROM wtmenRobinsons INNER JOIN
wtmenSends ON wtmenSends.Email NOT LIKE '%' + wtmenRobinsons.Filter AND wtmenRobinsons.IsDomain = 1) as T1 inner join
wtmenRobinsons on wtmenRobinsons.Filter = T1.Email)
View 2 Replies
View Related
Jul 20, 2007
I am trying to clean up an ugly query that's based on trying to find items that exist in one table but not the other.
My tables are like this:
ITEMSitemID,itemName,itemDescriptionetc...
ORDERITEMSOrderLineID,OrderID,itemID
ORDERSOrderID,OrderCompleted
Currently my query looks something like this:
Select Items.* from ITEMS where not exists (select 1 from ORDERITEMS inner join ORDERS on ORDERITEMS.OrderID = ORDERS.OrderID where ORDERITEMS.itemID=ITEMS.itemID and (ORDERS.OrderCompleted=1))
So this query is looking for ITEMS what don't have a corresponding entry in the ORDERITEMS table. As I understand it this is pretty inefficient as it is going to be executing the sub query in the Not Exists statement for each entry in the ITEMS table. Is the preferred method to do something along the lines of somehow making the sub query into a derived table and doing a left or right join?
Thanks for reading!
Ryan
View 7 Replies
View Related
Nov 15, 2001
I am trying to write a query that does not use inner joins to see if it returns rows faster than a query using inner joins.
My trouble is that I don't know how to write the syntax for the 3rd table that I am comparing against.
Can I nest Exists in a Where clause?
If so how is the 2nd exists statement added to the query?
see my sample query below
================================================== ===
'With 2 tables
SELECT DISTINCT s1.ID, s1.SITE
FROM SITE_TBL s1
WHERE EXISTS (SELECT *
FROM DEVICE_TBL d1
WHERE s1.ID = d1.SITE_ID AND d1.DELETEFLAG <> 'D')
ORDER BY SITE
'with 3 tables this doesn't work
SELECT DISTINCT s1.ID, s1.SITE
FROM SITE_TBL s1
WHERE EXISTS (SELECT *
FROM DEVICE_TBL d1
WHERE s1.ID = d1.SITE_ID AND d1.DELETEFLAG <> 'D')
AND
(SELECT *
FROM BIG_TBL b1
WHERE d1.fqdn = b1.xyz)
ORDER BY SITE
================================================== ===
Thanks for the help
Jim
View 1 Replies
View Related
Jul 23, 2005
I've been presented with a task to do a query similar to the followingand I was curious as to what the quickest query would look like.Anyone have any ideas??Some_Id Value1 A1 B1 C2 C2 A2 B3 B3 C4 C5 Q5 C5 R6 T7 P7 BThe problem is that I want to select one record for each ID. If arecord with the value of 'A' exists, then I want to select that recordfor that ID. If not, I want to select the record with the value 'B'for that ID if it exists. Otherwise, just give me the first record forthat ID that exists. The result set would look like this:Some_ID Value1 A2 A3 B4 C5 Q6 T7 BThanks for your input!
View 12 Replies
View Related
Sep 21, 2007
In a SP I need to know if certain records already exist
but the query is parameter dependent so I can't code
IF EXISTS (SELECT ...)
because the proper select must be calculated.
Using
EXEC (@CalculatedQuery)
IF @@ROWCOUNT = 0
Puts the results of @CalculatedQuery into my SP result set.
This is highly undesireable.
View 5 Replies
View Related
Jul 14, 2015
I need to find out if a Transaction ID exists in Table A that does not exist in Table B. If that is the case, then I need to insert into Table B all of the Transaction IDs and Descriptions that are not already in. Seems to me this would involve If Not Exists and then an insert into Table B. This would work easily if there were only one row. What is the best way to handle it if there are a bunch of rows? Should there be some type of looping?
View 12 Replies
View Related
Aug 3, 2007
I want to only go after Distinct email addressess that contain an @ symbol.
But then I want to return all fields. How do you correctly do that for Microsoft SQL Server?IF EXISTS (SELECT DISTINCT user_username FROM usr WHERE(user_username LIKE N'%@%'))
SELECT *
FROM usr Order By user_username
View 2 Replies
View Related
Jan 10, 2014
I'm trying to check which price grids are in use using the price grid_id, and seeing whether this grid_id exists in another query that checks all active contracts. If the grid_id is present (active) I want to return 'Yes', if not I want it to return 'No'.
There are 385 price grids, but my query is only returning the 315 that are active, and ignoring any that are not used. My code is below, how I can see all the records whether Yes or No:
Select distinct
pg.grid_id [Price Grid],
pg.grid_name [Grid Name],
case when exists
(Select
c.grid_id from
customers c
inner join deltickhdr dh on dh.acct = c.custnum and dh.stage <5
where
c.type = 'C' and
dh.dticket is not null) then 'Yes' else 'No' end [Active]
From gridhdr pg inner join customers c on c.grid_id = pg.grid_id
Order by pg.grid_id
View 4 Replies
View Related
Aug 10, 2015
So, I've got this query running and it works great providing there is a record in both DataBases. Now, I need to get all of those that have a record in DBServer1 but not in TranscendDB. I assume i'd use an If not exists, but can't figure out the syntax when using the Linked Object...
select Portfolio
from [TranscendDB].[dbo].[CMContactEvents] as CM
inner join
(
select N.SSN, A.ACCOUNTNUMBER
from [DBServer1].[DB1].[dbo].[Account] AS A,
[Code] ......
View 4 Replies
View Related
Jul 27, 2015
I have a table with dates and values and other columns. In a proc i need to get the result as Month and the values for all the months whether or not the data exists for the month.
The Similar table would be-
create table testing(
DepDate datetime,
val int)
insert into testing values ('2014-01-10 00:00:00.000', 1)
insert into testing values ('2014-05-19 00:00:00.000', 10)
insert into testing values ('2014-08-15 00:00:00.000', 20)
insert into testing values ('2014-11-20 00:00:00.000', 30)
But in result i want the table as -
Month Value
Jan1
Febnull
Marnull
Aprnull
May10
Junnull
Julnull
Aug20
Sepnull
Octnull
Nov30
Decnull
View 9 Replies
View Related
Jul 30, 2015
For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?
I want my output to be
CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc
Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.
I want to do it using SELECT. Is it possible?
View 13 Replies
View Related
Jul 10, 2015
I have a query that performs a comparison between 2 different databases and returns the results of the comparison. It returns 2 columns. The 1st column is the value of the object being compared, and the 2nd column is a number representing any discrepancies.What I would like to do is use the results from this 1st query in the where clause of another separate query so that this 2nd query will only run for any primary values from the 1st query where a secondary value in the 1st query is not equal to zero.I was thinking of using an "IN" function in the 2nd query to pull data from the 1st column in the 1st query where the 2nd column in the 1st query != 0, but I'm having trouble ironing out the correct syntax, and conceptualizing this optimally.
While I would prefer to only return values from the 1st query where the comparison value != 0 in order to have a concise list to work with, I am having difficulty in that the comparison value is a mathematical calculation of 2 different tables in 2 different databases, and so far I've been forced to include it in the select criteria because the where clause does not accept it.Also, I am not a DBA by trade. I am a system administrator writing SQL code for reporting data from an application I support.
View 6 Replies
View Related
May 9, 2015
I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).
To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:
COLUMN_NAME Value
----------- -----
colB 123
colA XYZ
I've tried dynamic SQL to no success, probably not executing the concept correctly...
Below is what I have:
CREATE TABLE myTable (colA VARCHAR(3), colB VARCHAR(3), colC VARCHAR(3))
INSERT INTO myTable (colA, colB, colC) VALUES ('ABC', '123', '0X')
INSERT INTO myTable (colA, colB, colC) VALUES ('XYZ', '789', 'X0')
;WITH cte AS
(
SELECT CAST(PATINDEX('%[^0]%', colC) AS SMALLINT) pos, STUFF(colC, 1, PATINDEX('%[^0]%', colC), '') colC
[Code] ....
View 4 Replies
View Related
Jul 22, 2015
I am trying to optimize a stored procedure in SQL 2008. When I look at an actual execution plan generated from when I run it in SSMS it shows a table being used in the plan that has no relation to what is actually in the query script and this is where the biggest performance hit occurs.
I've never seen a table show up before that wasn't part of the query. why this might occur and how to correct it? I can't just change the query script because the table in question isn't there.
View 10 Replies
View Related
May 22, 2007
Hello to all,
I have a problem with ms sql query. I hope that somebody can help me.
i have a table "Relationships". There are two Fields (IDMember und RelationshipIDs) in this table. IDMember is the Owner ID (type: integer) und RelationshipIDs saves all partners of this Owner ( type: varchar(1000)). Example Datas for Table Relationships: IDMember Relationships .
3387 (2345, 2388,4567,....)
4567 (8990, 7865, 3387...)
i wirte a query to check if there is Relationship between two members.
Query:
Declare @IDM int; Declare @IDO int; Set @IDM = 3387, @IDO = 4567;
select *
from Relationship where (IDMember = @IDM) and ( cast(@ID0 as char(100)) in
(select Relationship .[RelationshipIDs] from Relationship where IDMember = @IDM))
But I get nothing by this query.
Can Someone tell me where is the problem? Thanks
Best Regards
Pinsha
View 9 Replies
View Related
Sep 20, 2006
Hi,
This is on Sybase but I'm guessing that the same situation would happen on SQL Server. (Please confirm if you know).
I'm looking at these new databases and I'm seeing code similar to this all over the place:
if not exists (select 1 from dbo.t1 where f1 = @p1)
begin
select @errno = @errno | 1
end
There's a unique clustered in dex on t1.f1.
The execution plan shows this for this statement:
FROM TABLE
dbo.t1
EXISTS TABLE : nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
It's not using my index!!!!!
It seems to be the case with EXISTS statements. Can anybody confirm?
I also hinted to use the index but it still didn't use it.
If the existence check really doesn't use the index, what's a good code alternative to this check?
I did this and it's working great but I wonder if there's a better alternative. I don't really like doing the SET ROWCOUNT 1 and then SET ROWCOUNT 0 thing. SELECT TOP 1 won't work on Sybase, :-(.
SET ROWCOUNT 1
SELECT @cnt = (SELECT 1 FROM dbo.t1 (index ix01)
WHERE f1 = @p1
)
SET ROWCOUNT 0
Appreciate your help.
View 3 Replies
View Related
Nov 12, 2007
Event
Event_ID
Person_ID
Event
Start_Date
1001
50
Referral
02-02-2007
1002
50
Appointment
03-02-2007
1003
50
Assessment
07-02-2007
1004
84
Referral
14-02-2007
1005
84
Appointment
20-02-2007
1006
50
Referral
01-03-2007
Outcome
OutcomeID
Person_ID
Event_ID
Outcome_Date
Outcome
4000
50
1001
02-02-2007
Appointment
4001
50
1002
03-02-2007
Assessment
4002
50
1003
09-02-2007
No Further Action
4003
84
1004
15-02-2007
Appointment
4004
84
1005
22-02-2007
No Further Action
4005
50
1006
03-03-2007
No Further Action
Result
Person_ID
Event_ID
Start_Date
OutcomeID
Outcome_Date
50
1001
02-02-2007
4002
09-02-2007
84
1004
14-02-2007
4004
22-02-2007
50
1006
01-03-2007
4005
03-03-2007
I have a problem writing a query for the above two tables Event and Outcome. The event table conisists of an event id, the person id, the event and the start date of the event. The Outcome table is the based on the outcome of the event; the table includes the outcome id, the person id, the event id, when the outcome decision was made (this can be after the event start date) and the outcome.
A client may have a referral which goes onto an appointment then an assessment. If the outcome is No Further Action then no further events take place. No Further action can be outcomed at any stage. A person may only have one event open at a time i.e. they cannot have a new referral if they have an assessment.
I want to create a query that displays when the referral started (event start_date) until it ended (when No Further Action was selected). Because a person may have referrals I am struggling with the query with regards to dates.
View 7 Replies
View Related
Aug 18, 2015
I have table like below, its period wise ,here the value get cumulative period wise.
amtname period
10CHR201202
20TNG201202
10CHR201203
20TNG201203
View 3 Replies
View Related
