ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,026 through 5,040 (of 10,143 total)

RE: Insertion in table

This should get you started:
-- Inline tally from Jeff Moden et al.
DECLARE
@LastBookNo INT = 23,
@LastPageNo INT = 80,
@BooksToInsert INT = 2,
@PagesPerBook INT = 10
;WITH E1(N) AS (
...
^{“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 18, 2013 at 4:57 am

#1598001
RE: How to add below complex conditions in SQL script..

bhushan_juare (3/15/2013)
[font="Courier New"]Hi Chris,
I have done some modifications in my script.. and I am getting correct result set but their are some (im.MATNR As ITEM) entries who are not present...
^{“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 18, 2013 at 2:31 am

#1597979
RE: Get missing records

The #temp table isn't strictly necessary but may improve performance:
;WITH MassagedData AS (
SELECT Company,
MIN_Number = MIN(Number),
MAX_Number = MAX(Number),
rn = ROW_NUMBER() OVER(PARTITION BY Company ORDER BY MIN(Number))
FROM (
SELECT Company,...
^{“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 15, 2013 at 7:53 am

#1597499
RE: COMPUTE Clause problem

What version of SQL Server are you connected to?
^{“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 15, 2013 at 3:27 am

#1597396
RE: Are the posted questions getting worse?

L' Eomot Inversé (3/14/2013)
Stefan Krzywicki (3/14/2013)
I'd be happy if DST was year-round again.
That would be alright here, but I'd hate that if I were in England in wintertime.
I've been to...
^{“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 15, 2013 at 2:34 am

#1597385
RE: table Joins and overby clause

Looks like whatever is causing the inconsistency has been lost in the simplification process. Try this:
-- Query 3:
SELECT a.col1, a.col2, b.col3
FROM tab1 a
FULL OUTER JOIN (
SELECT
col1,
col2,
col3--,
--row_number() over(partition by...
^{“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 15, 2013 at 2:26 am

#1597383
RE: how can i improve the sp performance.

mkarthikeyan.mohan (3/15/2013)
Dear friends,
i build the stored procedure was much more logic, each and every column having calculation.That was handled by function. each an every function also having loops.i was doing...
^{“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 15, 2013 at 2:16 am

#1597378
RE: How to make triple-pass UPDATE single-pass?

TheSQLGuru (3/14/2013)
Hmm, I wonder what happens if the codeid's repeat instead of being unique across all 3 code tables. Hopefully the way the aggregates are done will factor 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

March 14, 2013 at 7:38 am

#1596997
RE: How to make triple-pass UPDATE single-pass?

sqlnyc (3/12/2013)
I'm attempting to optimize some code that I was just handed, and I'm not exactly sure if what I want to do is possible. ...
I'd check that it's doing...
^{“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 14, 2013 at 7:31 am

#1596989
RE: adding a new not null bit column with default 0

GilaMonster (3/14/2013)
Yes.
ALTER TABLE <table name> ADD <column name> BIT NOT NULL DEFAULT 0;
Better.
^{“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 14, 2013 at 7:07 am

#1596979
RE: How to add below complex conditions in SQL script..

bhushan_juare (3/14/2013)
[font="Courier New"]Hi Chris,
Thanks for reply One thing i wanna ask you Do I need to add same logic for condition 2..?
Because what I am getting is one side results...
^{“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 14, 2013 at 7:02 am

#1596973
RE: adding a new not null bit column with default 0

ALTER TABLE MyTable ADD NewColumn BIT
GO
UPDATE MyTable SET NewColumn = 0
GO
ALTER TABLE MyTable ADD CONSTRAINT NewColumnDefault DEFAULT 0 FOR NewColumn
^{“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 14, 2013 at 6:59 am

#1596972
RE: How to add below complex conditions in SQL script..

Incidentally, your WHERE clause turns this LEFT JOIN SALES_DATA sd
ON so.VKBUR = sd.VKBUR into an INNER JOIN.
The same happens with table VBAP inner joined to SALES_DATA. If you...
^{“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 14, 2013 at 6:15 am

#1596949
RE: How to add below complex conditions in SQL script..

Something like this?
SELECT --DISTINCT -- not needed, covered by GROUP BY
year(sd.FKDAT) As YEARWISE_DATA,
so.vkbur As MARKET,
so.bezei As NAME,
sd.kunrg As PARTY,
cm.NAME1 As PARTY_NAME,
im.MATNR As...
^{“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 14, 2013 at 6:10 am

#1596948
RE: Complex SQL QUERY with DateDIFF

Eugene Elutin (3/14/2013)
ChrisM@Work (3/14/2013)
Eugene, that is absolutely spot on, and your last sole-user sample is an excellent demonstration of the distribution limitation. However, the sample sets provided by the OP...
^{“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 14, 2013 at 4:53 am

#1596919

Viewing 15 posts - 5,026 through 5,040 (of 10,143 total)