Using result of a query in input parameters for a common table expression
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
0
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
sql
sql-server dynamic-sql asked Nov 16 '18 at 13:17
KajaKaja
1,122103763
add a comment |
0
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
sql
sql-server dynamic-sql asked Nov 16 '18 at 13:17
KajaKaja
1,122103763
add a comment |
0
0
0
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
sql
sql-server dynamic-sql asked Nov 16 '18 at 13:17
KajaKaja
1,122103763
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
sql
sql-server dynamic-sql sql
sql-server dynamic-sql asked Nov 16 '18 at 13:17
KajaKaja
1,122103763
asked Nov 16 '18 at 13:17
KajaKaja
1,122103763
edited Nov 18 '18 at 11:25
Kaja
asked Nov 16 '18 at 13:17
KajaKaja
1,122103763
asked Nov 16 '18 at 13:17
KajaKaja
1,122103763
asked Nov 16 '18 at 13:17
KajaKaja
1,122103763
1,122103763
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
1
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
answered Nov 16 '18 at 16:49
Jason A. LongJason A. Long
3,8101513
Thank you Jason, I have updated my post. Could you please take a look at this?
– Kaja
Nov 17 '18 at 18:55
1
Thnaks I got it! I have updated my post again. Thank you for your hint
– Kaja
Nov 18 '18 at 11:26
@Kaja - Great, glad to see you got it figured out. Thank you for updating to provide your final working solution.. Well done. :)
– Jason A. Long
Nov 18 '18 at 18:13
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53338689%2fusing-result-of-a-query-in-input-parameters-for-a-common-table-expression%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
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
answered Nov 16 '18 at 16:49
Jason A. LongJason A. Long
3,8101513
Thank you Jason, I have updated my post. Could you please take a look at this?
– Kaja
Nov 17 '18 at 18:55
1
Thnaks I got it! I have updated my post again. Thank you for your hint
– Kaja
Nov 18 '18 at 11:26
@Kaja - Great, glad to see you got it figured out. Thank you for updating to provide your final working solution.. Well done. :)
– Jason A. Long
Nov 18 '18 at 18:13
add a comment |
1
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
answered Nov 16 '18 at 16:49
Jason A. LongJason A. Long
3,8101513
Thank you Jason, I have updated my post. Could you please take a look at this?
– Kaja
Nov 17 '18 at 18:55
1
Thnaks I got it! I have updated my post again. Thank you for your hint
– Kaja
Nov 18 '18 at 11:26
@Kaja - Great, glad to see you got it figured out. Thank you for updating to provide your final working solution.. Well done. :)
– Jason A. Long
Nov 18 '18 at 18:13
add a comment |
1
1
1
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
answered Nov 16 '18 at 16:49
Jason A. LongJason A. Long
3,8101513
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
answered Nov 16 '18 at 16:49
Jason A. LongJason A. Long
3,8101513
answered Nov 16 '18 at 16:49
Jason A. LongJason A. Long
3,8101513
answered Nov 16 '18 at 16:49
Jason A. LongJason A. Long
3,8101513
answered Nov 16 '18 at 16:49
Jason A. LongJason A. Long
3,8101513
3,8101513
Thank you Jason, I have updated my post. Could you please take a look at this?
– Kaja
Nov 17 '18 at 18:55
1
Thnaks I got it! I have updated my post again. Thank you for your hint
– Kaja
Nov 18 '18 at 11:26
@Kaja - Great, glad to see you got it figured out. Thank you for updating to provide your final working solution.. Well done. :)
– Jason A. Long
Nov 18 '18 at 18:13
add a comment |
Thank you Jason, I have updated my post. Could you please take a look at this?
– Kaja
Nov 17 '18 at 18:55
1
Thnaks I got it! I have updated my post again. Thank you for your hint
– Kaja
Nov 18 '18 at 11:26
@Kaja - Great, glad to see you got it figured out. Thank you for updating to provide your final working solution.. Well done. :)
– Jason A. Long
Nov 18 '18 at 18:13
Thank you Jason, I have updated my post. Could you please take a look at this?
– Kaja
Nov 17 '18 at 18:55
Thank you Jason, I have updated my post. Could you please take a look at this?
– Kaja
Nov 17 '18 at 18:55
1
1
Thnaks I got it! I have updated my post again. Thank you for your hint
– Kaja
Nov 18 '18 at 11:26
Thnaks I got it! I have updated my post again. Thank you for your hint
– Kaja
Nov 18 '18 at 11:26
@Kaja - Great, glad to see you got it figured out. Thank you for updating to provide your final working solution.. Well done. :)
– Jason A. Long
Nov 18 '18 at 18:13
@Kaja - Great, glad to see you got it figured out. Thank you for updating to provide your final working solution.. Well done. :)
– Jason A. Long
Nov 18 '18 at 18:13
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53338689%2fusing-result-of-a-query-in-input-parameters-for-a-common-table-expression%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
