ChrisM@Work

Forum Replies Created

Viewing 15 posts - 8,086 through 8,100 (of 10,143 total)

RE: GETDATE in WHERE Clause

Vipul Jaba (3/11/2010)
Try this:
SELECT
dbo.Product.ProductID,
dbo.Product.Category,
dbo.Product.ProductCode,
dbo.Product.Description,
dbo.Product.Price,
dbo.[Order].CustomerID,
dbo.[Order].OrderID,
dbo.[Order].OrderDate
FROM
dbo.[Order]
INNER JOIN dbo.Product ON dbo.[Order].ProductID = dbo.Product.ProductID
WHERE
convert(datetime,convert(varchar(20),dbo.[Order].OrderDate,106)) = convert(datetime,convert(varchar(20),getdate(),106))
dbo.[Order].OrderDate is no longer SARGable.
The INNER JOIN will lose orders with no...
^{“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 11, 2010 at 3:21 am

#1131908
RE: Today's Random Word!

Flying carpets
^{“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 11, 2010 at 2:00 am

#1131871
RE: Are the posted questions getting worse?

Lynn Pettis (3/10/2010)
Paul White (3/10/2010)
Lynn Pettis (3/10/2010)
Actually, the last 10 minutes or so of the game was quite exciting compared to the first 70 minutes. It took that save...
^{“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 10, 2010 at 8:40 am

#1131451
RE: GETDATE in WHERE Clause

Lynn Pettis (3/10/2010)
I'd be curious to know why this where clause didn't work:
WHERE ( ord.OrderDate >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) 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

March 10, 2010 at 7:20 am

#1131380
RE: Where are you?

GilaMonster (3/10/2010)
Chris Morris-439714 (3/10/2010)
Yes. In Swindon, computers change hands too quickly for any serious development to take place.
Sounds like Johannesburg. I'd probably feel right at home there.
Of course, you have...
^{“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 10, 2010 at 7:19 am

#1131377
RE: Are the posted questions getting worse?

Lynn Pettis (3/9/2010)
However, I appreciate the sentiment, thank you! :smooooth:
Lynn Pettis, Most Valuable Parent does it for me.
^{“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 10, 2010 at 7:13 am

#1131364
RE: count(uniqueidentifier data type field)

Like Lutz, I don't have an answer to why you can't use COUNT().
However, I'd suggest that, since you can't (and shouldn't be able to) use MAX() by design because...
^{“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 10, 2010 at 6:53 am

#1131343
RE: Removing overlapping rows based on dates

What about this row?
06 January 2010 |05 February 2010
^{“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 10, 2010 at 5:51 am

#1131279
RE: Having total amount at every row level

Bhuvnesh (3/10/2010)
try this DECLARE @Ranges_1 TABLE (
[ctr] int identity ,
[Id] int NOT NULL,
[varchar](3) NOT NULL,
[dos] int NOT NULL,
[paid] float NOT NULL
)
INSERT INTO @Ranges_1 VALUES (1, '123', '20', '10.75');
INSERT INTO...
^{“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 10, 2010 at 4:50 am

#1131259
RE: GETDATE in WHERE Clause

Aeterna (3/10/2010)
Or try this to get today's orders:
CREATE VIEW TodaysOrders
AS
SELECT
prd.ProductID,
prd.Category,
prd.ProductCode,
prd.Description,
prd.Price,
...
^{“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 10, 2010 at 4:00 am

#1131247
RE: GETDATE in WHERE Clause

This is the equivalent without using a variable:
SELECT
prd.ProductID,
prd.[Description],
prd.Price,
ord.OrderID,
ord.OrderDate
FROM dbo.[Order] ord
LEFT JOIN dbo.Product prd
ON ord.ProductID = prd.ProductID
WHERE ord.OrderDate = CAST(GETDATE() AS DATE)
^{“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 10, 2010 at 3:26 am

#1131236
RE: GETDATE in WHERE Clause

rburke 87921 (3/10/2010)
Forget that last post. I'm being a k*ob!. That works now thankyou very much.
Heh funny! No you're not, you're learning.
Was it the inner join?
^{“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 10, 2010 at 3:13 am

#1131226
RE: GETDATE in WHERE Clause

rburke 87921 (3/10/2010)
I probably should have mentioned that I'm trying to create a view. Doesn't seem to like the DECLARE statement.
Why try to turn a query into a view when...
^{“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 10, 2010 at 3:06 am

#1131224
RE: Where are you?

Stuart Davies (3/10/2010)
Live and work near Swindon, Wilts in the UK
(and that is near not in Swindon)
S Hodkinson (3/10/2010)
So is Swindon that bad that you differentiate between 'near' and 'in'...
^{“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 10, 2010 at 3:02 am

#1131221
RE: GETDATE in WHERE Clause

Thanks!
Try this first:
DECLARE @Today DATE = GETDATE()
SELECT
prd.ProductID,
prd.Category,
prd.ProductCode,
prd.Description,
prd.Price,
...
^{“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 10, 2010 at 2:46 am

#1131214

Viewing 15 posts - 8,086 through 8,100 (of 10,143 total)