hugo.larson@yahoo.com [firebird-support]
2016-12-06 07:18:55 UTC
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
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