Need help to do new SQL Statement

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

SQL Server 2012 Forums

Transact-SQL (2012)

Need help to do new SQL Statement

Author

Topic

sandrapoh
Starting Member

1 Post

Posted - 2015-04-09 : 01:51:43

Dear All,
Good day... I have came out with 2 SQL statement as shown below.

STATEMENT ONE
=============

select B.cal_year, SUM(days_to_cancel) from fact_bookings A

join dim_date as B on
B.dim_date_key=A.dim_booking_date_singapore_key

group by B.cal_year
order by B.cal_year

OUTPUT ONE
==========
cal_year SUM(days_to_cancel)
2013 10138723
2014 10333452

STATEMENT TWO
=============
select B.cal_year, SUM(day_target)
from booking_targets_daily A

join dim_date as B on
B.dim_date_key=A.dim_date_key

group by B.cal_year
order by B.cal_year

OUTPUT TWO
==========
cal_year SUM(Booking_Targets)
2010 52272827
2011 62556778
2012 62166480
2013 75703462
2014 81248699
2015 87440948.35

I need a Statement that combined the above 2 statement which output to be like the below. How to?

cal_year SUM(days_to_cancel) Sum(Booking_Targets)
2013 10138723 75703462
2014 10333452 81248699

Many Thanks&Best Regards
Sandra Poh

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2015-04-09 : 06:40:45

will you get only single record always for each year in both the outputs?

Javeed Ahmed
https://www.linkedin.com/pub/javeed-ahmed/25/5b/95

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2015-04-09 : 06:46:39

can you try this

select B.cal_year, SUM(days_to_cancel) as days_to_cancel into #temp1 from fact_bookings A
join dim_date as B on
B.dim_date_key=A.dim_booking_date_singapore_key
group by B.cal_year
order by B.cal_year

select B.cal_year, SUM(day_target) as day_target into #temp2
from booking_targets_daily A
join dim_date as B on
B.dim_date_key=A.dim_date_key
group by B.cal_year
order by B.cal_year

select a.cal_year,
a.days_to_cancel,
b.day_target from #temp1 a
inner join #temp2 b
on a.cal_year=b.cal_year

drop #temp1
drop #temp2

Javeed Ahmed
https://www.linkedin.com/pub/javeed-ahmed/25/5b/95

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources