SQL Server 2008 :: Compare If Row-Combination Exists In Other Table

Mar 10, 2015

I've the following two tables:

create table #temp1 (name varchar(5), id int)
insert into #temp1 (name, id)
(
select 'a', 5
union
select 'a', 8

[code]....

As a result I would need every name from #temp2, where both searchred id's (5 & 8) from #temp1 are included.In this example i would like to get 'e' as a result, because in #temp2 'e' has the id's 5, 8 and 25.I've tried using cross apply, but cross apply returns every Name that have one of the ids... in this case it would also return 'c'...

Selectdistinct b.name
from(
Selectdistinct name
, id
from#temp1
wherename = 'a'
) as a
cross join(
Selectdistinct name
, id
from#temp2
) as b
wherea.id = b.id

View 3 Replies


ADVERTISEMENT

SQL Server 2008 :: Compare Dates In Rows Of A Table?

Apr 8, 2015

I have the following information in a table. What I would like to do is pull out all the visits for each customer that are less than 30 days apart.

Customer# VisitDate
9082012-07-28 00:00:00.000
9082013-09-20 00:00:00.000
9082013-12-23 00:00:00.000
9082014-01-10 00:00:00.000
9082014-01-27 00:00:00.000
9082014-02-16 00:00:00.000
9082014-05-21 00:00:00.000
9082014-05-30 00:00:00.000
9082014-10-01 00:00:00.000
9082015-02-28 00:00:00.000
9082015-03-22 00:00:00.000
9272012-02-16 00:00:00.000
9272014-12-14 00:00:00.000
9272014-12-23 00:00:00.000

View 2 Replies View Related

SQL Server 2008 :: Compare Data Between Identical Table Structure?

Jun 18, 2015

I have two identical table structure with at least 60 or more columns!!

Table A
ID numeric(18, 0) Primary Key,
RefID numeric(18, 0),
CoNum Varchar(15),
PrevCode Varchar(15),
DispCode VArchar(15),
EffDate Date,
Sal numeric(18, 0),
SDeposit numeric(18, 0) Etc....

I need to compare Data between two table and provide result like this!

RefId ID, CoNum, PrevCode, DispCode, EffDate, Sal, SDeposit
1, ,No Match, ,20,5200,0

So condition like. If table A Column Data does not Equal Table B Column data then

If datatype is Date then display Difference of Days.

if Datatype is Numeric then display Difference between value.( it display 0 if doesn't have difference)

if Datatype is Varchar then display No Match word.

View 0 Replies View Related

SQL Server 2008 :: Compare Data / Loop Through Dates And Insert Into Table

Sep 21, 2015

I have three tables:

"PaymentsLog"
"DatePeriod"
"PaidOrders"

As per below

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PaymentsLog](

[Code] ....

Is there a way to look at the DatePeriod table and use the StartDtae and EndDate as the periods to be used in the select statement and then cursor through each date between these two dates and then insert the data in to the PaymentsLog table?

View 3 Replies View Related

SQL Server 2008 :: Searching To See If XML Node Exists?

Mar 5, 2015

All I really need to know is how to see if a specific node exists. Here's the XML, as well as the query I have:

<PaymentData xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="Com.Commerce.Data/PaymentData.xsd"><CreditCardResult Code="0" Message="APPROVED" Last4="xxxx" AuthorizationNumber="123456" Amount="6.34000000" /></PaymentData>

Basically I just need to see if the CreditCardResult node exists, and return that relevant row. That's it!

Here's the where clause so far. I've tried every variation I can think of and checked tons of forums and tech sites.. no luck..

SELECT TOP 1000 * FROM tblOrder
WHERE zPaymentData.exist('(/PaymentData/CreditCardResult[1])') = 1

View 4 Replies View Related

SQL Server 2008 :: How To Compare String

Jun 19, 2015

I need to compare to string in sql

requirement is

Select Reson_id from reason_data where reason='ryete / werewr ddsad '

is there any way without string function? bcz string function take more exaction time

View 9 Replies View Related

SQL Server 2008 :: How To Compare The Date Column

Mar 30, 2015

I need to compare the date column. The date column is 2012-01-22 in this format.

So I need to convert getdate() into this format yyyy-mm-dd.

What is the style number I have to use?

I used the link [URL] but I didn't see this format.

View 5 Replies View Related

SQL Server 2008 :: Check If File Exists Using Script Task

Mar 17, 2015

I need to create a script task in sql server 2008 R2 to check if a file exists in a directory. For example, to see if output.dat exist under c:results. If the file exists, then send out an email stating the file exists, if not then send out another email stating the file does not exists.I noticed there is a huge difference between the script task in sql 2005 and sql 2008 r2.

View 3 Replies View Related

SQL Server 2008 :: If Exists Placement Theory For Optional Tables

Nov 4, 2015

I like writing concise and compact sql code without cursors if possible. My current dilemma has me stuck though.I have 3 tables, but one of them is optionally used and contains a key element of TimeOut to determine which Anesthesia CrnaID to use. It is the optionally used part that has me stumped.

Surgery table
CaseID
Patient
(Sample data: 101,SallyDoe 102,JohnDoe)

Anesthesia table
CaseID
CrnaID
(Sample data:
101,Melvin
102,Bart
102,Jack)

AnesthesiaTime table (this table is optionally used, only if the crna's take a break on long cases)
CaseID
CrnaID
TimeIn
TimeOut
(Sample data:
102,Jack,0800,1030
102,Bart,1030,1130
102,Jack,1130,1215)

Select Patient INNER JOIN Anesthesia produced too many case results. So, I figured out there is an AnesthesiaTime table that only gets used if the anesthesia guys take time-outs. That doesn't happen all the time. I could use TOP 1 on the Anesthesia table, but technically I need to read the AnesthesiaTime table and locate the last time and pull that crna, Jack. I'm not sure how to deal with an optional table. I believe the IF Exists will be pertinent, but not sure of how to build this query. I've tried subquery without success.

View 2 Replies View Related

SQL Server 2008 :: Compare Up To 9 String Variables For Similarity?

Mar 6, 2015

We're converting to new student info system. Sometimes registrar entered the same school into the schools table but spelled it differently. Trying to find all student assigned transfer credits from the same school but the school name is different. My db shows a max of 9 different schools students have rec'd transfer credits. Spending too much time trying to figure out best way to do it w/o a ton of IF stmts. Looking at Soundex and Difference functions. Still looks like a lot of coding. how to compare up to 9 string variables in sqlserver 2008?

View 2 Replies View Related

SQL Server 2008 :: Compare Parent And Child Data?

Aug 4, 2015

DECLARE @ParentTable TABLE (ProductID BIGINT,ItemID BIGINT)
DECLARE @ChildParentTable TABLE (ParentID BIGINT,ProductID BIGINT,ItemID BIGINT)

--In Parent table(@ParentTable) there are 4 and 5 items each for product 101 and 102 respectively

INSERT INTO @ParentTable(ProductID,ItemID) VALUES(101,1234),(101,1578),(101,1590),(101,1237)
INSERT INTO @ParentTable(ProductID,ItemID) VALUES(102,5465),(102,5466),(102,5474),(102,5489),(102,6543)

--child products 701 and 901 are derived from parent products(partially) 101 and 102 respectively

INSERT INTO @ChildParentTable(ParentID,ProductID,ItemID) VALUES(101,701,1234),(101,701,1590),(101,701,1578)
INSERT INTO @ChildParentTable(ParentID,ProductID,ItemID) VALUES(901,102,5465),(901,102,5474),(901,102,8976)

--Here what I need is

--For product 701 there is one item missing ie 1237 which exists in its parent 101

--and For product 901 there are 3 items missing ie 5466,5489 and 6543 which exists in its parent 102

--and extra item exists ie 8976 so my result table should lokk like this

/*
ParentProductID ProductIDItemIDIsExtra IsMissing
1017011237 01
1029015466 01
1029015489 01
1029016543 01
1029018976 10
*/

View 1 Replies View Related

SQL Server 2008 :: Join Two Tables - Compare Amounts (Rent)

Jun 20, 2015

Refer to my query thread on msdn SQL site, how I can achieve the result.

[URL] .....

View 0 Replies View Related

SQL Server 2008 :: Easy Way To Compare Contents Of Objects Between 2 Different Databases?

Jul 20, 2015

Is there an easy way to compare the contents of objects between 2 different databases? For example, say I had 2 databases, My_DB_1 and My_DB_2, each with a SEC_User table. Say I wanted to do an object-to-object comparison between databases to see if there were any differences. Here's some sample SQL:

use My_DB_1
select * from sys.dm_sql_referencing_entities('dbo.sec_user', 'object')

use My_DB_2
select * from sys.dm_sql_referencing_entities('dbo.sec_user', 'object')

Say that the result sets above both returned a SEC_GetUser sproc object ref. Is there a way to write SQL that will compare the SEC_GetUser sprocs (and other objects in the above rowsets) from both databases? For example, if My_DB_1.SEC_GetUser returns an extra column in the result set than My_DB_2.SEC_GetUser then I'd like my comparison SQL to return a single column "IsDifferent" with SEC_GetUser as a row....

View 1 Replies View Related

When Have 4 Tables How To Compare Table With 4nd Table? ( Need To Join And Compare With Datatime)

Jul 22, 2007

Table MediaImportLog
column ↘ImportIndex     ImportFileTime            ImportSource
value    ↘80507             20060506001100          815
              80511             20061109120011           CRD                       ? P.S the values type of ImportFileTime 20060506001100 → 2006 -year 05-month 06-day 00-HH 11-minute 00-second】
Table  BillerChain
column↘BillerInfoCode       ChainCode
value   ↘750                      815
value   ↘81162                  CRD
Table   Biller
column↘CompanyCode         BillerCode
value   ↘999                     750
value   ↘81162                  516
TAble DataBackup
column↘CompanyCode         Keepmonth
value   ↘999                     6
value   ↘81162                 12
 
---------------------------------------------------
 
when I'm in MediaImportLog , I want use column ImportSource to compare with column ChainCode in table BillerChain ( so I get BillerInfoCode) and then use the BillerInfoCode I got to compare with column BillerCode in Table Bill ( I get CompanyCode) finally I use CompanyCode to compare with column CompanyCode in table DataBackup so I can get the company's keepmonth
How can I get the keepmonth? can I use parameters ? 
 
thank you very much 

View 3 Replies View Related

Help Me Getting A Unique Combination From Table

Jun 18, 2004

Hi, I have following table

BetId GameId
==== ======
500 108
500 109
501 108
501 109
501 110
502 108
502 109

My query would have the form: select BetId where GameId in(108,109)
from Bets then it has to get me BetId : 500 and 502.
Not 501,since this is different combination(108,109,110) ;)

View 3 Replies View Related

Combination Of Columns Not In Another Table

May 13, 2014

I want a list of rows in one table where a combination of two columns aren't in another table. One column is character and the second column is date/time. This happens to be SQL 2005. My first attempt using only one column worked OK:

SELECT * FROM TABLEA
WHERE CUSTID NOT IN (SELECT CUSTNO FROM TABLEB)

But when I added the combination of customer number and sales date, it made the query "run away" - I had to cancel it and it didn't return anything:

SELECT * FROM TABLEA
WHERE CUSTID + CONVERT(VARCHAR, SLS_DATE, 101) NOT IN
(SELECT CUSTNO + CONVERT(VARCHAR, SALES_DATE, 101) FROM TABLEB)

I wonder what might be wrong, but also whether this is the correct approach to begin with.

View 3 Replies View Related

SQL Server Admin 2014 :: Few Record Loss In Table Primary Key Where Same Records Exists In Foreign Key Table?

Jun 21, 2015

Previously same records exists in table having primary key and table having foreign key . we have faced 7 records were lost from primary key table but same record exists in foreign key table.

View 3 Replies View Related

SQL Server 2012 :: Compare Two Table Data And Insert Changed Field To Third Table

Aug 12, 2014

I want Compare two Table data and insert changed field to the third table ...

View 9 Replies View Related

IF NOT EXISTS (... - EXISTS TABLE : Nested Iteration. Table Scan.Forward Scan.

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

Permutation And Combination Between Source And Lookup Table?

Jun 21, 2015

how this can be achieved by writing a procedure in SQL Server DB.Remember that if there is a non-null value in the benchmark that is not equal to the equivalent criteria in the Hosting input then there is no match. Therefore those with region = IN would not match any of the given benchmarks. It is best fit as long as the non-matching values are to null value benchmark criteria.

Hosting_Site (Source File)
YEAR MONTH HOSTING CLASS REGION HOSTING COST
2015 1 A UK 75
2015 1 A IN 80
2015 1 B IN 60
2015 1 C US 100

BENCHMARK (Lookup Table)
Row Year Month Hosting class Region BENCHMARK COST
1 2015 1 A US 100
2 2015 1 B US 200
3 2015 1 A UK 50
4 2015 1 A null 70

STANDARD CLASS COST (If the criteria between source file and the lookup file doesn't match then have to load this cost)

Row Year Hosting class BENCHMARK COST
1 2015 A 22
2 2015 B 33

Then result should be:

FACT LOAD:

YEAR MONTH HOSTING CLASS REGION HOSTING COST BENCHMARK COST
2015 1 A UK 75 50
2015 1 A IN 80 70
2015 1 B IN 60 33 (Since for B class there is no region = IN or = null the Standard Class Cost must be used)
2015 1 C US 100 ??? (Reject record since there is no default value in Standard Class Cost or in Benchmark)

View 13 Replies View Related

How To Fetch Record From Temp Table - Unique Combination

Sep 23, 2014

Table :StudentTeacherRelation

Id StdId TeacherName Day subject
1 1 Archana Monday English
2 1 Archana Tue Marathi
3 1 Shama Wed Hindi
4 1 shama Thus Hindi
5 1 Kavita Fri Hindi
6 2 Archana Mon english
7 2 Dipti Tues Hindi

Second table : Student

Id Sname Cid
1 Shalini 1
2 Monika 1
3 Rohan 3

I want to fetch uniq combination of stuid and subject.Result should show all subject of student whether may be teachername and day. If I choose shalini whose stuid is 1,all subject for shalini(hindi,english,marathi) should come. Record from either of three should come

Id StdId TeacherName Day subject
3 1 Shama Wed Hindi
4 1 shama Thus Hindi
5 1 Kavita Fri Hindi

I want fetch studentname along with teachername,day and subject whose cid = 1 here is my query

select Student.Sname,TeacherName, Day,subject
from StudentTeacherRelation
inner join Student
Student.id = StudentTeacherRelation.StuId
where cid = 1

I want place result of it in temp,Want fetch max(id) from temp table by doing group by on Sname and Subject.find all id from temp table where that id present in max id.

show
Id StdId TeacherName Day subject
where (1,2,3,4,5,6,7-- all id from temp) in (1,2,5,6,7 -- max id from temp by doing group by on Sname and subject)

So it will show record Id StdId TeacherName Day subject where id is 1,2,5,6,7.Only five record should come.How to do that?

View 1 Replies View Related

How Do I Know If A Table Exists In A SQL Server Database?

Jul 11, 2005

I need to do some processing but only if a specific table 'table1' exists in the SQL Server database. How can I check if this  'table1' exists using  either ADO.Net code or  SQL Server query?

View 12 Replies View Related

Compare A SQL Server Table To An Oracle Table

Jul 26, 2007

How do I compare a SQL Server table to an Oracle table? Looking to compare table structure and data. Is there a tool that I can use to perform this?

View 1 Replies View Related

How To Check Whether Server Is Exists With Some Table In Particular Database.

May 29, 2006



hi,



I want to know through applicaiton how to check whether the Server named"Myserver" is Exists with Database named "MyDatabase" with Tables named ('Table1','Table2','Table3').

For any Database like (MS Access, Oracle, MS SQL-Server,MySQL)..



Please help me.....



thank's in advance.......

Paresh Patel.

Paresh.onmail@hotmail.com

View 1 Replies View Related

Transact SQL :: Comparing 2 Records / Multiple Instances In The Same Table For A Specific Combination?

Jun 10, 2015

I have a problem where I have 2 compare 2 records from the same table. This part looks easy but the problem is for a User there can be multiple records and I have 2 compare each record with its previous instance based on the timestamp. Not only I have to compare I have to perform some analysis. Below is the Table script and sample output.

Givens: All SQL Server 2008 or 2012 tools at your disposal.

Production database contains the following tables (simplified for example: constraints ignored, etc.) associated with a racing video game’s server.

-- A player of our game

-- Table greater than 10 million rows

CREATE
TABLE [dbo].[User]
(
[UserId]              
[bigint] NOT
NULL  
,[country]             
[int] NULL   
-- User’s home country
,[name]                
[nvarchar](15)
NULL  -- User’s displayable name (‘John’, ‘Bill’)
,[subscriptionTier]    
[int] NULL
)
-- 0 == free, 1 == paid, for instance

Assume that rows get written into the event tables at a rate of 1,000 a minute,are never updated once written and currently are only read on a replica/reporting server.

Question Background: Write up a single query that would return the following: List of users and whose “TotalMoneyEarned” value ever grew (between logon events) at a rate of more than 1,000 per minute (we’d consider these suspicious and flag them for later investigation). 

For instance, if the sample data were:

-- example of [Events.UserLogon] data  -- not the query output we want

EventId     UserId               TotalMoneyEarned LogonDate
----------- -------------------- ---------------- -----------------------
1           1               1000             2010-10-16 00:19:56.460
2           1               1500             2010-10-16 00:20:56.460
3           1               3000             2010-10-16 00:21:56.460
4           1               10000            2010-10-16 00:29:56.460

Event 1 is okay because there’s nothing to compare it against

Event 2 is okay because the TotalMoneyEarned only grew 500 in a minute

Event 3 should be flagged, as the value grew 1500 in a minute

Event 4 is okay, as it grew 7,000 in 8 minutes (< 1000 per minute)

Query Output (your query should return data in a format like this):

User      Flagged Logon Time    Rate Since Last Logon (money/minute)
John      2010-10-16 00:21:56   1500
Dave      2010-10-16 00:30:50   3200
Bill      2010-10-16 00:35:23   1000

It is likely that you will need to create sample data for both the User and [Events.Logon] tables.  We are looking for a single query that returns data like what is represented in Query Output.

View 3 Replies View Related

Does The Varray Or/and Nested Table Mechanism Exists In Sql Server

Jun 6, 2007

Hi.Like in subject. I know the varray and nested tables from oracle, and I'm trying to implement them in sql server. I've been googling for any information but with no result. Can somebody direct me ??Sorry for any english mistakesThanks for help 

View 2 Replies View Related

SQL Server 2014 :: Using WHERE Not Exists For A Fact Table Load?

Oct 20, 2014

I have a stored procedure in that attempts to perform a WHERE NOT EXISTS check to insert new records. If the table is empty, the procedure will load the table. However, an insert does not occur when a change to one or more source fields occurs against an existing record. The following is my code:

declare
@Created_By nchar(50)
,@Created_Date datetime
,@Updated_By nchar(50)
,@Updated_Date datetime
select @Created_By = system_user

[code]....

I expected that when one of the source values of any field in the second WHERE clause changes, that the procedure would insert a new record. Why is this not happening? One other note: I am not 'allowed' to use MERGE.

View 1 Replies View Related

Analysis :: SSAS 2008 - Relation Already Exists For Child Columns

Jul 22, 2015

Not sure where the culprit is for this error propping up. I've added a new measure group and Im trying to generate the schema for the resulting fact table, but something isn't right.  However, I don't think its my new measure group that's causing the issue, because I removed it and still get this issue when I select Database > Generate Relational Schema

View 2 Replies View Related

SQL Server 2008 :: Insert From Table 1 To Table 2 Only If Record Doesn't Exist In Table 2?

Jul 24, 2015

I'm inserting from TempAccrual to VacationAccrual . It works nicely, however if I run this script again it will insert the same values again in VacationAccrual. How do I block that? IF there is a small change in one of the column in TempAccrual then allow insert. Here is my query

INSERT INTO vacationaccrual
(empno,
accrued_vacation,
accrued_sick_effective_date,
accrued_sick,
import_date)

[Code] ....

View 4 Replies View Related

SQL Server 2012 :: Joining Dim To Fact Table Where Dim Table Key Exists In Multiple Fact Columns

Oct 26, 2015

Say you have a fact table with a few columns that all reference the same key column in a dimension table, you want to write a view to return the information for those keys?

USE MyTestDB;
GO
SET NOCOUNT ON;
IF OBJECT_ID ('dbo.FactTemp' ,'U') IS NOT NULL
DROP TABLE dbo.FactTemp;

[Code] ....

I'm using very small data at the moment, and the query plan and statistics don't really say which way.

View 2 Replies View Related

SQL Server 2012 :: Compare A Table With Physical Path

Mar 31, 2015

I have a table with two columns

id | filepath
--------------------------------------------------
1| D:Doc filesThe BestHHT.JPG
2| D:Doc filesThe Bestsealed_pack.txt
3| D:Doc filesThe Bestlsbom.JPG
4| D:Doc filesThe Bestmoc.png
5| D:Doc filesThe Beststock.txt
6| D:Doc filesThe Bestdepot.JPG

And in a physical system there are more files than the table.

D:Doc filesThe BestHHT.JPG
D:Doc filesThe Bestsealed_pack.txt
D:Doc filesThe BestJKSlsbom.JPG
D:Doc filesThe Bestmoc.png
D:Doc filesThe Beststock.txt
D:Doc filesThe BestGDNdepot.JPG

D:Doc filesThe BestCASA.JPG
D:Doc filesThe BestSO.txt
D:Doc filesThe BestBA.JPG

I want to compare the filepath column in table with physical drive files and get the details of files which in table and not in physical and viceversa...

View 3 Replies View Related

SQL Server 2014 :: How To Repeat The Same Row Combination

Jan 23, 2015

I am working on a scenario where I would need to repeat the same row for previous MonthIDs, here's the example (snapshot attached):

CustomerIDMonthIDRSCIDFlag1Flag2Flag3
1232943456000
1233001234011
1233033542010
1233032345111
1233043542010
1233042345111
1233053542010
1233052345111
1233062345111
1233063542010
1233072345111
1233073542010
1233083542010
1233093542010

1.For MonthIDs 309 & 307, I would need to add same combinations to 4 prior months (for 309, I would need the 309 row as 308, 307, 306, 305 and for 307, I would need to add 306, 305, 304, 303).

2.From the snapshot, Green rows are added for 309 MonthID and Orange rows are added for 307.

3.While we are adding 309 row to prior to 4 months, if any prior month has different combination than 309, that same combination need to be repeated for prior months.

I was using the below code to achieve the same:

declare @SC_j as int = 0;
declare @SC_k as int = 4;
while @SC_j <= 4
begin
insert into #Test
select distinct dt.CustomerID

[code]....

But my code starts from 1st MonthID however I would want this to be starting from last MonthID which I am not able to achieve.

View 9 Replies View Related

SQL Server 2008 :: Capture Sessions Which Modify A Table With Details Modified In Table?

Apr 10, 2015

I created am inventory table with few columns say, Servername, version, patching details, etc

I want a tracking of the table.

Let's say people are asked to modify the base table and I want a complete capture of the details modified and the session of the user ( ) who (system_user) is actually modifying the details.

View 1 Replies View Related







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