/* Vypocet produkce MSG Tower */

SELECT *
FROM OPENQUERY 
(RAK_HIS03

, ''
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = dateadd(d,-1,getdate())
SET @EndDate = getdate()
SET NOCOUNT OFF

select    tag as TagName
              , min(StartDateTime)  as CollectionDate
              , vValue as Value
              , BatchId from

(

SELECT t1.tag,cast(t1.vValue as float) as vValue ,t2.StartDateTime, t2.vValue as BatchId
from (

SELECT temp.TagName as tag, vValue,StartDateTime From (
SELECT  * 
 FROM History
WHERE History.TagName IN (''''BlownPowder totalizer'''')
AND wwRetrievalMode = ''''Cyclic''''

AND wwVersion = ''''Latest''''
AND DateTime >= @StartDate
AND DateTime <= @EndDate) temp
LEFT JOIN AnalogTag ON AnalogTag.TagName =temp.TagName
LEFT JOIN EngineeringUnit ON AnalogTag.EUKey = EngineeringUnit.EUKey
LEFT JOIN QualityMap ON QualityMap.QualityDetail = temp.QualityDetail
WHERE temp.StartDateTime >= @StartDate and (vValue is not null)

) as t1

join 

(

SELECT temp.TagName as tag2 , vValue,StartDateTime From (
SELECT  * 
 FROM History
WHERE History.TagName IN (''''MSG_CRU_QW_Data.BatchID'''')
AND wwRetrievalMode = ''''Cyclic''''

AND wwVersion = ''''Latest''''
AND DateTime >= @StartDate/
AND DateTime <= @EndDate) temp
LEFT JOIN AnalogTag ON AnalogTag.TagName =temp.TagName
LEFT JOIN EngineeringUnit ON AnalogTag.EUKey = EngineeringUnit.EUKey
LEFT JOIN QualityMap ON QualityMap.QualityDetail = temp.QualityDetail
WHERE (temp.StartDateTime >= @StartDate) and (vValue is not null)
) as t2 on t1.StartDateTime = t2.StartDateTime

)
AS subquery 

group by tag,BatchId,vValue

order by CollectionDate
''
)'