Firebird/SQL Query doing my head in.

Forums › IT Pro and developers › Firebird/SQL Query doing my head in.

1 | 2 | 3

1572 posts

Uber Geek

#481505 15-Jun-2011 14:42

Change your FROM to this:

FROM HourList
LEFT OUTER JOIN INVOICE ON HourValues = EXTRACT(HOUR FROM wheninvoiced)

And you should be good to go.

If the Extract does return a different data type to the one in your HourList table, do a cast/convert to fix.

Acropoli

58 posts

Master Geek

#481519 15-Jun-2011 15:02

Okay, this is what I have so far, and it's returning 17 rows.

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

Looking at the code, have I put the Coalesce in the incorrect place? Shouldn't we be converting the nulls for the missing hours to zero? The way I read this is that we are looking for nulls in the salestax column, which there wouldn't be any.

muppet

2570 posts

Uber Geek

Trusted

#481529 15-Jun-2011 15:11

[Watching from the wings. This is like a suspense novel, I hope you figure it out]

Audiophiles are such twits! They buy such pointless stuff: Gold plated cables, $2000 power cords. Idiots.

OOOHHHH HYPERFIBRE!

Acropoli

58 posts

Master Geek

#481532 15-Jun-2011 17:03

and so day 3 almost comes to a close. NZKC, where are you located city-wise? We may have a restaurant near you, and I'd love to throw some vouchers your way if you wouldn't mind taking a hands-on look logging in remotely, as this is clearly driving my little novice brain crazy. :)

Acropoli

58 posts

Master Geek

#481619 15-Jun-2011 21:03

Yay, got it working without terminating now. Here's the code:

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

This produces 24 rows of data, including hours with zero's, but at 8pm, there was already data in the 9, 10, and 11pm fields.... I'm thinking there is probably a better variable than '> yesterday' to get todays only data?

nzkc

1572 posts

Uber Geek

#481644 15-Jun-2011 22:36

@Acropli, I'm in Auckland. Hibiscus Coast to be exact.

As for your date > 'yesterday' issue...what datatype is the whenInvoiced column?

I'll take a look at the Firebird documentation to see if I can work out what you need. I suspect that doing a today - 1 gives you the same time as "now"...just a day earlier. Thats why you'd have gotten values for times in the future. Strictly they're not in the future, they're within the last 24 hours.

nzkc

1572 posts

Uber Geek

#481650 15-Jun-2011 22:44

Right...seems Firebird has some date literals you might want to look into. Bottom of the page here: http://web.firebirdsql.org/doc/contrib/FirebirdDateLiterals.html

You could do:

WHERE
whenInvoiced BETWEEN 'Yesterday' AND 'Today'

That would give you all records for the WHOLE of "yesterday" and NONE for "today".

If you wanted the last 24 hours, then best to do a "today" - 24hours. If I'm not mistaken, that would be:

WHERE
whenInvoiced >= DATEADD(-24 HOUR TO NOW())

see: http://www.firebirdsql.org/refdocs/langrefupd21-intfunc-dateadd.html

Requires 2.1 or later though

Sharesies

Trade NZ and US shares and funds with Sharesies (affiliate link).

Acropoli

58 posts

Master Geek

#481671 15-Jun-2011 23:58

Hi NZKC,

Thanks for that, I feel a tad guilty as you've done all the research for me. Though in saying that, I've googled for the past 3 days lol, but it's easier if you know what you're looking for, so I appreciate the help :)

What I'm trying to accomplish is the current day's data only. So at midnight, the data will be completely blank. At mid-day, 00:00-12:00 will be populated, and 13:00 - 23:59 will be blank. Like a live hourly feed of the day.

So, using your comments above, I should be able to use BETWEEN 'Yesterday' AND 'Tomorrow'. I'll give that a shot. or even just whenInvoiced = 'today' seeing as the Date Literals states that 'Today' takes the days date and time of 00:00:00, so in effect it should pull the days records from midnight onwards.

I'll let you know how I get on, thanks for all your help so far, you've been a champ. Unfortunately we don't have a restaurant in Aucks, but I'd be happy to flick you some vouchers should you ever want to visit Hamilton in the next 6 months :P

Edit: Oh, and how do I add a second criteria to this query? I also want to filter it by a value in the REMOTELOCATIONID column located within the INVOICE table.

Acropoli

58 posts

Master Geek

#481679 16-Jun-2011 01:18

I must of had a brain-fade when I wrote the above. Sorry, it's late, and my brain wouldn't let me go to bed without using the newly suggested info.

I think I've accomplished getting only todays data, using wheninvoiced BETWEEN 'today' AND 'tomorrow'. New code is as follows:

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

Now if I could just grab a hand with adding the RemoteLocationID filter to the query, that would be great.

nzkc

1572 posts

Uber Geek

#481718 16-Jun-2011 09:02

Its pretty simple...

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

:-)

Also, good use of the restriction in the join - takes some people years to get that. For those following this thread, putting the "wheninvoiced between 'today' and 'tomorrow'" in the WHERE clause changes the query completely. The way to think of it is:

Do the restriction on the data then join on the resulting subset

as opposed to

Do the join then restrict the whole result set

Acropoli

58 posts

Master Geek

#481736 16-Jun-2011 09:36

Hi again,

I thought it might be something like that but unfortunately so far today that's only returning 1 row of data, it's completely ignoring our previous request to return zero values also.

nzkc

1572 posts

Uber Geek

#481740 16-Jun-2011 09:56

Are you returning the HourList.HourValue column?

Your query above didn't have it.

You could also try changing your JOIN from:

and wheninvoiced between 'today' and 'tomorrow'

To:

and wheninvoiced >= 'today'

Unless you have future values loaded.

Acropoli

58 posts

Master Geek

#481748 16-Jun-2011 10:14

SELECT extract(hour from hourlist.HOURVALUES),
COALESCE(sum(salestax * 7.66666666), 0)
FROM HOURLIST
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

This is returning 2 rows of values, populating both the hourvalues and coalesce columns. This may actually suffice in the meantime. Thanks NZKC!

Acropoli

58 posts

Master Geek

#481759 16-Jun-2011 10:34

Aww crap, if it's not one thing, its another, then another, then another

Putting this query into FBExport causes a single issue with the 'and' function.

fbexport.exe -Sc -H LOCALHOST -D c:\users\[bleeped].gdb -U [bleeped] -P [bleeped] -F mynewfile.csv -J "" -T -Q "SELECT extract(hour from hourlist.hourvalues), Coalesce(sum(salestax * 7.66666666), 0) FROM hourlist 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

returns:
SQL Message : -104
Invalid token

Token unknown - line 1, column 194
and

Can I rephrase or replace this AND with something else?

Acropoli

58 posts

Master Geek

#481839 16-Jun-2011 13:35

I've sent an email off to the developer of FBExport, as it's refusing to accept the AND statement, thus I cannot continue to export to a comma delimited csv.

1 | 2 | 3