ChrisM@Work

Forum Replies Created

Viewing 15 posts - 8,176 through 8,190 (of 10,143 total)

RE: Split out a table by date?

-- Make some sample data
DROP TABLE #TableWithStartAndEndDate
CREATE TABLE #TableWithStartAndEndDate (TiD INT, StartDate DATETIME, EndDate DATETIME)
INSERT INTO #TableWithStartAndEndDate (TiD, StartDate, EndDate)
SELECT 1, '2010-01-18 01:00:00.000', '2010-01-25 23:00:00.000' UNION ALL
SELECT 2, '2010-01-18 02:00:00.000',...
^{“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

January 26, 2010 at 4:46 am

#1108560
RE: SQL SERVER 2008 Pivot

-- Sample data setup for #sipDimensionPos
--===== Create table
DROP TABLE #sipDimensionPos
CREATE TABLE #sipDimensionPos(
[id_pos] [bigint] NULL,
[id_cliente] [bigint] NOT NULL,
[razonSocial_cliente] [varchar](100) NULL,
[id_CliPadre] [int] NULL,
[nombre_CliPadre] [varchar](100) NULL,
[id_canal] [smallint] NULL,
[id_sucursal] [smallint] NULL,
[establecimiento] [varchar](100) NULL,
[nombre_Localidad] [varchar](100)...
^{“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

January 26, 2010 at 2:49 am

#1108518
RE: SQL SERVER 2008 Pivot

-- @PivotCliPadres populated
-- with distinct values, and in order
DECLARE @PivotCliPadres VARCHAR(1000)
SELECT @PivotCliPadres = COALESCE(
@PivotCliPadres + ',[' + cast(id_CliPadre as varchar) + ']',
'[' + cast(id_CliPadre as varchar)+ ']')
FROM (
SELECT id_CliPadre
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

January 26, 2010 at 2:47 am

#1108516
RE: Not getting desired results

There's another way of writing this type of query which might be more intuitive to you, and help you along with your new query. This replaces the query as in...
^{“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

January 26, 2010 at 2:15 am

#1108509
RE: Are the posted questions getting worse?

TSQL101 at Mumbai Tech.
Graduate as a Senior Software Engineer.
^{“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

January 25, 2010 at 10:11 am

#1108260
RE: Not getting desired results

This might run faster:
SELECT c.crs_id AS id,
c.crs_name,
c.crs_author,
c.sch_start,
c.sch_end
FROM SNP_crs c
--INNER JOIN SNP_crsunits u ON u.crs_id = c.crs_id
--INNER JOIN SNP_media m ON m.crsunit_id = u.crsunit_id
LEFT JOIN (SELECT ud.crs_id
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

January 25, 2010 at 9:04 am

#1108204
RE: Not getting desired results

jordon.shaw (1/25/2010)
That's it! You're a genus! Thank you so much for your help!!!
Heh not quite mate, just a jobbing programmer, but thanks.
Do you really need the aggregated columns...
^{“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

January 25, 2010 at 8:54 am

#1108198
RE: Not getting desired results

SELECT c.crs_id AS id,
c.crs_name,
c.crs_author,
c.sch_start,...
^{“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

January 25, 2010 at 8:30 am

#1108185
RE: Not getting desired results

Which output columns vary within the "group by"? Have a gander at them, and decide which of your options would best suit you: MIN / MAX / AVG etc
^{“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

January 25, 2010 at 8:15 am

#1108173
RE: Not getting desired results

No worries mate.
Have a look at this, I reckon it's getting close:
SELECT c.crs_id AS id,
c.crs_name,
...
^{“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

January 25, 2010 at 8:04 am

#1108167
RE: Not getting desired results

jordon.shaw (1/25/2010)
Chris Morris-439714 (1/25/2010)
Nope, wasn't meant to. There are a few questions at the bottom of the code list, any chance of an answer?
I had posted my table structure. ...
^{“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

January 25, 2010 at 7:38 am

#1108147
RE: Query multiple descriptions from single field of codes

Jeff Moden (1/24/2010)
lobbymuncher (1/24/2010)
Here's a completely different way which returns the codes in the same row.
Oh... be careful... you have to consider how hard the I/O system needs to work...
^{“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

January 25, 2010 at 6:53 am

#1108122
RE: Not getting desired results

jordon.shaw (1/24/2010)
That still isn't working.
Nope, wasn't meant to. There are a few questions at the bottom of the code list, any chance of an answer?
^{“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

January 25, 2010 at 6:06 am

#1108096
RE: charindex or like condition

This is worse than the original query and the suggestions which have been made have not been implemented.
Are you able to create a view or stored procedure on 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

January 22, 2010 at 8:10 am

#1107368
RE: Where are you?

Roy Ernest (1/22/2010)
Born and educated in Kerala, India (Southern tip of India). Our state is known as Gods own paradise because of its beauty. Back waters, Mountains, forests, beaches 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

January 22, 2010 at 8:00 am

#1107356

Viewing 15 posts - 8,176 through 8,190 (of 10,143 total)