T-SQL (SS2K8) :: Compare Next Row With Previous Row Of Same Table?

Sep 16, 2014

I need to compare the next row with the previous row of same table and produce nonidentical column.for eg... say my table has

Row 1 => 1001 Abhas 120 150 180
Row 2 => 1001 Abhas 150 150 180

then my output would be as below:

StudId Name fee1 fee2 fee3
1001 120
1001 Abhas 150 150 150

i.e in first row of resultset, i want to show only those values which are changed alongwith studID and next row should display all values.

View 9 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Compare With Previous Records

Oct 20, 2014

I am having a table which contains data of students like:

StudentID,StudentName,Term,RESult.

Sample data :

StudentID,StudentName,Term,RESult.
1,ABC,Term1,Pass
1,ABC,Term2,Fail
1,ABC,Term3,Pass
1,ABC,Term4,Pass
1,ABC,Term5,Pass

Now i want to compare Result and dislay prevterm where student fail:

Now my output would be as: Now I want to compare latest term i.e. Term5 with prev Terms and if found Mismatch in result then i want to display as below:

studentID PrevFailTerm, CurrentTerm
1,Term2,Term5

View 1 Replies View Related

T-SQL (SS2K8) :: Find Previous Date From Table

Nov 4, 2015

I want to find previous date from selected date. below is the sample data.

DECLARE @StartDate SMALLDATETIME = '1/11/2016'

declare @tempdat table(repdate smalldatetime)
insert into @tempdat values ('10/26/2015')
insert into @tempdat values ('10/29/2015')
insert into @tempdat values ('11/1/2015')
insert into @tempdat values ('11/27/2015')
insert into @tempdat values ('11/25/2015')
insert into @tempdat values ('11/20/2015')
insert into @tempdat values ('11/10/2015')
insert into @tempdat values ('11/10/2015')
insert into @tempdat values ('11/11/2015')
insert into @tempdat values ('11/11/2015')

Now if i pass the date '10/26/2015' then i want select prev date of passed date. in this example no prev date is available, so result set would be nothing.

if i pass the date '11/10/2015' then result should be '11/1/2015' which is prev small date available in table.

View 4 Replies View Related

T-SQL (SS2K8) :: Compare Rows In The Same Table?

Aug 7, 2014

We have a table setup to track changes that are made to another table, for auditing purposes. How do we compare the most recent record in the change table with the previous record in the change table? Particularly, we have a column named DUE_DATE in the change table and want to identify when the most recent change has a different DUE_DATE than the previous change made.

View 8 Replies View Related

T-SQL (SS2K8) :: Table With Score Info For Groups - Ranking For Current And Previous Week

Jan 21, 2015

I have a table with score info for each group, and the table also contains historical data, I need to get the ranking for the current week and previous week, here is what I did and the result is apparently wrong:

