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.
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 tableName Group# Account# Start Date Sent DateCompany1 123 111 5/1/2012 5/1/2012Company2 123 222 5/1/2012 1/1/2012Company3 123 333 5/1/2012 2/1/2012My 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 MinSentDateCompany2 123 222 5/1/2012 1/1/2012 1/1/2012Company3 123 333 5/1/2012 2/1/2012 1/1/2012So to simply, my outer query could be as simple as Select Name, Account#, StartDate, SentDatefrom TABLE1where SentDate < StartDateNow 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 asSelect Name, Account#, StartDate, SentDate,MIN(SentDate) OVER (PARTITION BY Group#) AS MinSentDatefrom TABLE1where SentDate < StartDate ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|