Vfrdtyky

I have such a query:

select distinct Project_Id,keyword,SE_Id from Table1 ;

It returns me almost 14.000 rows.

I have another SQL-query which looks like the following:

with DateWithValue as (



SELECT * 

  FROM DateTable as dt LEFT JOIN Table1 as PK on dt.Date_ID=PK.Load_Date and PK.Project_Id=? and Pk.keyword=? and SE_Id=?

LEFT JOIN Table2 as pr on PK.Project_ID=pr.Project_ID

where PK.Domain is not null and 

dt.Date_ID>= (SELECT min(Load_Date) from Table1 where Project_Id=? and keyword=? and SE_Id=? ) and

dt.Date_ID<= (SELECT max(Load_Date) from Table1 where Project_Id=? and keyword=? and SE_Id=? )

--order by Date_ID

)

select * from DateTable as dt Left join DateWithValue on dt.Date_ID=DateWithValue.Date_ID 

where DateWithValue.Date_ID is not null

order by dt.Date_ID

In this query ? should be replaced wthe ith result of the first query.

How can I combine these two queries? do you have any Idea for me

UPDATE: I have changed my query as following (to analyse better, I hold an example):

    SELECT 

    dt.Date_ID

    ,Pk.keyword

--  ,pr.Company_BK

--  ,pr.Project_URL

    ,t2.pos_position

    ,pk.Project_Id

    ,PK.SE_Id

FROM 

    DimDate as dt 

    JOIN 

    (

    SELECT

    t1.Project_Id,

    t1.keyword,

    t1.SE_Id,

    Min_Load_Date =  MIN(t1.Load_Date),

    Max_Load_Date = MAX(t1.Load_Date)

FROM

    [RL].[SearchMetrics_ProjectKeyword] t1

    where keyword='aktueller gaspreis pro kwh' and Project_Id=383601 and SE_ID=1  

GROUP BY

    t1.Project_Id,

    t1.keyword,

    t1.SE_Id

    )

     as PK 

        ON dt.Date_ID >=  PK.Min_Load_Date

        AND dt.Date_ID <  PK.Max_Load_Date

        --AND PK.Project_Id=? 

        --AND Pk.keyword=? 

        --AND SE_Id=?

        LEFT JOIN [RL].[SearchMetrics_ProjectKeyword] t2

          on Pk.Project_Id=t2.Project_Id and PK.keyword=t2.keyword and PK.SE_Id=t2.SE_Id and t2.Load_Date >=  dt.Date_ID and t2.Load_Date <=  dt.Date_ID

--    LEFT JOIN MDM.SearchMetrics_Project as pr 

 --       ON PK.Project_ID=pr.Project_ID

with this query I get such a result:

The structure is exactly what I was looking for, but the new challenge is the column pos_positin, which has sometimes NULL values. But it should not be like this.
This column between 20180406 and 20180412 should be 19 and between 20180413 and 20180423 be 21
How can I achieve that with this query?

UPDATE 2: I got it here is the solution:

with cte as

(

    SELECT 

    dt.Date_ID

    ,Pk.keyword

--  ,pr.Company_BK

--  ,pr.Project_URL

    ,t2.pos_position

    ,pk.Project_Id

    ,PK.SE_Id

FROM 

    DimDate as dt 

    JOIN 

    (

    SELECT

    t1.Project_Id,

    t1.keyword,

    t1.SE_Id,

    Min_Load_Date =  MIN(t1.Load_Date),

    Max_Load_Date = MAX(t1.Load_Date)

FROM

    [RL].[SearchMetrics_ProjectKeyword] t1

    where keyword='citire gaze' and Project_Id=1458724 and SE_ID=79

GROUP BY

    t1.Project_Id,

    t1.keyword,

    t1.SE_Id

    )

     as PK 

        ON dt.Date_ID >=  PK.Min_Load_Date

        AND dt.Date_ID <  PK.Max_Load_Date

        --AND PK.Project_Id=? 

        --AND Pk.keyword=? 

        --AND SE_Id=?

        LEFT JOIN [RL].[SearchMetrics_ProjectKeyword] t2

          on Pk.Project_Id=t2.Project_Id and PK.keyword=t2.keyword and PK.SE_Id=t2.SE_Id and t2.Load_Date >=  dt.Date_ID and t2.Load_Date <=  dt.Date_ID

--    LEFT JOIN MDM.SearchMetrics_Project as pr 

 --       ON PK.Project_ID=pr.Project_ID

    )

    select a.Date_ID

    ,a.keyword

    ,a.Project_Id

    ,a.SE_Id

    ,ISNULL(a.pos_position,x.pos_position) pos_position

     from cte a outer apply

(select top 1 pos_position 

    from cte b 

    where b.Date_ID<a.Date_ID and 

        b.Project_Id is not null and 

        b.pos_position is not null and 

        a.pos_position is null order by Date_ID desc)x

This should get you what you want and skip a ton of performance pain...

Change you're 1st query to this...

-- #first_query...

SELECT

    t1.Project_Id,

    t1.keyword,

    t1.SE_Id,

    Min_Load_Date = CONVERT(DATE, MIN(t1.Load_Date)),

    Max_Load_Date = CONVERT(DATE(MAX(t1.Load_Date))

FROM

    dbo.Table1 t1

GROUP BY

    t1.Project_Id,

    t1.keyword,

    t1.SE_Id;

Then you're second query would just look like this...

SELECT 

    * 

FROM 

    dbo.DateTable as dt 

    JOIN #First_Query as PK 

        ON dt.Date_ID >=  PK.Min_Load_Date

        AND dt.Date_ID < DATEADD(DAY, 1, Max_Load_Date)

        --AND PK.Project_Id=? 

        --AND Pk.keyword=? 

        --AND SE_Id=?

    LEFT JOIN Table2 as pr 

        ON PK.Project_ID=pr.Project_ID;

--where 

--  PK.Domain is not null 

--  AND  dt.Date_ID>= (SELECT min(Load_Date) from Table1 where Project_Id=? and keyword=? and SE_Id=? ) 

--  AND dt.Date_ID<= (SELECT max(Load_Date) from Table1 where Project_Id=? and keyword=? and SE_Id=? )

--order by Date_ID