select CurRank = row_number() OVER (ORDER BY cr.CurScore desc) , cr.group_name,cr.CurScore
, lastWeek.PreRank, lastWeek.group_name,lastWeek.PreScore
from
(select group_name,
Avg(case when datediff(day, asAtDate, getdate()) <= 7 then sumscore else 0 end) as CurScore

[Code] ....

The query consists two parts: from current week and previous week respectively. Each part returns correct result, the final merged result is wrong.

View 3 Replies View Related

T-SQL (SS2K8) :: Table Compare - Getting False Matches

Mar 26, 2014

I have two tables I am trying to compare as I have created a new procedure to replace an old one and want to check if the new procedure produces similar results.

The problem is that when I run my compare I get false matches. Example:

CREATE TABLE #ABC (Acct VARCHAR(10), Que INT);
INSERT INTO #ABC VALUES
('2310947',110),
('2310947',245);

[Code] ....

Which gives me two records when I really do not want any as the tables are identical.

View 2 Replies View Related

T-SQL (SS2K8) :: Take ID Value From Maximum ID And Compare Rest ID Value From Table

Jul 2, 2014

--drop table #temp
create table #temp (id int, idvalue int)
insert into #temp(id,idvalue)
select 1095,75

[code]...

I need to take the id value from maximum's id, and compare the rest id value from the table. i need to check the diffrence , if diffrence is more than 18, then i need to raise the flag as failure otherwise the whole test is success. i need to take 63 and compare rest 69,65,61,75.check the diffrence less than 18 or not.

View 3 Replies View Related

T-SQL (SS2K8) :: Compare Data In A Single Table By Month Period?

May 28, 2014

i would like to see the 2014-06 matched results (3rd query), if the same ssn and acctno is exist in 2012-06 and 2013-06 and 2014-06 then eliminate from results, otherwise show it

select ssn, acctno From jnj.drgSamples where Channel ='KM' and TrailMonth ='2012-06'
select ssn, acctno From jnj.drgSamples where Channel ='KM' and TrailMonth ='2013-06'
select ssn, acctno From jnj.drgSamples where Channel ='KM' and TrailMonth ='2014-06'

i have written the below query but it shows only matched across three queries, but i want to display / delete from 2014-06 records if the ssn and acctno is exist in 2012-06 and 2013-06

select c.* from (
(select * From jnj.drgSamples where Channel ='KM' and TrailMonth ='2012-06' ) a join
(select * From jnj.drgSamples where Channel ='KM' and TrailMonth ='2013-06' ) b on a.SSN = b.SSN and a.acctno = b.acctno join
(select * From jnj.drgSamples where Channel ='KM' and TrailMonth ='2014-06' ) C on a.SSN = c.SSN and a.acctno = c.acctno join
)

View 4 Replies View Related

Compare Previous To Current Record Row

Mar 3, 2014

My current code returns account_number with multiple start_date regardless of the value is same or not. However, I would like to get only the account number when the value on start_date is different within same account_number.

select
acct_number
count(start_date) from table_A
group by acct_number, start_date
having(count(start_date) > 1)

View 5 Replies View Related

How To Compare The Previous Row With Current Row In SQL Server 2000

Nov 10, 2005

Hi,

I have to compare the previous row with current row in a table in SQL Server 2000. Please help me how to do this in a optimized way. Table contains nearly 30 columns and rows count is more than 10 digits.

View 7 Replies View Related

Transact SQL :: Query To Compare Current Price With That Of Previous Value Of Item

Nov 18, 2015

There are two tables testmaster and testdetail. If the value of Price for a particular ID in testdetail is more than the threshold value defined in testmaster, the output should have a new column with value as 'High Value', if the value is less than the threshold the new output should be 'Low Value' other wise 'Ignore'

Example: for ID=3, threshold is defined as 40% in testmaster table, but on 11/12/2015 the new price is 100 which 100% more than the previous value, so the status is High Value as shown below.

ID Date
Price Status
1 11/12/2015 100 Low Value

2 11/12/2015 160 Ignore
3 11/12/2015 100 High Value

create table testmaster
(
ID int,
Threshold int
)
create table testdetail
(
ID int,
Date varchar(20),
Price float
)

[Code] ...

View 15 Replies View Related

T-SQL (SS2K8) :: Set Value Based On Previous Row

Apr 11, 2014

I am trying to code a rule at the moment which sets a value based on the value of the previous row. This is as far as I have got so far. I am trying to run it as a set based command and I believe I may need to make use of a numbers table.

Anyway the code is....

declare @data table
(ID int identity(1,1),
DeviceID int,
EventTypeID int,
EventID int)

[Code] ....

So the output is currently incorrect. Each DeviceID needs to have a ID assigned to the MIN(ID) which isn't yet in the code above. This ID has to be sequential across the full table and not dependent on ID.

Next the rule is coded in the case statement above.

So for each DeviceID, when the EventType goes from 1, 2 or 4, to 0 or 3, the following record after the 0 or 3 will have a new EventID. And conversely when the EventType goes from 0 or 3 to 1,2, or 4, the record that is the 1, 2 or 4 will have a new EventID.

View 6 Replies View Related

T-SQL (SS2K8) :: Add Balance Of Previous Row To Current Row

Jul 2, 2014

I am novice to intermediate writer of T-SQL. Here is my current Query:

SELECT [FISCALYEAR],
[ACCTPERIOD],
SUM([ACTIVITYDEBIT]) AS TrialBalanceDebit,
[POSTINGTYPE]
FROM [dbo].[TB_Lookup]
WHERE [POSTINGTYPE]='Profit & Loss'
GROUP BY [FISCALYEAR],[ACCTPERIOD], [POSTINGTYPE]
ORDER BY acctperiod ASCand this is what is produces.

FISCALYEARACCTPERIODTrialBalanceDebitPOSTINGTYPE
2014 201401 282361372.13000 Profit & Loss
2014 201402 227246272.86000 Profit & Loss
2014 201403 315489534.33000 Profit & Loss
2014 201404 287423793.76150 Profit & Loss
2014 201405 256521290.76000 Profit & Loss
2014 201406 65582951.30000 Profit & Loss

Now I need a way to add another field that takes the TrialBalanceDebit from current ACCTPERIOD and adds it to the Previous ACCTPERIOD TrialBalanceDebit.

View 9 Replies View Related

T-SQL (SS2K8) :: Set Current Row Using Values In Previous Row

Feb 25, 2015

I've tried all sorts of code i.e. cross apply, running totals, etc. Cannot get this to work. I am trying to add a previous row value but only doing it for each group.

Source records
DECLARE @tbl table (Item int, Sequence int, StartTime datetime, Duration int)
INSERT INTO @tbl (Item,Sequence,StartTime, Duration) VALUES (1,1,'2/25/2015 12:00 am',10),(1,2,null,20),(1,3, null,22),(2,1,'2/25/2015 1:00 am',15),(2,2,null,30),(2,3, null,45),(2,4, null,5)
select * from @tbl

ItemSequenceStartTimeDuration
1102/25/15 0:0010
12null 20
13null 22
2102/25/15 1:0015
22null 30
23null 45
2 4 null 5

I would like to set the start time of the next row to be equal to the previous row time + duration. I know the start time of each group of 'Items' when the 'Sequence' number = 1. The last 'duration' value in the group would be ignored.

My expected output would be:

ItemSequenceStartTimeDuration
1102/25/15 0:0010
1202/25/15 0:1020
1302/25/15 0:3022
2102/25/15 1:0015
2202/25/15 1:1530
2302/25/15 1:4545
2402/25/15 2:305

View 7 Replies View Related

T-SQL (SS2K8) :: Get First Day Previous Quarter From Todays Date

Aug 22, 2014

How do I get first day of last month of previous quarter from today's date? I know my question is little confusing. I need to get 06/01/2014 using t-sql.

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

T-SQL (SS2K8) :: Calculate And Return Previous Date At 18:00 Hours

Mar 4, 2014

How can I calculate and return the previous Date at 18:00 Hours?

Here is a miserable attempt:

DECLARE @RunDate SmallDateTime
DECLARE @CurrentDate SmallDateTime
DECLARE @RunDateWoTime SmallDateTime
SET @CurrentDate = GETDATE()

SET @RunDate = DATEADD(day,-1,@CurrentDate)-- AS CurrentDate
SELECT @RunDate AS RunDate

-- Desired Result is the following:

-- 2014-03-03 18:00

View 9 Replies View Related

T-SQL (SS2K8) :: Carry Forward Values From Previous Rows

Mar 23, 2014

I am working on a rewards program and I have a table whenever customer completes a trip, his total fare,business points earned for that particular trip and respective Promotional points gets inserted.

Now I have a scenario whenever customer business points accumulates to 10 then need to award 3 promotional points.

If Business Points=14 for a single trip then for the first 10 points respective Promo points will be awarded and the remaining 4 points should get carry forward for the next trip and this 4 points should get accumulated with the next trip Business Points and so on.

Basically need to check for every 10 Business points accumulated award some Promo points and carry forward remaining points.

Here is the sample table structure and data :

CREATE TABLE [dbo].[tblRedeems]
(
[Mobileno] [varchar](50) NOT NULL,
[TripNo] [int] NOT NULL,
[CustomerName] [varchar](50) NULL,
[TripEndTime] DATETIME NOT NULL,

[Code] .....

View 5 Replies View Related

T-SQL (SS2K8) :: Date Logic - How To Get Previous 6 Months Data

Sep 29, 2014

I am working in sqlserver 2008 R2 and below is my sample research query. I am trying to get previous 6 months data.

WITH CutomMonths
AS (
SELECT UPPER(convert(VARCHAR(3), datename(month, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0)))) Month
,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0) startdate
,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N + 1, 0) enddate
FROM (
VALUES (1)

[Code] ...

Current output what i am getting:

Expected Output:

I found why the April month i didn't get the $20 because the startdate of my perks CTE '2014-04-03'. If it is '2014-04-01' then i will get the expected output.

But i should not change the the date on perks. How to neglect this date issue and consider the month instead to get the expected output.

View 6 Replies View Related

T-SQL (SS2K8) :: Finding Previous Even Numbered Month And Appropriate Year From Given Date

Mar 25, 2014

I'm trying to write some T-SQL to return the previous even numbered month and appropriate year from given date.

Examples given:
03-25-2014 should return 02-xx-2014
01-01-2014 should return 12-xx-2013

View 2 Replies View Related

T-SQL (SS2K8) :: Pass Dates For Previous Months Start And End Date

Mar 16, 2015

I've SSRS sales report to which I need to pass the dates for previous month's start date and end date which I am able to pass using below code. However, since the sales report has data from the past year(2014) I need to pass the dates for last year as well. The below code gives StartDate1 as 2015-02-01 and EndDate1 as 2015-02-28. I need to get the dates for past year like 2014-02-01 as StartDate2 and 2014-02-28 as EndDate2

SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000201', GETDATE()), '19000101') AS StartDate1,
DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '18991231') AS EndDate1

View 1 Replies View Related

T-SQL (SS2K8) :: Update DateTime Field With Date-inserted From Previous Record?

Jan 14, 2015

My goal is to update the "PriorInsert" field with the "DateInserted" from the previously inserted record where the WorkOrder, MachineNo, and Operator are all in the same group.

While trying to get to the correct previous record, I wrote the query below.

P.S. The attached .txt file includes a create and insert tbl_tmp sampling.

select top 1
a.ID,
a.WorkOrder,
a.MachineNo,
a.Operator,
a.PriorInsert,

[code]...

View 2 Replies View Related

T-SQL (SS2K8) :: Compare Records In Tables?

Mar 3, 2014

There are four tables

1. Matter

MID, CID, RType

001, a, m
002, a, m
003, b, m
004, c, m

2. Category

CID. RType
a, T
b, T
c, T

3. Security assignmnet

RID, RType, GID
001, m, g01
002, m, g01
002, m, g02
002, m, g03
003, m, g01
003, m, g03
a, T, g01
a, T, g02
a, T, g03
b, T, g02
b, T, g03
b, T, g04

4. Group

GID
g01
g02
g03
g04

I'd like to find the record in table #1 "Matter" which has exact record of "GID" in table #3 "Security Assignment" compare with table #2 "Category"

In this case, it is record of "002" bacause "002" in table#1 "Matter" and the record "a" in table #2 "category" both has exact GID records(g01, g02, g03) in table #3, "Security Assignment"

How can I create qury to find all the possible record in the table #2?

View 9 Replies View Related

T-SQL (SS2K8) :: Compare Data Between 2 Rows?

Jun 27, 2014

I have the following recordset:

cmdBatchNbPdsLbsZONE
817159644 1.55320031
817159652 9.09590031
817159679 2.5891806
817159687 5.7123006
817159709 2.3903006
817159733 2.2792006
817159741 2.0647007
817159768 1.2430007
817159784 4.1547006
817159792 3.56576013

I need to extract the corresponding price from the following table:

Zone MaxWeight Price
---------------------- ---------------------------------------
31 1.70 7.14
31 2.20 8.76
31 3.30 9.47
31 4.40 9.69
31 5.50 10.61
31 6.60 11.05
31 7.70 11.49
31 8.80 11.93
31 9.90 12.37
31 11.00 12.81
31 12.10 13.23

In this case, the 2 first rows should give a price of

1) 7.14 (weight between 0 - 1.70)

2) 11.93 (weight between 8.80 - 9.90)

How can I do that with a query?

View 4 Replies View Related

T-SQL (SS2K8) :: Compare And Insert Record

Oct 15, 2014

I am having below two tables:

1) TableA : Which contains 5 columns(Column1,..........Column5)
2)TableB : Which contains 10 columns(Column1,..........Column10)

TableB contains millions of data.Now I want select all 5 columns from tableA but combination of Column1,Column2,Column3 if present in tableB, then i want exclude that records.I am doing as below:

select * from TableA a join TableB b a.column1!=b.column1 and a.column2!=b.column2 and a.column3!=b.column3 )

But query is taking almost 5 minutes. Is there is another approach?

View 4 Replies View Related

T-SQL (SS2K8) :: How To Compare Two Rows And Two Different Columns

Oct 22, 2014

