Solved

each month

Posted on 2008-06-13
2
192 Views
Last Modified: 2010-03-20
bit brain dead today, i have this code, how can i make it automatically do previous month at the mo i have to enter manually....


select count(*) as times,member_id,licensed,used 
from user_count 
where used > licensed+1 and dt_stamp between '2007-02-01' and '2007-02-28'
group by member_id,licensed,used
order by member_id, used

Open in new window

0
Comment
Question by:James Murrell
2 Comments
 
LVL 15

Accepted Solution

by:
Lee Osborne earned 500 total points
ID: 21777575
You could use the WHERE like this:

WHERE used > licensed+1 and dt_stamp BETWEEN DateSerial(Year(Now()), Month(Now()) - 1, 1) AND DateSerial(Year(Now()), Month(Now()), 0)

This should look at the previous month.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21777623
select count(*) as times,member_id,licensed,used
from user_count
where used > licensed+1 and dt_stamp between dateadd(m, -1, getdate()) and gettdate()
group by member_id,licensed,used
order by member_id, used
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question