Correlated Subquery Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Correlated Subquery Help

Author

Topic

Darth Emphatic
Starting Member

1 Post

Posted - 2012-11-16 : 15:34:58

Everyone,

New here and needed some help. I am definitely a novice SQL user. I am attempting to use a corelated subquery to return the min date for a specific column with the results of my outer query. So, I am using the subquery in my select line.

I also have 3 or 4 joins in place because the data I need is in multiple tables. Where things get tricky is the outer query returns data at one granularity while the sub-query needs to pull the min date from another granularity. I am trying to put this in general terms, because, for various reasons, I cannot expose my query.

Suffice it say, my outer query is selecting records where one date falls before another date.

ie. Take this simplified table

Name Group# Account# Start Date Sent Date
Company1 123 111 5/1/2012 5/1/2012
Company2 123 222 5/1/2012 1/1/2012
Company3 123 333 5/1/2012 2/1/2012

My outer query is essentially looking for any instances where the Sent date is prior to the start date. For the table above, the query would return Account 222 and 333. In my select corelated subquery I need to select the min sent date from all accounts in the matching group#. In the table above, it should return 1/1/2012 as that is the min sent date.

I would expect, once I add the corelated subquery, my results based on the table above would return as follows:

Name Group# Account# StartDate SentDate MinSentDate
Company2 123 222 5/1/2012 1/1/2012 1/1/2012
Company3 123 333 5/1/2012 2/1/2012 1/1/2012

So to simply, my outer query could be as simple as

Select Name, Account#, StartDate, SentDate
from TABLE1
where SentDate < StartDate

Now my outer query is much more complex, but I hope that that all makes sense. I am sorry I cannot provide the outer query I am working with, but can anyone, based on the info above, help me write the subquery that would return the min sent date for each row?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-16 : 22:16:19

as per your given query, its as simple as


Select Name, Account#, StartDate, SentDate,
MIN(SentDate) OVER (PARTITION BY Group#) AS MinSentDate
from TABLE1
where SentDate < StartDate

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources