Query Help - doing my head in.

Dev Shed Forums Sponsor:

June 15th, 2011, 02:19 AM

Acropoli

Registered User

Join Date: Jun 2011

Posts: 2

Time spent in forums: 1 h 51 m 13 sec
Reputation Power: 0

Query Help - doing my head in.

Hi All,

For the past few days I've been struggling to get this to work, and you guru's will probably be able to take one look, laugh at me, and identify the problem within a matter of seconds.

Quick bit of history. I have a table called INVOICE which contains a column called "WHENINVOICED" which contains datetime data, and a column called "SALESTAX" which contains financial data which I want to collate into hourly increments for today only, from 00:00 - 23:59

I then have another table called "HOURLIST" with a column called "HourValues" which contains row data of:
01.01.2011, 00:00:00.000
01.01.2011, 01:00:00.000
01.01.2011, 02:00:00.000 .... etc
0-23 for each hour of the day. This column is also formatted as timestamp, for what I thought would make the process easier, having both fields identically formatted.

What I'm trying to accomplish is a query which sums all salestax data multiplied by 7.66666666 for every given hour, and even report those hours for which there is no data.

Here's my query so far:

Code:

SELECT
 COALESCE(sum(salestax * 7.66666666), 0)
 FROM HOURLIST
 LEFT OUTER JOIN INVOICE ON extract(HOUR from hourvalues) = EXTRACT(HOUR FROM wheninvoiced)
WHERE wheninvoiced > 'yesterday'
group by HOURLIST.HOURVALUES order by hourlist.HOURVALUES

This produces only 17 rows:
46.076667
28.366667
1445.166665
483.536666
2055.203332
2662.403331
1312.533332
1057.309999
411.163333
631.656666
4076.979996
6833.223327
4418.989996
1572.739999
526.930000
287.730000
56.580000

As you can see, as there are only 17 results, I'm missing 7 hours of zero records. I'm also not confident that it's pulling "today's data" from 00:00 - 23:59.

Any suggestions?

TIA

June 15th, 2011, 02:33 AM

swampBoogie

Contributing User

Join Date: Jan 2003

Location: Paris Uppland

Posts: 2,352

Time spent in forums: 1 Month 4 Days 9 h 55 m 42 sec
Reputation Power: 390

Any restraints on the right table in the outer join should be in the on clause, otherwise it will be the same as an inner join.

Code:

LEFT OUTER JOIN INVOICE ON extract(HOUR from hourvalues) = EXTRACT(HOUR FROM wheninvoiced)
and wheninvoiced > 'yesterday'

Comments on this post

Acropoli agrees!

June 15th, 2011, 03:00 AM

Acropoli

Registered User

Join Date: Jun 2011

Posts: 2

Time spent in forums: 1 h 51 m 13 sec
Reputation Power: 0

wow swampBoogie, thanks! That fixed it right up. And thanks for explaining it too, it assists in the learning process

Unfortunately when performing this query with FBExport, it returns:

Code:

SQL Message : -104
Invalid token

Token unknown - line 1, column 194
and

The AND, being from this statement:
LEFT outer join invoice on extract(hour from hourvalues) = (extract(hour from wheninvoiced) and wheninvoiced >= 'today' WHERE RemotelocationID = 11 group by hourlist.hourvalues order by hourlist.hourvalues

I've sent an email to the FBExport developers requesting assistance.

Query Help - doing my head in.

Developer Shed Advertisers and Affiliates