Issue with adding new column - 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

Issue with adding new column

Author

Topic

Petronas
Posting Yak Master

134 Posts

Posted - 2009-03-31 : 15:58:26

Hi All,

I have the below query, it is pulling the data in the following format:

product_name, Product_price,Channel,Enroll_date (which is based on order_date),
Active_count and Cancel_count.

What I want is to add a column "Processed_date" (which is based on the order_received_date)
How do I go about doing this...

select product_name,product_price,channel,
enroll_date,
sum(active_count)Active_count,
sum(cancelled_count)Cancel_count
from
( select v.product_name,v.product_price,campaign_type channel,
convert(datetime,convert(varchar(11),o.order_Date,111))enroll_date,
count(*) active_count,
0 enroll_count,
0 cancelled_count
from orders o with (nolock),Marketing v with (nolock)
where o.marketing_id = v.Marketing_ID
and o.cancel_date is null
and o.order_date > '1/1/2009'
and o.order_date <= '3/31/2009'
group by product_name,
convert(datetime,convert(varchar(11),o.order_Date,111)),campaign_type,v.product_price

union all

select v.product_name,v.product_price,campaign_type channel,
convert(datetime,convert(varchar(11),o.cancel_Date))cancel_date,
0 active_count,
0 enroll_count,
count(*) cancelled_count
from orders o with (nolock),Marketing v with (nolock)
where o.marketing_id = v.Marketing_ID
and o.cancel_date is not null
and o.cancel_date > '1/1/2009'
and o.cancel_date <= '3/31/2009'
group by product_name,convert(datetime,convert(varchar(11),o.cancel_Date)),campaign_type,v.product_price
)a
group by product_name, enroll_date,channel,product_price

Thanks,
Petronas

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-31 : 16:10:55

sum(active_count)Active_count,
0 as enroll_count,
sum(cancelled_count)Cancel_count

E 12°55'05.63"
N 56°04'39.26"

Petronas
Posting Yak Master

134 Posts

Posted - 2009-03-31 : 16:22:01

Thanks Peso,

Appreciate your help,
Petronas

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources