Solved

each month

Posted on 2008-06-13
2
190 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now