ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,836 through 2,850 (of 10,143 total)

RE: BETWEEN values conundrum

Can you post the actual code you are using?
^{“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 24, 2014 at 3:47 am

#1754575
RE: Derived table query

No problem - can you post the whole query?
^{“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 24, 2014 at 3:19 am

#1754565
RE: Rank duplicates, but only rows involved in duplicates

jeff.born (10/23/2014)
That worked, I knew there had to be a better way and that is it.
Not so fast...ROW_NUMBER() over a whole table is expensive. Scott's method is usually far more...
^{“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 24, 2014 at 2:14 am

#1754547
RE: BETWEEN values conundrum

The sample customer matches two rows in the postcodes table:
SELECT
c.SHORT_POSTCODE,
c1.SHORT_POSTCODESquish,
x.StartPostCodeSquish,
x.EndPostCodeSquish,
CASE WHEN c1.SHORT_POSTCODESquish BETWEEN x.StartPostCodeSquish AND x.EndPostCodeSquish THEN 'Match' ELSE '' END
FROM h_Customer c
CROSS APPLY (SELECT SHORT_POSTCODESquish...
^{“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 23, 2014 at 9:49 am

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

vee_jess (10/23/2014)
Chris,
This seems to be doing the trick :). I will carry on testing and will either mark this as answered or come back with more questions.
Thank you very much.
Excellent...
^{“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 23, 2014 at 9:28 am

#1754386
RE: BETWEEN values conundrum

pnr8uk (10/23/2014)
...in the input table I have a postcode BETWEEN two values eg: CV1 and CV10 I want to get a third value from the look up table. ...
There won't...
^{“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 23, 2014 at 7:52 am

#1754349
RE: Adding an 'ORDER BY' clause removes part of a list.

This is interesting. If you order by a column name then the expected result is returned. If you order by an ordinal position, 1 or 2, you get an 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 23, 2014 at 7:44 am

#1754345
RE: Inconsistent Query Results in SQL Server 2008 R2

Have you checked that you're reading from data and not cache, in each case?
http://en.helpdoc-online.com/powerbuilder_9.0/source/pbugp154.htm
^{“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 23, 2014 at 5:48 am

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

Some more sample data would probably make sense. Have a play with this and see how you get on:
-- Amended function
ALTER FUNCTION [dbo].[IF_Calendar] (@Enddate DATE)
RETURNS TABLE AS RETURN
WITH
e1...
^{“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 23, 2014 at 4:45 am

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

vee_jess (10/22/2014)
Hi Cris,
As a title will be best please.
Also, please note that I need to report on data October,November and December 2012,2013 and 2014. Now, I will get
4...
^{“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 23, 2014 at 1:37 am

#1754253
RE: Review on SELECT query

Luis Cazares (10/22/2014)
Did you leave something out of your post? There seems to be no reason to have the 3 left joins in that query as it is.
Without DISTINCT, those...
^{“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 23, 2014 at 1:30 am

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

vee_jess (10/22/2014)
Chris,
Absolutely. A Month (and probably a year) on the report will be good. The dataset will contain October, November, December data for 2012,2013 and 2014 so it will probably...
^{“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 9:42 am

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

vee_jess (10/22/2014)
Hi Chris,
Thank you so much, seems to be doing the job however, the will be a broader dataset and more months. I would these records to the sample dataset
('2012-12-03','DM1','Monday',441),
('2012-12-03','DM3','Monday',10),
('2012-12-10','DM4','Monday',25),...
^{“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 8:12 am

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

No problem. First, put the calendar code into a function:
CREATE FUNCTION [dbo].[IF_Calendar] (@Enddate DATE)
RETURNS TABLE AS RETURN
WITH
e1 AS (SELECT d.n FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n)),
e2 AS (SELECT a.n...
^{“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 7:32 am

#1754040
RE: Review on SELECT query

spaghettidba (10/22/2014)
Your query can be rewritten like this:
SELECT A.activityCode
,A.ActivityName + ISNULL(A.description, '') AS ActivityName
,A.displayOrder
,A.activityStartDate
FROM Activities AS A
LEFT JOIN...
^{“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:41 am

#1754005

Viewing 15 posts - 2,836 through 2,850 (of 10,143 total)