CODEWITHSUNDEEP
sqlsql-server-2005
mapet
mapet

Reputation: 878

sql deleting Extra column data

The Original Query:

7/20/2011   I
7/20/2011   O
7/19/2011   I
7/18/2011   O
7/17/2011   I

I = Check In while O = check Out

then I make a query to separate the I And O so It will return like this

select 
    case when CHECKTYPE='I' then checktime else 0 end as 'Check in',
    case when CHECKTYPE='O' then checktime else 0 end as 'Check Out',
    CHECKTYPE,BadgeNo,EmpName 
from 
    Checkinout 
where 
     and checktime BETWEEN '07/17/2011' and '07/26/2011' and EmpName = 'ASIDOY,' and BadgeNo <> '1900-01-01 00:00:00.000'
group by 
    checktime,CHECKTYPE,BadgeNo,EmpName

Result

Check IN    Check out   Status
7/17/2011   1/1/1900    I
1/1/1900    7/18/2011   O
7/19/2011   1/1/1900    I
1/1/1900    7/20/2011   O
7/20/2011   1/1/1900    I

But I have one problem the query create puts (1/1/1900) in order to fill all blanks and i want to delete it. But I dont know how to write the query for it, in order to return like this.

Check In    Check Out
7/17/2011   7/18/2011
7/19/2011   7/20/2011
7/20/2011   7/21/2011

Upvotes: 0

Views: 73

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

Assuming that there's always a check in before a "matching" check out, and not knowing how to deal with the missing check out for the third line of your result yet...

You need to join together the rows which you wish to contribute column values to the final output. It's going to be something like this:

SELECT
     ci.checktime,
     co.checktime
FROM
     CheckInOut ci
         inner join
     CheckInOut co
         on
             ci.checktime < co.checktime
         left join
     CheckInOut co_anti
         on
             ci.checktime < co_anti.checktime and
             co_anti.checktime < co.checktime and
             co_anti.checktype = 'o'
WHERE
    ci.checktype = 'i' and
    co.checktype = 'o' and
    co_anti.checktime is null

We're joining the table to itself 3 times in order to ensure that the row from co that matches against the row from ci is the earliest such row that could match.

I haven't included the other columns or conditions at this point, because I just wanted to focus on what's required to get the columns shown in your desired result. You can hopefully adapt the above to fit your other requirements.

Upvotes: 0

sudheshna
sudheshna

Reputation: 1150

try the following query

select 
    case when CHECKTYPE='I' then checktime end as Check in,
    case when CHECKTYPE='O' then checktime end as Check Out,
    CHECKTYPE,BadgeNo,EmpName 
from 
    Checkinout

it will return NULL for empty columns

Upvotes: 2

Related Questions