ChrisM@Work

Forum Replies Created

Viewing 15 posts - 8,296 through 8,310 (of 10,143 total)

RE: Creating view from stored procedure results

Can you explain the context?
^{“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

January 4, 2010 at 2:29 am

#1098312
RE: insert statement

adaa_2007 (1/4/2010)
insert statement with join or with multiple tables
i hav 3 tables with no unique column. n i want to insert tjhe record in 3 tables in one insert query...
^{“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

January 4, 2010 at 2:27 am

#1098310
RE: StoreProcedure not reteurning any rows which containg local variables

abcim (12/23/2009)
Correct
YES
OK, now try running the code I posted a couple of pages earlier. Note that it doesn't do the calculation between the two values yet, but it does show...
^{“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

December 23, 2009 at 8:12 am

#1095943
RE: StoreProcedure not reteurning any rows which containg local variables

abcim (12/23/2009)
Yes this is very good Point to implement this
Well I'm relieved that we agree on this point because it paves the way to make your query much simpler 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

December 23, 2009 at 7:04 am

#1095897
RE: Help with a datediff calc

Hi Paul
Any chance of setting up some sample data for this? Say 12 rows, with just the columns which are referenced in your queries?
Cheers
ChrisM
^{“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

December 23, 2009 at 6:54 am

#1095894
RE: StoreProcedure not reteurning any rows which containg local variables

abcim (12/23/2009)
>>Can you answer this: for any one reportID passed into the sproc to retrieve your data, what is the reportID returned by this bit:
... SELECT Top 1 ReportId 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

December 23, 2009 at 6:35 am

#1095888
RE: StoreProcedure not reteurning any rows which containg local variables

abcim (12/23/2009)
What do you mean Function is a poor programming practice?
Good Lord, no - functions can be incredibly useful.
Can you answer this: for any one reportID passed into the sproc...
^{“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

December 23, 2009 at 6:06 am

#1095880
RE: SQL Pivot Query

oliver.morris (12/23/2009)
use the PIVOT T-SQL
http://www.tsqltutorials.com/pivot.php
Can you demonstrate how to use the PIVOT T-SQL with an unknown i.e. dynamic column set?
^{“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

December 23, 2009 at 5:11 am

#1095872
RE: StoreProcedure not reteurning any rows which containg local variables

abcim (12/23/2009)
Sorry this will not work
It's always good to see the OP (original poster) posting up their final solution, it's often a source of learning and discussion. However, I feel...
^{“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

December 23, 2009 at 4:08 am

#1095857
RE: trying to do an update with Distinct

Back to basics...
-- 1. Write a query which returns the rows which you want updated from the table you want to update.
SELECT DISTINCT Confirmed, Attended, R.ParticipantID, NameFirst, NameLast, C.ClassID, Title
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

December 23, 2009 at 3:35 am

#1095847
RE: Sorting and Ranking

SqlUser-369680 (12/23/2009)
Hi,
Any info on how to sort and rank 4 columns at the same time.
I have 4 columns
Select Salary, TransAllowance, HouseAllw, Cash from Payroll
1st column should be asc - 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

December 23, 2009 at 2:46 am

#1095836
RE: Are the posted questions getting worse?

john.arnott (12/22/2009)
Lynn Pettis (12/22/2009)
I'm starting to notice a trend in a few questions where people expect tables to store records in a specific order. Anyone else seeing that trend?
(Okay,...
^{“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

December 23, 2009 at 2:21 am

#1095829
RE: Help with a datediff calc

SELECT pickdate,
rownum,
datediff(day, nextd.pickdate, d.pickdate) AS StatusDays
FROM #DaysinStatus AS d
LEFT JOIN #DaysinStatus AS nextd ON nextd.rownum = d.rownum + 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

December 22, 2009 at 10:20 am

#1095521
RE: Data reformatting question

Gosh that was quick. Here's my belated tardy effort:
-- Create a sample tally table
SELECT IDENTITY(int,1,1) as number
INTO #Numbers
from syscolumns
-- Create a sample data table
DROP TABLE #Sample
CREATE TABLE #Sample (SampleValue...
^{“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

December 22, 2009 at 10:06 am

#1095510
RE: StoreProcedure not reteurning any rows which containg local variables

Try this:
DECLARE @ReportId int, @ReportId2 INT
SET @ReportId = -- YOUR VALUE
SELECT Top 1 @ReportId2 = ReportId
FROM MaturitiesReports
WHERE [Report_Month] < (
SELECT [Report_Month]
FROM MaturitiesReports
WHERE [ReportId] = @ReportId)
ORDER...
^{“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

December 22, 2009 at 7:58 am

#1095411

Viewing 15 posts - 8,296 through 8,310 (of 10,143 total)