min and max 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

SQL Server 2005 Forums

Transact-SQL (2005)

min and max date

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-03-25 : 13:50:39

In a table there is a field called ID, Date, Amount
There are several records in this table.
When retrieving I do a group by:

1-
select ID, Min(Date), sum(Amount) from tblMain
group by ID

2-
select ID, Max(Date), sum(Amount) from tblMain
group by ID

The above query works if there are different dates for the same ID.
Question:
If the dates for an ID is the same i.e. min date and max date is the same, then how do I make sure I do not run both query 1 and 2 ?
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-25 : 13:56:50

make them in same query


select ID, Min(Date),NULLIF(Max(Date),Min(Date)), sum(Amount) from tblMain
group by ID

so that you return NULL if they're same

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-25 : 13:57:18

select ID, Min(Date) as MinDate, Max(Date) as MaxDate, sum(Amount) as Amount from tblMain
group by ID

No, you're never too old to Yak'n'Roll if you're too young to die.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-03-25 : 14:09:53

quote:
Originally posted by visakh16

make them in same query

select ID, Min(Date),NULLIF(Max(Date),Min(Date)), sum(Amount) from tblMain
group by ID

so that you return NULL if they're same

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

Hi,
How about using UNION i.e.
select ID, Min(date), sum(amount) group by ID
UNION
select ID, Max(date), sum(amount) group by ID

The result does not seem to be duplicated.
What do you think?
Thanks

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-03-25 : 14:15:59

Note that I want to use either min or max if the dates are the same.
And do not want to have the min(date) and max(date) in one row.
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-25 : 14:18:07

union then if they're different you'll get them in different rows not as columns of same row

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

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-25 : 14:21:51

quote:
Originally posted by arkiboys
Hi,
How about using UNION i.e.
select ID, Min(date), sum(amount) group by ID
UNION
select ID, Max(date), sum(amount) group by ID

The result does not seem to be duplicated.
What do you think?
Thanks

How would you know, for each individual row, if a date is the min date, or the max date?

If you want 2 separate queries, one that returns the min date, and one that returns the max date, but only if it's different from the min date, then you can do this:

1-
select ID, Min(Date), sum(Amount) from tblMain
group by ID

2-
select ID, Max(Date), sum(Amount) from tblMain
group by ID
HAVING MAX(Date) != MIN(Date)

There are 10 types of people in the world, those that understand binary, and those that don't.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-03-26 : 05:19:20

Thank you all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources