0 Replies Latest reply: Jun 22, 2012 2:31 AM by Rahul Dhoble RSS

    Help regarding convert SQL Query to QlikView query

      I want to create qlikview query same as below sql query . Please suggest.

       

      DECLARE @gh AS TABLE (JN BIGINT,FT NVARCHAR(50),Ptime DATETIME)

      INSERT INTO @gh SELECT 1,'CB2','12:30'
      Union
      SELECT 2,'CB2','12:37'
      Union
      SELECT 3,'CB2','13:45'
      Union
      SELECT 4,'CB1','12:37'
      Union
      SELECT 5,'CB1','12:40'
      Union
      SELECT 6,'CB2','13:40'

      SELECT gh1.JN,gh1.FT,gh1.Ptime,COALESCE(gh2.jn,gh3.jn) as OtheFT, COALESCE(gh2.Ptime,gh3.Ptime) AS OtherPtime--,gh3.Ptime , DATEADD(MINUTE,30,gh1.Ptime),gh1.Ptime
      FROM @gh gh1 Left Outer Join @gh gh2 on (gh1.FT=gh2.FT AND gh1.JN<>gh2.JN AND gh2.Ptime >= DATEADD(MINUTE,-30,gh1.Ptime) AND gh2.Ptime <= gh1.Ptime)
          Left Outer Join @gh gh3 on (gh1.FT=gh3.FT AND gh1.JN<>gh3.JN AND gh3.Ptime <= DATEADD(MINUTE,30,gh1.Ptime) AND gh3.Ptime >= gh1.Ptime)