[firebird-support] JOIN ON SELECT PROCEDURE

 

Hello,

I wonder if I could get help on the following issue.

I have a PROCEDURE GETDATES which returns all the dates of a month.
2016-12-01
2016-12-02
....

And this table

TABLE DEBIT
ID (INT)
DEBITTIME (TIMESTAMP)
TOTALSALE (BIGDECIMAL)

Every day has multiple row of table DEBIT

The request is a report with row of all days of the months (one per day) fetched from the procedure aggregated on each day
If no sale occurred still that date from the procedure should be returned from the query with null aggregated
TOTALSALE.

Example:
2016-12-01    null
2016-12-02    2254,54
2016-12-03    125,25
2016-12-04   null

I tried the following but it does not work.

select gd.DAYS, sum(DEBIT.TOTALSALE )
from DEBIT
left join GETDATES('2016-12-01') gd on gd.DAYS = cast(debit.timedebit as date)
and debit.TIMEDEBIT between '2016-12-01' and '2016-12-31'
group by 1

Any help is very appreciated.

Hugo Larson

__._,_.___

---

Posted by: hugo.larson@yahoo.com

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (1)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit Your Group

• New Members 2

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___