New user needs help

[Michelle]

Hi all

I am new to sql code and am not even sure i am posting this in the right
place but would be grateful for any assistance.

I am trying to write a sql query as follows:

I want to select data based on a route(OUTBOUND_ROUTE),
date(DATE_CREATED) and originating country(CARD_COUNTRY_ID). I have
another field call TOTAL_QUOTATION and my aim is to have one record
displayed for each date (7 dates in total which are the 7 days previous
to when user runs query) showing a total figure. I need to do a
calculation on TOTAL_QUOTATION whereby if CARD_COUNTRY_ID = "gb" then
divide total_quotation by 100 else multiply total_quotation by 0.667753
and then divide by 100 (this makes each total_quotation figure a £
value). So regardless of route and card_country_id i want each date to
show the £ total.

Here is my code

SELECT DATE_CREATED,
SUM(CASE CARD_COUNTRY_ID
WHEN 'GB'
THEN TOTAL_QUOTATION/100
ELSE (TOTAL_QUOTATION*0.667753)/100
END) AS ShortSea
FROM PO_ECOM.V_BOOKINGS
WHERE(OUTBOUND_ROUTE = 'DOVECALA'
OR OUTBOUND_ROUTE = 'CALADOVE')
AND (DATE_CREATED LIKE to_DATE(SYSDATE -7, 'DD-MON-YYYY')
OR DATE_CREATED LIKE TO_DATE(SYSDATE -6, 'DD-MON-YYYY')
OR DATE_CREATED LIKE TO_DATE(SYSDATE -5, 'DD-MON-YYYY')
OR DATE_CREATED LIKE TO_DATE(SYSDATE -4, 'DD-MON-YYYY')
OR DATE_CREATED LIKE TO_DATE(SYSDATE -3, 'DD-MON-YYYY')
OR DATE_CREATED LIKE TO_DATE(SYSDATE -2, 'DD-MON-YYYY')
OR DATE_CREATED LIKE TO_DATE(SYSDATE -1, 'DD-MON-YYYY'))
GROUP BY DATE_CREATED
what i am expecting as output is something like

29/06/04 30,000
30/06/04 10,000

instead i am getting something like

29/06/04 15
29/06/04 155.55555
etc

From this i can see that the code is running my calculation but is not
adding all the totals together by date.

I would be grateful for any help

thanks
Michelle

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[David Portas]

This is a Microsoft SQL Server group but your query isn't SQL Server code.
It looks more like Oracle. If this is an Oracle question you should get more
help in comp.databases.oracle.server.

Without seeing some sample data it's difficult to determine where the
problem is. The best way to get help is to post some code to reproduce the
problem: a CREATE TABLE statement for your table and a few INSERT statements
of sample data, then explain what result you want from that sample data.

Hope this helps.

--
David Portas
SQL Server MVP
--