SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Correlated Subquery Help		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Darth Emphatic
Starting Member

USA
1 Posts
Posted - 11/16/2012 :  15:34:58  Show Profile  Reply with Quote
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

India
47950 Posts
Posted - 11/16/2012 :  22:16:19  Show Profile  Reply with Quote
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/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000