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
 Showing all the first day of the months

Author  Topic 

Petronas
Posting Yak Master

134 Posts

Posted - 2009-07-20 : 14:55:18

Hi,

I want to show the result set as:

Order_received_date count

2009-04-01 2787
2009-05-01 7890
2009-04-01 7999
2009-05-01 8789
2009-06-01 8990
2009-07-01 9230

But with the below code I am getting just:
2009-06-01 8990
2009-07-01 9230


Thanks,
Petronas





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


declare @start_date datetime,

set @start_date ='8/1/2009'

select o.product_id, order_Received_date,b.bill_date,b.response_date,
convert(varchar(11),o.cancel_date,110)cancel_date,
max(case when b.billing_cycle =1 then convert(varchar(11),b.bill_sent_date,110) end) as First_Billing_date
into #temp1
from orders o (nolock)
inner join billing b(nolock) On o.order_id=b.order_id
where o.product_id ='Quality'
and b.response_id = 'a'
and b.tran_type in ('c')
and o.order_received_date < @start_date
and (o.cancel_date is null or o.cancel_date >=@start_date)
group by o.order_id,o.product_id,order_Received_date,
convert(varchar(11),o.cancel_date,110),b.bill_date,b.response_date


--select * from #temp1

select order_Received_date,
bill_date,response_date
from #temp1
where 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 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 first_billing_date is not null
group by order_Received_date,bill_date,response_date

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-20 : 15:10:44
SELECT *
FROM ...
WHERE DATEPART(DAY, BillDate) = 1



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Petronas
Posting Yak Master

134 Posts

Posted - 2009-07-20 : 15:17:10
Thanks Peso, With your solution I am not getting any records.

Appreciate your help,
Petronas
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-20 : 15:22:13
To be fair, none of the queries match your posted expected output.
Have a read here http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx, and give it a new try.



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Petronas
Posting Yak Master

134 Posts

Posted - 2009-07-20 : 15:23:44
Thanks Peso...
Go to Top of Page
   

- Advertisement -