Group by specific date - 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

Group by specific date

Author

Topic

Redser
Starting Member

9 Posts

Posted - 2012-06-19 : 06:00:46

I have two tables Application & Decision. Each Application can have many decisions. Each decision has a PK of Decision_ID, FK Application_ID and a DecisionDate

I’m trying to identify only those applications where the latest decision is prior to a specific date.

Example: Identify Applications were all decisions were made in 2011

If an application had 3 decisions, 2 in Nov/2011 and 1 in Feb/2012 I don’t want it returned.

If all 3 decisions were made in 2011 then I want it to returned the Application_ID

I know this code doesn't work but it's my starting point.

use Database
select Application_ID from tbl_Decision
where DecisionDate < '2012-01-01'
group by Application_ID.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-19 : 06:27:56

select Application_ID
from tbl_Decision
group by Application_ID
having max(DecisionDate) < '2012-01-01'

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Redser
Starting Member

9 Posts

Posted - 2012-06-19 : 08:17:46

Thanks for you help nigelrivett. Much appreciated

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources