April 18, 2014 at 12:42 pm
Can't seem to get the syntax correct. Maybe there is a better way to do this.
I'm trying to put an Outer Apply within an Outer Apply.
Here is the query:
SELECT
emp.eid, emp.agentid,
info.emailid,
mail.EmailAddr, mail.LName, mail.FName
FROM employees AS emp
outer apply
(select emailid
from
(select emailid
,ROW_NUMBER() OVER(PARTITION BY agentid ORDER BY endDate DESC, modDt Desc) AS RowNum
from employeeinfo
where (stDate <= dateadd(dd, datediff(dd, 0, getdate()), 0)
and
endDate > dateadd(dd, datediff(dd, 0, getdate()), 0)
)
) AS z
whereRowNum = 1) as info
Where emp.agent = info.agent
And here is the 2nd Outer Apply I want to put into the 1st Outer Apply:
outer apply
(select top 1 EmailAddr, LName, FName
from employeemail x
where CAST(info.emailID + '@company1.com' as varchar(255)) = x.mail
) As mail
Once I find the info.emailid in the 1st Outer Apply, I want to use that in the 2nd Outer Apply. But I keep getting syntax errors.
Any ideas as how to code this?
Thanks,
Sqlraider
April 18, 2014 at 2:17 pm
DDL, sample data and desired output would go a long way to getting you an answer. It is not at all clear what you mean by putting an outer apply inside another outer apply.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 18, 2014 at 2:44 pm
Sean Lange (4/18/2014)
DDL, sample data and desired output would go a long way to getting you an answer. It is not at all clear what you mean by putting an outer apply inside another outer apply.
Maybe I am going about this wrong.
The 1st query gives me what I want and I'm trying to add the next piece in.
Here is the result of the 1st query:
EID AgentID info.emailID
sql123 59231 Sqlraider.Syntax
Now I need to take Sqlraider.Syntax and add '@company1.com' to it for: 'Sqlraider.Syntax@company1.com'. Using this I need to read another table were I could have none, one, or many in the results but I only need the Top 1. Pulling the information from that row.
I don't know what info.emailID is until the 1st Outer Apply executes so I don't what I need to achieve my desired results. I do know that if I put the 1st query in a temp table and then run the 2nd Outer Apply against it I'd get the desired results but I want to achieve this in one query not two if possible.
April 18, 2014 at 3:15 pm
To paraphrase what Sean has already said, please provide CREATE TABLE scripts for all of the relevant tables, INSERT Statements to populate those tables with test data, and and example of what the desired output will look like.
This will make it much easier for us to understand your situation and the result you're trying to achieve.
April 18, 2014 at 3:28 pm
Try this:
SELECT
emp.eid, emp.agentid,
info.emailid,
mail.EmailAddr, mail.LName, mail.FName
FROM employees AS emp
outer apply
(select top (1) emailid
from employeeinfo ei
where
ei.agentid = emp.agentid and
(stDate <= dateadd(dd, datediff(dd, 0, getdate()), 0)
and
endDate > dateadd(dd, datediff(dd, 0, getdate()), 0)
)
order by endDate DESC, modDt Desc
) AS info
outer apply
(select top 1 EmailAddr, LName, FName
from employeemail x
where CAST(info.emailID + '@company1.com' as varchar(255)) = x.mail
) As mail
Where emp.agent = info.agent
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 21, 2014 at 9:27 am
ScottPletcher (4/18/2014)
Try this:
SELECT
emp.eid, emp.agentid,
info.emailid,
mail.EmailAddr, mail.LName, mail.FName
FROM employees AS emp
outer apply
(select top (1) emailid
from employeeinfo ei
where
ei.agentid = emp.agentid and
(stDate <= dateadd(dd, datediff(dd, 0, getdate()), 0)
and
endDate > dateadd(dd, datediff(dd, 0, getdate()), 0)
)
order by endDate DESC, modDt Desc
) AS info
outer apply
(select top 1 EmailAddr, LName, FName
from employeemail x
where CAST(info.emailID + '@company1.com' as varchar(255)) = x.mail
) As mail
Where emp.agent = info.agent
Sorry it to so long to reply...been putting out fires over the weekend.
This is what I was looking for and gets me the desired results.
Thanks,
Sqlraider
April 21, 2014 at 9:50 am
Sqlraider (4/21/2014)
ScottPletcher (4/18/2014)
Try this:
SELECT
emp.eid, emp.agentid,
info.emailid,
mail.EmailAddr, mail.LName, mail.FName
FROM employees AS emp
outer apply
(select top (1) emailid
from employeeinfo ei
where
ei.agentid = emp.agentid and
(stDate <= dateadd(dd, datediff(dd, 0, getdate()), 0)
and
endDate > dateadd(dd, datediff(dd, 0, getdate()), 0)
)
order by endDate DESC, modDt Desc
) AS info
outer apply
(select top 1 EmailAddr, LName, FName
from employeemail x
where CAST(info.emailID + '@company1.com' as varchar(255)) = x.mail
) As mail
Where emp.agent = info.agent
Sorry it to so long to reply...been putting out fires over the weekend.
This is what I was looking for and gets me the desired results.
Thanks,
Sqlraider
Great, glad it helped!
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply