ChrisM@Work

Forum Replies Created

Viewing 15 posts - 4,936 through 4,950 (of 10,143 total)

RE: Splitting the rows -- distributing on logic

What's confusing folks here is that this sample datainsert into #sample
select '11003291001-----NOB30000','0V003','A00A00',269,285,305,1 union all
select '11003291001-----NOB30000','03003','A00A00',287,30,305,2
is both an order and a description of the available stock. Split them out and...
^{“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

April 3, 2013 at 2:01 am

#1603047
RE: Calculate number of days missed per term

Thanks for the ddl and dml, it makes a real difference. I'd recommend you split this into two steps. It will make the end result easier to understand, test and...
^{“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

April 3, 2013 at 1:43 am

#1603046
RE: How will you optimize this Query (having many SELF (LEFT) Joins) ?

theashishtrivedi (4/2/2013)
Thanks for the reply Chris,
I have already posted some sample data in my earlier post. Due to some legal restrictions, I can't post more data.
Can you please read the...
^{“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

April 2, 2013 at 4:31 am

#1602736
RE: Deadlock on update command

The SELECT query is no longer using index IX_suspect_status_rsn_shipopt, it's now using IX_sessionID, same as the UDATE query, because of the changes made in the last iteration. Using IX_sessionID, the...
^{“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

April 2, 2013 at 4:17 am

#1602730
RE: How will you optimize this Query (having many SELF (LEFT) Joins) ?

-- Some reformatting to make the code easier to read
-- and a small change to make the logic a little easier to follow.
-- Please check that this query returns the...
^{“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

April 2, 2013 at 2:35 am

#1602699
RE: Duplicate record check Left or Inner Join ?

Try modifying this instead;
;WITH Dupechecker AS (
SELECT ID, NUM, ECD, ?,
dupecount = COUNT(*) OVER(PARTITION BY Col1,Col2,COl3,COl4)
FROM Table_A
WHERE ID = 5628
)
INSERT INTO table_b (A,B, C, D)
SELECT
ID, NUM, ECD,
ERR_MSG_TXT...
^{“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

March 28, 2013 at 10:17 am

#1601733
RE: Duplicate record check Left or Inner Join ?

HiralChhaya (3/28/2013)
So Inner Join will work... right? I was confused weather to use Left Outer Join or not.
Thanks a lot for help
Yes, it will work.
You will have to correct...
^{“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

March 28, 2013 at 10:06 am

#1601722
RE: Duplicate record check Left or Inner Join ?

HiralChhaya (3/28/2013)
They are 4
select
A.ID, 1
A.NUM, 2
A.ECD, 3
Col1||Col2||Col3||Col4 as ERR_MSG_TXT 4 this is concatenation of columns of Table_A, the error_mesg_text will display duplicate rows data
Thanks
-- 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

March 28, 2013 at 10:00 am

#1601718
RE: Duplicate record check Left or Inner Join ?

HiralChhaya (3/28/2013)
table have only 4 columns
You've listed 7 columns in table A in your query - it would help us if you were to correct the column names.
^{“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

March 28, 2013 at 9:44 am

#1601706
RE: Duplicate record check Left or Inner Join ?

Neither, use OVER() with COUNT:
;WITH Dupechecker AS (
SELECT
A.ID,
A.NUM,
A.ECD,
Col1||Col2||Col3||Col4 as ERR_MSG_TXT,
dupecount = COUNT(*) OVER(PARTITION BY Col1,Col2,COl3,COl4)
FROM Table_A
WHERE A.ID = 5628
)
SELECT *
FROM Dupechecker
WHERE dupecount > 1
^{“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

March 28, 2013 at 9:32 am

#1601695
RE: select case with multiple

kmundt (3/28/2013)
The status is always being set, because I require them in .NET to select something, but even when they select both and it's 'B' it just selects the Closed...
^{“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

March 28, 2013 at 8:29 am

#1601647
RE: some one write query for this problem

writearjun53 (3/28/2013)
Hello Kingston Dhasian,
I have tried this query but it has show error "Invalid column name DS.ITEM".
I have converted this query as my table structure.
Regards,
Arjun
You will need to a) read...
^{“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

March 28, 2013 at 5:36 am

#1601565
RE: Performance ISSUE by USING CURSOR HELP !!!!

ERIC CRUDELI (3/28/2013)
4 millions from DI81SW (#TEMP1)
I have to insert 1.3 millions (#TEMP2)
I used the following script but I'm anxious because I have to insert 1.3 millions of line :doze:
Are...
^{“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

March 28, 2013 at 4:20 am

#1601532
RE: Performance ISSUE by USING CURSOR HELP !!!!

Please run this query and post the result:
SELECT COUNT(*)
FROM [DI81SW].[CNET_EXP].[dbo].[NOYAU_NUM_TELEPHONE] AS CNet WITH (NOLOCK)
WHERE (CNet.[SMS] = 1)
^{“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

March 28, 2013 at 3:47 am

#1601514
RE: Performance ISSUE by USING CURSOR HELP !!!!

ERIC CRUDELI (3/28/2013)
Yes all the time on DI628SW. DI81SW server linked
Thanks. Final question:
SELECT COUNT(*)
FROM [DI81SW].[CNET_EXP].[dbo].[NOYAU_NUM_TELEPHONE] AS CNet WITH (NOLOCK)
WHERE (CNet.[SMS] = 1)
How many rows are returned by this?
^{“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

March 28, 2013 at 2:39 am

#1601490

Viewing 15 posts - 4,936 through 4,950 (of 10,143 total)