ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,826 through 3,840 (of 10,143 total)

RE: Unable to convert character to datetime

SELECT CounterDateTime = CAST(CAST(CounterDateTime AS CHAR(24)) AS DATETIME)
FROM (SELECT CounterDateTime = '2013-12-30 12:09:00.123' + CHAR(10)) d
-- Msg 241, Level 16, State 1, Line 1
-- Conversion failed when converting date and/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

December 31, 2013 at 5:23 am

#1676571
RE: Flatten hierarchy

Try this - it's called a CROSSTAB query:
SELECT id,
LevelDesc1 = MAX(CASE WHEN levelid = 1 THEN leveldesc ELSE NULL END),
LevelDesc2 = MAX(CASE WHEN levelid = 2 THEN leveldesc ELSE 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

December 31, 2013 at 5:11 am

#1676569
RE: Stored Procedure OUTPUT param VS RETURN VS SELECT

The return value of a stored procedure is traditionally used for this:
RETURN 0 if the procedure ran successfully
RETURN -n if the procedure failed.
ALTER PROCEDURE TestOutput @Outcome CHAR (4)
AS IF...
^{“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

December 31, 2013 at 5:05 am

#1676568
RE: SUM function for subquery

BlackIceAngel (12/31/2013)
That is perfect thank you
The solution needs the SUM() function though for my requirements,
SELECT SUM(ABS(k.Amount + k.VATAmount)) AS TOTAL.
Love this site!
So long as you are aware that SUM()...
^{“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

December 31, 2013 at 4:48 am

#1676566
RE: Are the posted questions getting worse?

dwain.c (12/31/2013)
ChrisM@Work (12/31/2013)
Stefan Krzywicki (12/30/2013)
wolfkillj (12/30/2013)
Stefan Krzywicki (12/30/2013)
wolfkillj (12/30/2013)
GilaMonster (12/30/2013)
wolfkillj (12/30/2013)
is there really a performance problem or are you just "tuning" the query as a knee-jerk response to seeing a...
^{“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

December 31, 2013 at 4:39 am

#1676565
RE: SUM function for subquery

You're welcome, thanks for posting the final query. If it works for you and generates the results you want, then that's good and you're probably done - but I'd recommend...
^{“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

December 31, 2013 at 4:13 am

#1676560
RE: SUM function for subquery

Can you post your solution please? It's closure for your thread, offers closure for those who have helped you, and may help others who stumble upon this thread whilst seeking...
^{“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

December 31, 2013 at 3:37 am

#1676556
RE: how to calculate no.of .months billed customer

raghuldrag (12/31/2013)
i need the output like
for example 2013-04-01 is in table my expectation output is 04-2013 (extract month with year)......
Is '2013-04-01' a date datatype or a string datatype?
You are...
^{“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

December 31, 2013 at 3:35 am

#1676555
RE: Stored procedure taking too long to run

The good ol' ROW_NUMBER() trick might be worth a try. A few tests on a similar-sized table here indicates relative costs of 20% for the table scan, 30% for 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

December 31, 2013 at 3:29 am

#1676553
RE: How to count months for these table with inner join of sql server 2000?

Split thread. Please post replies to the original thread here, where you will obtain background information and progress to date.
^{“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

December 31, 2013 at 2:56 am

#1676547
RE: How to split the date format into month with year?

Split thread. Please post replies to the original thread here, where you will obtain background information and progress to date.
^{“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

December 31, 2013 at 2:55 am

#1676546
RE: SUM function for subquery

Here's the very first query from your very first post, reformatted and with table aliases applied for readability:
SELECT
SUM(Amount=ABS((
SELECT Top 1 Amount + VATAmount
FROM FAAccKitty k1 WITH(NOLOCK)
Where k1.PlaceID...
^{“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

December 31, 2013 at 2:52 am

#1676544
RE: How to use the subquery column highglighted in the GROUP BY

eobiki10 (12/30/2013)
Thanks Chris for responding to this query. I have been able to solve the problem.
EO
Can you post your solution please? It provides closure for this thread and for 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

December 31, 2013 at 1:55 am

#1676535
RE: Are the posted questions getting worse?

Stefan Krzywicki (12/30/2013)
wolfkillj (12/30/2013)
Stefan Krzywicki (12/30/2013)
wolfkillj (12/30/2013)
GilaMonster (12/30/2013)
wolfkillj (12/30/2013)
is there really a performance problem or are you just "tuning" the query as a knee-jerk response to seeing a scan 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

December 31, 2013 at 1:53 am

#1676534
RE: Problem with a query

WayneHess (12/30/2013)
Thanks. I've tried that to make sure my numbers are correct and I can get the numbers to pull correctly even filtering down to one section and get my...
^{“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

December 30, 2013 at 10:07 am

#1676426

Viewing 15 posts - 3,826 through 3,840 (of 10,143 total)