Join Two Tables and filter records in SQL Server

Last Reply on May 19, 2017 03:17 AM By dharmendr

Posted on May 19, 2017 12:38 AM

I have Two Table

A

id.    shipment.  vehicle_name.  vehicle_id.      begin.
123  11111        B9373VO          10884919       2017-05-19 11:00

B

vehicle_id.  date.                           type.    description.  geofence
10884919  2017-05-19 11:30:00.000 31        EXIT           Aps
10884919  2017-05-19 12:00:00.000 30        ENTER         Uli
10884919  2017-05-19 11:00:00.000 13        STOP          IDLING

 

how to create view with join table with add field

origin = type = 31 and destination = type 30 based from date table A

 

 

Posted on May 19, 2017 03:17 AM

Hi Ruben12345,

Refer the below sample query and change the where condition as per your need.

DECLARE @A AS TABLE(id INT,shipment INT,vehicle_name VARCHAR(20),vehicle_id INT,[begin] DATETIME)
INSERT INTO @A VALUES(123,11111,'B9373VO',10884919,'2017-05-19 11:00')

DECLARE @B AS TABLE(vehicle_id INT,[date] DATETIME,[type] INT,[description] VARCHAR(20),geofence VARCHAR(10))
INSERT INTO @B VALUES(10884919,'2017-05-19 11:30:00.000',31,'EXIT','Aps')
INSERT INTO @B VALUES(10884919,'2017-05-19 12:00:00.000',30,'ENTER','Uli')
INSERT INTO @B VALUES(10884919,'2017-05-19 11:00:00.000',13,'STOP','IDLING')

SELECT	A.id,A.shipment,A.vehicle_name,A.vehicle_id,A.[begin]
		,B.[date],B.[type],B.[description],B.geofence 
FROM @A A
INNER JOIN @B B ON A.vehicle_id = B.vehicle_id
WHERE B.[type] IN (31,30)

OutPut

id shipment vehicle_name vehicle_id begin date type description geofence
123 11111 B9373VO 10884919 2017-05-19 11:00:00.000 2017-05-19 11:30:00.000 31 EXIT Aps
123 11111 B9373VO 10884919 2017-05-19 11:00:00.000 2017-05-19 12:00:00.000 30 ENTER Uli