ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,356 through 2,370 (of 10,143 total)

RE: How to partition and add sequence/rownumber using CTE or Row_number()

sqlbidev (7/6/2015)
Hi there,
I have the following table struction, lets call it table A.
bookidstartdate endate
2001 2000-01-01 2000-01-05
3001 ...
^{“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

July 6, 2015 at 7:25 am

#1810428
RE: How to partition and add sequence/rownumber using CTE or Row_number()

sqlbidev (7/6/2015)
Hi there,
I have the following table struction, lets call it table A.
bookidstartdate endate
2001 2000-01-01 2000-01-05
3001 ...
^{“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

July 6, 2015 at 6:40 am

#1810415
RE: Operand type clash: int is incompatible with date

mandania (7/3/2015)
they are both date type
Here's your error:
case
when Rownum<= datediff(day, salesdate, baseenddate) then DATEADD(mm, RowNum, salesdate)
else 0 end
You have two different datatypes returned by your CASE....
^{“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

July 3, 2015 at 6:58 am

#1810203
RE: Operand type clash: int is incompatible with date

mandania (7/3/2015)
Hello, i have been trying to solve this for sometime but i cant get around it. hopefully somebody can help me out here.
this is a piece of my code:
...
^{“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

July 3, 2015 at 6:33 am

#1810197
RE: Are the posted questions getting worse?

Koen Verbeeck (7/3/2015)
GilaMonster (7/3/2015)
TomThomson (7/3/2015)
But don't make the mistake of thinking that all-nighters are always a sign of problems, or long hours (how can long hours be "heroic"?).
No, I'm...
^{“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

July 3, 2015 at 6:28 am

#1810195
RE: Are the posted questions getting worse?

Sean Lange (7/2/2015)
jasona.work (7/2/2015)
Today is the kind of day (and it's only been an hour and a half since I got to work) that you clock watch...
OTOH, got plenty 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

July 2, 2015 at 7:19 am

#1809869
RE: Are the posted questions getting worse?

Ed Wagner (7/2/2015)
jasona.work (7/2/2015)
Today is the kind of day (and it's only been an hour and a half since I got to work) that you clock watch...
OTOH, got plenty 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

July 2, 2015 at 6:59 am

#1809856
RE: OVER clause with Aggregate function - Wierd

Run this tweak to observe a less irregular usage 😉
;WITH CTE AS
(
SELECT 1 AS ID, 10000 AS SALARY,GETDATE() AS DOJ
UNION ALL
SELECT 1, 13000,GETDATE() - 10
UNION ALL
SELECT 3, 190000,GETDATE() +...
^{“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

July 2, 2015 at 6:55 am

#1809853
RE: Are the posted questions getting worse?

Koen Verbeeck (7/2/2015)
ChrisM@Work (7/2/2015)
Koen Verbeeck (7/2/2015)
BL0B_EATER (7/2/2015)
Yesterday it was about 35C, and I was working from home with no A/C.
Sounds good to me... were you in the garden sipping on...
^{“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

July 2, 2015 at 6:47 am

#1809841
RE: Self Join alternative

Sam Garth (7/2/2015)
Thank you all for the suggestion, however If I was to run the following
INSERT INTO #Data
VALUES
(4,'Got Dressed','2015-07-02 06:00:00.000')
And then run my query, It would show me two entries...
^{“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

July 2, 2015 at 6:46 am

#1809839
RE: Are the posted questions getting worse?

Koen Verbeeck (7/2/2015)
BL0B_EATER (7/2/2015)
Yesterday it was about 35C, and I was working from home with no A/C.
Sounds good to me... were you in the garden sipping on a cool beverage...
^{“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

July 2, 2015 at 6:40 am

#1809831
RE: Are the posted questions getting worse?

Eirikur Eiriksson (7/2/2015)
BL0B_EATER (7/2/2015)
Yesterday was ridiculous in the UK - 34C.. and in an office with no A/C.
Didn't measure the temperature in the Central Line tube yesterday but I've been...
^{“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

July 2, 2015 at 6:40 am

#1809830
RE: Are the posted questions getting worse?

BL0B_EATER (7/2/2015)
ChrisM@Work (7/2/2015)
BL0B_EATER (7/2/2015)
Yesterday was ridiculous in the UK - 34C.. and in an office with no A/C.
That's not so good. Neither were the delays on the train network -...
^{“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

July 2, 2015 at 6:37 am

#1809827
RE: Are the posted questions getting worse?

BL0B_EATER (7/2/2015)
Yesterday was ridiculous in the UK - 34C.. and in an office with no A/C.
That's not so good. Neither were the delays on the train network - it took...
^{“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

July 2, 2015 at 5:17 am

#1809794
RE: Self Join alternative

Sam Garth (7/1/2015)
Hi
I am trying to increase performance of a query that joins a table 19 times.
The current example works similarly to below...
CREATE TABLE #Data(PersonID INT, Activity nvarchar(200), ActivityTime DATETIME)
INSERT...
^{“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

July 1, 2015 at 7:31 am

#1809472

Viewing 15 posts - 2,356 through 2,370 (of 10,143 total)