ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,931 through 3,945 (of 10,143 total)

RE: select joining tables without any where clause

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...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 8, 2013 at 6:08 am

#1656693
RE: split performance

Not only a faster solution, but a comparison of many including the two you've listed: DelimitedSplit8k[/url] by Jeff Moden et al.
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 8, 2013 at 5:56 am

#1656687
RE: Filter Countries, States and Cities which in same row in one table.

Bhuvnesh (10/8/2013)
select * from TABLE where countryid = @country
select * from TABLE where countryid = @country OR stateID = @stateid
select * from TABLE where...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 8, 2013 at 5:26 am

#1656677
RE: Slow Script

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...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 7, 2013 at 1:25 am

#1656230
RE: I need help Please :(

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...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 7, 2013 at 1:20 am

#1656229
RE: Data Warehouse Update to Live Table

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...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 7, 2013 at 1:17 am

#1656228
RE: I need help Please :(

--------------------------------------------------------------------------------
-- 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...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 4, 2013 at 7:23 am

#1655892
RE: I need help Please :(

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...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 4, 2013 at 4:58 am

#1655834
RE: I need help Please :(

sharon27 (10/4/2013)
ChrisM@Work (10/4/2013)
Hi Sharon,
reader 1 is IN, reader 2 is OUT?
yes sir..
Phew! That's going to help so much. Did you try the code I posted yesterday?
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 4, 2013 at 3:32 am

#1655804
RE: I need help Please :(

Hi Sharon,
reader 1 is IN, reader 2 is OUT?
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 4, 2013 at 3:10 am

#1655790
RE: Help building query, please

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...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 4, 2013 at 3:04 am

#1655785
RE: I need help Please :(

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...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 4, 2013 at 2:19 am

#1655772
RE: split a comma delimited string into columns

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.
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 4, 2013 at 1:18 am

#1655759
RE: Replace

shobanapraveen (10/3/2013)
Can any one help to get quick results while running query in PostgreSQL?
when i run my query its taking such a long time due to huge data's in db.
Thanks,
Shobana
Have...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 4, 2013 at 1:04 am

#1655757
RE: I need help Please :(

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...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 3, 2013 at 6:17 am

#1655526

Viewing 15 posts - 3,931 through 3,945 (of 10,143 total)