Merge two rows with condition SQL View

Question

I have a View which has a SQL Script as:

    Select
        a.iAssetId,
        ac.eEventCode,
        vm.dtUTCDateTime,
        g.iGeofenceId,
        g.sGeofenceName,
        c.sCategoryName,
        c.iCategoryId,
        s.sSiteName,
        s.iSiteId,
        CASE WHEN ac.eEventCode = 6 THEN vm.dtUTCDateTime ELSE NULL END as EnterTime,
        CASE WHEN ac.eEventCode = 7 THEN vm.dtUTCDateTime ELSE NULL END as ExitTime,

CASE WHEN
 a.iAssetId  = Lead(a.iAssetId) OVER (ORDER BY a.iAssetId)
                AND g.iGeofenceId = Lead(g.iGeofenceId) OVER (ORDER BY a.iAssetId)
                AND ac.eEventCode != Lead(ac.eEventCode)  OVER (ORDER BY a.iAssetId)
                THEN DATEDIFF(minute, vm.dtUTCDateTime, Lead(vm.dtUTCDateTime)  OVER (ORDER BY a.iAssetId))  ELSE NULL END as Test


    From AssetCommunicationSummary ac
    Inner join VehicleMonitoringLog vm on vm.iVehicleMonitoringId = ac.iVehicleMonitoringId
    Inner Join Geofences g on g.iGeofenceId = vm.iGeofenceId
    Inner Join Assets a on a.iAssetId = ac.iAssetId 
    Inner Join Categories c on c.iCategoryId =  a.iCategoryId
    Inner Join Sites s on s.iSiteId = c.iSiteId
     Where ac.eEventCode = 6 OR ac.eEventCode = 7
     Group by
         a.iAssetId,
            ac.eEventCode,
            vm.dtUTCDateTime,
            g.iGeofenceId,
            g.sGeofenceName,
            c.sCategoryName,
            c.iCategoryId,
            s.sSiteName,
            s.iSiteId

I have used Lead to calculate the Time differenc in minutes for leading rows based on conditions.

I need to now merge the leading Row and the Current Row based on Condition.

Is there a possible way to do this?

The goal is to get the EnterTime and ExitTime in the Same Row with Time Difference in the Column Next to it.

My result is like this:

Answer 1

If your eventcode is always going to be 6 and 7, then you can just join to that table twice using that clause in the join itself. I think I've got the rest of your schema joined up properly below, but if not, you can adjust it around to fit.

Select
    a.iAssetId,
    vmEnter.dtUTCDateTime,
    g.iGeofenceId,
    g.sGeofenceName,
    c.sCategoryName,
    c.iCategoryId,
    s.sSiteName,
    s.iSiteId,
    vmEnter.dtUTCDateTime as EnterTime,
    vmExit.dtUTCDateTime as ExitTime,
    DATEDIFF(minute, vmEnter.dtUTCDateTime, vmExit.dtUTCDateTime) as ExitTime,
From Sites s
    Inner Join Categories c on s.iSiteId = c.iSiteId
    Inner Join Assets a on c.iCategoryId =  a.iCategoryId
    Inner Join AssetCommunicationSummary acEnter on a.iAssetId = acEnter.iAssetId and acEnter.eEventCode = 6
    Inner Join VehicleMonitoringLog vmEnter on vmEnter.iVehicleMonitoringId = acEnter.iVehicleMonitoringId 
    Inner Join AssetCommunicationSummary acExit on a.iAssetId = acExit.iAssetId and acExit.eEventCode = 7
    Inner Join VehicleMonitoringLog vmExit on vmExit.iVehicleMonitoringId = acExit.iVehicleMonitoringId 
    Inner Join Geofences g on g.iGeofenceId = vmEnter.iGeofenceId

Answer 2

Try this

SELECT iAssetid,
       iGeoFenceId,
       iGeoFenceName,
       sCategoryNamea,
       iCategoryid,
       sSiteName,
       Max(EnterTime) As EnterTime,
       Min(ExitTime) As ExitTime,
       Datediff(minute,  Max(EnterTime), Min(ExitTime)) As Timediff
FROM   #vw_Table
GROUP  BY iAssetid,
          iGeoFenceId,
          iGeoFenceName,
          sCategoryNamea,
          iCategoryid,
          sSiteName

Answer 3

Im gonna guess that eventcode = 6 means thats the intake time

if so two of your data paris dont make much sense as the exit time is before the intake time,

The Query below only accounts for when amd if eventcode 6 = intake time and the fact that exit time should be before entertime. query is based on the output you provided and not the view query.

if doing a select * on your view table gives you that output then replace vw_table with yourviewstablename

There are Nulls in the timedif of sqlfiddle because

• there was only one instance of assetid 2
• assetid 4 and 6 have exit time that happened before entertimes

SQLFIDDLE

 select
            v1.iAssetid,
            v1.EnterTime, 
            v2.ExitTime,
            datediff(minute,  v1.Entertime, v2.Exittime) timedif


        from vw_table v1 
          left join vw_table v2 on
            v1.iAssetid= v2.iAssetid
            and v1.sCategoryNamea = v2.sCategoryNamea
            and v2.eEventcode = 7 
            and v2.dtUTCDatetime  >= v1.dtUTCDatetime  


        where  
          v1.eEventcode = 6

Answer 4

You can use this ddl to test and see the idea of what is going on. It's copy and paste ready, if you want to see a difference in times, make sure you wait before you insert each record.

Create table testing
(
  Id int ,
  Enter DateTime, 
  Exitt DateTime,
  Eventt int,
  GeoCode int
)

insert into testing values (1, GETDATE(),null,6,10)
insert into testing values (1, null,GETDATE(),7,10)

insert into testing values (1, GETDATE(),null,6,11)
insert into testing values (1, null,GETDATE(),7,11)

insert into testing values (2, GETDATE(),null,6,10)
insert into testing values (2, null,GETDATE(),7,10)

create table #temp1
(
  Id int, EnterTime datetime, GeoCode int
)

create table #temp2
(
  Id int, ExitTime datetime, GeoCode int
)

insert into #temp1
Select Id, MAX(Enter), GeoCode from testing where Eventt = 6 group by Id,GeoCode

insert into #temp2
Select Id, MAX(Exitt),GeoCode from testing where Eventt = 7 group by Id,GeoCode

Select t1.Id, t1.EnterTime,t2.ExitTime, t1.GeoCode, DATEDIFF(ss,t1.EnterTime,t2.ExitTime)
from #temp1 t1
inner join #temp2 t2 on t2.Id = t1.Id 
                     and t1.GeoCode = t2.GeoCode    

This is basically pseudo code so your going to need to modify, but everything you need is here.

Answer 5

You can merge two result sets by adding Row_Number to them and then join on that. Like

SELECT DISTINCT tbl1.col1, tbl2.col2
FROM
   (SELECT FirstName AS col1, ROW_NUMBER() OVER (ORDER BY FirstName) Number FROM dbo.UBUser) tbl1
INNER JOIN
  (SELECT LastName AS col2, ROW_NUMBER() OVER (ORDER BY LastName) Number FROM dbo.UBUser) tbl2 
ON tbl1.Number = tbl2.Number

This way you will be able to have EnterTime and ExitTime in the Same Row with Time Difference in the Column Next to it.