Combine the counts - 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

Combine the counts

Author

Topic

Petronas
Posting Yak Master

134 Posts

Posted - 2009-07-27 : 15:56:56

Hi ,
I have the below query. It is getting the Order Count and the Billing count from 3/1 to 6/1.
For the order count we have data for 3/1, 4/1, 5/1,6/1 but for the Billing_count we have data
for 4/1,5/1,6/1, as this product was sold in March, but the billing started in April.

I want the result set to be as:

Order_date Order_Count Bill_count
2009-03-01 2000 NULL
2009-04-01 9000 5460
2009-05-01 12000 9800
2009-06-01 13000 11000

I am not sure how I combine the counts from Temp1 and Temp2 and show NULL for Bill_count.

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

Petronas
Posting Yak Master

134 Posts

Posted - 2009-07-27 : 16:18:17

I got the solution.. Used full outer join.