How To Simplify SQL Query For Get Faster Result

jacky.3311711027 · March 29, 2019, 3:49am

Hello I want to ask about how to simplify query in sql server,
my table look like this

data

DECLARE @startDate Date
SET @startDate = '2019-03-20'
declare @listOfDate table (
	area varchar(20),
	location varchar(50),
	ipaddress varchar(20),
	date datetime
)
declare @listOfData table(
	area varchar(20),
	location varchar(50),
	ipaddress varchar(20),
	T6 float, 	T5 float,	T4 float,	T3 float,
	T2 float,	T1 float,	T0 float,	H6 float, 
	H5 float,	H4 float,	H3 float,	H2 float,
	H1 float,	H0 float
)
--insert the tables that you want to work with.
INSERT INTO @listOfDate SELECT area, location, ip, MAX(timerecord) FROM INF_Facility_Temperature Where 
timerecord <= CONVERT(varchar(10), DATEADD(DAY, 6, @startDate), 120) 
AND
timerecord >= @startDate
GROUP by area, Location, ip, CAST(timerecord as date)

--Cursor for iterating
declare @temperature cursor,
		@timerecord datetime,
		@area varchar(20),
		@location varchar(50),
		@ip varchar(20)
        

set @temperature = cursor for select * from @listOfDate

open @temperature
fetch next from @temperature into @area,@location, @ip, @timerecord
while(@@fetch_status = 0)
begin
	IF EXISTS(SELECT area, location, ipaddress from @listOfData WHERE area=@area AND location=@location AND ipaddress = @ip)
	BEGIN
		UPDATE @listOfData SET
		T1 = CASE  
				WHEN T1 = '0' THEN (SELECT (CASE CONVERT(varchar(10),@timerecord, 120) WHEN CONVERT(varchar(10), DATEADD(DAY, 1, @startDate), 120) THEN temp ELSE 0 END) AS 'T-1' FROM INF_Facility_Temperature where CONVERT(Varchar(30), timerecord, 120) = CONVERT(Varchar(30), @timerecord, 120)
								AND
								area= @area
								AND
								Location = @location
								AND
								ip = @ip
								AND
								timerecord <= CONVERT(varchar(10), DATEADD(DAY, 7, @startDate), 120))
				ELSE T1 END
				,
		T2 = CASE  
				WHEN T2 = '0' THEN (SELECT (CASE CONVERT(varchar(10),@timerecord, 120) WHEN CONVERT(varchar(10), DATEADD(DAY, 2, @startDate), 120) THEN temp ELSE 0 END) AS 'T-2' FROM INF_Facility_Temperature where CONVERT(Varchar(30), timerecord, 120) = CONVERT(Varchar(30), @timerecord, 120)
								AND
								area= @area
								AND
								Location = @location
								AND
								ip = @ip
								AND
								timerecord <= CONVERT(varchar(10), DATEADD(DAY, 7, @startDate), 120))
				ELSE T2 END 
				,
		T3 = CASE  
				WHEN T3 = '0' THEN (SELECT (CASE CONVERT(varchar(10),@timerecord, 120) WHEN CONVERT(varchar(10), DATEADD(DAY, 3, @startDate), 120) THEN temp ELSE 0 END) AS 'T-2' FROM INF_Facility_Temperature where CONVERT(Varchar(30), timerecord, 120) = CONVERT(Varchar(30), @timerecord, 120)
								AND
								area= @area
								AND
								Location = @location
								AND
								ip = @ip
								AND
								timerecord <= CONVERT(varchar(10), DATEADD(DAY, 7, @startDate), 120))
				ELSE T3 END ,
		T4 = CASE  
				WHEN T4 = '0' THEN (SELECT (CASE CONVERT(varchar(10),@timerecord, 120) WHEN CONVERT(varchar(10), DATEADD(DAY, 4, @startDate), 120) THEN temp ELSE 0 END) AS 'T-2' FROM INF_Facility_Temperature where CONVERT(Varchar(30), timerecord, 120) = CONVERT(Varchar(30), @timerecord, 120)
								AND
								area= @area
								AND
								Location = @location
								AND
								ip = @ip
								AND
								timerecord <= CONVERT(varchar(10), DATEADD(DAY, 7, @startDate), 120))
				ELSE T4 END 
				,
		T5 = CASE  
				WHEN T5 = '0' THEN (SELECT (CASE CONVERT(varchar(10),@timerecord, 120) WHEN CONVERT(varchar(10), DATEADD(DAY, 5, @startDate), 120) THEN temp ELSE 0 END) AS 'T-2' FROM INF_Facility_Temperature where CONVERT(Varchar(30), timerecord, 120) = CONVERT(Varchar(30), @timerecord, 120)
								AND
								area= @area
								AND
								Location = @location
								AND
								ip = @ip
								AND
								timerecord <= CONVERT(varchar(10), DATEADD(DAY, 7, @startDate), 120))
				ELSE T5 END ,
		T6 = CASE  
				WHEN T6 = '0' THEN (SELECT (CASE CONVERT(varchar(10),@timerecord, 120) WHEN CONVERT(varchar(10), DATEADD(DAY, 6, @startDate), 120) THEN temp ELSE 0 END) AS 'T-2' FROM INF_Facility_Temperature where CONVERT(Varchar(30), timerecord, 120) = CONVERT(Varchar(30), @timerecord, 120)
								AND
								area= @area
								AND
								Location = @location
								AND
								ip = @ip
								AND
								timerecord <= CONVERT(varchar(10), DATEADD(DAY, 7, @startDate), 120))
				ELSE T6 END ,
		T0 = CASE  
				WHEN T0 = '0' THEN (SELECT (CASE CONVERT(varchar(10),@timerecord, 120) WHEN CONVERT(varchar(10), DATEADD(DAY, 0, @startDate), 120) THEN temp ELSE 0 END) AS 'T-2' FROM INF_Facility_Temperature where CONVERT(Varchar(30), timerecord, 120) = CONVERT(Varchar(30), @timerecord, 120)
								AND
								area= @area
								AND
								Location = @location
								AND
								ip = @ip
								AND
								timerecord <= CONVERT(varchar(10), DATEADD(DAY, 7, @startDate), 120))
				ELSE T0 END,
		H1 = CASE  
				WHEN H1 = '0' THEN (SELECT (CASE CONVERT(varchar(10),@timerecord, 120) WHEN CONVERT(varchar(10), DATEADD(DAY, 1, @startDate), 120) THEN humi ELSE 0 END) AS 'T-1' FROM INF_Facility_Temperature where CONVERT(Varchar(30), timerecord, 120) = CONVERT(Varchar(30), @timerecord, 120)
								AND
								area= @area
								AND
								Location = @location
								AND
								ip = @ip
								AND
								timerecord <= CONVERT(varchar(10), DATEADD(DAY, 7, @startDate), 120))
				ELSE H1 END
				,
		H2 = CASE  
				WHEN H2 = '0' THEN (SELECT (CASE CONVERT(varchar(10),@timerecord, 120) WHEN CONVERT(varchar(10), DATEADD(DAY, 2, @startDate), 120) THEN humi ELSE 0 END) AS 'T-2' FROM INF_Facility_Temperature where CONVERT(Varchar(30), timerecord, 120) = CONVERT(Varchar(30), @timerecord, 120)
								AND
								area= @area
								AND
								Location = @location
								AND
								ip = @ip
								AND
								timerecord <= CONVERT(varchar(10), DATEADD(DAY, 7, @startDate), 120))
				ELSE H2 END 
				,
		H3 = CASE  
				WHEN H3 = '0' THEN (SELECT (CASE CONVERT(varchar(10),@timerecord, 120) WHEN CONVERT(varchar(10), DATEADD(DAY, 3, @startDate), 120) THEN humi ELSE 0 END) AS 'T-2' FROM INF_Facility_Temperature where CONVERT(Varchar(30), timerecord, 120) = CONVERT(Varchar(30), @timerecord, 120)
								AND
								area= @area
								AND
								Location = @location
								AND
								ip = @ip
								AND
								timerecord <= CONVERT(varchar(10), DATEADD(DAY, 7, @startDate), 120))
				ELSE H3 END ,
		H4 = CASE  
				WHEN H4 = '0' THEN (SELECT (CASE CONVERT(varchar(10),@timerecord, 120) WHEN CONVERT(varchar(10), DATEADD(DAY, 4, @startDate), 120) THEN humi ELSE 0 END) AS 'T-2' FROM INF_Facility_Temperature where CONVERT(Varchar(30), timerecord, 120) = CONVERT(Varchar(30), @timerecord, 120)
								AND
								area= @area
								AND
								Location = @location
								AND
								ip = @ip
								AND
								timerecord <= CONVERT(varchar(10), DATEADD(DAY, 7, @startDate), 120))
				ELSE H4 END 
				,
		H5 = CASE  
				WHEN H5 = '0' THEN (SELECT (CASE CONVERT(varchar(10),@timerecord, 120) WHEN CONVERT(varchar(10), DATEADD(DAY, 5, @startDate), 120) THEN humi ELSE 0 END) AS 'T-2' FROM INF_Facility_Temperature where CONVERT(Varchar(30), timerecord, 120) = CONVERT(Varchar(30), @timerecord, 120)
								AND
								area= @area
								AND
								Location = @location
								AND
								ip = @ip
								AND
								timerecord <= CONVERT(varchar(10), DATEADD(DAY, 7, @startDate), 120))
				ELSE H5 END ,
		H6 = CASE  
				WHEN H6 = '0' THEN (SELECT (CASE CONVERT(varchar(10),@timerecord, 120) WHEN CONVERT(varchar(10), DATEADD(DAY, 6, @startDate), 120) THEN humi ELSE 0 END) AS 'T-2' FROM INF_Facility_Temperature where CONVERT(Varchar(30), timerecord, 120) = CONVERT(Varchar(30), @timerecord, 120)
								AND
								area= @area
								AND
								Location = @location
								AND
								ip = @ip
								AND
								timerecord <= CONVERT(varchar(10), DATEADD(DAY, 7, @startDate), 120))
				ELSE T6 END ,
		H0 = CASE  
				WHEN H0 = '0' THEN (SELECT (CASE CONVERT(varchar(10),@timerecord, 120) WHEN CONVERT(varchar(10), DATEADD(DAY, 0, @startDate), 120) THEN humi ELSE 0 END) AS 'T-2' FROM INF_Facility_Temperature where CONVERT(Varchar(30), timerecord, 120) = CONVERT(Varchar(30), @timerecord, 120)
								AND
								area= @area
								AND
								Location = @location
								AND
								ip = @ip
								AND
								timerecord <= CONVERT(varchar(10), DATEADD(DAY, 7, @startDate), 120))
				ELSE H0 END

		WHERE area = @area AND location = @location AND ipaddress= @ip
	END
	ELSE
	BEGIN
		INSERT INTO @listOfData
		SELECT area, location, ip, 
		(CASE CONVERT(varchar(10),@timerecord, 120) WHEN CONVERT(varchar(10), DATEADD(DAY, 6, @startDate), 120) THEN temp ELSE 0 END) AS 'T-6',
		(CASE CONVERT(varchar(10),@timerecord, 120) WHEN CONVERT(varchar(10), DATEADD(DAY, 5, @startDate), 120) THEN temp ELSE 0 END) AS 'T-5',
		(CASE CONVERT(varchar(10),@timerecord, 120) WHEN CONVERT(varchar(10), DATEADD(DAY, 4, @startDate), 120) THEN temp ELSE 0 END) AS 'T-4',
		(CASE CONVERT(varchar(10),@timerecord, 120) WHEN CONVERT(varchar(10), DATEADD(DAY, 3, @startDate), 120) THEN temp ELSE 0 END) AS 'T-3',
		(CASE CONVERT(varchar(10),@timerecord, 120) WHEN CONVERT(varchar(10), DATEADD(DAY, 2, @startDate), 120) THEN temp ELSE 0 END) AS 'T-2',
		(CASE CONVERT(varchar(10),@timerecord, 120) WHEN CONVERT(varchar(10), DATEADD(DAY, 1, @startDate), 120) THEN temp ELSE 0 END) AS 'T-1',
		(CASE CONVERT(varchar(10),@timerecord, 120) WHEN CONVERT(varchar(10), DATEADD(DAY, 0, @startDate), 120) THEN temp ELSE 0 END) AS 'T-0',
		(CASE CONVERT(varchar(10),@timerecord, 120) WHEN CONVERT(varchar(10), DATEADD(DAY, 6, @startDate), 120) THEN humi ELSE 0 END) AS 'H-6',
		(CASE CONVERT(varchar(10),@timerecord, 120) WHEN CONVERT(varchar(10), DATEADD(DAY, 5, @startDate), 120) THEN humi ELSE 0 END) AS 'H-5',
		(CASE CONVERT(varchar(10),@timerecord, 120) WHEN CONVERT(varchar(10), DATEADD(DAY, 4, @startDate), 120) THEN humi ELSE 0 END) AS 'H-4',
		(CASE CONVERT(varchar(10),@timerecord, 120) WHEN CONVERT(varchar(10), DATEADD(DAY, 3, @startDate), 120) THEN humi ELSE 0 END) AS 'H-3',
		(CASE CONVERT(varchar(10),@timerecord, 120) WHEN CONVERT(varchar(10), DATEADD(DAY, 2, @startDate), 120) THEN humi ELSE 0 END) AS 'H-2',
		(CASE CONVERT(varchar(10),@timerecord, 120) WHEN CONVERT(varchar(10), DATEADD(DAY, 1, @startDate), 120) THEN humi ELSE 0 END) AS 'H-1',
		(CASE CONVERT(varchar(10),@timerecord, 120) WHEN CONVERT(varchar(10), DATEADD(DAY, 0, @startDate), 120) THEN humi ELSE 0 END) AS 'H-0'
		 from INF_Facility_Temperature where CONVERT(Varchar(30), timerecord, 120) = CONVERT(Varchar(30), @timerecord, 120)
		AND
		area= @area
		AND
		Location = @location
		AND
		ip = @ip
		AND
		timerecord <= CONVERT(varchar(10), DATEADD(DAY, 7, @startDate), 120)
	END

    fetch next from @temperature into @area, @location, @ip, @timerecord
END
SELECT * FROM @listOfData order by area, location

close @temperature
deallocate @temperature

the result should be like this:

actually its work perfecly, but its need a long time to execute. is there any way to get faster result?
in my current condition, i need more than 40seconds to finish execute this query

If anybody have a way, Please Tell me. Thank you

Lewie · March 29, 2019, 5:14am

Try limiting your data type conversion to varchar
CONVERT(VARCHAR(30), timerecord, 120) = CONVERT(VARCHAR(30), @timerecord, 120)
should be
timerecord = @timerecord etc

jacky.3311711027 · March 29, 2019, 5:48am

hello @Lewie, i have tried your suggestion. but the result of the query return an empty record.
as showed below,

harishgg1 · March 29, 2019, 2:16pm

hi

i created the drop create data script
somebody please carry the torch

drop create data ..

drop table #abc 
go 

create table #abc 
(
area varchar(3) ,
location varchar(10) ,
ip varchar(100),
humi varchar(10),
temp varchar(10),
door varchar(10),
N2 int ,
timerecord datetime 
)
go 

insert into #abc select 'T4','T4-Wave','10.128.174.38','51.3','25.6','Close',0,'2019-03-13 08:26:35:000'
insert into #abc select 'T4','T4-Wave','10.128.174.38','48.9','25','Close',0,'2019-03-13 08:34:35:000'
insert into #abc select 'T4','T4-Wave','10.128.174.38','51.3','25.6','Close',0,'2019-03-13 08:38:35.000'
insert into #abc select 'T4','T4-Wave','10.128.174.38','52','25.6','Close',0,'2019-03-13 08:39:35.000'
insert into #abc select 'T4','T4-Wave','10.128.174.38','51.2','25.6','Close',0,'2019-03-13 08:45:35.000'
insert into #abc select 'T4','T4-Wave','10.128.174.38','49.7','25.6','Close',0,'2019-03-13 08:29:35.000'

