want to combine 2 different reports into 1

[bj1]

Hi All,

This is what i have got so far:

declare @ThisDate datetime
create table #DayList (FakeDate datetime)
set @ThisDate = '08/17/2003 14:00:00'

while not(
(round(convert(float,@ThisDate),0,1) )> (round(convert(float, convert(datetime,'08/19/2003 13:59:59')),0,1))
)
begin
insert #DayList values (@ThisDate)
set @ThisDate = DateAdd(d,1,@ThisDate)
end

create table #DataList (FakeDate datetime, vDescription nvarchar(50), sSensorTypeID integer,
sDescr nvarchar(50), stmCalculatedValue float, stmCalculatedValueDescription nvarchar(50),
xdDateTime datetime, xdValue float, stSensorDataTypeIDC integer, Longitude numeric(18,10),
Latitude numeric(18,10), LonLatDescr varchar(250), xdID integer
)

insert into #DataList
select #DayList.*, VT.[Description], S.sSensorTypeID, S.sDescr,
STM.stmCalculatedValue, STM.stmCalculatedValueDescription,
XD.xdDateTime, xdValue, ST.stSensorDataTypeIDC, XD.xdLongitude,
XD.xdLatitude, cast('' as varchar(250)), XD.xdID
FROM (((ext_sensortypes ST INNER JOIN Ext_sensors S ON ST.stSensorTypeID = S.sSensorTypeID)
INNER JOIN His_ExternalData XD ON S.sSensorID = XD.xdSensorID)
INNER JOIN v_AllTrailerandVehicles VT ON S.sUnitID = VT.UnitID)

JOIN Ext_SensorTypeMessages STM ON (S.sSensorTypeID = STM.stmSensorTypeID
and XD.xdValue = STM.stmCalculatedValue)

Join #DayList on (DatePart(d, #DayList.FakeDate)=DatePart(d,XD.xdDateTime)
and
DatePart(m, #DayList.FakeDate)=DatePart(m,XD.xdDateTime)
and
DatePart(yyyy, #DayList.FakeDate)=DatePart(yyyy,XD.xdDateTime))

WHERE
(vt.[Description] in ('UWW593') ) and
(XD.xdDateTime >= '08/17/2003 14:00:00' )
AND (XD.xdDateTime <= '08/19/2003 13:59:59' )
AND (ST.stSensorDataTypeIDC in (1))
ORDER BY vt.[Description], FakeDate, sSensorTypeID, XD.xdID, XD.xdDateTime



create table #final_DataList (FakeDate datetime, vDescription nvarchar(50), sSensorTypeID integer,
sDescr nvarchar(50), stmCalculatedValue float, stmCalculatedValueDescription nvarchar(50),
xdDateTime datetime, xdValue float, stSensorDataTypeIDC integer, Longitude numeric(18,10),
Latitude numeric(18,10), LonLatDescr varchar(250), xdID integer
)

declare cur_data cursor
for
Select vDescription,FakeDate, sSensorTypeID, sDescr,xdID, xdDateTime,
xdValue, stSensorDataTypeIDC, stmCalculatedValue,
stmCalculatedValueDescription ,Longitude,Latitude,LonLatDescr
from #DataList
order by vDescription, sDescr, xdID, xdDateTime /* ,FakeDate , sSensorTypeID, xdID, xdDateTime*/

declare @vDescription nvarchar(50), @sDescr nvarchar(50), @stmCalculatedValueDescription nvarchar(50),@LonLatDescr varchar(250)
declare @sSensorTypeID int, @xdID int, @stSensorDataTypeIDC int
declare @FakeDate datetime, @xdDateTime datetime
declare @xdValue float,@stmCalculatedValue float
declare @Longitude numeric(18,10), @Latitude numeric(18,10)

declare @prev_vDescription nvarchar(50), @prev_sDescr nvarchar(50)

declare @prev_xdValue float

open cur_data

fetch next from cur_data
into @vDescription,@FakeDate,@sSensorTypeID,@sDescr, @xdID, @xdDateTime,
@xdValue, @stSensorDataTypeIDC, @stmCalculatedValue,
@stmCalculatedValueDescription,@Longitude,@Latitude,@LonLatDescr

set @prev_xdValue = -100
set @prev_vDescription= ''
set @prev_sDescr = ''

while @@fetch_status = 0
begin
if @prev_xdValue <> @xdValue or @prev_vDescription <> @vDescription or @prev_sDescr <> @sDescr
begin
set @prev_xdValue = @xdValue
set @prev_vDescription = @vDescription
set @prev_sDescr =@sDescr

insert into #final_DataList
(vDescription,FakeDate, sSensorTypeID,sDescr,xdID,xdDateTime,
xdValue,stSensorDataTypeIDC,stmCalculatedValue,
stmCalculatedValueDescription,Longitude, Latitude,LonLatDescr )
values
(@vDescription,@FakeDate,@sSensorTypeID,@sDescr,@xdID,@xdDateTime,
@xdValue,@stSensorDataTypeIDC,@stmCalculatedValue,
@stmCalculatedValueDescription,@Longitude, @Latitude, @LonLatDescr )

end
fetch next from cur_data
into @vDescription,@FakeDate,@sSensorTypeID,@sDescr, @xdID, @xdDateTime,
@xdValue, @stSensorDataTypeIDC, @stmCalculatedValue,
@stmCalculatedValueDescription,@Longitude,@Latitude,@LonLatDescr


end

close cur_data
deallocate cur_data

Select vDescription, sSensorTypeID, sDescr, xdDateTime,
xdValue, stSensorDataTypeIDC, Longitude,
Latitude, LonLatDescr, stmCalculatedValue,
stmCalculatedValueDescription

from #final_DataList

order by vDescription,FakeDate,sSensorTypeID,xdID,xdDateTime

drop table #final_DataList


drop table #DayList

drop table #DataList

To give meaning to what the output could be:

vDescription: UWW593
sSensorTypeID: 8
sDescr: Cleaning machine
xdDateTime: 2003-08-18 01:49:07.000
xdValue: 1.0 or 0.0
stSensorDataTypeIDC: 1
Longitude: 151.7558333333
Latitude: -32.9256666667
LonLatDescr:
stmCalculatedValue: 1.0 or 0.0
stmCalculatedValueDescription: On or Off

AND i also have another table:

SELECT JE.jeLocationId, JE.jeGpsDateTime, JE.jePulses, JE.jeType, JE.jeTag, UT.utScaler AS aemScaler, VT.unitID as vUnitID, VT.MakeModel as vMakeModel, VT.[Description] as vDescription, VT.Registration as vRegistration, L.locDescription, JE.jeLongitude as Longitude, JE.jeLatitude as Latitude, cast('' as varchar(250)) as LonLatDescr, CASE jeType WHEN 10 THEN 10 WHEN 2 THEN 10 WHEN 30 then 20 when -2 then 30 when 1 then 40 when 9 then 40 ELSE 99 END as TypeOrder, VT.typeIDC
FROM ((((His_JourneyEvents JE
INNER JOIN Vmi_Units U ON JE.jeUnitID = U.unUnitID)
INNER JOIN Stc_UnitTypes UT ON U.unUnitTypeID = UT.utUnitTypeID)
INNER JOIN v_AllTrailerandVehicles VT ON JE.jeUnitID = VT.UnitID)
LEFT OUTER JOIN Vmi_Locations L ON JE.jeLocationID = L.locLocationID)
WHERE (vt.[Description] in ('UWW593')) and ((JE.jeGpsDateTime >= '08/17/2003 14:00:00') and (JE.jeGpsDateTime <= '08/19/2003 14:00:00'))
and JE.jeType in (1.00, 2.00, -2.00, 30.00, 10.00, 9.00) and (VT.typeIDC in (1,2))
order by typeIDC, vt.[Description], jeGpsDateTime, TypeOrder

To give meaning to what the output could be:

JE.jeGpsDateTime: 2003-08-18 00:17:22.000
JE.jeType: 2, -2, 30, 1, 9
VT.unitID as vUnitID: 93383
VT.[Description] as vDescription:UWW593
JE.jeLongitude as Longitude: 151.7576666667
JE.jeLatitude as Latitude: -32.9210000000

The fields i haven't described, i don't think are relevant at this stage. But let me know.

Anyway, basically i want to join the 2 table together:
I think the problem lies with the date fields from both tables, because they are both recording different movements. But i want to see something like this:

JE.jeGpsDateTime xdDateTime xdValue
----------------------------------------------------------
2003-08-18 00:17:22.000
2003-08-18 00:27:35.000 2003-08-18 00:27:33.000 1.0
2003-08-18 01:39:03.000
2003-08-18 01:48:46.000 0.0
2003-08-18 01:49:09.000
----------------------------------------------------------

Is this possible to do?
Does this make sense? If not let me know what other info you need.

Thanks in advance,
B