Looping Through - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Looping Through

Author

Topic

Petronas
Posting Yak Master

134 Posts

Posted - 2009-07-21 : 10:29:00

Hi,

I need to loop through to get counts for each month from 2/1/2009 to now.
I am not sure how do I proceed after the select statement?

Thanks,
Petronas

Query

DECLARE @now datetime,
@cnt int,
@start_date DATETIME,
@end_date DATETIME

SET @now = GETDATE()
SET @CNT = datediff(mm,'2/1/2009',@now)

WHILE (@CNT > 0)
BEGIN

SELECT @start_date = DATEADD(mm,-@cnt,CONVERT(varchar,DATEADD(dd,-DAY(@now)+1,@now),111))
SELECT @end_date = DATEADD(mm,1,@start_date)

if object_id('#temp1') is not null drop table #temp1
--if object_id('#temp2') is not null drop table #temp2

select distinct(o.order_id),o.product_id, convert(datetime,convert(varchar(2), month(order_Received_date)) +'/1/' + convert(varchar(4), year (order_Received_date)))order_Received_date,b.bill_sent_date,b.bill_response_date,
convert(varchar(11),o.cancel_date,110)cancel_date,
max(case when b.billing_cycle =1 then convert(varchar(11),b.bill_date,110) end) as First_Billing_date
into #temp1
from orders_base o (nolock)
inner join V_billingwithhistory b(nolock) On o.order_id=b.order_id
where o.product_id ='quality'
and b.response_type_id = 'a'
and o.order_received_date < @start_date
and (o.cancel_date is null or o.cancel_date >=@start_date)
and b.bill_date between convert(datetime,convert(varchar(11), dateadd(m,-1,@start_date)))- datepart(dd,@start_date) + 1 and convert(datetime,convert(varchar(11), @start_date))- datepart(dd,@start_date + 1 )
and b.response_date between convert(datetime,convert(varchar(11), dateadd(m,-1,@start_date)))- datepart(dd,@start_date) + 1 and convert(datetime,convert(varchar(11), @start_date))- datepart(dd,@start_date + 1 )
--and b.first_billing_date is not null
group by o.order_id,o.product_id,convert(datetime,convert(varchar(2), month(order_Received_date)) +'/1/' + convert(varchar(4), year (order_Received_date))),
convert(varchar(11),o.cancel_date,110),b.bill_date,b.response_date

--select * from #temp1

SELECT @CNT = @CNT - 1

END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-21 : 10:46:31

Duplicate post
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=129732

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources