#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2011
    Posts
    2
    Rep Power
    0

    Question 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
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,375
    Rep Power
    391
    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
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2011
    Posts
    2
    Rep 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.

IMN logo majestic logo threadwatch logo seochat tools logo