Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.


View this topic in a long page with up to 500 replies per page Create new topic
1 | 2 | 3
380 posts

Ultimate Geek
+1 received by user: 38


  Reply # 481505 15-Jun-2011 14:42
Send private message

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.



58 posts

Master Geek


  Reply # 481519 15-Jun-2011 15:02
Send private message

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.

 
 
 
 


1881 posts

Uber Geek
+1 received by user: 627

Trusted

  Reply # 481529 15-Jun-2011 15:11
Send private message

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




It looks like I'm using an adblocker. I should consider whitelisting Geekzone in my adblocker or a subscription. The Quick Reply box will appear for me when Geekzone is whitelisted. Hooray for me! If I want to reply to this topic I should click on Compose Reply.




58 posts

Master Geek


  Reply # 481532 15-Jun-2011 17:03
Send private message

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. :)



58 posts

Master Geek


  Reply # 481619 15-Jun-2011 21:03
Send private message

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?

380 posts

Ultimate Geek
+1 received by user: 38


  Reply # 481644 15-Jun-2011 22:36
Send private message

@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.


380 posts

Ultimate Geek
+1 received by user: 38


  Reply # 481650 15-Jun-2011 22:44
Send private message

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



58 posts

Master Geek


  Reply # 481671 15-Jun-2011 23:58
Send private message

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.



58 posts

Master Geek


  Reply # 481679 16-Jun-2011 01:18
Send private message

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.

380 posts

Ultimate Geek
+1 received by user: 38


  Reply # 481718 16-Jun-2011 09:02
Send private message

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





58 posts

Master Geek


  Reply # 481736 16-Jun-2011 09:36
Send private message

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.

380 posts

Ultimate Geek
+1 received by user: 38


  Reply # 481740 16-Jun-2011 09:56
Send private message

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.




58 posts

Master Geek


  Reply # 481748 16-Jun-2011 10:14
Send private message

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!



58 posts

Master Geek


  Reply # 481759 16-Jun-2011 10:34
Send private message

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

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?



58 posts

Master Geek


  Reply # 481839 16-Jun-2011 13:35
Send private message

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
View this topic in a long page with up to 500 replies per page Create new topic



Twitter »

Follow us to receive Twitter updates when new discussions are posted in our forums:



Follow us to receive Twitter updates when news items and blogs are posted in our frontpage:



Follow us to receive Twitter updates when tech item prices are listed in our price comparison site:





News »

New Zealand government to create digital advisory group
Posted 16-Dec-2017 08:47


Australia datum changes means whole country moving 1.8 metres north-east
Posted 16-Dec-2017 08:39


UAV Traffic Management Trial launching today in New Zealand
Posted 12-Dec-2017 16:06


UFB connections pass 460,000
Posted 11-Dec-2017 11:26


The Warehouse Group to adopt IBM Cloud to support digital transformation
Posted 11-Dec-2017 11:22


Dimension Data peeks into digital business 2018
Posted 11-Dec-2017 10:55


2018 Cyber Security Predictions
Posted 7-Dec-2017 14:55


Global Govtech Accelerator to drive public sector innovation in Wellington
Posted 7-Dec-2017 11:21


Stuff Pix media strategy a new direction
Posted 7-Dec-2017 09:37


Digital transformation is dead
Posted 7-Dec-2017 09:31


Fake news and cyber security
Posted 7-Dec-2017 09:27


Dimension Data New Zealand strengthens cybersecurity practice
Posted 5-Dec-2017 20:27


Epson NZ launches new Expression Premium Photo range
Posted 5-Dec-2017 20:26


Eventbrite and Twickets launch integration partnership in Australia and New Zealand
Posted 5-Dec-2017 20:23


New Fujifilm macro lens lands in New Zealand
Posted 5-Dec-2017 20:16



Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.



Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.

Alternatively, you can receive a daily email with Geekzone updates.