ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,746 through 5,760 (of 10,143 total)

RE: Outer join not working correctly

Not far off. You need a left outer join to the items table: this will return all rows from the left hand side of the join (venue stock) whether or...
^{“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

September 4, 2012 at 2:02 am

#1533143
RE: UNION TWO TABLES WITH ONLY ONE COLUMN IN COMMON

mickyT (9/3/2012)
You could try the following query.
select f.Path, f.FolderName, f.FolderSize, d.FileName, d.FileSize
from @folders f
left outer join @Files d on f.Path = d.Path
group by grouping sets ((f.Path, Foldername, FolderSize), (f.path,...
^{“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

September 4, 2012 at 1:49 am

#1533138
RE: Insert from excel to sql using Microsoft.ACE.OLEDB.12.0

Use a staging table:
CREATE procedure mySP
@excelfile varchar(200)
AS
DECLARE @sql varchar(1024)
CREATE TABLE #MyLocalTempTable ([columns which nicely match the Excel file])
SET @sql='SELECT...
^{“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

September 3, 2012 at 9:29 am

#1533019
RE: updating the last two spaces of a column

thomasrichardson2000 (9/3/2012)
Thanks so far, I will clarify further. The requirement is that i update the version field with the letters 'BT' at the end, So if the column is defined...
^{“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

September 3, 2012 at 6:07 am

#1532935
RE: UNION TWO TABLES WITH ONLY ONE COLUMN IN COMMON

You need "placeholder" columns:
SELECT
[Path],
Foldername,
FolderSize,
[Filename],
FileSize
FROM (
SELECT
[Path],
[Filename] = CAST(NULL AS Varchar(50)),
FileSize = CAST(NULL AS INT),
Foldername,
FolderSize
FROM @folders
UNION ALL
SELECT
[Path],
[Filename],
FileSize,
Foldername = CAST(NULL...
^{“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

September 3, 2012 at 6:00 am

#1532932
RE: Date Query Format Problem

jerome.morris (9/3/2012)
Right I think I was doing the convert in the wrong place, this returns results I want to see.
SELECT MachineName, ModeName, FileName,...
^{“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

September 3, 2012 at 5:32 am

#1532922
RE: Date Query Format Problem

jerome.morris (9/3/2012)
Thanks Chris, sorry to post and post but I feel like I am close. I dont have a holing table anymore and the only problem I have is running...
^{“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

September 3, 2012 at 5:22 am

#1532917
RE: Date Query Format Problem

jerome.morris (9/3/2012)
Hi Jefff, where am I going wrong with this query please ...
Jay, you are likely to progress far more quickly with this problem - the date format problem -...
^{“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

September 3, 2012 at 5:04 am

#1532906
RE: Help with Stored Procedure

NiKo22 (8/31/2012)
I have created a stored procedure that uses a temp table to do some updates.
the problem is that the update statement outside the stored procedure works fine , but...
^{“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

September 3, 2012 at 4:05 am

#1532888
RE: Error converting data type nvarchar to numeric

rachelbr 87580 (9/2/2012)
Hi,
I have this stored proc that I run nightly. I keep on having issues with it failing because it's having issues converting a nvarchar. This...
^{“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

September 3, 2012 at 2:25 am

#1532859
RE: The Beer Cooler Thread

nwerner (8/31/2012)
Meantime Naval College Old Porter at Brasserie V Belgian restaurant in Madison, Wisconsin last night, moules+frites 😎 http://www.brasseriev.com
The writeup is amazing. Meantime [/url]brew some great pale ales -...
^{“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

September 3, 2012 at 2:05 am

#1532853
RE: Date Query Format Problem

DECLARE @StartDate_Time DATETIME, @EndDate_Time DATETIME
SELECT @StartDate_Time = CONVERT(DATETIME,'06/06/2012',103), @EndDate_Time = CONVERT(DATETIME,'07/06/2012',103)
SELECT @StartDate_Time, @EndDate_Time
SELECT
x.ProperStartDateTime,
d.*
FROM SLADB.dbo.ProdDataTB d
CROSS APPLY (
SELECT
[Year] = RIGHT(d.StartDate_Time,4),
x.MonthNum,
[Day] = SUBSTRING(d.StartDate_Time,9,2),
[Time] = SUBSTRING(d.StartDate_Time,12,8)
FROM (VALUES...
^{“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 31, 2012 at 8:19 am

#1532413
RE: Date Query Format Problem

SELECT
d.StartDate_Time,
date_string = p.[Year]+'-'+p.MonthNum+'-'+p.[Day]+' '+p.[Time],
date_datetime = CONVERT(DATETIME,p.[Year]+'-'+p.MonthNum+'-'+p.[Day]+' '+p.[Time],120)
FROM (SELECT StartDate_Time = 'Wed Sep 01 03:00:00 2010') d
CROSS APPLY (
SELECT
[Year] = RIGHT(d.StartDate_Time,4),
x.MonthNum,
[Day] = SUBSTRING(d.StartDate_Time,9,2),
[Time] = SUBSTRING(d.StartDate_Time,12,8)
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

August 31, 2012 at 7:23 am

#1532378
RE: Date Query Format Problem

Jerome, can you post the CREATE TABLE script for SLADB.dbo.ProdDataTB? Remove the other columns if you wish. To get the script, right-click on the table in object explorer in SSMS.
^{“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 31, 2012 at 6:59 am

#1532368
RE: Creating a global temp table in a stored procedure

Potso (8/31/2012)
Thanks I knew it was something simple and dumb i was just not thinking about. And i am dropping the table everytime after the stored procedure is run 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

August 31, 2012 at 6:54 am

#1532362

Viewing 15 posts - 5,746 through 5,760 (of 10,143 total)