ChrisM@Work

Forum Replies Created

Viewing 15 posts - 4,246 through 4,260 (of 10,143 total)

RE: Why doesn't the substring work?

marc.eilander 13301 (7/31/2013)
Hi, Thank you for your response. However the goal is to only retrieve those lines starting with Int.nr.TXT:. I checked the data, all lines have a valid...
^{“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 31, 2013 at 4:09 am

#1637289
RE: Salesman Running Totals by Date problem...

Charlottecb (7/30/2013)
Hi Chris,
Firstly, thanks again for taking the time to help me...
The final script you wrote doesn't quite give me what I need - If I add an order for...
^{“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 31, 2013 at 2:08 am

#1637257
RE: T-SQL subquery

Have a look at the two queries you are UNIONing. The WHERE clauses are the exact opposite (bar a small oversight, WO.ORDER_DATE >= @vLAST_ORDER_DATE should be WO.ORDER_DATE > @vLAST_ORDER_DATE). That...
^{“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 31, 2013 at 1:51 am

#1637251
RE: Today's Random Word!

laurie-789651 (7/30/2013)
ChrisM@Work (7/30/2013)
JAZZ Master (7/30/2013)
Revenant (7/30/2013)
L' Eomot Inversé (7/29/2013)
crookj (7/29/2013)
sing4you (7/29/2013)
L' Eomot Inversé (7/29/2013)
The Dixie Flatline (7/29/2013)
JAZZ Master (7/29/2013)
Daniel Bowlin (7/29/2013)
ache
pain
gain
loss
profit
Seer
Sear
Sears
Craftsman
Crofter
Bonxie
Spoot
^{“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 31, 2013 at 12:56 am

#1637232
RE: Today's Random Word!

SQLRNNR (7/30/2013)
Ventilate
Ventolin
^{“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 31, 2013 at 12:55 am

#1637231
RE: help with incrementing

;WITH Source_table (VENDID, [TYPE]) AS
(SELECT 'ABC', 1 UNION ALL
SELECT 'ABC', 0 UNION ALL
SELECT 'ABC', 0 UNION ALL
SELECT 'XYZ', 1 UNION ALL
SELECT 'XYZ', 0 UNION ALL
SELECT 'XYZ', 0 UNION ALL
SELECT 'XYZ',...
^{“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 30, 2013 at 9:33 am

#1637045
RE: Today's Random Word!

JAZZ Master (7/30/2013)
Revenant (7/30/2013)
L' Eomot Inversé (7/29/2013)
crookj (7/29/2013)
sing4you (7/29/2013)
L' Eomot Inversé (7/29/2013)
The Dixie Flatline (7/29/2013)
JAZZ Master (7/29/2013)
Daniel Bowlin (7/29/2013)
ache
pain
gain
loss
profit
Seer
Sear
Sears
Craftsman
Crofter
^{“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 30, 2013 at 9:23 am

#1637038
RE: Salesman Running Totals by Date problem...

Charlottecb (7/30/2013)
Thanks ChrisM, that's great - it fixed my issue. Many thanks for your help.
Final question, any idea how to modify this to group by the week instead 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 30, 2013 at 8:23 am

#1637010
RE: Salesman Running Totals by Date problem...

Your calendar function contains a recursive CTE. You can use option(MAXRECURSION 0) or something to get around your error message. Here's the definition of my calendar iTVF:
ALTER FUNCTION [dbo].[IF_Calendar]
(
@StartDate...
^{“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 30, 2013 at 7:38 am

#1636980
RE: Select variable based upon sub query

thunderousity (7/30/2013)
ChrisM@Work (7/30/2013)
thunderousity (7/30/2013)
That doesn't work as the WHERE just selects that car
Gotcha. I think an efficient TSQL version might use EXISTS, something like this:
SELECT *
FROM MyTable
WHERE EXISTS (
...
^{“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 30, 2013 at 7:22 am

#1636972
RE: Fiscal Week in TSQL

Try this, Ryan.
Usage is like this:
SELECT *
FROM dbo.IF_Calendar ('2000-01-01','2013-12-31','monday')
It's hardcoded for your fiscal year.
CREATE FUNCTION [dbo].[IF_Calendar]
(
@StartDate DATE,
@EndDate DATE,
@FirstWeekDay VARCHAR(10) -- e.g. 'monday'
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN...
^{“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 30, 2013 at 6:23 am

#1636936
RE: Select variable based upon sub query

thunderousity (7/30/2013)
That doesn't work as the WHERE just selects that car
Gotcha. I think an efficient TSQL version might use EXISTS, something like this:
SELECT *
FROM MyTable
WHERE EXISTS (
...
^{“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 30, 2013 at 6:10 am

#1636932
RE: Salesman Running Totals by Date problem...

I use an inline tally table calendar generator, otherwise everything is the same as your environment:
;WITH Agg AS (
SELECT
s.SalesmanID,
s.Name,
c.DateRange,
o.OrderTotal,
seq = ROW_NUMBER() OVER(PARTITION BY s.SalesmanID ORDER BY c.DateRange)
FROM...
^{“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 30, 2013 at 6:04 am

#1636929
RE: CAN ANY ONE WRITE QUERY FOR BELOW DESCRIPTION!

crmitchell (7/30/2013)
ChrisM@Work (7/29/2013)
margarett.hance 40946 (7/29/2013)
select * from data where Desc like '%CA%'
The OP is now selling real estate in Argentina, 3 1/2 years later.
Margarett, if the leftmost part 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 30, 2013 at 5:32 am

#1636924
RE: Change sub query variable based upon sub query

Duplicate thread.
^{“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 30, 2013 at 4:44 am

#1636909

Viewing 15 posts - 4,246 through 4,260 (of 10,143 total)