ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,221 through 5,235 (of 10,143 total)

RE: Calculating a Moving Average

dwain.c (2/11/2013)
ChrisM@Work (2/11/2013)
Gabriel Priester wrote an article discussing the calculation of moving average. Various alternatives are covered in the discussion.
A QU for moving averages??? :w00t: Who'da thunk it!
It's almost 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

February 11, 2013 at 1:47 am

#1586421
RE: Calculating a Moving Average

Gabriel Priester wrote an article discussing the calculation of moving average. Various alternatives are covered in the discussion.
^{“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

February 11, 2013 at 1:04 am

#1586411
RE: All Possible Combinations Loop

Or possibly this:
-- GROUP BY ROLLUP and GROUP BY CUBE seem to be close.
-- Neither can handle more than 12 columns ("variables") in one sitting
-- 4 sample rows generate...
^{“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

February 8, 2013 at 8:51 am

#1586007
RE: How to Add Leading Zeros

Are you trying to turn these three values into a time? Try this:
SELECT
d.SendTime / 360000 AS Hour,
d.SendTime % 360000 / 6000 AS Minute,
d.SendTime % 6000 / 100...
^{“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

February 8, 2013 at 8:48 am

#1586004
RE: Finding patterns in rows (date ordered)

It's easy enough to fill in gaps. What you want to do after that is up to you. Here are a couple of ideas.
SET DATEFORMAT YMD
DECLARE @Shifts TABLE
(staffid int,...
^{“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

February 8, 2013 at 7:23 am

#1585956
RE: Sort by/Order By

Lynn Pettis (2/8/2013)
Knowledge test for wafw1971.
You do know that there is no guarantee on the order of the data returned by SQL Server if you don't have an 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

February 8, 2013 at 6:52 am

#1585933
RE: Sort by/Order By

wafw1971 (2/8/2013)
... I have been told to hardcode like the Financial Month Section below.
That would be
01 January
02 February
03 March
Are you sure you want to do 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

February 8, 2013 at 6:01 am

#1585909
RE: Sort by/Order By

wafw1971 (2/8/2013)
...
SELECT DISTINCT CalendarMonth
FROM Time3
ORDER BY CalendarMonth
I get January, February, March and not April, August, December.
No. When you ORDER BY CalendarMonth, it will do exactly that, not what you automagically...
^{“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

February 8, 2013 at 5:39 am

#1585897
RE: All Possible Combinations Loop

It's difficult to figure out exactly what you are looking for here, but I suspect that one or other of the GROUP BY extensions might just get you what 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

February 8, 2013 at 5:04 am

#1585875
RE: Cursor

Minnu (2/8/2013)
Hi Team,
- if cursor is not closed / deallcated, then below is the error msg
Msg 16915, Level 16, State 1, Line 7
A cursor with the name 'cur_emp' already exists.
Msg...
^{“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

February 8, 2013 at 4:22 am

#1585857
RE: Need Help in Selecting Random and Non Sequential Data

jeganbenitto.francis (2/8/2013)
Okies, Now i get the Row Numbers from you query.
But i have a small silly doubt again on this,
How can i show the ID (which is more than 8...
^{“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

February 8, 2013 at 4:14 am

#1585854
RE: Merge statement with openrowset

S_Kumar_S (2/8/2013)
HI All
Is it possible to run the MERGE statement using Openrowset or openquery? I tried with below statement:
You want to run this MERGE as a remote query on SERVER=MYSERVER....
^{“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

February 8, 2013 at 4:10 am

#1585853
RE: Need Help in Selecting Random and Non Sequential Data

Try this:
;WITH SampleData AS (
SELECT ID = RIGHT('0'+CAST(n AS VARCHAR(2)),2)
FROM (SELECT TOP 99 n = ROW_NUMBER() OVER(ORDER BY name) FROM SYS.columns) d
),
CutOfSample AS (
SELECT TOP(30) -- fetch more rows than...
^{“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

February 8, 2013 at 3:53 am

#1585845
RE: Need Help in Selecting Random and Non Sequential Data

How many rows does the table 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

February 8, 2013 at 3:24 am

#1585832
RE: Need Help in Selecting Random and Non Sequential Data

Since the numbers are no longer random, why not break the randomness rules again and use this very simple algorithm?
;WITH SampleData AS (
SELECT ID = RIGHT('0'+CAST(n AS VARCHAR(2)),2)
FROM (SELECT TOP...
^{“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

February 8, 2013 at 2:43 am

#1585814

Viewing 15 posts - 5,221 through 5,235 (of 10,143 total)