Looping through records - 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 records

Author

Topic

Petronas
Posting Yak Master

134 Posts

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

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

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-07-21 : 13:00:13

Petronas,

I don't see why you need to loop through anything. Can you be more specific about exactly what it is you want to count?

Jim

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-21 : 15:35:17

I'm confused as well as to what you are trying to achomplish. I redid you query, but not being sure of the intent, it is hard to know if it is right....

DECLARE 
	@start_date DATETIME,
	@end_date DATETIME


SELECT @start_date = '20090201'
SELECT @end_date = DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0) + 1


SELECT 
	o.order_id,
	o.product_id,
	DATEADD(MONTH, DATEDIFF(MONTH, 0, order_Received_date), 0) AS order_Received_date
	b.bill_sent_date,
	b.bill_response_date,
	DATEADD(DAY, DATEDIFF(DAY, 0, o.cancel_date), 0) AS cancel_date
	MAX
	(
		CASE
			WHEN b.billing_cycle = 1
				THEN DATEADD(DAY, DATEDIFF(DAY, 0, b.bill_date), 0)
		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 > DATEADD(MONTH, -1, @startdate)
	AND b.bill_date < @enddate
	AND b.response_date > DATEADD(MONTH, -1, @startdate)
	AND b.response_date < @enddate
GROUP BY
	o.order_id,
	o.product_id,
	DATEADD(MONTH, DATEDIFF(MONTH, 0, order_Received_date), 0)
	DATEADD(DAY, DATEDIFF(DAY, 0, o.cancel_date), 0),
	b.bill_date,
	b.response_date

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources