Sql Query Help(Inner Join With Date Column)

[cherlapalli_jailer]

urgent kaaka pothe t'row morning try chesta

[ChampakDas]

 brother vachi koteyi nannu...nakem ardam kavatle

do you want the join to happen on datetime column (nenu echina query ade) ? If yes, what is that range in the where clause requirement not clear

Datetime tho join work avvaledhu...

 

actual ga ee where clause lo which date range it is searching...??

where TE.DeviceEventDate between dateadd(mi,-5,GE.DeviceEventDate) and dateadd(mi,61,GE.DeviceEventDate)

 

[Guest PangaNamam]

Just inner join without fomatting the date field ayithe work kadu anukunta...cartesian product lekunte duplicate rows vasthayi...

I have 2 tables

 

 

Table A:

 

Id     DeviceNumber      DeviceType    DeviceEventDate      DeviceLocation

1      ABC123                 I                      2013-11-06 13:55    HOU

2      ABC123                 I                      2013-11-06 13:59    HOU

3      ABC123                O                     2013-11-06 14:05    HOU

4      ABC123                 I                      2013-11-06 14:25    HOU

 

 

 

Table B:

 

    DeviceNumber      DeviceType    DeviceEventDate      DeviceLocation

      ABC123                 I                      2013-11-06 13:55    HOU

      ABC123                 I                      2013-11-06 13:59    HOU

      ABC123                O                     2013-11-06 14:05    HOU

 

 

 

 

Iam doing an innerjoin to retrieve common rows from both tables but the join is returing the extra row(id=4)....how can we filter the join using dates?

 

[libraguy863]

Bro how about a left join, if you want to get all the records from 1st table .

[chillax]

I have 2 tables

 

 

Table A:

 

Id     DeviceNumber      DeviceType    DeviceEventDate      DeviceLocation

1      ABC123                 I                      2013-11-06 13:55    HOU

2      ABC123                 I                      2013-11-06 13:59    HOU

3      ABC123                O                     2013-11-06 14:05    HOU

4      ABC123                 I                      2013-11-06 14:25    HOU

 

 

 

Table B:

 

    DeviceNumber      DeviceType    DeviceEventDate      DeviceLocation

      ABC123                 I                      2013-11-06 13:55    HOU

      ABC123                 I                      2013-11-06 13:59    HOU

      ABC123                O                     2013-11-06 14:05    HOU

 

 

 

 

Iam doing an innerjoin to retrieve common rows from both tables but the join is returing the extra row(id=4)....how can we filter the join using dates?

database lo with respect to dates and timestamps , its always better to explicitly convert them before comparing them...

 

try converting the date fields in the comparision as 

 

to_char(date1,'mm/dd/yyyy hh24:mi:ss')=to_char(date2,'mm/dd/yyyy hh24:mi:ss')

 

it should work...let me know if it works for you or not.... :police:  :police:

[sk789]

Select a.*
From tableA as a
Inner join table B as b
On a.devicenumber = b.devicenumber
And a.devicetype = b.devicetype
And a.deviceeventdate = b.deviceeventdate
And a.devicelocation = b.devicelocation

I think this would work to get only the matched rows from table A and table B

[pachimirchi]

I have 2 tables

 

 

Table A:

 

Id     DeviceNumber      DeviceType    DeviceEventDate      DeviceLocation

1      ABC123                 I                      2013-11-06 13:55    HOU

2      ABC123                 I                      2013-11-06 13:59    HOU

3      ABC123                O                     2013-11-06 14:05    HOU

4      ABC123                 I                      2013-11-06 14:25    HOU

 

 

 

Table B:

 

    DeviceNumber      DeviceType    DeviceEventDate      DeviceLocation

      ABC123                 I                      2013-11-06 13:55    HOU

      ABC123                 I                      2013-11-06 13:59    HOU

      ABC123                O                     2013-11-06 14:05    HOU

 

 

 

 

Iam doing an innerjoin to retrieve common rows from both tables but the join is returing the extra row(id=4)....how can we filter the join using dates?

works for me .. perhaps nee ID column identity emo..

 

 

declare

@ge table (id int ,devicenumber varchar(15),devicetype char(1),deviceeventdate datetime,devicelocation varchar(7))

declare

@te table (id int ,devicenumber varchar(15),devicetype char(1),deviceeventdate datetime,devicelocation varchar(7))

Insert

@ge

select

1,'ABC123','I','2013-11-06 13:55','HOU'

union

select

2,'ABC123','I','2013-11-06 13:59','HOU'

union

select

3,'ABC123','O','2013-11-06 14:05','HOU'

union

select

4,'ABC123','I','2013-11-06 14:25','HOU'

Insert

@te

select

1,'ABC123','I','2013-11-06 13:55','HOU'

union

select

2,'ABC123','I','2013-11-06 13:59','HOU'

union

select

3,'ABC123','O','2013-11-06 14:05','HOU'

 

 

select

* from

@ge ge

inner join @te te

on

ge.devicenumber=te.devicenumber

and

ge.devicetype=te.devicetype

and

ge.devicelocation=te.devicelocation

and

cast(ge.deviceeventdate as datetime)=cast(te.deviceeventdate as datetime)

[cherlapalli_jailer]

Hi Nissan 

Did u get the answer??

[150bryant]

date ni char laga marchi then join chey...