ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,211 through 3,225 (of 10,143 total)

RE: Running total error

Here's a quick rejig of that sample data, and a reformat of the code. Is it only the running total column which is incorrect?
IF OBJECT_ID ('tempdb..#GLT') IS NOT NULL DROP...
^{“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

June 24, 2014 at 5:21 am

#1724285
RE: Help finding a date

Sachin Nandanwar (6/24/2014)
ChrisM@Work (6/24/2014)
With a duration of 20, this returns an incorrect timeslot (2014-06-23 08:30:00.000 - 2014-06-23 09:15:00.000)
No it returns
(2014-06-23 09:15:00.0002014-06-23 10:00:00.000)
Try also DECLARE @duration INT = 120, which...
^{“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

June 24, 2014 at 4:34 am

#1724271
RE: Help finding a date

Sachin Nandanwar (6/24/2014)
ChrisM@Work (6/24/2014)
With a duration of 20, this returns an incorrect timeslot (2014-06-23 08:30:00.000 - 2014-06-23 09:15:00.000)
No it returns
(2014-06-23 09:15:00.0002014-06-23 10:00:00.000)
Sorry, my mistake, I extended the dataset 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

June 24, 2014 at 4:08 am

#1724265
RE: Help finding a date

DECLARE @duration INT=20
SELECT TOP 1
time_sched_start_dt = MAX(CASE WHEN dir = 'S' THEN dt END),
time_sched_end_dt = MAX(CASE WHEN dir = 'E' THEN dt END)
FROM ( -- f
SELECT dir, dt, Grouper...
^{“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

June 24, 2014 at 2:57 am

#1724254
RE: Help finding a date

Sachin Nandanwar (6/24/2014)
The query is purely based on the sample data provided and all the assumptions stated in the earlier posts.
See if this helps.
DECLARE @duration INT=70
SELECT TOP 1 time_sched_start_dt,
...
^{“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

June 24, 2014 at 2:56 am

#1724253
RE: Update Part of a SQL field with charindex

Sachin Nandanwar (6/24/2014)
You can use REPLACE function as well.
declare @str as table( val varchar(20))
insert @str
select '12-FLA-2-02'
declare @update varchar(20)='11111'
select * from @str
update @str set val=replace(val,LEFT(val, CHARINDEX('-', val)-1) ,@update)
select * from @str
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

June 24, 2014 at 2:04 am

#1724247
RE: Are the posted questions getting worse?

Koen Verbeeck (6/23/2014)
😎 13000th point 😎
Also went over 9000 forum posts a while back. :w00t:
Top work geezer, most of 'em are pretty darned good posts too.
^{“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

June 24, 2014 at 1:47 am

#1724244
RE: Help\Advice on building a query

No worries, thanks for the feedback. You'll find some discussion of the method and a link to IBG's original code here.
^{“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

June 24, 2014 at 1:43 am

#1724243
RE: Apology for today's article

Koen Verbeeck (6/23/2014)
chrisn-585491 (6/23/2014)
So, yeah... Sad...
Sad is the word. Sadder still is that at my work a colleague had a bet to guess the background of the author... 🙁
Unfortunately,...
^{“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

June 23, 2014 at 9:44 am

#1724130
RE: Case Where Clause for Datetime from Varchar field

-- Simplify THE query using (CROSS) APPLY:
select case
when cs.COMMENT like '%CED:%' and DateFilter > convert(varchar(10),getdate(),103) then DateFilter
when cs.COMMENT like '%CED:%' and DateFilter <= convert(varchar(10),getdate(),103) then DateResult
when cs.COMMENT not...
^{“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

June 23, 2014 at 6:37 am

#1724046
RE: Just curious, what are your SQL pet peeves ?

andrew gothard (6/20/2014)
Ed Wagner (6/20/2014)
andrew gothard (6/20/2014)
Ed Wagner (6/19/2014)
andrew gothard (6/19/2014)
below86 (6/18/2014)
andrew gothard (6/18/2014)
below86 (6/16/2014)
andrew gothard (6/15/2014)
Koen Verbeeck (6/14/2014)
Badly phrased, sorry. In a column that should be a date,...
^{“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

June 23, 2014 at 5:18 am

#1724013
RE: Running total error

pwalter83 (6/23/2014)
ChrisM@Work (6/23/2014)
Paul, you have two threads open which cover the same problem. Can I suggest you choose one of them and close the other? You can do that 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

June 23, 2014 at 5:10 am

#1724008
RE: Running total error

Paul, you have two threads open which cover the same problem. Can I suggest you choose one of them and close the other? You can do that by posting something...
^{“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

June 23, 2014 at 4:25 am

#1723995
RE: Running total error

pwalter83 (6/23/2014)
ChrisM@Work (6/23/2014)
pwalter83 (6/23/2014)
... I need to use the following query to get the desired resultset. I think that is where the problem is
If the query you "need to use"...
^{“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

June 23, 2014 at 3:14 am

#1723947
RE: Running total error

pwalter83 (6/23/2014)
... I need to use the following query to get the desired resultset. I think that is where the problem is
If the query you "need to use" doesn't work...
^{“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

June 23, 2014 at 2:49 am

#1723933

Viewing 15 posts - 3,211 through 3,225 (of 10,143 total)