ChrisM@Work

Forum Replies Created

Viewing 15 posts - 4,111 through 4,125 (of 10,143 total)

RE: Need help with my tsql code

Never mind. Try this:
SELECT @retvalOUT = CONVERT(TINYINT,x.OutputColumn)
FROM [dbo].[SLADCSchedule]
CROSS APPLY (
SELECT OutputColumn =
CASE @DayOfWeek
WHEN 1 THEN Sunday
WHEN 2 THEN Monday
WHEN 3 THEN Tuesday
WHEN 4 THEN Wednesday
WHEN 5 THEN Thursday
WHEN...
^{“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

August 20, 2013 at 6:18 am

#1643040
RE: Need help with my tsql code

Here's the query generated by your very strange code:
Select @retvalOUT = Convert(TinyInt,Sunday)
from [dbo].[SLADCSchedule]
where ScheduleStart <= 'Apr 1 2012 12:00AM'
and ScheduleEnd >= 'Apr 1 2012 12:00AM'...
^{“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

August 20, 2013 at 6:12 am

#1643035
RE: Table data in tabular form

The word Yep is a link 😉
^{“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

August 20, 2013 at 6:04 am

#1643031
RE: Table data in tabular form

Yep[/url].
^{“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

August 20, 2013 at 5:58 am

#1643028
RE: Data Problem

Much the same question was posted here and another three or four threads on ssc in the last two or three weeks. Chopping and changing threads in this manner 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

August 20, 2013 at 5:38 am

#1643017
RE: need help and getting unique set

dwain.c (8/19/2013)
Not sure this is a particularly efficient way to do this but this might work:
SELECT set_id, product, attribute=CAST(item AS INT)
FROM (
SELECT set_id=MIN(set_id), product, allattributes
...
^{“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

August 20, 2013 at 5:21 am

#1643016
RE: need help and getting unique set

Hi Walton
How many sets will you have?
How many rows will each set have?
Thanks.
^{“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

August 20, 2013 at 4:07 am

#1642994
RE: Split data from one column into multiple colums

LinksUp (8/18/2013)
I think this is what you are after:
select d.name, d.packsize, doc.doctorname, doc.location
from Doctor_Prescribed_Drugs dpd
left join Doctor doc on dpd.doctorref = doc.doctorref
left join Drugs...
^{“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

August 20, 2013 at 1:25 am

#1642961
RE: Select specific set of data from text column

This looks straightforward. If you can set up the sample data as a CREATE TABLE/INSERT INTO script, I'm sure someone will help.
^{“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

August 20, 2013 at 1:17 am

#1642959
RE: what' wrong with my script

robert.gerald.taylor (8/19/2013)
ChrisM@Work (8/19/2013)
robert.gerald.taylor (8/19/2013)
You're doing a lot in you If/else, couldn't you do something like:
SET @start = COALESCE(@startdate, DATEADD(yy, -1, @endDate), GETDATE());
SET @end = COALESCE(@enddate, DATEADD(yy, 1, @startdate), DATEADD(yy, 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

August 19, 2013 at 8:50 am

#1642733
RE: what' wrong with my script

robert.gerald.taylor (8/19/2013)
You're doing a lot in you If/else, couldn't you do something like:
SET @start = COALESCE(@startdate, DATEADD(yy, -1, @endDate), GETDATE());
SET @end = COALESCE(@enddate, DATEADD(yy, 1, @startdate), DATEADD(yy, 1, GETDATE()));
Seems cleaner...
^{“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

August 19, 2013 at 7:19 am

#1642676
RE: Help on query

vignesh.ms (8/19/2013)
Hi jethrow ..
if the all the marks are same need to show all the column names
Here's a CROSS APPLY VALUES version. CAV is a longhand UNPIVOT, it's a little...
^{“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

August 19, 2013 at 5:01 am

#1642617
RE: can not create temp directory

santhosh411 (8/19/2013)
Again i got this error today and this error came from the HP monitoring tool.
I don't have any more details.
Why are you posting this in a SQL Server 2008...
^{“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

August 19, 2013 at 4:28 am

#1642609
RE: Should He Stay or Should He Go?

sgmunson (8/18/2013)
Boy, a typo sure can bite you (Ouch! :w00t: ), and once committed, keep biting... Poor Jeff... his bite took out his CMOS battery...
Anyway, you appear to...
^{“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

August 19, 2013 at 3:35 am

#1642593
RE: Query Performance

ringovski (8/18/2013)
ChrisM@Work (8/16/2013)
Stefan_G (8/16/2013)
thava (8/15/2013)
...
This is a good idea - scan the large tables a single time.
To improve performance even more you should include a WHERE condition for GNLFSMID as...
^{“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

August 19, 2013 at 3:15 am

#1642585

Viewing 15 posts - 4,111 through 4,125 (of 10,143 total)