Knowledgebase: TroubleShooting
[QODBC-Desktop] Troubleshooting - How do I get Timeframe information - Sales by Hour reporting using QODBC
Posted by Jack - QODBC Support on 09 March 2016 01:02 PM

Troubleshooting - How do I get Timeframe information - Sales by Hour reporting using QODBC

Problem Description:

This is a no technical question but one I need to answer myself before jumping into QODBC. I'm considering start working with QODBC to get the information we can't get easily from QuickBooks reports. What I would like to ask people working with QODBC and QB and QB POS is if QODBC is able to access and work with information related to the time of purchases. For example view items that sell the most at 11 AM or between 4 and 5 PM and also see what those items are. I don't know if this information is located on the QB or the QB POS file. If someone could tell me If I could I access this type information, I could start working with QODBC. I would appreciate any help. 

Solution:

You can use below query to get Timeframe information using QODBC

For Example:

SELECT {fn HOUR(TimeCreated)} as Hour, SalesLineQuantity as Qty, Type, RefNumber as Num, SalesLineItemRefFullName as Item, SalesLineDesc as Memo, SalesLineRate as SalesPrice, SalesLineAmount as Amount, TimeModified, CustomFieldContract as Contract# FROM SalesLine NOSYNC where (TxnDate >= {d'2007-12-01'} and TxnDate <={d'2007-12-31'}) and {fn CONVERT( {fn HOUR(TimeCreated)}, SQL_VARCHAR)} = '22' and SalesLineType='Item' and SalesLineItemRefFullName is not null and SalesLineQuantity > 0 order by SalesLineQuantity DESC, SalesLineItemRefFullName



Where '22' is the hour using 24 hour format.
(TxnDate >= {d'2007-12-01'} and TxnDate <={d'2007-12-31'}) is the start and end date for the report

(0 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).