ChrisM@Work

Forum Replies Created

Viewing 15 posts - 1,921 through 1,935 (of 10,143 total)

RE: 4 Queries Required Very Urgent

J Livingston SQL (11/12/2015)
homework I assume?
have you actually set up some sample tables and data and run your answers to see if they are correct?
or are you looking for any...
^{“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

November 12, 2015 at 3:18 am

#1839290
RE: Delete using MERGE

Sure. Try this:
CREATE TABLE #Audit2 (RowID INT IDENTITY(1,1), CustomerID INT, [Action] CHAR(1), oldValue INT, newValue INT NULL) ;
CREATE TABLE #Trans2 (CustomerID INT, Value INT);
INSERT #Trans2 VALUES (1,100), (2,200), (3,300), (4,400),...
^{“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

November 12, 2015 at 2:21 am

#1839279
RE: Delete using MERGE

oradbguru (11/11/2015)
The merge would be based on a single unique id. I am deleting millions of records from a table that has 150+ million records in it. This...
^{“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

November 11, 2015 at 10:21 am

#1839163
RE: Need help finding one set of data within another set of data for update

Also late to the party; have you considered stripping the odd text elements out of the column to leave the city name? You'd want to do this as a one-off.
DROP...
^{“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

November 11, 2015 at 2:10 am

#1839047
RE: SQL Column update with comparison with another table

Your code looks ok. What datatype is dbo.R_ResrvStock.Vin?
^{“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

November 10, 2015 at 4:47 am

#1838793
RE: Select case - using "in" instead of "="

daliborh (11/10/2015)
Ok guys, going nuts here.
Let me explain the aim here:
What I'm trying to accomplish is,
make sql select values '01x', '02x' in line "and left(i.itemID,3) in
based on 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

November 10, 2015 at 2:33 am

#1838776
RE: Get FirstDate and LastDate of Every month from two Dates

david.fundakowski (11/9/2015)
I would go with a simple recursive statement.
DECLARE @dateone AS DATE = '2012-01-01'
DECLARE @datetwo AS DATE = '2015-04-12'
;WITH cte AS (
SELECT 1 AS month_
UNION ALL
SELECT 1+ month_...
^{“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

November 10, 2015 at 1:57 am

#1838774
RE: Group by + Count + Case

saregama (11/9/2015)
Can you please help me here to write sql query on a table with below values to get desired output only using groupby and count and Case statement.
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

November 9, 2015 at 5:34 am

#1838516
RE: Get FirstDate and LastDate of Every month from two Dates

Why write a slow unwieldy multi-statement table-valued function when an inline table-valued function will do?
DECLARE
@StartDate DATE = '05/02/2015',
@EndDate DATE = '11/25/2015'
SELECT
ID = n+1,
FirstDate = DATEADD(MONTH,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

November 9, 2015 at 2:47 am

#1838491
RE: In vs Outer Join

farax_x (11/9/2015)
I want to compare two tables via liked server (number of records which exists only in dbo.Token)
SELECT COUNT(1)
FROM dbo.Token TD WITH ( NOLOCK )
WHERE ...
^{“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

November 9, 2015 at 2:12 am

#1838481
RE: DateAdd overflow using Numbers Table

ArcticEd32 (11/5/2015)
Thanks to all for the replies
kaj (11/5/2015)
The only thing I can think of that would cause this is if you have an unordered table, where n in one 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

November 6, 2015 at 2:52 am

#1838165
RE: TREATMENT GAPS OF 2 OR MORE DAYS USING SERVICE DATES

Mick Lovell (11/5/2015)
1. The field I'm using for service date is a datetime. They all appear to have '12:00:00 AM' as the TIME for each service date.
2. I am...
^{“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

November 5, 2015 at 10:06 am

#1838045
RE: TREATMENT GAPS OF 2 OR MORE DAYS USING SERVICE DATES

Do any of the dates have times?
Do you have multiple visits on the same day?
Is there another column which would distinguish between two people with the same name?
^{“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

November 5, 2015 at 9:35 am

#1838034
RE: Are the posted questions getting worse?

yb751 (11/5/2015)
Brandie Tarvin (11/5/2015)
At risk of being lynched: Crystal Reports is probably the easiest ad-hoc reporting tool a user with no experience can learn to use.
Well I agree with you...
^{“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

November 5, 2015 at 6:33 am

#1837951
RE: Are the posted questions getting worse?

Brandie Tarvin (11/5/2015)
BWFC (11/5/2015)
Woolly nebulous request of the month alert.
I've been asked to look into ad-hoc reporting solutions so users can create their own reports.
All I know is it's 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

November 5, 2015 at 6:18 am

#1837940

Viewing 15 posts - 1,921 through 1,935 (of 10,143 total)