Optimize my SQL code without temporary table

Question

I want to optimize my code without using temporary table, is it possible? I try to use JOINS, but I dont know how to use JOIN in the same table with my conditions:

select 'Quest1' q1, 
        date1 as date_q1,
        date1 as date_q2
into #Temp
from table1
where id in (select min(id) 
                from table1 
                where date1 = '2019-01-01'
                group by sy_id, date1) 
and sy_id is not null;

update #Temp
set date_q2 =  table1.date1
from table1 
where table1.cal_id = 7 
and #Temporal.sy_id = table1.sy_id
select q1, DATEDIFF(d, date_q1, date_q2) as av 
from #Temp
union all
select 'Quest2' q1, DATEDIFF(d, date_ref, date1) as av
from table1 
where id in (select min(id) 
                from table1 
                where date1 = '2019-01-01' 
                group by sy_id, date1)

Edit, solved.

Select q1, avg(Diferencia) as av from (select 'Quest1' q1, datediff(d, date1, (select top 1 d.date1 from table1 d where d.cal_id = 7 and d.sy_id = sy_id)) av from table1 where id in (select min(id) from table1 where date1 >= '2019-01-01' group by sy_id, date1) and sy_id is not null union all select 'Quest2' q1, datediff(d, date_ref, date1) av from table1 where id in (select min(id) from table1 where date1 >= '2019-01-01' group by sy_id) group by q1

Your code already *uses* a temporary table, so your question is unclear. Are you trying to not use it? If the latter, please provide sample data, desired results, and an explanation of what the code is supposed to be doing. In addition, you code mentions tables that are no defined. — Gordon Linoff, Mar 11 '19 at 14:12
I'm trying to make a report, so the software does not work well with temporary tables, my code gives the average time of the "Quests", according to entry and release of the "Quests" — David A.C, Mar 11 '19 at 14:24

score 0 · Accepted Answer · answered Mar 11 '19 at 14:24

You should check CTEs : https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-2017

More specifically: Select first row in each GROUP BY group?

Use this query to select top row by group, then you can join.

Alternatively you can use a subquery:

SELECT
    date_diff(
        some_date,
#subquery - min date or whatever
        SELECT MIN (...) FROM "x" AS "x2" WHERE "x2"."id"="x1"."id" 
    )
FROM "x" AS "x1"

Optimize my SQL code without temporary table

1 Answers1