NineIron (10/8/2013)

---

So, I understand that a where clause isn't required in a join but, my understanding of a join is "comparing" a column from one table with a column from...

Not only a faster solution, but a comparison of many including the two you've listed: DelimitedSplit8k[/url] by Jeff Moden et al.

Bhuvnesh (10/8/2013)

---

select * from TABLE where countryid = @country

select * from TABLE where countryid = @country OR stateID = @stateid

select * from TABLE where...

homebrew01 (10/6/2013)

---

ChrisM@Work (10/1/2013)

---

This I reckon is the correct way to write your query:

select T2.COL1, T2.COL2, T1.COL1, T1.COL2, T1.COL3

from T1

join T2

on T1.COL4 = T2.COL3

where T2.COL4 != 'data1'

and T1.COL1 is not...

Jeff Moden (10/5/2013)

---

It's going to be very interesting if the company ever adds extra readers. 😉

We're talking about computers here and it always fascinates me that an IN/OUT reader system...

Have you looked at the Actual Execution Plan for the statement? It may also be worth examining the equivalent SELECT statement during a busy period. Run this, substituting your real...

--------------------------------------------------------------------------------

-- Modified sample dataset

--------------------------------------------------------------------------------

DROP TABLE EMPTMS

CREATE TABLE EMPTMS

(Uidfirstname varchar (50),

Uidlastname varchar(50),

Reader int,

dtdate datetime);

SET DATEFORMAT YMD

INSERT INTO EMPTMS (uidfirstname, uidlastname, reader, dtdate) VALUES

('001','Alvin','1','2013-09-20 6:30:000 '),

('001','Alvin','2','2013-09-20 17:50:000 '),

('001','Alvin','1','2013-09-21 6:30:000 '),

('001','Alvin','2','2013-09-21 17:50:000...

Ohhh...thanks Wolfie. My code addresses hanging IN or OUT punches but not in this manner. I'll have a play.

The data set I posted up includes your extras and a few...

Phew! That's going to help so much. Did you try the code I posted yesterday?

Hi Sharon,

reader 1 is IN, reader 2 is OUT?

You need to cross join the calendar with the list of servers, like this:

;WITH Calendar AS (

SELECT

Server_range.ServerName,

Today = d.d,

Tomorrow = DATEADD(DAY, 1, d.d)

FROM dbo._dates d

CROSS APPLY...

sharon27 (10/4/2013)

---

Hi wolfkillj sorry for confusing everyone. Our system has installed 2 readers at the Main door which everyone use punch as an access . thats is the reason why...

You could use Jeff Moden's string splitter [/url]and pivot the result. Alternatively, if the number of columns is known up front, a CROSS APPLY cascade [/url]would be quicker.

Have...

CREATE TABLE EMPTMS

(Uidfirstname varchar (50),

Uidlastname varchar(50),

Reader int,

dtdate datetime);

INSERT INTO EMPTMS (uidfirstname, uidlastname, reader, dtdate) VALUES

('001','Alvin','1','2013-09-20 6:30:000 '),

('001','Alvin','2','2013-09-20 17:50:000 '),

('002','Rebecca','1','2013-09-20 22:10:000 '),

('003','Aliyah','1','2013-09-20 9:08:000 '),

('002','Rebecca','2','2013-09-21 7:30:000 '),

('001','Alvin','1','2013-09-21 6:30:000 '),

('001','Alvin','2','2013-09-21 17:50:000 '),

('002','Rebecca','1','2013-09-21...