Differences between monthly statistics values

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 2000 Forums

Transact-SQL (2000)

Differences between monthly statistics values

Author

Topic

adlo
Posting Yak Master

108 Posts

Posted - 2004-11-23 : 18:40:26

I have a stats table that gets written to on a daily basis with the membership totals of my database.
Stats Table
DateCreated DateTime
MemberStats int

I have two questions
1) How do I only retrieve the earliest date (1 row per month) for every month. (i.e. should only give my 2004/11/01 not 2004/11/02 for November.

2) I need to get the memership growth/decline for each month compared to the previous month.

E.g.
2004/08/01,3600
2004/09/01,3612
2004/10/01,3610
2004/10/02,3606

Must be
2004/08/01,0
2004/09/01,12
2004/10/01,-2
2004/10/02,-4

Thanks for the help

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-11-23 : 19:01:46

I'm not sure if this is the solution, but it might point you in the right direction.

http://www.sqlteam.com/item.asp?ItemID=3856

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

VIG
Yak Posting Veteran

86 Posts

Posted - 2004-11-23 : 23:57:21

select min(DateCreated) 
from Stats
GROUP BY month(DateCreated)

declare @t table(d datetime,v int)
insert @t 
select '2004/08/01',3600 union all
select'2004/09/01',3612 union all
select '2004/10/01',3610 union all
select '2004/10/02',3606 
select d,
 v-isnull((select top 1 v from @t t1 where t1.d <t.d order by t1.d desc),v)
from @t t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources