Solved

SQL PROBLEMS

Posted on 2009-05-09
10
178 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 92

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VB.NET 1 21
Help Required 3 89
Mysql Left Join Case 10 41
How do sunrise and sunset times change with altitude 9 20
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

815 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now