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 count2009-04-01 27872009-05-01 78902009-04-01 79992009-05-01 87892009-06-01 89902009-07-01 9230But with the below code I am getting just:2009-06-01 89902009-07-01 9230Thanks,Petronasif object_id('#temp1') is not null drop table #temp1declare @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_dateinto #temp1 from orders o (nolock) inner join billing b(nolock) On o.order_id=b.order_idwhere o.product_id ='Quality' and b.response_id = 'a' and b.tran_type in ('c') and o.order_received_date < @start_dateand (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 #temp1select order_Received_date,bill_date,response_date from #temp1where 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" |
|
|
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 |
|
|
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" |
|
|
Petronas
Posting Yak Master
134 Posts |
Posted - 2009-07-20 : 15:23:44
|
Thanks Peso... |
|
|
|
|
|