CODEWITHSUNDEEP
sqljoinunion
R K Sharma
R K Sharma

Reputation: 855

How to use union in sql?

I have a table in sql database [dbo].[ClockInClockOut] with 3 columns: [UserId], ClockInClockOutTypeId], [CreatedDate]. I need to show the data like 

UserName  CreatedDate      TimeIn      TimeOut

abc      12/31/2013        13:19:51    13:22:37
xyz      1/31/2013         14:19:51    15:19:51

I am doing this in sql:

select u.[UserName],
          CONVERT(VARCHAR(20), c.[CreatedDate], 101) as [Date],
          CASE WHEN c.[ClockInClockOutTypeId]=1 and  THEN CONVERT(VARCHAR(20), c.[CreatedDate], 108) ELSE null END as InTime,
          CASE WHEN c.[ClockInClockOutTypeId]=2 THEN CONVERT(VARCHAR(20), c.[CreatedDate], 108) ELSE null END as OutTime
   from [ClockInClockOut] as c 
   inner join [UserProfile] as u on c.UserId = u.[UserId]

And I am getting this result:

   UserName  CreatedDate      TimeIn       TimeOut

    abc      12/31/2013        13:19:51    null
    abc      12/31/2013           null     14:19:51

In the 1st line 3rd column time in is ok, but I need the time out in 4th column instead of 2nd line of the 4th column.

Upvotes: 1

Views: 121

Answers (3)

M.Ali
M.Ali

Reputation: 69494

Test Data

DECLARE @ClockInClockOut TABLE 
([UserId] INT, [ClockInClockOutTypeId] SMALLINT, [CreatedDate] DATETIME)

INSERT INTO @ClockInClockOut
VALUES 
(1,1,'2013-12-10 09:42:08.603'),
(1,2,'2013-12-10 16:42:08.603'),
(1,1,'2013-12-11 16:42:08.603'),
(1,2,'2013-12-11 18:42:08.603'),
(2,1,'2013-12-12 09:42:08.603'),
(2,2,'2013-12-12 16:42:08.603'),
(2,1,'2013-12-13 11:42:08.603'),
(2,2,'2013-12-13 15:42:08.603')

DECLARE @UserProfile TABLE (USERID INT, USERNAME VARCHAR(20))
INSERT INTO @UserProfile
VALUES (1, 'Mark'),
       (2, 'John')

Query

 ;With TimeI
 AS
   (
   SELECT UserID, [CreatedDate], 
               CONVERT(VARCHAR(20), [CreatedDate], 108)  AS InTime
   FROM @ClockInClockOut
   WHERE [ClockInClockOutTypeId]=1 
   ),
TimeO 
AS
  (
     SELECT UserID,[CreatedDate], 
              CONVERT(VARCHAR(20), [CreatedDate], 108)  AS OutTime
   FROM @ClockInClockOut
   WHERE [ClockInClockOutTypeId]=2
  )
SELECT DISTINCT USERNAME , CAST(CO.[CreatedDate] AS DATE) AS [CreatedDate], TI.Intime, T.OutTime
FROM @UserProfile UP INNER JOIN @ClockInClockOut CO
ON UP.USERID = CO.UserId
INNER JOIN TimeI TI
ON UP.USERID = ti.UserId 
AND CAST(CO.[CreatedDate] AS DATE) = CAST(TI.[CreatedDate] AS DATE) 
INNER JOIN TimeO T
ON UP.USERID = t.UserId 
AND CAST(CO.[CreatedDate] AS DATE) = CAST(T.[CreatedDate] AS DATE)

Result Set

USERNAME    CreatedDate      Intime      OutTime
John          2013-12-12    09:42:08    16:42:08
John          2013-12-13    11:42:08    15:42:08
Mark          2013-12-10    09:42:08    16:42:08
Mark          2013-12-11    16:42:08    18:42:08

Upvotes: 1

Jonathan Leffler
Jonathan Leffler

Reputation: 753495

It is probably easier to use a self-join on the ClockInClockOut table than to attempt a UNION.

SELECT u.UserName, i.Date, i.InTime, o.OutTime
  FROM (SELECT u.UserName,
               CONVERT(VARCHAR(20), c.CreatedDate, 101) AS Date,
               CONVERT(VARCHAR(20), c.CreatedDate, 108) AS InTime
          FROM ClockInClockOut AS c 
        WHERE c.ClockInClockOutTypeId = 1) AS i
  JOIN (SELECT u.UserName,
               CONVERT(VARCHAR(20), c.CreatedDate, 101) AS Date,
               CONVERT(VARCHAR(20), c.CreatedDate, 108) AS InTime
          FROM ClockInClockOut AS c 
         WHERE c.ClockInClockOutTypeId = 2) AS o
    ON i.UserName = o.UserName AND i.Date = o.Date
  JOIN UserProfile AS u ON i.UserId = u.UserId

You might need to make the join conditions more complex if you need to deal with people who checkin more than once on a given day, or who checkin before midnight and checkout after midnight, etc. But for the simple day-shift once in, once out case, this will work.

Upvotes: 1

Travis
Travis

Reputation: 10547

You need to join ClockInClockOut back to itself for the second of the times, not union the results.

I'd do something similar to

SELECT 
  u.UserName,
  CONVERT(VARCHAR(20), c1.[CreatedDate], 101) as [Date],
  CONVERT(VARCHAR(20), c1.[CreatedDate], 108) as InTime,
  CONVERT(VARCHAR(20), c2.[CreatedDate], 108) as OutTime
FROM [ClockInClockOut] as c1
INNER JOIN [ClockInClockOut] as c2 ON c1.UserId = c2.UserId 
           -- getting the specific date is a little tricky, unquestionably better ways exist
           AND CONVERT(VARCHAR(20), c1.[CreatedDate], 101) = CONVERT(VARCHAR(20), c2.[CreatedDate], 101)
INNER JOIN [UserProfile] as u on c1.UserId = u.[UserId] 
WHERE
  c1.ClockInClockOutTypeId = 1 AND c2.ClockInClockOutTypeId = 2

Or, if you don't like, you can aggregate your initial query...

SELECT
  a.UserName, a.Date
  MAX(a.InTime) As InTime,
  MAX(a.OutTime) As OutTime
FROM (
  SELECT 
      u.[UserName],
      CONVERT(VARCHAR(20), c.[CreatedDate], 101) as [Date],
      CASE WHEN c.[ClockInClockOutTypeId]=1 and  THEN CONVERT(VARCHAR(20), c.[CreatedDate], 108) ELSE null END as InTime,
      CASE WHEN c.[ClockInClockOutTypeId]=2 THEN CONVERT(VARCHAR(20), c.[CreatedDate], 108) ELSE null END as OutTime
  FROM[ClockInClockOut] as c 
  INNER JOIN [UserProfile] as u on c.UserId = u.[UserId] 
) a
GROUP BY a.UserName, a.Date

Upvotes: 2

Related Questions