Need in and out to come in same line and remove null values

Question

I am having a table called alfa

intime                      |   outime                     |    ID  |   Accountid   |
----------------------------------------------------------------------------------- 
2016-06-23 06:53:00.000     |   NULL                        |   1   |   1234        |            
NULL                        |   2016-06-23 17:04:00.000     |   2   |   1234        |

I am expecting the output like

intime                      |   outime                     |    Accountid   |   
-------------------------------------------------------------------------------------------------       
2016-06-23 06:53:00.000     |   2016-06-23 17:04:00.000     |   1234        |

I am trying to run this query:

    select * from alfa
    where intime is not null

and  exists
(select * from alfa as a
where a.accountid=alfa.accountid
and out is not null)

But it not giving me the output as expected.

score 1 · Accepted Answer · edited May 23 '17 at 12:08

Without knowing full data the query might be way wrong, but assuming you will always have only 2 records for the same AccountId (one record where intime is null and one record where outime is null), you can use join on the same table to get the data you need;

SELECT a.intime, b.outime, a.AccountId
FROM alfa a 
    LEFT JOIN alfa b ON a.AccountId = b.AccountId 
        AND CONVERT(date, a.intime) = CONVERT(date, b.outime)
        AND b.intime is null
WHERE a.outime is null

Here I used alias a and filtered only records that have outime value null. Then I joined it to alias b (where records only have intime value of null) based on AccountId.

The query assumes that you will always have records where outime is null, but not necessarily where intime is null, therefore LEFT JOIN was used.

UPDATE: Based on comment I added another condition to JOIN - joining date only from intime to date only from outime. The method for extracting the date only is based on this answer.

for every accountid there will be 2 entry, for 1st entry intime will be the actual intime and outtime will be null, and for 2nd entry intime will be null and outtime will be the actual outtime. And it will be on daily basis — Red Devil, Jul 12 '16 at 03:49
this is what i expected your updated answer is correct. thank you very much — Red Devil, Jul 12 '16 at 04:03

score 0 · Answer 2 · answered Jul 12 '16 at 11:41

0

Please see if this is what you need.

SELECT MIN(t.intime),MAX(isnull(t.outime,0)),accountid
FROM alfa t
group by t.accountid

answered Jul 12 '16 at 11:41

PowerStar

893
5
15

Need in and out to come in same line and remove null values

2 Answers2