CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2012subquery
Anyname Donotcare
Anyname Donotcare

Reputation: 11393

How to get data with a must different conditions?

If i have tables with the following structure :

USERS : USERID,NAME

TIMETB : CHECKTIME,Sysdate,modified,USERID

If i have sample data like this :

USERS :

   USERID    NAME
    434      moh
    77       john
    66       yara

TIMETB :

CHECKTIME              USERID  modified
2015-12-21 07:20:00.000  434      0
2015-12-21 08:39:00.000  434      2
2015-12-22 07:31:00.000  434      0
2015-12-21 06:55:00.000  77       0
2015-12-21 07:39:00.000  77       0
2015-12-25 07:11:00.000  66       0
2015-12-25 07:22:00.000  66       0
2015-12-25 07:50:00.000  66       2
2015-12-26 07:40:00.000  66       2
2015-12-26 07:21:00.000  66       2

Now i want to get the users who have two or more different transactions(modified) at the same date :

The result i expect is :

CHECKTIME               USERID  modified  NAME
2015-12-21 07:20:00.000  434      0       moh
2015-12-21 08:39:00.000  434      2       moh
2015-12-25 07:11:00.000  66       0       yara
2015-12-25 07:22:00.000  66       0       yara
2015-12-25 07:50:00.000  66       2       yara

I write the following query but i get more than i expect i mean i get users who have transactions of the same (modified) !!.

SELECT a.CHECKTIME,
       a.Sysdate,
       (CASE WHEN a.modified = 0 THEN 'ADD' ELSE 'DELETE' END) AS modified,
       b.BADGENUMBER,
       b.name,
       a.Emp_num AS Creator
FROM TIMETB a
INNER JOIN Users b ON a.USERID = b.USERID
WHERE YEAR(checktime) = 2015
    AND MONTH(checktime) = 12
    AND (
        SELECT COUNT(*)
        FROM TIMETB cc
        WHERE cc.USERID = a.USERID
            AND CONVERT(DATE, cc.CHECKTIME) = CONVERT(DATE, a.CHECKTIME)
            AND cc.modified IN (0, 2)
    ) >= 2
    AND a.modified IS NOT NULL
    AND a.Emp_num IS NOT NULL

Upvotes: 0

Views: 37

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You use window functions for this:

select t.*
from (select t.*,
             count(*) over (partition by userid, cast(checktime as date)) as cnt
      from timetb t
     ) t
where cnt >= 2;

If you want the name, just join in the appropriate table.

EDIT:

If you want different values of a column, a simple way is to compare the min and max values:

select t.*
from (select t.*,
             min(modified) over (partition by userid, cast(checktime as date)) as minm,
             max(modified) over (partition by userid, cast(checktime as date)) as maxm
      from timetb t
     ) t
where minm <> maxm;

Upvotes: 1

Related Questions