ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,361 through 3,375 (of 10,143 total)

RE: Drop Identity column from temp table

And you will only ever change FundID, ProductID or both?
^{“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

May 12, 2014 at 2:34 am

#1712556
RE: Drop Identity column from temp table

hoseam (5/12/2014)
Can I also mention my Code has changed to this:
declare
@New_Value varchar(50),
@Col varchar(50),
@TableName varchar(50)
select @TableName = [TABLE_NAME]
from [Hosea_tblDef_Cloning_Table]
select @New_Value = [NEW_VALUE]
from [Hosea_tblDef_Cloning_Table]
SELECT '[' + t.table_schema + '].[' + t.table_name...
^{“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

May 12, 2014 at 2:29 am

#1712552
RE: Drop Identity column from temp table

CREATE TABLE [dbo].[Hosea_tblDef_RETURNS](
[RETURNS_ID] [int] IDENTITY(1,1) NOT NULL,
[REPORT_ID] [varchar](50) NOT NULL,
[COMPANY] [varchar](150) NULL,
[PRODUCT_TYPE] [varchar](150) NULL,
[PRODUCT_ID] [varchar](150) NULL,
[PRODUCT_DESC] [varchar](150) NULL,
[FUND_ID] [varchar](150) NULL,
[FUND_INCEPTION_DATE] [varchar](6) NULL,
[RETURNS_TYPE_KEY] [varchar](150) NULL,
[RETURNS_TYPE_FILTER] [varchar](150) NULL,
[RETURNS_MONTH_FILTER] [varchar](150) NULL,
CONSTRAINT [PK_tblDef_RETURNS] PRIMARY...
^{“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

May 12, 2014 at 2:11 am

#1712547
RE: Drop Identity column from temp table

hoseam (5/12/2014)
Any luck Chris??
No. Running your script generates the following error.
Msg 110, Level 15, State 1, Line 22
There are fewer columns in the INSERT statement than values specified in 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

May 12, 2014 at 2:01 am

#1712544
RE: Drop Identity column from temp table

Same for table Hosea_tblDef_RETURNS please - ddl and dml. Cheers.
^{“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

May 9, 2014 at 6:56 am

#1712143
RE: Drop Identity column from temp table

Can you provide a few sample rows for table [dbo].[Hosea_tblDef_Cloning_Table] please?
As INSERT's to the table ddl you've already posted. Cheers
^{“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

May 9, 2014 at 6:32 am

#1712133
RE: Cumulative sum of previous rows

simonc 39536 (5/9/2014)
...
In this instance you should use ROWS because it is the more correct solution for what you are doing,
it just so happens that the ORDER 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

May 9, 2014 at 6:29 am

#1712131
RE: How to make sure that all the rows are inserted into datamodel and then truncate staging table

What are you using to perform this ETL job? Is it just TSQL or are you using SSIS or some other tool?
"In my ETL job I would like to truncate...
^{“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

May 9, 2014 at 3:24 am

#1712091
RE: T-SQL Calculation

J Livingston SQL (5/8/2014)
ChrisM@Work (5/8/2014)
Otherwise you will get answers like this:
SELECT *, Trend = ([Week]/5.0) * MAX(Forecast) OVER(PARTITION BY [TYPE])
FROM #REport
Which exactly meets your requirements (apart from the trivial matter...
^{“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

May 8, 2014 at 9:22 am

#1711923
RE: T-SQL Calculation

Otherwise you will get answers like this:
SELECT *, Trend = ([Week]/5.0) * MAX(Forecast) OVER(PARTITION BY [TYPE])
FROM #REport
Which exactly meets your requirements (apart from the trivial matter of rounding) but is...
^{“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

May 8, 2014 at 8:50 am

#1711900
RE: Cumulative sum of previous rows

Nice article, Ben - does exactly what it says on the tin, although many folks lurking around here would be more familiar with the name Running Total.
The old version of...
^{“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

May 8, 2014 at 1:49 am

#1711771
RE: Are the posted questions getting worse?

Ed Wagner (5/7/2014)
ChrisM@Work (5/7/2014)
The OP's posting history suggests a haphazard way of working which can be difficult for responders to correctly interpret.
I think this is a very nice way 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

May 7, 2014 at 7:00 am

#1711477
RE: DEMO_CODE = 'DE1'

SELECT [stuff]
FROM [thing]
WHERE
(demo_code = 'DE1' AND demo_vchar_data = 'U')
OR
(demo_code = 'RAP' AND demo_vchar_data = 'YES')
^{“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

May 7, 2014 at 6:15 am

#1711446
RE: Transactions problem

Remove the batch separator.
^{“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

May 7, 2014 at 5:41 am

#1711439
RE: Are the posted questions getting worse?

DBCC TIMEWARP?
I was tempted to suggest that only IBG and Jeff's queries can run that fast. Also, that queries running that fast can overheat the processor and should be slowed...
^{“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

May 7, 2014 at 4:06 am

#1711401

Viewing 15 posts - 3,361 through 3,375 (of 10,143 total)