ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,851 through 2,865 (of 10,143 total)

RE: Comparing data for a certain day of the week by month for every year

-- Get all dates from two years ago (rolled back to the first of the month)
-- up to and including today. Include the day name and the relative number
-- of...
^{“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

October 22, 2014 at 5:24 am

#1754000
RE: Issue with a slow Query

-- You could try breaking up the query. Here's one way, there are a few possibilities:
SELECT
[wck.wck_id] = wck.wck_id,
[pd.mode_id] = pd.mode_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

October 22, 2014 at 3:42 am

#1753977
RE: Using >= or <= with DateTime parameter not working?

SELECT * FROM orders WHERE CAST(orderdate AS DATE) BETWEEN @begindate AND @enddate
http://sqlblog.com/blogs/rob_farley/archive/2010/01/22/sargable-functions-in-sql-server.aspx
^{“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

October 22, 2014 at 2:17 am

#1753950
RE: dual purpose OUTPUT parameter

Wouldn't it be easier and more natural to refactor this as a function?
^{“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

October 21, 2014 at 10:01 am

#1753810
RE: When Work Gets in the Way of Work

"However the last few years I find myself crunched to get work done in my work week".
Only the last few years? I've often wondered how you manage to get so...
^{“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

October 21, 2014 at 1:46 am

#1753679
RE: Extract text from numbers

compufreak (10/20/2014)
okay so i had a look at some other tables and the field is linked to another table.
so basically if the table contains prj(8345) then i am sure it...
^{“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

October 20, 2014 at 5:25 am

#1753465
RE: de-aggregate or Un-aggregate rows. whats best method?

You could use an inline tally table too:
CREATE TABLE #MyTable (OrderID INT IDENTITY(1,1), ItemName VARCHAR(20), UnitPrice INT, Quanity INT)
INSERT INTO #MyTable (ItemName, UnitPrice, Quanity)
VALUES ('car', 10, 2), ('Train', 20, 1),...
^{“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

October 20, 2014 at 5:16 am

#1753462
RE: Getting different results!

Thanks! Try this...
-- Sample data
CREATE TABLE #ADMTABLE (CLASS1 VARCHAR(15), IQREGIONID VARCHAR(10), USERTYPES1 TEXT)
INSERT INTO #ADMTABLE (CLASS1, IQREGIONID, USERTYPES1)
SELECT 'criteria', 'ID No', 'USERTEXT1, Source currency , USERTEXT2, Description for accounts ,...
^{“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

October 17, 2014 at 6:40 am

#1753130
RE: how to create Stored procedure?

GilaMonster (10/16/2014)
Look at the very first red underline in that screen shot (on the very first line), that's your problem.
Also, the name of the stored procedure.
^{“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

October 16, 2014 at 7:02 am

#1752845
RE: how to create Stored procedure?

Elliswhite (10/16/2014)
I also tried it with passing paremeter but still facing syntax error
Can you share the syntax error?
^{“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

October 16, 2014 at 6:56 am

#1752839
RE: how to create Stored procedure?

Elliswhite (10/16/2014)
Execute GetBooksbyBorrowerID
We're not mindreaders around here - did the procedure fail to create, run with an error, run but fail to generate results...?
^{“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

October 16, 2014 at 6:25 am

#1752819
RE: how to create Stored procedure?

1) You're calling a different stored procedure (SP_Task1) from the one you've just created (GetBooksbyBorrowerID).
Edit: must learn to read.
^{“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

October 16, 2014 at 6:14 am

#1752809
RE: Getting different results!

ams00601 (10/16/2014)
Hi Chris,
I tried using the code you gave but still no joy!
Any other ideas?
Not really. The code you've posted so far indicates that you may be a novice...
^{“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

October 16, 2014 at 4:04 am

#1752784
RE: Getting different results!

Gosh that's horrible! Have a play with this:
SELECT x1.UserTypes, x2.pos
FROM ADMTABLE AT
CROSS APPLY (SELECT UserTypes = CAST(AT.USERTYPES1 AS VARCHAR(MAX))) x1
CROSS APPLY (SELECT pos = CHARINDEX('Working Paper',x1.UserTypes)) x2
WHERE AT.CLASS1 = 'criteria'
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

October 15, 2014 at 8:42 am

#1752571
RE: Getting different results!

ams00601 (10/15/2014)
Hi,
I've tried casting and converting to varchar(max), but still no joy!
Can you post some of your code? Guessing could take a while...
^{“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

October 15, 2014 at 7:26 am

#1752523

Viewing 15 posts - 2,851 through 2,865 (of 10,143 total)