Selecting ultimo dates of a month

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 Administration

Selecting ultimo dates of a month

Author

Topic

MartinFalch
Starting Member

5 Posts

Posted - 2009-11-13 : 07:16:02

Hi there,

I have a lot of daily series, with observations on each day. Now I wish to create new tables, containing the same data, but only the last observation of each month. Is there some way to select this ultimo date within SQL, so as to select the 31st when this is the last date, the 30st when this is the last date etc?

My code is quite simple, looks like this:

-------------------

Select *
into ER_CAD_M
from ER_CAD
where ObsDateCAD = ???

-------------------

If someone can help me out on this, I would be really grateful!

Best regards,

Martin Falch

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-13 : 07:40:25

Try this:

where ObsDateCAD = (select max(ObsDateCAD) from CAD group by Year(ObsDateCAD),Month(ObsDateCAD))

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-13 : 07:46:24

quote:
Originally posted by webfred

Try this:

where ObsDateCAD = (select max(ObsDateCAD) from CAD group by Year(ObsDateCAD),Month(ObsDateCAD))

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

1 Will give error about subquery returns more than one value
2 If time is included, it wont work

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-13 : 07:59:06

Madhi, you're right - it is friday


select col1, col2, col3, ...
from
(select 
row_number() over (partition by ObsDateCAD order by ObsDateCAD desc) as rownum,
* 
from CAD t1
where t1.ObsDateCAD=
(
select
max(ObsDateCAD) as ObsDateCAD
from CAD
where year(t1.ObsDateCAD)=year(ObsDateCAD)
  and month(t1.ObsDateCAD)=month(ObsDateCAD)
)
)dt
where rownum=1

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-13 : 08:07:07

Ok. My method


Select t1.*
into ER_CAD_M
from ER_CAD as t1 inner join
(
select 
	dateadd(month,datediff(month,-1,ObsDateCAD ),-1)  as ObsDateCAD1, 
	dateadd(month,datediff(month,-1,ObsDateCAD ),0)  as ObsDateCAD2 
from 
	ER_CAD
group by 
	ObsDateCAD 
) as t2
on t1.ObsDateCAD >= t2.ObsDateCAD1 and t1.ObsDateCAD < t2.ObsDateCAD2

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-13 : 08:17:13

Oh!
My solution on my testtable gives 10 rows and they are correct.
Your solution on my testtable gives 93 rows.

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-13 : 08:31:10

Did you sample data include time too?

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-13 : 08:45:04

Of course! Why not?

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-13 : 08:58:18

[code]
declare @CAD table (col1 varchar(255), ObsDateCAD datetime)
insert @CAD values('blabla','Jun 28 2006 4:07PM')
insert @CAD values('blabla','Aug 3 2006 12:18PM')
insert @CAD values('blabla','Aug 3 2006 12:18PM')
insert @CAD values('blabla','Aug 3 2006 12:18PM')
insert @CAD values('blabla','Aug 3 2006 12:18PM')
insert @CAD values('blabla','Aug 3 2006 12:18PM')
insert @CAD values('blabla','Aug 3 2006 12:18PM')
insert @CAD values('blabla','Aug 3 2006 12:18PM')
insert @CAD values('blabla','Aug 3 2006 12:18PM')
insert @CAD values('blabla','Aug 3 2006 12:18PM')
insert @CAD values('blabla','Aug 3 2006 12:18PM')
insert @CAD values('blabla','Aug 3 2006 12:18PM')
insert @CAD values('blabla','Aug 3 2006 12:20PM')
insert @CAD values('blabla','Aug 3 2006 12:20PM')
insert @CAD values('blabla','Aug 16 2006 4:08PM')
insert @CAD values('blabla','Aug 16 2006 4:08PM')
insert @CAD values('blabla','Aug 16 2006 4:08PM')
insert @CAD values('blabla','Aug 16 2006 4:08PM')
insert @CAD values('blabla','Aug 16 2006 4:08PM')
insert @CAD values('blabla','Aug 16 2006 4:09PM')
insert @CAD values('blabla','Sep 1 2006 12:10PM')
insert @CAD values('blabla','Sep 1 2006 12:10PM')
insert @CAD values('blabla','Sep 1 2006 12:10PM')
insert @CAD values('blabla','Sep 1 2006 12:10PM')
insert @CAD values('blabla','Sep 1 2006 12:10PM')
insert @CAD values('blabla','Sep 1 2006 12:10PM')
insert @CAD values('blabla','Sep 1 2006 12:10PM')
insert @CAD values('blabla','Sep 1 2006 12:10PM')
insert @CAD values('blabla','Sep 1 2006 12:10PM')
insert @CAD values('blabla','Sep 1 2006 12:10PM')
insert @CAD values('blabla','Sep 1 2006 12:10PM')
insert @CAD values('blabla','Sep 1 2006 12:10PM')
insert @CAD values('blabla','Sep 1 2006 12:10PM')
insert @CAD values('blabla','Sep 1 2006 12:10PM')
insert @CAD values('blabla','Sep 1 2006 12:10PM')
insert @CAD values('blabla','Sep 1 2006 12:10PM')
insert @CAD values('blabla','Sep 1 2006 12:10PM')
insert @CAD values('blabla','Sep 1 2006 12:10PM')
insert @CAD values('blabla','Sep 6 2006 2:05PM')
insert @CAD values('blabla','Sep 6 2006 2:05PM')
insert @CAD values('blabla','Sep 25 2006 2:08PM')
insert @CAD values('blabla','Sep 25 2006 2:09PM')
insert @CAD values('blabla','Sep 25 2006 2:09PM')
insert @CAD values('blabla','Sep 25 2006 2:09PM')
insert @CAD values('blabla','Sep 25 2006 2:10PM')
insert @CAD values('blabla','Sep 25 2006 2:10PM')
insert @CAD values('blabla','Sep 25 2006 2:10PM')
insert @CAD values('blabla','Sep 25 2006 2:10PM')
insert @CAD values('blabla','Sep 25 2006 2:10PM')
insert @CAD values('blabla','Sep 25 2006 2:24PM')
insert @CAD values('blabla','Nov 2 2006 8:40AM')
insert @CAD values('blabla','Nov 2 2006 8:40AM')
insert @CAD values('blabla','Nov 2 2006 8:40AM')
insert @CAD values('blabla','Nov 2 2006 8:40AM')
insert @CAD values('blabla','Nov 2 2006 8:40AM')
insert @CAD values('blabla','Nov 2 2006 9:19AM')
insert @CAD values('blabla','Nov 30 2006 5:54PM')
insert @CAD values('blabla','Nov 30 2006 5:54PM')
insert @CAD values('blabla','Nov 30 2006 5:58PM')
insert @CAD values('blabla','Nov 30 2006 6:06PM')
insert @CAD values('blabla','Nov 30 2006 6:06PM')
insert @CAD values('blabla','Feb 1 2007 12:44PM')
insert @CAD values('blabla','Feb 1 2007 12:44PM')
insert @CAD values('blabla','Feb 1 2007 12:44PM')
insert @CAD values('blabla','Feb 28 2007 12:38PM')
insert @CAD values('blabla','Feb 28 2007 12:38PM')
insert @CAD values('blabla','Feb 28 2007 12:38PM')
insert @CAD values('blabla','Feb 28 2007 12:38PM')
insert @CAD values('blabla','Feb 28 2007 12:38PM')
insert @CAD values('blabla','Feb 28 2007 12:38PM')
insert @CAD values('blabla','Feb 28 2007 12:38PM')
insert @CAD values('blabla','Feb 28 2007 12:38PM')
insert @CAD values('blabla','Nov 2 2006 9:19AM')
insert @CAD values('blabla','Apr 11 2007 1:00PM')
insert @CAD values('blabla','Apr 11 2007 1:00PM')
insert @CAD values('blabla','May 10 2007 12:44PM')
insert @CAD values('blabla','May 10 2007 12:44PM')
insert @CAD values('blabla','May 10 2007 12:44PM')
insert @CAD values('blabla','May 10 2007 12:44PM')
insert @CAD values('blabla','May 24 2007 12:07PM')
insert @CAD values('blabla','May 24 2007 12:07PM')
insert @CAD values('blabla','Jun 20 2007 12:34PM')
insert @CAD values('blabla','Jun 20 2007 12:34PM')
insert @CAD values('blabla','Jun 20 2007 12:36PM')
insert @CAD values('blabla','Jun 20 2007 12:36PM')
insert @CAD values('blabla','Jun 20 2007 12:36PM')
insert @CAD values('blabla','Jun 20 2007 12:36PM')
insert @CAD values('blabla','Jun 20 2007 12:36PM')
insert @CAD values('blabla','Jun 20 2007 12:36PM')
insert @CAD values('blabla','Jun 20 2007 12:36PM')
insert @CAD values('blabla','Jun 20 2007 12:36PM')
insert @CAD values('blabla','Jun 20 2007 12:36PM')
insert @CAD values('blabla','Jun 20 2007 12:36PM')
insert @CAD values('blabla','Jun 20 2007 12:36PM')
insert @CAD values('blabla','Jun 20 2007 12:36PM')
insert @CAD values('blabla','Jun 20 2007 12:36PM')
insert @CAD values('blabla','Jun 20 2007 2:21PM')
insert @CAD values('blabla','Jul 23 2007 12:07PM')
insert @CAD values('blabla','Jul 23 2007 12:07PM')
insert @CAD values('blabla','Jul 23 2007 12:07PM')
insert @CAD values('blabla','Jul 23 2007 12:07PM')
insert @CAD values('blabla','Jul 23 2007 12:07PM')
insert @CAD values('blabla','Jul 23 2007 12:07PM')
insert @CAD values('blabla','Jul 23 2007 12:07PM')
insert @CAD values('blabla','Jul 23 2007 12:07PM')
insert @CAD values('blabla','Jul 23 2007 12:07PM')
insert @CAD values('blabla','Jul 23 2007 12:07PM')
insert @CAD values('blabla','Jul 23 2007 12:07PM')
insert @CAD values('blabla','Jul 23 2007 12:07PM')
insert @CAD values('blabla','Jul 23 2007 12:07PM')
insert @CAD values('blabla','Jul 23 2007 12:07PM')
insert @CAD values('blabla','Jul 23 2007 12:07PM')
insert @CAD values('blabla','Jul 23 2007 12:07PM')
insert @CAD values('blabla','Aug 7 2007 12:23PM')
insert @CAD values('blabla','Aug 7 2007 12:23PM')
insert @CAD values('blabla','Aug 7 2007 12:23PM')
insert @CAD values('blabla','Aug 7 2007 12:23PM')
insert @CAD values('blabla','Aug 7 2007 12:23PM')
insert @CAD values('blabla','Aug 7 2007 12:23PM')
insert @CAD values('blabla','Aug 7 2007 12:23PM')
insert @CAD values('blabla','Aug 7 2007 1:39PM')

