Sql Query Help(Inner Join With Date Column)

[dotnetrockz]

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?

[ChampakDas]

paste your query here

[dotnetrockz]

select (COLUMNS)

from

Table A ge

inner join Table B te

on te.DeviceNumber = ge.DeviceNumber

and isnull(TE.DeviceType,'') = isnull(GE.DeviceType,'')

and isnull(te.DeviceLocation,'') = isnull(ge.DeviceLocation,'')

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

[ChampakDas]

bagane working kada

[ChampakDas]

in any of hte records I dont see 14:25

 

ABC123    I     2013-11-06 13:55:00.000    HOU    ABC123    I     2013-11-06 13:55:00.000    HOU
ABC123    I     2013-11-06 13:59:00.000    HOU    ABC123    I     2013-11-06 13:55:00.000    HOU
ABC123    I     2013-11-06 13:55:00.000    HOU    ABC123    I     2013-11-06 13:59:00.000    HOU
ABC123    I     2013-11-06 13:59:00.000    HOU    ABC123    I     2013-11-06 13:59:00.000    HOU
ABC123    o     2013-11-06 14:05:00.000    HOU    ABC123    o     2013-11-06 14:05:00.000    HOU

[dotnetrockz]

YES Thats bcoz......i changed the where clause

 

where TE.DeviceEventDate between dateadd(mi,-5,GE.DeviceEventDate) and dateadd(mi,61,GE.DeviceEventDate)----(interval used to be 30 min) so it used to pick the 4th one too

 

instead of these datetime manipulation is there any way to fix this

 

 

 

i
 

[ChampakDas]

epudu ardam ayindi

YES Thats bcoz......i changed the where clause

 

where TE.DeviceEventDate between dateadd(mi,-5,GE.DeviceEventDate) and dateadd(mi,61,GE.DeviceEventDate)----(interval used to be 30 min) so it used to pick the 4th one too

 

instead of these datetime manipulation is there any way to fix this

 

 

 

i
 

 

[ChampakDas]

columns list lo b list petuko

SELECT TE.*
  FROM A GE
  JOIN B TE
    ON TE.DT=GE.DT
   AND TE.DL = GE.DL
   AND TE.DED=TE.DED
  JOIN B TE2
    ON TE2.DT = TE.DT
   AND TE2.DED= TE2.DED
   

[Gutka_Mukesh]

CITI#H@

[dotnetrockz]

 

columns list lo b list petuko

SELECT TE.*
  FROM A GE
  JOIN B TE
    ON TE.DT=GE.DT
   AND TE.DL = GE.DL
   AND TE.DED=TE.DED
  JOIN B TE2
    ON TE2.DT = TE.DT
   AND TE2.DED= TE2.DED
   

ON TE.DT=GE.DT
 idhi try chesa work avvaledhu.....thats why i was using the between clause

 

[ChampakDas]

now okay kada??

 

 

[dotnetrockz]

ok ante...edo okati chesi result vache laga code chesthunna

 

 

prasthuthanki where clause ni ila change chesa

DeviceEventDate >=dateadd(mi,-1,@mineventdt) and DeviceEventDate <=dateadd(mi,1,@mineventdt)

 

 

ikkada @mineventdt,@maxeventdt dates table B(3 records unn atbale ) vati max and min dates

[mukunda]

pinky kosam...nenu  CITI#H@ board pattukunna

[ChampakDas]

aa query work kaleda

ok ante...edo okati chesi result vache laga code chesthunna

 

 

prasthuthanki where clause ni ila change chesa

DeviceEventDate >=dateadd(mi,-1,@mineventdt) and DeviceEventDate <=dateadd(mi,1,@mineventdt)

 

 

ikkada @mineventdt,@maxeventdt dates table B(3 records unn atbale ) vati max and min dates

 

[dotnetrockz]

aa query work kaleda

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)