Help to display result set - 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

Help to display result set

Author

Topic

Petronas
Posting Yak Master

134 Posts

Posted - 2009-06-15 : 09:55:36

Hi All,

I need to get all the data for fist_billing, second_billing and third_billing, which is
stored in the table billing as billing_cycle=1,2,3. But in this BIlling table it is stored as

Order_id Billing_cycle Bill_date
5123456 1 4/14/2009
5123456 2 5/15/2009
7168907 1 3/2/2009
7168907 2 4/7/2009
7168907 3 5/5/2009

I have the below query:

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

select distinct(o.order_id),o.product_id
,min(convert(varchar(11),f.mail_date,110))date,
into #temp1
from fullfillorders f (nolock),orders_base o (nolock)
where o.order_id=f.order_id
and o.product_id in('pp1','pp2')
group by o.order_id,o.product_id

select t.order_id,t.product_id,date as intial_date,
case when b.billing_cycle =1 then convert(varchar(11),b.bill_date,110) end as First_Billing_date,
case when b.billing_cycle =2 then convert(varchar(11),b.bill_date,110) end as Second_Billing_date,
case when b.billing_cycle =3 then convert(varchar(11),b.bill_date,110) end as Third_Billing_date
from #temp2 t (nolock), billing b(nolock)
where t.order_id=b.order_id
order by 4

I am getting the following result set:

Order_id Product_id Initial_date First_Billing Second_billing Third_Billing
7168907 pp1 2/9/2009 3/2/2009 NULL NULL
7168907 pp1 2/9/2009 NULL 4/7/2009 NULL
7168907 pp1 2/9/2009 NULL NULL 5/5/2009

What I want to get is the following:

Order_id Product_id Initial_date First_Billing SEcond_billing Third_Billing
7168907 pp1 2/9/2009 3/2/2009 4/7/2009 5/5/2009

Thanks for your help,
Petronas

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-15 : 10:00:27

[code]
select t.order_id,t.product_id,date as intial_date,
max(case when b.billing_cycle =1 then convert(varchar(11),b.bill_date,110) end) as First_Billing_date,
max(case when b.billing_cycle =2 then convert(varchar(11),b.bill_date,110) end) as Second_Billing_date,
max(case when b.billing_cycle =3 then convert(varchar(11),b.bill_date,110) end) as Third_Billing_date
from #temp2 t (nolock), billing b(nolock)
where t.order_id=b.order_id
GROUP BY t.order_id,t.product_id,date
order by 4
[/code]

KH
[spoiler]Time is always against us[/spoiler]