ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,011 through 5,025 (of 10,143 total)

RE: Obtaining Before & After Record & Missing Record.

-- make some sample data
DROP TABLE #Sample
CREATE TABLE #Sample (Record INT, [Concatenate] CHAR(12))
INSERT INTO #Sample (Record, [Concatenate])
SELECT 1, '201201010510' UNION ALL
SELECT 2, '201201010511' UNION ALL
SELECT 3, '201201010514' UNION ALL
SELECT 4,...
^{“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

March 20, 2013 at 3:31 am

#1598790
RE: best optimized query for the requirement

Shanmuga Raj (3/20/2013)
Getting error :
Conversion failed when converting the varchar value ' | | |0|0|0' to data type int.
ChrisM@Work (3/20/2013)
Depending upon existing indexes, this could be a significant improvement:
DROP TABLE...
^{“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

March 20, 2013 at 3:03 am

#1598778
RE: best optimized query for the requirement

Depending upon existing indexes, this could be a significant improvement:
DROP TABLE #fact_sales
CREATE TABLE #fact_sales (agent_id INT, product_code INT, product_id VARCHAR(5))
INSERT INTO #fact_sales (agent_id, product_code, product_id)
VALUES
(1,1,'1'),
(1,1,'2'),
(1,1,'3'),
(1,1,'4'),
(1,1,'5'),
(2,3,'1'),
(2,3,'1'),
(3,2,'1'),
(3,2,'1'),
(4,1,'1'),
(4,1,'2')
CREATE CLUSTERED INDEX cx_Everything...
^{“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

March 20, 2013 at 2:34 am

#1598771
RE: Insertion in table

Have a play with this;
DECLARE
@LastBookNo INT = 23,
@LastPageNo INT = 80,
@BooksToInsert INT = 2,
@PagesPerBook INT = 10
;WITH E1(N) AS (
...
^{“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

March 19, 2013 at 7:19 am

#1598411
RE: Deadlock on update command

DDL for the table and all indexes, and the actual plans for both queries, would help diagnosis.
An index on sessionid including status and rsn may solve the 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

March 19, 2013 at 6:55 am

#1598398
RE: Insertion in table

kapil_kk (3/19/2013)
ChrisM@Work (3/19/2013)
kapil_kk (3/18/2013)
...How to achieve this?
plz help
Do you need help with the stub code I posted?
Thanks Chris, I have implemented that thing....
But I am not clear why you posted...
^{“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

March 19, 2013 at 6:34 am

#1598391
RE: How to add below complex conditions in SQL script..

bhushan_juare (3/19/2013)
[font="Courier New"]Hi Chris,
Yes, my concern is absolutely right because sales_data table gave me all order quantities details(i.e MATNR) whose Purchase Order is generated and ready to dispatched else 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

March 19, 2013 at 6:27 am

#1598389
RE: select first alphabet

;WITH Sampledata (MyString) AS (
SELECT 'value Based' UNION ALL
SELECT 'value Discount' UNION ALL
SELECT 'Percentage Discount')
SELECT
s.MyString,
Word1Initial = LEFT(s.MyString,1),
Word2Initial = SUBSTRING(s.MyString, x.pos,1)
FROM Sampledata s
CROSS APPLY (SELECT pos =...
^{“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

March 19, 2013 at 2:36 am

#1598328
RE: select first alphabet

kapil_kk (3/19/2013)
i have a column in my table which has values like 'value Based', 'value Discount', 'Percentage Discount'.
I want to extract first letter from these words as 'VB', 'VD', 'PD'.
How...
^{“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

March 19, 2013 at 2:14 am

#1598323
RE: Insertion in table

kapil_kk (3/18/2013)
...How to achieve this?
plz help
Do you need help with the stub code I posted?
^{“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

March 19, 2013 at 1:52 am

#1598316
RE: how to combine and convert two integer columns to datetime

opc.three (3/15/2013)
PS Forgot to mention the function is undocumented so take that into consideration.
It's a cough scalar function too.
Combine the best bits of Lynn's and Scott's solutions and roll...
^{“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

March 18, 2013 at 10:05 am

#1598096
RE: Print error message

Split thread. Original here.
^{“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

March 18, 2013 at 8:00 am

#1598051
RE: Top N makes query run faster

Nested loops prefetch bail-out?
Try concatenating on the remote side and casting the result to something sensible. You might also benefit from using two OPENQUERY queries (to guarantee remote execution 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

March 18, 2013 at 7:26 am

#1598046
RE: CAST CONVERT doesn't work

walter.habegger (3/18/2013)
Thank you both for your replies, as a result, I saw that all data of the source field where correct.
I removed the .NET Framework 3.5.1 (KB2789645) and 4 (KB2789642)...
^{“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

March 18, 2013 at 6:36 am

#1598030
RE: First and Last number

kapil_kk (3/18/2013)
;with cte as
((select min(VoucherNo) VoucherNo, TransactionID from GV_Voucher
group by TransactionID
--order by VoucherNo asc
)
union all
select max(VoucherNo)VoucherNo, TransactionID from GV_Voucher
group by TransactionID
--order by VoucherNo desc
)
select * from cte
Thsi will...
^{“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

March 18, 2013 at 5:58 am

#1598017

Viewing 15 posts - 5,011 through 5,025 (of 10,143 total)