ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,736 through 3,750 (of 10,143 total)

RE: select latest records

Use ROW_NUMBER().
If you can knock up a CREATE TABLE and a few INSERTs, someone will show you 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

January 21, 2014 at 6:17 am

#1681853
RE: TSQL Help to get results based on starttime

Move your search predicates from the WHERE clause to the output. This a) gives you some output and b) shows you why it doesn't work:
--I'm not able to get results...
^{“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, 2014 at 2:46 am

#1681782
RE: How to calculate sales from the table?

I can't tell what you are trying to do here because the numbers and the descriptions don't appear to match up. It doesn't make sense. Let's restructure your sample data...
^{“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, 2014 at 2:17 am

#1681773
RE: How to neglect lowest value in my o/p?

As a frequent visitor to ssc you already know that you are more likely to get a tested and accurate solution if you provide sample data in a ready-to-consume format....
^{“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, 2014 at 1:56 am

#1681767
RE: Replace order of operations

You can use replace:
SELECT *
FROM (SELECT ssn = '0123456789') d
CROSS APPLY (
SELECT
ssnAllway = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
ssnhalfway,
'a','3'),'b','9'),'c','1'),'d','6'),'e','2'),'f','8'),'g','0'),'h','7'),'i','4'),'j','5')
FROM (
SELECT
ssnHalfway = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
ssn,
'0','a'),'1','b'),'2','c'),'3','d'),'4','e'),'5','f'),'6','g'),'7','h'),'8','i'),'9','j')
) d
) y
SELECT *
FROM (SELECT ssn = '0123456789') d
CROSS APPLY (
SELECT
ssnAllway...
^{“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 20, 2014 at 9:42 am

#1681629
RE: Calculating Duration Using DATETIME Start and End Dates (SQL Spackle)

When the hour increments from one value to the next, or from 12 to 1, an hour boundary is said to have been crossed. The same happens for the other...
^{“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 20, 2014 at 8:11 am

#1681570
RE: Need query to add dummy records

declare @data table (CorpId INT, Days CHAR(3), Value MONEY)
INSERT INTO @data
SELECT 1, 'Sun' , 789.00
UNION SELECT 1, 'TUe' , 66.00
UNION SELECT 1,'Thu' , 566.00
UNION SELECT 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 20, 2014 at 5:58 am

#1681496
RE: select only similar names

SELECT d.name
FROM (
SELECT
n2.name,
ct = COUNT(*) OVER(PARTITION BY n1.name)
FROM names n1
INNER JOIN names n2 ON n1.name LIKE '%'+n2.name+'%'
) d
WHERE ct > 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 20, 2014 at 2:22 am

#1681455
RE: To get row from select query in order

ramachandran narayanan (1/19/2014)
...I have table called 'UserDetails'. If I execute below select query
it should display in order of uno= 7,13,5 but i get in order of
uno=5,7,13....
What are the business...
^{“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 20, 2014 at 1:39 am

#1681448
RE: Difference between two date/times and flag each hour in between

This looks like the output from a CROSSTAB query. If so, you would normally aggregate across whatever the partition is and use MAX() on each column value. Post your query...
^{“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, 2014 at 9:47 am

#1681142
RE: Perfromance Issue with the Query...

For testing purposes, comment out the INSERT part of the batch. Run this test batch a couple of times to obtain a performance baseline.
The difference between actual and estimated...
^{“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, 2014 at 2:29 am

#1680989
RE: delete from table based on where exists

Abu Dina (1/16/2014)
... It's just better to use a syntax similar to
SELECT * FROM SSC2 AS a WHERE EXISTS (SELECT ID, Name FROM SSC1 AS b WHERE a.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

January 16, 2014 at 3:32 am

#1680572
RE: Join

ashley.shookhye (1/15/2014)
Inner join works perfectly fine. Thank you so much.
Improving the signal-to-noise ratio of a query aids readability for you and for others who might inherit the code later. Try...
^{“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, 2014 at 2:11 am

#1680561
RE: delete from table based on where exists

The message you're getting suggests you have a trigger on the table you are deleting from, and that the trigger has been written to handle single-row changes, not the set-changes...
^{“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, 2014 at 1:44 am

#1680553
RE: delete from table based on where exists

-- if this outputs the results you want to delete,
SELECT b.*
FROM dbo.MediaDetails b
INNER Join dbo.MediaHeader a
ON a.MediaId = b.MediaId
WHERE NOT EXISTS (
SELECT 1
FROM dbo.BaseForecast c
WHERE c.Quantity > 0...
^{“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 15, 2014 at 6:40 am

#1680239

Viewing 15 posts - 3,736 through 3,750 (of 10,143 total)