Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Outer Apply within an Outer Apply Expand / Collapse
Author
Message
Posted Friday, April 18, 2014 12:42 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 11, 2014 4:04 PM
Points: 1,296, Visits: 1,817
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
where RowNum = 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
Post #1563139
Posted Friday, April 18, 2014 2:17 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:04 PM
Points: 13,296, Visits: 12,147
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1563171
Posted Friday, April 18, 2014 2:44 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 11, 2014 4:04 PM
Points: 1,296, Visits: 1,817
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.
Post #1563177
Posted Friday, April 18, 2014 3:15 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:29 PM
Points: 110, Visits: 709
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.


-Doug



http://xkcd.com/327/
Post #1563182
Posted Friday, April 18, 2014 3:27 PM This worked for the OP Answer marked as solution
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:55 PM
Points: 2,033, Visits: 3,041
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1563186
Posted Monday, April 21, 2014 9:27 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 11, 2014 4:04 PM
Points: 1,296, Visits: 1,817
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
Post #1563525
Posted Monday, April 21, 2014 9:50 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:55 PM
Points: 2,033, Visits: 3,041
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1563534
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse