How to make a SQL looping for date?

Question

select CAST(convert(varchar, a.rechargedate, 112) as datetime)as RechargeDate,
COUNT(distinct a.mobileno) AS UnitTotal,
SUM(a.amount) AS AmountTotal
from recharge a
where *a.rechargedate BETWEEN '2009-07-01' AND '2009-07-31'*
group by CAST(convert(varchar, a.rechargedate, 112) as datetime)
order by a.rechargedate

above is my sql query. in the
(((( a.rechargedate BETWEEN '2009-07-01' AND '2009-07-31' )))))
i would change it by using looping. so if next time i wanna change date to august. it will automatically loops by itself. i no need to manually key in the date to 2009-08-01........ got anyone can help me ? show me how to make it?

Answer 1

it all depends on your logic, here are some options:

1. take @startDate and @endDate as parameters for the sproc
2. if you can logically tie those dates to current date, you can calculate them in the sproc (or function) based on getdate() (will give you current date)

Answer 2

Not sure if this is just a query that you are using for yourself to veiw data, or if it is suppose to be in a sproc. If it is just a utility query, you could do something like this.,

declare @firstofmonth as smalldatetime
declare @endofmonth as smalldatetime

--Set the inital month to loop
set @firstofmonth = '01/01/2009'
set @endofmonth = '01/31/2009'

WHILE @firstofmonth >= '09/01/2009' --This would be the condition to end the loop

Begin

select CAST(convert(varchar, a.rechargedate, 112) as datetime)as RechargeDate,
 COUNT(distinct a.mobileno) AS UnitTotal,
 SUM(a.amount) AS AmountTotal
From recharge a
Where a.rechargedate BETWEEN @firstofmonth AND @endofmonth
group by CAST(convert(varchar, a.rechargedate, 112) as datetime)
order by a.rechargedate

SET @firstofmonth = DateAdd(m,1,@firstofmonth)
SET @endofmonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@firstofmonth())+1,0))

End