ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,701 through 5,715 (of 10,143 total)

RE: Datatype truncation

sqlnaive (9/7/2012)
Check it
declare @test-2 float
select @test-2 = 0.1234123412341234
select @test-2
select ltrim(rtrim(@test))
Results are as follows:
0.123412341234123
0.123412
RTRIM and LTRIM both take a varchar parameter. The float value is implicitly converted to a varchar, then...
^{“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 7, 2012 at 5:35 am

#1535004
RE: changing rows to columns in output

Like this?
select
DatePart(hour,Reading_Date) as [hour],
t.Tank_Product ,
convert(Date,Reading_Date,103) as [date],
sum(tr.Sales) as sales,
sum(tr.Price)as price ,
sum(tr.Amount)as amount
INTO #Results
from dbo.Tanks as t
inner join Transactions as tr
on t.Simmons_PanelID = @panelid
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

September 7, 2012 at 5:19 am

#1534998
RE: Extract Time from non standard time field

SELECT
TimeVC,
FirstColonPosition = CHARINDEX(':',TimeVC,1),
[Hours] = LEFT(TimeVC,CHARINDEX(':',TimeVC,1)-1)
FROM (
SELECT TimeVC = '73:59:59' UNION ALL
SELECT '144:44:59') d
^{“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 7, 2012 at 4:11 am

#1534973
RE: select * returning no rows , but select 1 returning the result from a table selection

Usman Butt (9/7/2012)
ASFSDJG (9/7/2012)
If we force clustered index it is display all the columns .
select * from <table_name> with (index (cluster index name>)) where field_id = 2
its gets result.
Execution plan...
^{“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 7, 2012 at 3:30 am

#1534958
RE: Cannot get Left JOIN to work correctly

Looks fine to me, Tom. It's important that you don't have dupes in the matrix table source (the CROSS JOIN bit), if the two columns returned are the PK's 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

September 7, 2012 at 2:38 am

#1534923
RE: Time as a high precision difference of dates

bmahf (9/6/2012)
So it turns out that each of these solutions is missing something, it's either the fraction part sometimes comes out incorrect, because it wasn't adjusted as I was doing...
^{“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 7, 2012 at 2:11 am

#1534914
RE: Time as a high precision difference of dates

ScottPletcher (9/6/2012)
bmahf (9/6/2012)
So it turns out that each of these solutions is missing something, it's either the fraction part sometimes comes out incorrect, because it wasn't adjusted as I was...
^{“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 7, 2012 at 2:09 am

#1534911
RE: Order by with case

Phil Parkin (9/6/2012)
Ah yes, thanks guys.
Note to self: read more carefully next time 🙂
Heh at least your code works, Phil - even if it doesn't quite match the spec. Mine...
^{“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 6, 2012 at 8:52 am

#1534532
RE: Time as a high precision difference of dates

ChrisM@Work (9/6/2012)
One more:
TimeResult3= CAST(
DATEADD(ns,ABS(DATEPART(ns,btime)-DATEPART(ns,atime)),
DATEADD(ss,ABS(DATEDIFF(ss, aTime, btime)),CAST('1900-01-01 00:00' AS datetime2)))
AS TIME(7))
Aarrgghh!! They all fall over given suitable edge cases in the data.
DATEDIFF counts boundaries -...
^{“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 6, 2012 at 8:50 am

#1534529
RE: Order by with case

Phil Parkin (9/6/2012)
mssqlsrv (9/6/2012)
I think you can not use variable in order by clause.
Variables are only allowed when ordering by an expression referencing a column name.
Yeah you can. Just tried...
^{“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 6, 2012 at 8:43 am

#1534520
RE: CASE STATEMENTS

Something like this:
SELECT
SubTotalSplit = CASE WHEN OrdMain.SubTotal = 0.01 THEN '= 0.01' ELSE '<> 0.01' END,
sumx = ( (SUM(CASE (LEFT(RIGHT(Rtrim(summary), 12), 5))
WHEN 'EURO/' THEN OrdItem.PriceEach * OrdItem.Quan...
^{“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 6, 2012 at 5:40 am

#1534402
RE: Time as a high precision difference of dates

One more:
TimeResult3= CAST(
DATEADD(ns,ABS(DATEPART(ns,btime)-DATEPART(ns,atime)),
DATEADD(ss,ABS(DATEDIFF(ss, aTime, btime)),CAST('1900-01-01 00:00' AS datetime2)))
AS TIME(7))
^{“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 6, 2012 at 4:53 am

#1534361
RE: Time as a high precision difference of dates

Nice one, Paul.
Here are two different one-line TSQL solutions:
;With MyCTE (aTime, bTime)
AS
(
SELECT CONVERT(datetime2,'1900-01-01 00:08:00.0000001'), CONVERT(datetime2,'1900-01-01 00:09:18.361')
UNION ALL SELECT '1900-01-01 00:08:00.0000001', '1900-01-01 00:13:50.705'
UNION ALL SELECT '1900-01-01 00:09:18.361', '1900-01-01 03:13:50.7050001'
UNION ALL...
^{“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 6, 2012 at 4:05 am

#1534345
RE: Order by with case

Try declaring @index as int. From BOL: "A sort column can be specified as a name or column alias, or a nonnegative integer representing the position of the name 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 6, 2012 at 3:20 am

#1534325
RE: Are the posted questions getting worse?

GilaMonster (9/5/2012)
Brandie Tarvin (9/5/2012)
jcrawf02 (9/5/2012)
bitbucket-25253 (9/5/2012)
Time for me to toot my own horn ... braging HECK yes.
My 73rd QOD has been scheduled for 06 Sep 2012 - 07 Sep 2012
Should...
^{“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 6, 2012 at 2:38 am

#1534303

Viewing 15 posts - 5,701 through 5,715 (of 10,143 total)