I am fairly new to SQL and writing queries so bear with my faults. I am learning on the job, which is good and bad. Below is a query that I have written to obtain some information. The problem arises when we have a patient who goes from Patient Type '1' to Patient Type '2'. This needs to be considered a singular visit and the only way I can think that this may work is if: for any specific medical record a dsch_ts is equal to the Admit TS on the next row.

How to complete something like this and my google searches have been fruitless. I attached a spreadsheet with an example of what I am getting.

SELECT DISTINCT
TPM300_PAT_VISIT.med_rec_no,
TSM040_PERSON_HDR.lst_nm AS 'Last Name',
TSM040_PERSON_HDR.fst_nm AS 'First Name',

[Code] ....

View 6 Replies View Related

T-SQL (SS2K8) :: Compare Two Varchar For Similarities?

Oct 28, 2014

I am asked to compare the address fields (three columns of nvarchar(100) ) of a customer database (around 10,000 records) and find any duplicates. If it is a character by character match, I could have just GROUPed and get the result.

But, I am expected to produce a list with similar addresses which the guys who entered may have use slightly different spelling or more or less characters, or a "." here and there.

is there any way to create a query for this?

View 5 Replies View Related

Compare The Value Of A Variable With Previous Variable From A Function ,reset The Counter When Val Changes

Oct 15, 2007

I am in the middle of taking course 2073B €“ Programming a Microsoft SQL Server 2000 Database. I noticed that in Module9: Implementing User-Defined Functions exercise 2, page 25; step 2 is not returning the correct answer.

Select employeeid,name,title,mgremployeeid from dbo.fn_findreports(2)

It returns manager id for both 2 and 5 and I think it should just return the results only for manager id 2. The query results for step 1 is correct but not for step 2.

Somewhere in the code I think it should compare the inemployeeid with the previous inemployeeid, and then add a counter. If the two inemployeeid are not the same then reset the counter. Then maybe add an if statement or a case statement. Can you help with the logic? Thanks!

Here is the code of the function in the book:

/*
** fn_FindReports.sql
**
** This multi-statement table-valued user-defined
** function takes an EmplyeeID number as its parameter
** and provides information about all employees who
** report to that person.
*/
USE ClassNorthwind
GO
/*
** As a multi-statement table-valued user-defined
** function it starts with the function name,
** input parameter definition and defines the output
** table.
*/
CREATE FUNCTION fn_FindReports (@InEmployeeID char(5))
RETURNS @reports TABLE
(EmployeeID char(5) PRIMARY KEY,
Name nvarchar(40) NOT NULL,
Title nvarchar(30),
MgrEmployeeID int,
processed tinyint default 0)
-- Returns a result set that lists all the employees who
-- report to a given employee directly or indirectly
AS
BEGIN
DECLARE @RowsAdded int
-- Initialize @reports with direct reports of the given employee
INSERT @reports
SELECT EmployeeID, Name = FirstName + ' ' + LastName, Title, ReportsTo, 0
FROM EMPLOYEES
WHERE ReportsTo = @InEmployeeID
SET @RowsAdded = @@rowcount
-- While new employees were added in the previous iteration
WHILE @RowsAdded > 0
BEGIN
-- Mark all employee records whose direct reports are going to be
-- found in this iteration
UPDATE @reports
SET processed = 1
WHERE processed = 0

-- Insert employees who report to employees marked 1
INSERT @reports
SELECT e.EmployeeID, Name = FirstName + ' ' + LastName , e.Title, e.ReportsTo, 0
FROM employees e, @reports r
WHERE e.ReportsTo = r.EmployeeID
AND r.processed = 1
SET @RowsAdded = @@rowcount
-- Mark all employee records whose direct reports have been
-- found in this iteration
UPDATE @reports
SET processed = 2
WHERE processed = 1
END
RETURN -- Provides the value of @reports as the result
END
GO

View 1 Replies View Related

T-SQL (SS2K8) :: Compare Tables With More Than 4.9 Million Records?

Mar 18, 2014

I want to compare ONLY 1 Column values from 2 tables having more than 4.9 million records. There is a difference of 4000 rows between the 2 tables.

SELECT ID From TABLE1 where ID not in (SELECT DISTINCT ID From TABLE2)

My above query took nearly 4.5 hours to run and I had to cancel it. Is there a better way to write the query . I just want to compare the ID - column values which are missing in TABLE2

View 7 Replies View Related

T-SQL (SS2K8) :: Compare Multiple Column Between Two Tables

Aug 14, 2014

