March 25, 2010 at 6:18 am
I have a table in which attendance is getting stored in terms of in and out
Date Empid in1 out1 in2 out2
2010-03-12 17:00:00.000 1 03/13/2010 8:52:05 AM 03/13/2010 5:11:47 PM X 03/13/2010 5:12:54 PM
Data is getting stored in table like this.
there are in1-in10
and out1 - out10 columns (total 20 columns for in and out) in a single row
columns which are empty are marked with -'x'
i want to get minimum in1 i.e. in1 - this is easy
also i want to get maximum out-2 i.e. in above example
query should return - in as - 03/13/2010 8:52:05 AM
out as - 03/13/2010 5:12:54 PM because this is the second out which is not empty and after this all other outs out3-out10 are empty
please help in writing the query for this
March 25, 2010 at 6:50 am
You will have to do some ugly nested case statements or subqueries to do this in a single select.
In my opinion this is a poor table design. It would have been better to have a table like this:
dd date (just the date part if a datetime)
empl_ID int
timein datetime
timeout datetime
for each in and out there would be a row. The query to find the first in and the last out would be trivial:
select min(timein), max(timeout)
from TABLE
where empl_ID = X and dd = DAY
The probability of survival is inversely proportional to the angle of arrival.
March 25, 2010 at 6:59 am
to build on sturner's idea, you could create a view which puts the data in the format sturner suggested, and do your queries per his suggestion:
Create View Sturners_Idea
AS
select empid, in1 As InTime,out1 As OutTime from BadDesign
UNION ALL
select empid, in2 As InTime,out2 As OutTime from BadDesign
UNION ALL
select empid, in3 As InTime,out3 As OutTime from BadDesign
--seven column pairs to go
Lowell
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply