Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL PROBLEMS

Posted on 2009-05-09
10
Medium Priority
?
183 Views
Last Modified: 2012-05-06
This SQL code below collects all my clocked in/out users and then opens a crystal report and shows my collected data.

The problem is if I put in a range of 01/04/2009 - 30/04/2009 it will give me dates for all the months in my database not just for the date range I want.
sql = "SELECT qCashierLog.CashierName, (SELECT TOP 1 EventTime FROM qCashierLog qC WHERE qC.EventType = 1 AND qC.CashierCode = qCashierLog.CashierCode AND qC.EventDate = qCashierLog.EventDate) AS [In], " & _
                  "(SELECT TOP 1 EventTime FROM qCashierLog qC WHERE qC.EventType = 2 AND qC.CashierCode = qCashierLog.CashierCode AND (qC.EventDate = qCashierLog.EventDate OR qC.EventDate = qCashierLog.EventDate + 1)) AS Out, PayRate, EventDate, iif([In]>[Out], DateDiff('n',[In],[Out]), DateDiff('n',[In],[Out])) AS Hours FROM qCashierLog " & _
                  "INNER JOIN Cashier ON qCashierLog.CashierCode = Cashier.Code " & _
                  "WHERE EventType = 1 AND EventDate BETWEEN '" & Format(DateFrom.Value, "dd/MM/yyyy") & "' AND '" & Format(DateTo.Value, "dd/MM/yyyy") & "' ORDER BY EventDate"

Open in new window

0
Comment
Question by:richard_gar
10 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 24345822
Hello richard_gar,

That suggests that your EventDate column is using a [n][var]char data type.  Is this true?  If so, can you
switch to datetime or smalldatetime?

Regards,

Patrick
0
 

Author Comment

by:richard_gar
ID: 24345859
well the thing is the table I am getting the data from has the clock in/out times in one column and I split that into two colums for my application to read it correctly

Example: 20/03/2009 13:12:00

So I have created a query in my database that the SQL code calls and in the query I split the data in the table I am getting the data from into two columns by doing the following in my query in MS ACCESS

SELECT CashierLog.CashierCode, CashierLog.CashierName, CashierLog.EventType, CDate(Format([EventDateTime],'dd/mm/yyyy')) AS EventDate, Format([EventDateTime],'Short Time') AS EventTime
FROM CashierLog;
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24345898
Hi,
If the type of the data columns in database is DateTime then try the following query.
sql = "SELECT qCashierLog.CashierName, (SELECT TOP 1 EventTime FROM qCashierLog qC WHERE qC.EventType = 1 AND qC.CashierCode = qCashierLog.CashierCode AND qC.EventDate = qCashierLog.EventDate) AS [In], " & _
                  "(SELECT TOP 1 EventTime FROM qCashierLog qC WHERE qC.EventType = 2 AND qC.CashierCode = qCashierLog.CashierCode AND (qC.EventDate = qCashierLog.EventDate OR qC.EventDate = qCashierLog.EventDate + 1)) AS Out, PayRate, EventDate, iif([In]>[Out], DateDiff('n',[In],[Out]), DateDiff('n',[In],[Out])) AS Hours FROM qCashierLog " & _
                  "INNER JOIN Cashier ON qCashierLog.CashierCode = Cashier.Code " & _
                  "WHERE EventType = 1 AND EventDate BETWEEN Convert(DateTime, '" & DateFrom.Value & "', 103) AND Convert(DateTime, '" & DateTo.Value & "', 103) ORDER BY EventDate"

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:richard_gar
ID: 24345950
getting erro: Undefined function 'Convert' in expression.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24346029
I think you are using SQL Server 2000. If so then try this
sql = "SELECT qCashierLog.CashierName, (SELECT TOP 1 EventTime FROM qCashierLog qC WHERE qC.EventType = 1 AND qC.CashierCode = qCashierLog.CashierCode AND qC.EventDate = qCashierLog.EventDate) AS [In], " & _
                  "(SELECT TOP 1 EventTime FROM qCashierLog qC WHERE qC.EventType = 2 AND qC.CashierCode = qCashierLog.CashierCode AND (qC.EventDate = qCashierLog.EventDate OR qC.EventDate = qCashierLog.EventDate + 1)) AS Out, PayRate, EventDate, iif([In]>[Out], DateDiff('n',[In],[Out]), DateDiff('n',[In],[Out])) AS Hours FROM qCashierLog " & _
                  "INNER JOIN Cashier ON qCashierLog.CashierCode = Cashier.Code " & _
                  "WHERE EventType = 1 AND EventDate BETWEEN CDate'" & DateFrom.Value & "') AND CDate('" & DateTo.Value & "') ORDER BY EventDate"

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24346046
>>getting erro: Undefined function 'Convert' in expression.<<
I would have to guess you are not using MS SQL Server, but isntead are using MS Access.
0
 
LVL 11

Expert Comment

by:Muhammad Ousama Ghazali
ID: 24346121
If it is SQL Server 2000 (or above), our experience with date matching shows that you we have to customize the date values as follows:

Start Date
Format(DateFrom.Value, "yyyy-MM-dd 00:00:00")

End Date
Format(DateTo.Value, "yyyy-MM-dd 23:59:59")

Replace your original query with the above and see the result. Hope it helps.
0
 

Author Comment

by:richard_gar
ID: 24347838
I am using access as the database
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24348163
Use the cdate function then.
0
 

Accepted Solution

by:
richard_gar earned 0 total points
ID: 24349610
I fixed the problem:
sql = "SELECT qCashierLog.CashierName, (SELECT TOP 1 EventTime FROM qCashierLog qC WHERE qC.EventType = 1 AND qC.CashierCode = qCashierLog.CashierCode AND qC.EventDate = qCashierLog.EventDate) AS [In], " & _
                  "(SELECT TOP 1 EventTime FROM qCashierLog qC WHERE qC.EventType = 2 AND qC.CashierCode = qCashierLog.CashierCode AND (qC.EventDate = qCashierLog.EventDate OR qC.EventDate = qCashierLog.EventDate + 1)) AS Out, PayRate, EventDate, iif([In]>[Out], 1440+DateDiff('n',[In],[Out]), DateDiff('n',[In],[Out])) AS Hours FROM qCashierLog " & _
                  "INNER JOIN Cashier ON qCashierLog.CashierCode = Cashier.Code " & _
                  "WHERE EventType = 1 AND EventDate >= #{0}# AND EventDate <= #{1}# ORDER BY EventDate"
            sql = String.Format(sql, Format(DateFrom.Value, "MM/dd/yyyy"), Format(DateTo.Value, "MM/dd/yyyy"))

Open in new window

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question