ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,196 through 3,210 (of 10,143 total)

RE: Are the posted questions getting worse?

Koen Verbeeck (7/2/2014)
Jo Pattyn (7/1/2014)
Funny! :w00t:
Anyway the Belgium Red Devils got past the USA soccer team after a thrilling match.
It was an awesome game.
The US team (actually just the goalkeeper...
^{“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, 2014 at 7:26 am

#1726480
RE: Grouping by percentage of time worked by person

Dohsan (7/2/2014)
ChrisM@Work (7/2/2014)
Try this:
SELECT PersonID, StartMonth = MIN(MONTHID), EndMonth = MAX(MONTHID), LOADPCT = PercentLoad
FROM (
SELECT *,
[Grouper] = MONTHID - ROW_NUMBER() OVER(PARTITION BY PersonID, PercentLoad ORDER BY MONTHID)
...
^{“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, 2014 at 7:16 am

#1726470
RE: Grouping by percentage of time worked by person

Try this:
SELECT PersonID, StartMonth = MIN(MONTHID), EndMonth = MAX(MONTHID), LOADPCT = PercentLoad
FROM (
SELECT *,
[Grouper] = MONTHID - ROW_NUMBER() OVER(PARTITION BY PersonID, PercentLoad ORDER BY MONTHID)
FROM #TestProject2...
^{“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, 2014 at 7:09 am

#1726465
RE: Running total error

pwalter83 (7/2/2014)
ChrisM@Work (7/2/2014)
pwalter83 (7/2/2014)
... it does not calculate the ratio for the first row...
If your calculation depends upon a value from a previous row, what should the result of 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

July 2, 2014 at 4:46 am

#1726436
RE: Running total error

pwalter83 (7/2/2014)
... it does not calculate the ratio for the first row...
If your calculation depends upon a value from a previous row, what should the result of the calculation be...
^{“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, 2014 at 3:42 am

#1726422
RE: How To Return Zero If No Rows Found

Your query has four output columns:
SELECT
ot.OperationalTypeAlternateKey AS [OperationalTypeAlternateKey]
,ot.OperationalType AS [Entry Type]
,Convert(Varchar, DateDayOfMonth)+' '+DateMonthName + ' '+ Convert(Varchar,DateYear) AS [Date]
,COUNT(*) AS [Total]
FROM
If you're returning a single row...
^{“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 25, 2014 at 5:38 am

#1724587
RE: Are the posted questions getting worse?

djj (6/25/2014)
Just saw a post (on another site) that the person had part of a query list but no quest was asked. Someone posted "What is your question?" 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

June 25, 2014 at 5:18 am

#1724580
RE: Running total error

The issue is a lack of clarity.
Don't give up. Have a look at the result of this:
IF OBJECT_ID ('tempdb..#GLT') IS NOT NULL DROP TABLE #GLT
CREATE TABLE #GLT (
[Sequence] [int] NULL,
[WK...
^{“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 25, 2014 at 3:56 am

#1724549
RE: Running total error

pwalter83 (6/25/2014)
ChrisM@Work (6/25/2014)
pwalter83 (6/25/2014)
ChrisM@Work (6/25/2014)
pwalter83 (6/24/2014)
ChrisM@Work (6/24/2014)
pwalter83 (6/24/2014)
Jeff Moden (6/24/2014)
pwalter83 (6/24/2014)
ChrisM@Work (6/24/2014)
Here's a quick rejig of that sample data, and a reformat of the code. Is it only the running...
^{“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 25, 2014 at 3:40 am

#1724545
RE: Running total error

pwalter83 (6/25/2014)
ChrisM@Work (6/25/2014)
pwalter83 (6/24/2014)
ChrisM@Work (6/24/2014)
pwalter83 (6/24/2014)
Jeff Moden (6/24/2014)
pwalter83 (6/24/2014)
ChrisM@Work (6/24/2014)
Here's a quick rejig of that sample data, and a reformat of the code. Is it only the running total column...
^{“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 25, 2014 at 2:57 am

#1724539
RE: Running total error

pwalter83 (6/24/2014)
ChrisM@Work (6/24/2014)
pwalter83 (6/24/2014)
Jeff Moden (6/24/2014)
pwalter83 (6/24/2014)
ChrisM@Work (6/24/2014)
Here's a quick rejig of that sample data, and a reformat of the code. Is it only the running total column which 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

June 25, 2014 at 1:52 am

#1724533
RE: Running total error

pwalter83 (6/24/2014)
Jeff Moden (6/24/2014)
pwalter83 (6/24/2014)
ChrisM@Work (6/24/2014)
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...
^{“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 9:26 am

#1724377
RE: Help finding a date

Rog Saber (6/23/2014)
I'm having trouble writing a query and could use some help please.
I want to find the earliest time_sched_start_dt, time_sched_end_dt where col_val is null
and the datediff between time_sched_start_dt 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

June 24, 2014 at 8:54 am

#1724366
RE: Help finding a date

Change
HAVING DATEDIFF(MINUTE,MAX(CASE WHEN dir = 'S' THEN dt END),MAX(CASE WHEN dir = 'E' THEN dt END)) > @duration
to
HAVING DATEDIFF(MINUTE,MAX(CASE WHEN dir = 'S' THEN dt END),MAX(CASE WHEN dir...
^{“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 7:50 am

#1724332
RE: Help finding a date

Rog Saber (6/24/2014)
Here is what I tried but I believe I am still having an issue. If I put the duration at 120 minutes it returns:
time_sched_start_dtenddt
2014-06-23 09:15:00.0002014-06-23 11:30:00.000
I don't want...
^{“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 7:45 am

#1724327

Viewing 15 posts - 3,196 through 3,210 (of 10,143 total)