ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,911 through 2,925 (of 10,143 total)

RE: Hourly

One hour, the hour before the hour we are in:
SELECT
timeStart = DATEADD(hour,DATEDIFF(hour,0,getdate())-1,0),
timeEnd =DATEADD(hour,DATEDIFF(hour,0,getdate()),0)
^{“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

September 24, 2014 at 5:20 am

#1747788
RE: Need help on a query in SQL 2005

-- Since group allocation must take place sequentially row by row, your good options
-- are quite limited. This solution uses a recursive CTE, you could also consider the
-- Quirky...
^{“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

September 23, 2014 at 3:20 am

#1747345
RE: SQL SERVER Comma Separated values XML PATH

cnu1252 (9/22/2014)
HI
Thanks for your help
Still facing the same problem.Actually the original query is mentioned below.
changed the column MenuName as NodeTraversed.
First Iam going to mentioned below function which splits...
^{“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

September 23, 2014 at 1:57 am

#1747319
RE: SQL Query Taking Too Long

-- this would help as Grant suggested, however refactoring the whole query
-- to work as a cross-tab would be far more efficient.
SELECT
aa.[AccountID],
aa.[AcctCode],
aa.[AcctName],
aa.[Segment0],
aa.[SegmentName1],
aa.[SegmentShortName1],
...
^{“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

September 12, 2014 at 9:44 am

#1745135
RE: Checking sequence of codes

richardmgreen1 (9/12/2014)
Thanks Chris
I'm going to bookmark this little lot as I'm fairly certain it will come up again (and again, and again).
You're welcome.
I'd recommend you work through the delimitedsplit article....
^{“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

September 12, 2014 at 9:04 am

#1745120
RE: Checking sequence of codes

richardmgreen1 (9/12/2014)
Thanks Chris
Just to check my understanding of this, it looks like the splitter will help to give each code it's own line number to start with.
Then, the SELECT after...
^{“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

September 12, 2014 at 8:51 am

#1745117
RE: Amend function to include time values

Choose how you wish to tackle DaysToCount, round up or down or whatever and incorporate it into the function.
CREATE FUNCTION [dbo].[CountFractionalHolidays]
/*
Put in some explanatory notes 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

September 12, 2014 at 8:19 am

#1745099
RE: Checking sequence of codes

Here you go, Richard. I think you know about Jeff Moden's splitter already:
;WITH SplitData AS (
SELECT *
FROM (SELECT ID = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), Code FROM #test) t
CROSS...
^{“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

September 12, 2014 at 8:05 am

#1745096
RE: Checking sequence of codes

Another question:
come before
Q07
Q08.
Both of them or either of them?
^{“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

September 12, 2014 at 7:26 am

#1745079
RE: Amend function to include time values

-- Following on from the last post, here's the same query as above with a couple of amendments.
-- Firstly, I've put the remaining filters into place, the two flag...
^{“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

September 12, 2014 at 7:08 am

#1745068
RE: Checking sequence of codes

richardmgreen1 (9/12/2014)
Hi all
I've run across a bit of a problem with checking the sequence of codes in a string.
The codes are semi-colon separated in this string, but they are also...
^{“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

September 12, 2014 at 6:38 am

#1745061
RE: Performance issue in store procedure

polo.csit (9/12/2014)
Table
Idintno410 0 no(n/a)(n/a)NULL
Titlenvarcharno1000 no(n/a)(n/a)Latin1_General_CI_AS
CompanyNamenvarcharno1000 no(n/a)(n/a)Latin1_General_CI_AS
.
.
.
Index
IX_CompanyNamenonclustered located...
^{“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

September 12, 2014 at 6:34 am

#1745059
RE: Amend function to include time values

Much better, thank you.
2. The 2nd scenario is the issue I am having which is not covered by the function.
If the start date is on a saturday (Sept 6th)...
^{“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

September 12, 2014 at 6:17 am

#1745054
RE: Amend function to include time values

Can you amend that post to use a 24-hour clock please?
^{“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

September 12, 2014 at 5:45 am

#1745040
RE: Amend function to include time values

pwalter83 (9/12/2014)
ChrisM@Work (9/12/2014)
If the existing function has been in use for some time then you should assume that it is tried and tested and wherever it’s in use, any changes...
^{“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

September 12, 2014 at 5:03 am

#1745027

Viewing 15 posts - 2,911 through 2,925 (of 10,143 total)