-- madhi
Select t1.*
--into ER_CAD_M
from @CAD as t1 inner join
(
select
dateadd(month,datediff(month,-1,ObsDateCAD ),-1) as ObsDateCAD1,
dateadd(month,datediff(month,-1,ObsDateCAD ),0) as ObsDateCAD2
from
@CAD
group by
ObsDateCAD
) as t2
on t1.ObsDateCAD >= t2.ObsDateCAD1 and t1.ObsDateCAD < t2.ObsDateCAD2

-- webfred
select * --col1, col2, col3, ...
from
(select
row_number() over (partition by ObsDateCAD order by ObsDateCAD desc) as rownum,
*
from @CAD t1
where t1.ObsDateCAD=
(
select
max(ObsDateCAD) as ObsDateCAD
from @CAD
where year(t1.ObsDateCAD)=year(ObsDateCAD)
and month(t1.ObsDateCAD)=month(ObsDateCAD)
)
)dt
where rownum=1

[code]

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-13 : 09:11:00

Well. Add distint t1.* in my query, now we can understand why the results are different

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-13 : 09:15:46

Sorry madhi, but this gives also a wrong result...

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-13 : 09:33:52

quote:
Originally posted by webfred

Sorry madhi, but this gives also a wrong result...

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

Yes. My query would consider only the last day of the month (not the last day of month available on the data). Thus my method would give less results. But I think your method is correct. Let us wait till OP replies

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources