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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 min and max date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

arkiboys
Flowing Fount of Yak Knowledge

1418 Posts

Posted - 03/25/2010 :  13:50:39  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 03/25/2010 :  13:56:50  Show Profile  Reply with Quote
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/

Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8766 Posts

Posted - 03/25/2010 :  13:57:18  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

arkiboys
Flowing Fount of Yak Knowledge

1418 Posts

Posted - 03/25/2010 :  14:09:53  Show Profile  Reply with Quote
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
Go to Top of Page

arkiboys
Flowing Fount of Yak Knowledge

1418 Posts

Posted - 03/25/2010 :  14:15:59  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/25/2010 :  14:18:07  Show Profile  Reply with Quote
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/

Go to Top of Page

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 03/25/2010 :  14:21:51  Show Profile  Reply with Quote
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.
Go to Top of Page

arkiboys
Flowing Fount of Yak Knowledge

1418 Posts

Posted - 03/26/2010 :  05:19:20  Show Profile  Reply with Quote
Thank you all.
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