Getting count for next month. - 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

Getting count for next month.

Author

Topic

Petronas
Posting Yak Master

134 Posts

Posted - 2009-07-28 : 15:26:21

Hi ,

I have the below query. It is getting the Order Count and the Billing count from 3/1 to 6/1.
The result set is the Date Order Count for that month and all the Bill_count for that same month.
eg:
Product_id date Order_count Billing_count
Firstcom 4/1/2009 12678 (for 4/1) 6340 (for 4/1)

Now I want to show the result set as:

Product_id date Order_count Billing_count Billing_count
Firstcom 4/1/2009 12678 (for 4/1) 6340 (for 4/1) 5689 (for 5/1)

I am not sure how I go about getting the Billing_count for the next month.

Thank you,
Petronas

DECLARE @now DATETIME,
@cnt INT,
@start_date DATETIME,
@end_date DATETIME,
@firstdate DATETIME,
@lastdate DATETIME
set @firstdate ='3/1/2009'
set @lastdate = '7/1/2009'

SET @now = GETDATE()
SET @CNT = datediff(mm,@firstdate,@lastdate)

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

CREATE TABLE #Temp1(
Order_id varchar(50),
Order_count int,
product_id varchar(20),
order_received_date datetime

)

CREATE TABLE #temp2
(
product_id varchar(50),
Billing_count int,
bill_date datetime)
WHILE (@CNT > 0)
BEGIN

SET @start_date = convert(datetime,convert(varchar(2),month(dateadd(mm,-@cnt,@lastdate) ) ) +'/1/'+convert(varchar(4),year(dateadd(mm,-@cnt,@lastdate) ) ))
SET @end_date = convert(datetime,convert(varchar(2),month(dateadd(mm,-@cnt+1,@lastdate) ) ) +'/1/'+convert(varchar(4),year(dateadd(mm,-@cnt+1,@lastdate) ) ))

----Order Count
insert into #Temp1
select
distinct(o.order_id),
count(*)Order_count,
o.product_id,
convert(datetime,convert(varchar(2), month(order_received_date)) +'/1/' + convert(varchar(4), year (order_received_date)))Order_received_date
from orders_base o (nolock)
where o.product_id ='firstcom'
and o.order_received_date > @start_date and o.order_received_date<=@end_date
group by o.product_id,
o.order_id,
convert(datetime,convert(varchar(2), month(order_received_date)) +'/1/' + convert(varchar(4), year (order_received_date)))

--Billing Count
insert into #temp2
select t.product_id,
count(*)Billing_count,
convert(datetime,convert(varchar(2), month(bill_date)) +'/1/' + convert(varchar(4), year (bill_date)))Bill_date
from #Temp1 t inner join V_billingwithhistory b(nolock) On t.order_id=b.order_id
where b.response_id = 'a'
and b.bill_date > @start_date
and b.bill_date<= @end_date
group by t.product_id,
convert(datetime,convert(varchar(2), month(bill_date)) +'/1/' + convert(varchar(4), year (bill_date)))

SELECT @CNT = @CNT - 1

END

select t1.product_id,
order_received_date as date,
count(*)Order_count ,Billing_count
from #temp1 t1
full outer join #temp2 t2 on order_received_date=bill_date
group by order_received_date,t1.product_id,Billing_count
order by 2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-30 : 14:16:40

can you post table structure with some sample data

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources