Selecting Unique Values Not in the Substring

Question

I can't wrap my head around this for some reason. I'm trying to get the ID from a table where multiple columns are distinct... basically something like -

Select ID from table where ID in (Select distinct ID, Card, PunchTime, PunchDate)

Though that obviously doesn't work. I want to get the IDs that are unique with all of those fields as the criteria. I can't seem to come up with syntax that works. I'm not sure what else I can say about it, it seems lke it should be simple when I look at it... but I've been bouncing off of it since yesterday and nothing is working. Anyone know which way I should be going? Thank you in advance!

edit: The posted things work but the results weren't what I expected. Here's some saple data:

ID  Card  PunchDate  PunchTime In/Out
================================
1  00123  3/17/2012  13:00  1
2  00123  3/17/2012  17:00  2
3  00123  3/17/2012  17:00  1
4  00123  3/17/2012  20:00  2
5  00456  3/17/2012  14:00  1
6  00456  3/17/2012  17:00  2

The reason I'm trying to do this is that the timekeeping software decides that anything with a card, punchdate, and punchtime that is identical to another is a duplicate, regardless of whether it is an in or out punch and deletes one. My only solution is to eliminate the duplicates and basically make the punches from the first in punch to the last outpunch where they are duplicated. So my goal is to select only the unique values based on card, punchdate, and punchtime. However what I have is not excluding the ID in the matter which is making it a unique value. I have a workaround for this so time isn't particularly an issue, but I would much rather figure out how I can get the right data.

Thank you all again for the fast replies!

Answer 1

With the assumption that there is no 2nd shift which starts on one day and ends on the next ...

Table:

DECLARE @table TABLE
    (
      [ID] INT IDENTITY,
      [Card] INT,
      [PunchDate] DATETIME,
      [PunchTime] DATETIME,
      [In/Out] TINYINT
    )
INSERT  INTO @table
        (
          [Card],
          [PunchDate],
          [PunchTime],
          [In/Out]
        )
        SELECT  00123,
                '3/17/2012',
                '3/17/2012 13:00',
                1
        UNION ALL
        SELECT  00123,
                '3/17/2012',
                '3/17/2012 17:00',
                2
        UNION ALL
        SELECT  00123,
                '3/17/2012',
                '3/17/2012 17:00',
                1
        UNION ALL
        SELECT  00123,
                '3/17/2012',
                '3/17/2012 20:00',
                2
        UNION ALL
        SELECT  00456,
                '3/17/2012',
                '3/17/2012 14:00',
                1
        UNION ALL
        SELECT  00456,
                '3/17/2012',
                '3/17/2012 17:00',
                2

Query:

SELECT  [Card],
        [PunchDate],
        MIN([PunchTime]) [PunchTime],
        [In/Out]
FROM    @table
WHERE   [In/Out] = 1
GROUP BY [Card],
        [PunchDate],
        [In/Out]
UNION
SELECT  [Card],
        [PunchDate],
        MAX([PunchTime]) [PunchTime],
        [In/Out]
FROM    @table
WHERE   [In/Out] = 2
GROUP BY [Card],
        [PunchDate],
        [In/Out]
ORDER BY [Card],
        [PunchDate]

Result:

Card    PunchDate   PunchTime   In/Out
123 2012-03-17 00:00:00.000 2012-03-17 13:00:00.000 1
123 2012-03-17 00:00:00.000 2012-03-17 20:00:00.000 2
456 2012-03-17 00:00:00.000 2012-03-17 14:00:00.000 1
456 2012-03-17 00:00:00.000 2012-03-17 17:00:00.000 2

Next he'll want this:

SELECT  a.[Card],
        a.[PunchDate],
        a.[PunchTime],
        b.[PunchTime],
        DATEDIFF(hour, a.[PunchTime], b.[PunchTime]) TotalTime
FROM    (
          SELECT    [Card],
                    [PunchDate],
                    MIN([PunchTime]) [PunchTime]
          FROM      @table
          WHERE     [In/Out] = 1
          GROUP BY  [Card],
                    [PunchDate]
        ) a
        INNER JOIN (
                     SELECT [Card],
                            [PunchDate],
                            MAX([PunchTime]) [PunchTime]
                     FROM   @table
                     WHERE  [In/Out] = 2
                     GROUP BY [Card],
                            [PunchDate]
                   ) b
            ON a.[Card] = b.[Card]
               AND a.[PunchDate] = b.[PunchDate]
ORDER BY a.[Card],
        a.[PunchDate]

Result

Card    PunchDate   PunchTime   PunchTime                   TotalTime
123 2012-03-17 00:00:00.000 2012-03-17 13:00:00.000 2012-03-17 20:00:00.000 7
456 2012-03-17 00:00:00.000 2012-03-17 14:00:00.000 2012-03-17 17:00:00.000 3

Answer 2

UPDATED ANSWER WITH NEW INFORMATION:

SELECT *
FROM TABLE
WHERE NOT EXISTS
(
    SELECT 1 
    FROM TABLE AS Duplicates
    WHERE Duplicates.Card = TABLE.Card
        AND Duplicates.PunchDate = TABLE.PunchDate
        AND Duplicates.PunchTime = TABLE.PunchTime
        AND Duplicates.ID != TABLE.ID
)

Basically, this is saying, get all of the records that do not have the same card, punchdate, punchtime (making sure to not count the same row against itself.)

Answer 3

Select
  *
FROM
  table
WHERE
  NOT EXISTS (
    SELECT
      *
    FROM
      table AS lookup
    WHERE
          ID       <> table.ID
      AND Card      = table.Card
      AND PunchTime = table.PunchTime
      AND PunchDate = table.PunchDate
  )

Answer 4

Select ID 
from table 
where ID 
in 
(SELECT A.ID FROM (Select distinct ID, Card, PunchTime, PunchDate) A);

In your query you have written.. you should have only the same number of columns outside the IN clause as well as inside IN clause. If you are using a single column you don't require any brackets outside the IN clause but if you have multiple columns then you need to include them with in the brackets.

Thumb Rule: SELECT Col1,Col2 ..Coln FROM TABLE WHERE Col1 IN (SELECT Col1 FROM TABLE ...) (For Single Column)

SELECT Col1,Col2 ..Coln FROM TABLE WHERE (Col1,Col2..Coln) IN (SELECT Col1,Col2..Coln FROM TABLE ...) (For Multiple Columns)

Answer 5

Select ID from table where ID in (
select ID from (
   Select distinct ID, 
   Card, 
   PunchTime, 
   PunchDate
   FROM 
   OTHER_TABLE
   ) x
)

You can only have one column in the IN clause; therefore, you need to alias (x- on my answer) the result and just select the ID column from there. Also note that inside the subselect, you need to specify the table where you are selecting the extra columns from (See CAPS on my answer).