Red Devil Red Devil - 4 months ago 8
SQL Question

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

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.

Answer

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.