Re: Pivot

On Sun, 20 Mar 2005 19:57:40 GMT, "Diedrik" <diedrik_at_akvavsk.nu> wrote:

>Hi
>I try to make two fields one for sales2005 and one for sales2004. I tried to
>join the table with itself but the result for 2005 is not correct. The SQL
>statement I use looks like this:
>
>SELECT SUM (sr.room_nights) SALES2004, SUM(sr2.room_nights) SALES2005
> FROM stay_records sr, stay_records sr2
> WHERE
> sr.guest_name_id = sr2.guest_name_id
> AND sr.actual_departure_date BETWEEN '01-jan-2004' AND
>'31-dec-2004'
> AND sr2.actual_departure_date BETWEEN '01-jan-2005' AND
>'31-dec-2005')
> GROUP BY
> sr.guest_name_id
>
>The result for 2004 is correct but the one for 2005 is the sum of
>sr_room_nights in all rows found multiplied by two. How do I do to get it
>right
>
>Thanks
>
>/Diedrik
>

select sum(decode(trunc(actual_departure_date,'YEAR'), 2004, sr.room_nights,0)) sales2004,
sum(decode(trunc(actual_departure_date,'YEAR'), 2005, sr.room_nights,0)) sales2005
from stay_records sr
where sr.actual_departure_date between
trunc(to_date('01-jan-2004'),'YEAR') and add_months(trunc(to_date('01-jan-2004'),'YEAR'),24)-(1/3600)

should do the tric.  

--
Sybrand Bakker, Senior Oracle DBA