sathiyan00 (10/8/2013)

---

but even this is also taking more time for 25K records.

with Improved SQL 8K “CSV Splitter” Function its taking around 25 sec

with xml its taking around 30sec

with substring its...

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...