ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,346 through 3,360 (of 10,143 total)

RE: Select * if null passed else by INT

The execution plans for those two queries is likely to be completely different. If you were to write the query as a single "catch-all" query, it would be optimised either...
^{“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

May 14, 2014 at 8:31 am

#1713499
RE: T-SQL query with loop ?

/* You missed out the ddl for table #C, the ReleaseNumber on the
last row of your sample data is probably incorrect, and your spec is sloppy
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

May 14, 2014 at 5:22 am

#1713403
RE: how to aggregate interval data to hourly values?

Luis Cazares (5/13/2014)
I wouldn't use the cross apply, but it's just a personal preference.
It should be interpreted the same way as Chris M's query.
SELECT MeteriD,
Reading = SUM(Reading),
DateByHour =...
^{“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

May 13, 2014 at 9:19 am

#1713131
RE: Please help to reduce query time

ashok84.kr (5/12/2014)
...number of rows returned = 1 million and time taken 35 seconds....
How are you timing this? It could take 35 seconds just to return this number of rows 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

May 13, 2014 at 6:19 am

#1712998
RE: Change display of result set from 'showing data as rows, to showing data as one or more columns'

Try this article on Dynamic Crosstabs[/url].
^{“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

May 13, 2014 at 6:14 am

#1712995
RE: Running totals for general ledger

haddoque2000 (5/13/2014)
...
Sample rows:
FinKeyDateKeyOrgaKeyCliKeyAccoKeyDim1KeyDim2KeyAmountDate
19720101001113611-541261,822010-10-01 00:00:00.000
19820101031113611-541261,822010-10-31 00:00:00.000
19920101101113611-550252,822010-11-01 00:00:00.000
20020101201113611-559243,822010-12-01 00:00:00.000
20120110131113611-568234,822011-01-31 00:00:00.000
2022004050611117116103332004-05-06 00:00:00.000
2032004050711117111436942004-05-07 00:00:00.000
204200405101111711-1466222004-05-10 00:00:00.000
205200405111111711-1595222004-05-11 00:00:00.000
...
Had you read the article suggested by Jeff and others you would have posted this as INSERTs. We're now...
^{“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

May 13, 2014 at 2:24 am

#1712931
RE: how to aggregate interval data to hourly values?

Megha P (5/13/2014)
Don't know performance wise it is good or bad..but i come up with below code
create Table #temp
(MeteriD INT,
ReadingDate DATETIME,
Reading INT
)
INSERT #temp
SELECT 3969,'1/01/2014 0:00', 0
UNION ALL
SELECT 3969,'1/01/2014 0:15', ...
^{“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

May 13, 2014 at 2:04 am

#1712928
RE: count events on date

Eirikur Eiriksson (5/12/2014)
Lynn Pettis (5/12/2014)
Eirikur Eiriksson (5/12/2014)
ChrisM@Work (5/12/2014)
Eirikur Eiriksson (5/12/2014)
Mike Saunders NL (5/12/2014)
Hi, this works! Thanks
The only thing that is missing is that the days with no events are not...
^{“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

May 13, 2014 at 1:48 am

#1712924
RE: count events on date

Eirikur Eiriksson (5/12/2014)
Mike Saunders NL (5/12/2014)
Hi, this works! Thanks
The only thing that is missing is that the days with no events are not mentioned. Is there a way to display...
^{“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

May 12, 2014 at 9:44 am

#1712735
RE: SELECT IN CLAUSE

Hi Luis
Sorry mate, answered here. I hadn't noticed this thread or I would have put up a dupe notice.
^{“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

May 12, 2014 at 8:47 am

#1712708
RE: count events on date

;WITH DateRange AS (
SELECT EventDate
FROM (
SELECT
FirstDate = DATEADD(month,DATEDIFF(month,0,MIN(startdate)),0),
LastDate = DATEADD(month,1+DATEDIFF(month,0,MAX(enddate)),-1)
FROM #events
) e
CROSS APPLY (
SELECT TOP(1 + DATEDIFF(day,e.FirstDate,e.LastDate))
EventDate = DATEADD(day,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1,FirstDate)
FROM
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n),...
^{“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

May 12, 2014 at 7:54 am

#1712680
RE: Running totals for general ledger

Can you set up some sample data for folks to code against? Coding up a running totals script is easy. Trying to imagine how your data looks before and after...
^{“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

May 12, 2014 at 7:06 am

#1712660
RE: Drop Identity column from temp table

SELECT
'INSERT INTO ' + t2.table_name + CHAR(10) +
' (' + STUFF([target].ColumnList,1,2,'') + ')' + CHAR(10) +
'SELECT ' + STUFF([source].ColumnList,1,2,'') + CHAR(10) +
'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

May 12, 2014 at 4:59 am

#1712604
RE: Drop Identity column from temp table

hoseam (5/12/2014)
Yes, any columns in the Hosea_tblDef_Cloning_Table, under the column [COLUMN_NAME], will be the combination.. In our select list selecting new values and where clause comparing with old values..
Just 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

May 12, 2014 at 2:53 am

#1712565
RE: Drop Identity column from temp table

hoseam (5/12/2014)
Product_ID and FundID are from Hosea_tblDef_Cloning_Table, from the column COLUMN_NAME, so any value in that column will always be there in the WHERE CLAUSE. on this case is (Product_ID...
^{“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

May 12, 2014 at 2:42 am

#1712562

Viewing 15 posts - 3,346 through 3,360 (of 10,143 total)