insert into #abc select 'T1','T1-Wave','10.208.174.47',58.6,22.2,'Close',0,'2019-03-13 08:43:34.000' 
insert into #abc select 'T1','T1-Wave','10.208.174.47',57.5,22.2,'Close',0,'2019-03-13 08:29:34.000'  
insert into #abc select 'T1','T1-Wave','10.208.174.47',53.8,21.5,'Close',0,'2019-03-13 08:39:34.000'  
insert into #abc select 'T1','T1-Wave','10.208.174.47',54.5,22.2,'Close',0,'2019-03-13 08:42:34.000'  
insert into #abc select 'T1','T1-Wave','10.208.174.47',56.1,22.2,'Close',0,'2019-03-13 08:26:34.000'  
insert into #abc select 'T1','T1-Wave','10.208.174.47',54.5,22.2,'Close',0,'2019-03-13 08:28:34.000'  
insert into #abc select 'T1','T1-Wave','10.208.174.47',55.4,21.7,'Close',0,'2019-03-13 08:36:34.000'  
insert into #abc select 'T1','T1-Wave','10.208.174.47',52.4,22.2,'Close',0,'2019-03-13 08:46:34.000'  
go 


select * from #abc 
go

bitsmed · March 29, 2019, 7:52pm

Try this:

declare @startdate datetime=cast('2019-03-20' as datetime);

with cte
  as (select area
            ,location
            ,ip
            ,humi
            ,temp
            ,timerecord
            ,datediff(day,@startdate,timerecord) as dayrecord
        from inf_facility_temperature
       where timerecord>=@startdate
         and timerecord<dateadd(day,7,@startdate)
     )
    ,cte_areas
  as (select area
            ,location
            ,ip
            ,dayrecord
        from cte
       group by area
               ,location
               ,ip
               ,dayrecord
     )
    ,cte_humi
  as (select area
            ,location
            ,ip
            ,humi
            ,dayrecord
        from (select area
                    ,location
                    ,ip
                    ,humi
                    ,dayrecord
                    ,row_number() over(partition by area
                                                   ,location
                                                   ,ip
                                                   ,dayrecord
                                           order by timerecord
                                      )
                     as rn
                from cte
               where humi<>0
             ) as a
       where rn=1
     )
    ,cte_temp
  as (select area
            ,location
            ,ip
            ,temp
            ,dayrecord
        from (select area
                    ,location
                    ,ip
                    ,temp
                    ,dayrecord
                    ,row_number() over(partition by area
                                                   ,location
                                                   ,ip
                                                   ,dayrecord
                                           order by timerecord
                                      )
                     as rn
                from cte
               where temp<>0
             ) as a
       where rn=1
     )
select a.area
      ,a.location
      ,a.ip
      ,case
          when isnull(b.dayrecord,c.dayrecord)=0
          then c.temp
          else 0
       end as T6
      ,case
          when isnull(b.dayrecord,c.dayrecord)=1
          then c.temp
          else 0
       end as T5
      ,case
          when isnull(b.dayrecord,c.dayrecord)=2
          then c.temp
          else 0
       end as T4
      ,case
          when isnull(b.dayrecord,c.dayrecord)=3
          then c.temp
          else 0
       end as T3
      ,case
          when isnull(b.dayrecord,c.dayrecord)=4
          then c.temp
          else 0
       end as T2
      ,case
          when isnull(b.dayrecord,c.dayrecord)=5
          then c.temp
          else 0
       end as T1
      ,case
          when isnull(b.dayrecord,c.dayrecord)=6
          then c.temp
          else 0
       end as T0
      ,case
          when isnull(b.dayrecord,c.dayrecord)=0
          then b.humi
          else 0
       end as H6
      ,case
          when isnull(b.dayrecord,c.dayrecord)=1
          then b.humi
          else 0
       end as H5
      ,case
          when isnull(b.dayrecord,c.dayrecord)=2
          then b.humi
          else 0
       end as H4
      ,case
          when isnull(b.dayrecord,c.dayrecord)=3
          then b.humi
          else 0
       end as H3
      ,case
          when isnull(b.dayrecord,c.dayrecord)=4
          then b.humi
          else 0
       end as H2
      ,case
          when isnull(b.dayrecord,c.dayrecord)=5
          then b.humi
          else 0
       end as H1
      ,case
          when isnull(b.dayrecord,c.dayrecord)=6
          then b.humi
          else 0
       end as H0
  from cte_areas as a
       left outer join cte_humi as b
                    on b.area=a.area
                   and b.location=a.location
                   and b.ip=a.ip
                   and b.dayrecord=a.dayrecord
       left outer join cte_temp as c
                    on c.area=a.area
                   and c.location=a.location
                   and c.ip=a.ip
                   and c.dayrecord=b.dayrecord
;

jacky.3311711027 · April 1, 2019, 8:12am

Hello @bitsmed,
i had try your query and actually it faster but the problem is it return value with same location multiple

but i need it only return one row per location with all information from T0-T6 abd H0-H6,
something like this :

Muj9 · April 1, 2019, 10:41am

declare @startdate datetime=cast('2019-03-20' as datetime);

with cte
  as (select area
            ,location
            ,ip
            ,humi
            ,temp
            ,timerecord
            ,datediff(day,@startdate,timerecord) as dayrecord
        from inf_facility_temperature
       where timerecord>=@startdate
         and timerecord<dateadd(day,7,@startdate)
     )
    ,cte_areas
  as (select area
            ,location
            ,ip
            ,dayrecord
        from cte
       group by area
               ,location
               ,ip
               ,dayrecord
     )
    ,cte_humi
  as (select area
            ,location
            ,ip
            ,humi
            ,dayrecord
        from (select area
                    ,location
                    ,ip
                    ,humi
                    ,dayrecord
                    ,row_number() over(partition by area
                                                   ,location
                                                   ,ip
                                                   ,dayrecord
                                           order by timerecord
                                      )
                     as rn
                from cte
               where humi<>0
             ) as a
       where rn=1
     )
    ,cte_temp
  as (select area
            ,location
            ,ip
            ,temp
            ,dayrecord
        from (select area
                    ,location
                    ,ip
                    ,temp
                    ,dayrecord
                    ,row_number() over(partition by area
                                                   ,location
                                                   ,ip
                                                   ,dayrecord
                                           order by timerecord
                                      )
                     as rn
                from cte
               where temp<>0
             ) as a
       where rn=1
     ), cte5 as( 
select 
a.area
      ,a.location
      ,a.ip
      ,case
          when isnull(b.dayrecord,c.dayrecord)=0
          then c.temp
          else 0
       end as T6
      ,case
          when isnull(b.dayrecord,c.dayrecord)=1
          then c.temp
          else 0
       end as T5
      ,case
          when isnull(b.dayrecord,c.dayrecord)=2
          then c.temp
          else 0
       end as T4
      ,case
          when isnull(b.dayrecord,c.dayrecord)=3
          then c.temp
          else 0
       end as T3
      ,case
          when isnull(b.dayrecord,c.dayrecord)=4
          then c.temp
          else 0
       end as T2
      ,case
          when isnull(b.dayrecord,c.dayrecord)=5
          then c.temp
          else 0
       end as T1
      ,case
          when isnull(b.dayrecord,c.dayrecord)=6
          then c.temp
          else 0
       end as T0
      ,case
          when isnull(b.dayrecord,c.dayrecord)=0
          then b.humi
          else 0
       end as H6
      ,case
          when isnull(b.dayrecord,c.dayrecord)=1
          then b.humi
          else 0
       end as H5
      ,case
          when isnull(b.dayrecord,c.dayrecord)=2
          then b.humi
          else 0
       end as H4
      ,case
          when isnull(b.dayrecord,c.dayrecord)=3
          then b.humi
          else 0
       end as H3
      ,case
          when isnull(b.dayrecord,c.dayrecord)=4
          then b.humi
          else 0
       end as H2
      ,case
          when isnull(b.dayrecord,c.dayrecord)=5
          then b.humi
          else 0
       end as H1
      ,case
          when isnull(b.dayrecord,c.dayrecord)=6
          then b.humi
          else 0
       end as H0
  from cte_areas as a
       left outer join cte_humi as b
                    on b.area=a.area
                   and b.location=a.location
                   and b.ip=a.ip
                   and b.dayrecord=a.dayrecord
       left outer join cte_temp as c
                    on c.area=a.area
                   and c.location=a.location
                   and c.ip=a.ip
                   and c.dayrecord=b.dayrecord
) 
select
 area
,location
,ip
,sum(T6) as T6
,sum(T5) as T5
,sum(T4) as T4
,sum(T3) as T3
,sum(T2) as T2
,sum(T1) as T1
,sum(T0) as T0
,sum(H6) as H6
,sum(H5) as H5
,sum(H4) as H4
,sum(H3) as H3
,sum(H2) as H2
,sum(H1) as H1
,sum(H0) as H0


 from cte5
 group by 
  area