I want to display records from @table1 only when combination of col2,col3 and col4 are present in @table2.In Below case I want output as: below two records only.

'test1', 'need this record', 25, {d '1901-01-01'}
'test3', 'some longer value', 23, {d '1900-01-01'}
declare @table1 table (
col1 varchar(10) not null,
col2 varchar(200) null,
col3 int not null,

[code]....

View 2 Replies View Related

T-SQL (SS2K8) :: Query To Compare Data In 2 Tables

Sep 17, 2015

Table 1 has "Gender" field with "Male" and "Female" in it, table 2 has "Gender" field with "M" and "F" in it. a query to compare data and list the differences.

View 4 Replies View Related

Compare Given Period In Current Year / Previous Year

Mar 15, 2006

HiI want to write a function that can return a sum for a given daterange. The same function should be able to return the sum for the sameperiod year before.Let me give an example:The Table LedgerTrans consist among other of the follwing fieldsAccountNum (Varchar)TransdateAmountMST (Real)The sample data could be1111, 01-01-2005, 100 USD1111, 18-01-2005, 125 USD1111, 15-03-2005, 50 USD1111,27-06-2005, 500 USD1111,02-01-2006, 250 USD1111,23-02-2006,12 USDIf the current day is 16. march 2006 I would like to have a functionwhich called twice could retrive the values.Previus period (for TransDate >= 01-01-2005 AND TransDate <=16-03-2005) = 275 USDCurrent period (for TransDate >= 01-01-2006 AND TransDate <=16-03-2006) = 262 USDThe function should be called with the AccountNum and current date(GetDate() ?) and f.ex. 0 or 1 for this year / previous year.How can I create a function that dynamically can do this ?I have tried f.ex. calling the function with@ThisYear as GetDate()SET @DateStart = datepart(d,0) + '-' + datepart(m,0) +'-'+datepart(y,@ThisYear)But the value for @dateStart is something like 12-07-1905 so thisdon't work.I Would appreciate any help on this.BR / Jan

View 3 Replies View Related

T-SQL (SS2K8) :: How To Compare Data (join) Based On Two Varchar Columns

Mar 15, 2014

-- My first Data

create table #myfirst (id int, city varchar(20))
insert into #myfirst values (500,'Newyork')
insert into #myfirst values (100,'Ediosn')
insert into #myfirst values (200,'Atlanta')
insert into #myfirst values (300,'Greenwoods')
insert into #myfirst values (400,'Hitchcok')
insert into #myfirst values (700,'Walmart')
insert into #myfirst values (800,'Madida')

-- My Second Data

create table #mySecond (id int, city varchar(20),Sector varchar(2))
insert into #mySecond values (1500,'Newyork','MK')
insert into #mySecond values (5500,'Ediosn','HH')
insert into #mySecond values (5060,'The Atlanta','JK')
insert into #mySecond values (7500,'The Greenwoods','DF')
insert into #mySecond values (9500,'Metro','KK')
insert into #mySecond values (3300,'Kilapr','MK')
insert into #mySecond values (9500,'Metro','NH')

--Third Second Data

create table #myThird (id int, city varchar(20),Sector varchar(2))
insert into #myThird values (33,'Walmart','PP')
insert into #myThird values (20,'Ediosn','DD')
select f.*,s.Sector from #myfirst f join #mySecond s on f.city = s.city
/*
idcitySector
500NewyorkMK
100EdiosnHH
*/

i have doubt on two things

1) How Can i compare the City names, by eliminating 'The ' at the beginning (if there is any in second tale city) between first and second

2) after comparing first and second if there is no match found in second them want to compare with third table values for those not found

--i tried below to solve first doubt, it is working but want to know any other wasys to do it

select f.*,s.Sector from #myfirst f join #mySecond s on replace (f.city, 'THE ','')= replace (s.city, 'THE ','')

--Expected results wull be

create table #ExpectResults (id int, city varchar(20),Sector varchar(2))
insert into #ExpectResults values (200,'Atlanta','JK')
insert into #ExpectResults values (100,'Ediosn','HH')
insert into #ExpectResults values (300,'Greenwoods','DF')
insert into #ExpectResults values (500,'Newyork','MK')
insert into #ExpectResults values (700, 'Walmart','PP')
insert into #ExpectResults values (800, 'Madidar','')

[code]....

View 1 Replies View Related







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