|
I wrote similar like what you wrote but it did not work.
Sorry i do not understand SQL you wrote. i think Dt is Date field right ? what about Time field ? I do not know which one is Date and one is Time...
Sorry, If it should work and you show me the result to sure that what you write is what i want...
I am very glad and to say thanks you so much for your help me...
It had help me some knowledge about SQL...
I will try to find it more and try to change other way to get the result what i want.
If you have any some about it, please share to me.
Thanks you again.
Best regard,
Sovann
VB.Net
|
|
|
|
|
I wasn't sure whether there was a date and a time field - or a single datetime field in your DB - I had assumed a single datetime field which I called dt.
FLOOR(CAST(Dt AS Float)) gives you just the Date portion of your DateTime field. if you have separate Date and Time fields you can replace
FLOOR(CAST(Dt AS Float))
by
dt
if your date field is called dt
The idea of the query is that it selects all records for all employees where there is an 'In' record ... so this
WHERE (status = 'I') AND (NOT EXISTS
(SELECT 1 AS Expr1
FROM clock AS c1
WHERE (EmpId = c.EmpId) AND (status = 'I') AND (Dt < c.Dt) AND (FLOOR(CAST(Dt AS Float)) = FLOOR(CAST(c.Dt AS Float)))))
selects where is is an In record (status = 'I') and
There are no records (not exists) for the same employee (empId = c.EmpId) that is an 'In' record (status = 'I') where the datetime is earlier (dt < c/DT) and the Date is the same.
You could rewrite this if you have a date column called dt and a time column called tm as
(EmpId = c.EmpId) AND (status = 'I') AND (tm < c.tm) AND (dt = c.Dt )
which makes it easier to read!
You can see then that this logic is just checking that tehre are no In records, for this employee, for the same date, that are earlier in the day - or, in other words, only include records that are the first In record for the day.
Having got this record, the first Out column, second In and Out columns are all based upon this record
I'm not on the computer where I created the database to test this right now - when I am, if I have time, I will change it to look like your DB and chaneg teh SQL for you - but if you follow my advice above, you should get there!@
good luck
___________________________________________
.\\axxx
(That's an 'M')
|
|
|
|
|
Thank you so much.
Yes i will follow your advice.
I hope i can get the result like what i want.
If still not, try other way more...
Thank you again.
Good luck too
VB.Net
|
|
|
|
|
In your example result you have 2 In columns and 2 Out columns. But what happens if a person goes in and out 3 or 4 times a day?
|
|
|
|
|
if a person goes in and out 3 or 4 time a day, one field will get null...
VB.Net
|
|
|
|
|
Sovann wrote: if a person goes in and out 3 or 4 time a day, one field will get null...
Don't quite understand. What field?
What I mean is that can a person go in and out undefined number of times during the same day? If he can, do you plan to use dynamic amount of columns (person, in1, out1, in2, out2, in3, out3...). Since this is very hard to achieve using plain sql
|
|
|
|
|
Oh sorry.
No over that columns bro.
that columns is (EmpID,Date,In1,Out1,In2,Out2) so each employee will have 4 attendants but some day they will absent in afternoon so their attendants will be just only 2 so
in columns In2, Out2 data will null. More is number of employee.
at the first i posted, i want to change data in Employee table like table under it. (hm... maybe is called crosstab.)
VB.Net
|
|
|
|
|
Hi
i am stuck in some issues professionally regarding Ab Inito, is there any one who can guide me as to how to go about this Ab Initio things, mates it is really compliv\cated and there is very limited resource available on net regarding Ab Inito......
This is an urgent requirement.......... 
|
|
|
|
|
PoisonCreed wrote: This is an urgent requirement.
but not urgent enough to contact their support team, which is listed on their website.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
I have already contacted their support team 15 days back , even asking them as to how to go about if i have to purchase the licensed copy of the product. but their answer is inconclusive and way of the track ......
Well what i have gathered so far is that company is very discreet and miser in disclosing any of their trade secrets regarding the product and hardly there is any more information regrading it available on web itself, i hardly found anything substantial so far.....
|
|
|
|
|
PoisonCreed wrote: that company is very discreet and miser in disclosing any of their trade secrets
I wouldn't expect them to reveal trade secrets, but they should have a decent support dept.
PoisonCreed wrote: to purchase the licensed copy
That may be the problem, I wouldn't give much support for a free version.
But, I would say, if they don't offer decent support use another product, there are plenty of other tools out there.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Dear Folk,
I am new in SQL Server.I just installed MS SQL Server 2005 in my system(Wista Ultimate) but cant see the query builder or enterprise prise Manager.is there new way installing it to get those two things?
hope your reply,
|
|
|
|
|
|
|
|
|
|
Oh , i get it now ! It worked , thank you so much  (I'll stop being a noob some day)
|
|
|
|
|
You're welcome
|
|
|
|
|
There is necessary to create foreign key relationship between tables when we create database. I create a database and define references between the tables but could not define any physical relation between tables.
Please advice, I need your help
Cheers
Rajesh
|
|
|
|
|
vnsraj wrote: There is necessary to create foreign key relationship between tables when we create database
If you ask if it is necessary, it isn't. An application can be built without using foreign keys. However I would recommend to use them since correctly used, they make the life a whole lot easier.
vnsraj wrote: I create a database and define references between the tables but could not define any physical relation between tables
The relation is not quite physical, but logical between two tables. In order ro use foreign key you must have primary key or unique key on master table and then you can define foreign key on the detail table. See examples A and B in CREATE TABLE[^]
|
|
|
|
|
Hi,
We have a real time application that responds to user's input. The app uses SQL 2005 Express. We observed the following.
After the application idles for a while, the first access to the database takes a few seconds to process. Then, consecutive accesses are nuch faster. The SQL Standard does not show this poor performance after the idling.
Any comments and suggestions are welcome.
Best,
Jun
|
|
|
|
|
Check the power savings settings on the machine that is running SQL Express. Maybe the disks have spun down during the idle period. That would cause a delay
Does it have anything to do with the type of query that is being executed ? Large select (causing a full table scan), large insert,update or delete ?
Interesting problem.
|
|
|
|
|
By idling, I meant the app does not use database for a while. The app is running at all times.
Best,
Jun
|
|
|
|
|
There could be several reasons for this. Basically SQL Express uses the same codebase than Standard so most likely there are environment, configuration or schema (mainly indexes) differences.
One thing could be that buffer cache is loosing already read pages thus forcing physical reads next time. Is the memory usage defined as dynamic and are there other applications eating up the memory from the machine?
Also you could check from database options that the database is not closed automatically and if connection pooling is used at client side, that the lifetime for a connection in the pool is sufficient (connection string options).
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
