ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,311 through 5,325 (of 10,143 total)

RE: Add a column Count from another table

-- To obtain accurate rowcounts from the two tables, you need to aggregate them separately
;WITH Matrix (listcode,[year]) AS (
(SELECT DISTINCT listcode FROM NamesTest) as A
CROSS JOIN
(SELECT DISTINCT [year] FROM NamesTest...
^{“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, 2013 at 1:20 am

#1579475
RE: Join on nvarchar column using LIKE

UPDATE od
SET value_exists = 1
FROM LII od
INNER JOIN VPL sl
ON sl.it LIKE '%' + od.value + '%'
WHERE od.tn = 'VPL'
AND od.cn = 'it'
AND od.opr =...
^{“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, 2013 at 12:59 am

#1579465
RE: Problem with CAST to VARCHAR with SUBSTRING Function

Welsh Corgi (1/19/2013)
I'm haveing trouble with a simple CAST to VARCHAR Statement.
SELECT
CASE WHEN CAST(LEFT(Customer.STARTDATE, 2 AS VARCHAR(2)))
...
^{“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 21, 2013 at 9:40 am

#1579305
RE: Need Alternate Solution

Have you considered using a more efficient inline table-valued function instead? Here are a couple of ideas:
DECLARE @amount MONEY
SET @amount = -3211987654321.10 -- [-3,210,987,654,321.10]
---------------------------------------------------------------------------------------
SELECT
FormattedAmount = CASE SIGN(@amount) WHEN -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

January 21, 2013 at 5:13 am

#1579202
RE: Monthly Aggregation

Dehqon D. (1/18/2013)
Thanks for idea @demonfox, you have got it right. But how to change it to fit my ChrisM@Work code.
Like this?
--===== Create the table
CREATE TABLE #T_BMAL(
[DocLinkID] [nvarchar](50)...
^{“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 21, 2013 at 1:51 am

#1579135
RE: Bug when aggregate in select?

Strip out the noise, and introduce another little statement.
SELECT [An Aggregate] = SUM(1)
FROM (SELECT n = 1) d
WHERE 1=0
SELECT [An Aggregate] = COUNT(*)
FROM (SELECT n = 1) d
WHERE 1=0
SELECT [Something]...
^{“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 18, 2013 at 3:35 am

#1578616
RE: Inserting Multiple Records Using While Loop

-- you only need to read the SalesGLLink table once:
INSERT INTO #TempSalesGLLink(
StoreId, LocId, SalesGLLinkUniqueNum, SalesTypeCd, LineCd,
VehSaleAcct, VehSaleDept, TradeACVAcct, TradeACVDept, OverAllowAcct, OverAllowDept, LoanDiscountAcct, LoanDiscountDept,
DueFromFinAcct, DueFromFinDept, CashDownAcct, CashDownDept, CostGoodsAcct, CostGoodsDept, InvSoldDept,...
^{“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 17, 2013 at 8:27 am

#1578346
RE: GetDateInString

HildaJ (1/17/2013)
Hard to maintain it's really not an option when it comes to working with databases, you got to do what you got to do. Data entered by users...
^{“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 17, 2013 at 8:17 am

#1578336
RE: GetDateInString

bli-963763 (1/17/2013)
I would never write a so complicated script for this simple functionality. It's too hard to maintain.
I'd very much like to see your simpler version.
^{“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 17, 2013 at 8:04 am

#1578324
RE: GetDateInString

-- Convert to an inline table-valued function: more efficient.
-- minimise the work done to identify a date within a string.
DECLARE @InputString NVARCHAR(500) = 'ABC01/01/2013XYZ'
SELECT
DateString = CASE WHEN ISDATE(PatternString) =...
^{“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 17, 2013 at 7:03 am

#1578300
RE: Histogram chart in sql

It looks ok to me, apart from UNION picking up the 40th bin. When this is done, the query is quite efficient:
;with cteHistogram (N, StartBin, EndBin, TotalCount)
as
(
select
N,
@min-2+(@interval*(N-1)) StartBin,...
^{“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 17, 2013 at 3:42 am

#1578201
RE: How to obtain Sum of count

dwain.c (1/16/2013)
... Are you sure you're not part Navaho?
No but one of my programming buddies went AWOL for six months last year - and resurfaced in Michigan married 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

January 17, 2013 at 1:11 am

#1578130
RE: How to obtain Sum of count

sabeer.mvit (1/16/2013)
The ccount is not obtain directly from table as done here.Am rather using the View written as below
With compTable(cid,ccid,ccount)
as
AS
(
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

January 17, 2013 at 1:09 am

#1578129
RE: Monthly Aggregation

Sorry, my mistake:
SELECT
y.MonthNo,
y.[Year],
y.[Month],
COUNT(doclinkid) as Registered, -- is this correct?
COUNT(DISTINCT doclinkid) as Registered, -- OR is this correct?
SUM(x.Finalised) as Finalised,
COUNT(*) - SUM(x.Finalised) AS [Remaining]
FROM REPWPK.T_BMAL
CROSS APPLY...
^{“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 16, 2013 at 9:36 am

#1577908
RE: How to obtain Sum of count

;WITH SampleData AS (
SELECT CId = 1, CCId = 'a', CCount = 3, totalCount = 6 UNION ALL
SELECT 1, 'a', 3, 6 UNION ALL
SELECT 1, 'b', 3, 6 UNION ALL
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

January 16, 2013 at 9:22 am

#1577893

Viewing 15 posts - 5,311 through 5,325 (of 10,143 total)