,location
,ip;

bitsmed · April 1, 2019, 3:34pm

declare @startdate datetime=cast('2019-03-20' as datetime);

with cte
  as (select area
            ,location
            ,ip
            ,humi
            ,temp
            ,timerecord
            ,datediff(day,@startdate,timerecord) as dayrecord
        from inf_facility_temperature
       where timerecord>=@startdate
         and timerecord<dateadd(day,7,@startdate)
     )
    ,cte_areas
  as (select area
            ,location
            ,ip
            ,dayrecord
        from cte
       group by area
               ,location
               ,ip
               ,dayrecord
     )
    ,cte_humi
  as (select area
            ,location
            ,ip
            ,humi
            ,dayrecord
        from (select area
                    ,location
                    ,ip
                    ,humi
                    ,dayrecord
                    ,row_number() over(partition by area
                                                   ,location
                                                   ,ip
                                                   ,dayrecord
                                           order by timerecord
                                      )
                     as rn
                from cte
               where humi<>0
             ) as a
       where rn=1
     )
    ,cte_temp
  as (select area
            ,location
            ,ip
            ,temp
            ,dayrecord
        from (select area
                    ,location
                    ,ip
                    ,temp
                    ,dayrecord
                    ,row_number() over(partition by area
                                                   ,location
                                                   ,ip
                                                   ,dayrecord
                                           order by timerecord
                                      )
                     as rn
                from cte
               where temp<>0
             ) as a
       where rn=1
     )
select a.area
      ,a.location
      ,a.ip
      ,sum(case
              when isnull(b.dayrecord,c.dayrecord)=0
              then c.temp
              else 0
           end
          )
       as T6
      ,sum(case
              when isnull(b.dayrecord,c.dayrecord)=1
              then c.temp
              else 0
           end
          )
       as T5
      ,sum(case
              when isnull(b.dayrecord,c.dayrecord)=2
              then c.temp
              else 0
           end
          )
       as T4
      ,sum(case
              when isnull(b.dayrecord,c.dayrecord)=3
              then c.temp
              else 0
           end
          )
       as T3
      ,sum(case
              when isnull(b.dayrecord,c.dayrecord)=4
              then c.temp
              else 0
           end
          )
       as T2
      ,sum(case
              when isnull(b.dayrecord,c.dayrecord)=5
              then c.temp
              else 0
           end
          )
       as T1
      ,sum(case
              when isnull(b.dayrecord,c.dayrecord)=6
              then c.temp
              else 0
           end
          )
       as T0
      ,sum(case
              when isnull(b.dayrecord,c.dayrecord)=0
              then b.humi
              else 0
           end
          )
       as H6
      ,sum(case
              when isnull(b.dayrecord,c.dayrecord)=1
              then b.humi
              else 0
           end
          )
       as H5
      ,sum(case
              when isnull(b.dayrecord,c.dayrecord)=2
              then b.humi
              else 0
           end
          )
       as H4
      ,sum(case
              when isnull(b.dayrecord,c.dayrecord)=3
              then b.humi
              else 0
           end
          )
       as H3
      ,sum(case
              when isnull(b.dayrecord,c.dayrecord)=4
              then b.humi
              else 0
           end
          )
       as H2
      ,sum(case
              when isnull(b.dayrecord,c.dayrecord)=5
              then b.humi
              else 0
           end
          )
       as H1
      ,sum(case
              when isnull(b.dayrecord,c.dayrecord)=6
              then b.humi
              else 0
           end
          )
       as H0
  from cte_areas as a
       left outer join cte_humi as b
                    on b.area=a.area
                   and b.location=a.location
                   and b.ip=a.ip
                   and b.dayrecord=a.dayrecord
       left outer join cte_temp as c
                    on c.area=a.area
                   and c.location=a.location
                   and c.ip=a.ip
                   and c.dayrecord=b.dayrecord
 group by a.area
         ,a.location
         ,a.ip
;

jacky.3311711027 · April 2, 2019, 1:02am

Thank you so much @bitsmed, it's work perfectly and need 3 second to execute.
thanks for your help, really apreciate it