select a row based on a particular condition

Question

I have a table like this:

CustID     VisitTime
 1         2012-12-31 11:59
 1         2013-1-1 00:00
 1         2013-1-1 00:05
 2         2013-1-1 00:20
 2         2013-1-1 10:00
 3         2013-1-1 11:40

Now, I want to choose those new customers whose visited the site from Jan 1st 12:00AM to Jan 31st 11:59pm.

For example: cust ID 1 also visited in Dec, hence cust ID 1 should not be selected. Only 2 and 3 should be selected.

How do I incorporate this logic using t-sql?

score 5 · Answer 1 · answered Aug 19 '13 at 10:05

5

SELECt t.CustID
FROM Table1 t
GROUP BY t.CustID
HAVING MIN(VisitTime) >='01/01/2013 00:00:00' AND MIN(VisitTime) < '02/01/2013 00:00:00'
--same test on MAX(vistTime) if needed

SqlFiddle

answered Aug 19 '13 at 10:05

Raphaël Althaus

59,727
6
96
122

@dasblinkenlight hmmm, was just referring to http://stackoverflow.com/a/749663/961526 . Wrong, or ? – Raphaël Althaus Aug 19 '13 at 10:20
Oops, you're right, somehow I thought this was MySQL with its one second precision. In SQL Server with the `>= ... AND <` is better. – Sergey Kalinichenko Aug 19 '13 at 10:23

score 0 · Accepted Answer · answered Aug 19 '13 at 10:08

0

SELECT DISTINCT CUSTID
FROM T as T1
WHERE VisitTime between '2013-01-01 00:00' AND '2013-01-31 23:59'
AND NOT EXISTS (SELECT T.CustID FROM T 
                       WHERE T.CustId=T1.CustId and VisitTime < '2013-01-01')

answered Aug 19 '13 at 10:08

valex

23,966
7
43
60

1

You should also add some explanation, it is usually better than a code-only answer. – Matteo Tassinari Aug 19 '13 at 10:25

select a row based on a particular condition

2 Answers2