query to count number of 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

SQL Server 2005 Forums

Transact-SQL (2005)

query to count number of month

Author

Topic

eirikr_1
Starting Member

27 Posts

Posted - 2010-02-10 : 14:57:33

can any one help me with this problem

i have a database column named

dateStr (yyyymmdd)
20091201
20091202
20091203 should returns count=3 for Dec

20091101
20091102
20091103 count=3 for Nov

20091001
20091002
20091003 count=3

20081201 count=1

20081101 count=1

20081001
20081002 count=2

I need a query to count number of month.
Please Help

shan
Yak Posting Veteran

84 Posts

Posted - 2010-02-10 : 15:16:57

See if this works for you...

declare @i table (dt varchar(10))

insert into @i
select('20091201')
union all
select('20091202')
union all
select('20091203')
union all
select('20091101')
union all
select('20091102')
union all
select('20091103')
union all
select('20091001')
union all
select('20091002')
union all
select('20091003')
union all
select('20081101')
union all
select('20081001')
union all
select('20081002')

select substring(dt,1,6) MTH,MAX(substring(dt,7,8)) MAX_DT from @i
group by substring(dt,1,6) order by substring(dt,1,6)

-Shan

eirikr_1
Starting Member

27 Posts

Posted - 2010-02-10 : 15:21:52

It works perfectly. Thank you very much Shan

shan
Yak Posting Veteran

84 Posts

Posted - 2010-02-10 : 15:32:33

you are welcome

-Shan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 00:24:27

quote:
Originally posted by shan

See if this works for you...

declare @i table (dt varchar(10))

insert into @i
select('20091201')
union all
select('20091202')
union all
select('20091203')
union all
select('20091101')
union all
select('20091102')
union all
select('20091103')
union all
select('20091001')
union all
select('20091002')
union all
select('20091003')
union all
select('20081101')
union all
select('20081001')
union all
select('20081002')

select substring(dt,1,6) MTH,MAX(substring(dt,7,8)) MAX_DT from @i
group by substring(dt,1,6) order by substring(dt,1,6)

-Shan

why have you declared a varchar field to store dates?
Always use proper datatype for your fields
Storing date values as varchar makes date manipulations difficult

for month grouping there's no need to convert it to varchar. you can simply do like


SELECT DATEADD(mm,DATEDIFF(mm,0,datefield),0),COUNT(*)
FROM table
GROUP BY DATEADD(mm,DATEDIFF(mm,0,datefield),0)

and formatting you can do at front end

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources