ChrisM@Work

Forum Replies Created

Viewing 15 posts - 9,526 through 9,540 (of 10,143 total)

RE: Formatting...date output

You will need to cast your return value as CHAR (or VARCHAR) if you want an embedded non-numeric character:
[font="Courier New"]DECLARE @weekNum INT, @WeekName CHAR(7)
SET @weekNum = 200843
SET @WeekName = STUFF(CAST(@weekNum...
^{“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

September 24, 2008 at 8:49 am

#876430
RE: Count between two days

Hi Ram
I'm not exactly sure what you're looking for: whether it's start and end date in the same row to get the days difference between the two, or to perform...
^{“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

September 24, 2008 at 8:12 am

#876387
RE: Divide by zero error encountered......

Hi b_boy
The part in bold is evaluating to zero for some rows:
(100.*
sum(case datepart(yy,t.orderdate) when 2008 then 1. else 0. end) /
...
^{“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

September 24, 2008 at 5:36 am

#876250
RE: select from GETDATE()?

Madhivanan, quite right! I don't know why the conversion I posted is being used - possibly from a previous version. It's used extensively here to generate a suffix for export...
^{“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

September 24, 2008 at 5:22 am

#876240
RE: select from GETDATE()?

SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 102), '.', '')
^{“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

September 24, 2008 at 5:10 am

#876226
RE: Add 1 to integer Field

You're welcome Gareth, many thanks for the feedback.
^{“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

September 23, 2008 at 8:34 am

#875714
RE: Add 1 to integer Field

Hi Gareth
A really useful habit to get into is to always SELECT whatever you're intending to UPDATE first. If you run a simple SELECT...
SELECT myColumn
FROM tbCities08
WHERE Unique_ID = 020001000080
...you 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

September 23, 2008 at 8:19 am

#875687
RE: getting values for current day using getDate() functon

Hi mpeters
Here's the theory:
Get the current datetime using getdate()
Strip off the time component, which gives today
Add a day to the result, which gives tomorrow with no time component:
[font="Courier New"]DECLARE...
^{“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

September 23, 2008 at 12:42 am

#875432
RE: Help on Dups

Now I gotcha, thanks for the extra explanation.
[font="Courier New"]DECLARE @Table TABLE (
        [id] bigint IDENTITY PRIMARY KEY CLUSTERED,
        lang VARCHAR(100),
        code CHAR(1) )
INSERT @Table
SELECT 'en','A' UNION ALL -- 1
SELECT 'en','A' UNION ALL --...
^{“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

September 22, 2008 at 11:58 pm

#875417
RE: Conditional logic in a view

Thanks for the feedback Robb 🙂
^{“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

September 22, 2008 at 10:21 am

#875131
RE: Help on Dups

Hello
Try this out, have a look at the result set (it will contain all the iterations of the dupes) and use it to help explain what you're trying to do.
[font="Courier...
^{“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

September 22, 2008 at 10:19 am

#875130
RE: Conditional logic in a view

rhagan (9/22/2008)
ChrisM-
The same table as the view references contains the field that will determine the what the user can see.
Example:
SELECT t.*
FROM TABLE t
WHERE t.deletedate IS NULL
AND ((IS_MEMBER('role') = 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

September 22, 2008 at 10:14 am

#875124
RE: Conditional logic in a view

rhagan (9/22/2008)
ChrisM-
The original purpose of this view was to exclude records stamped as delete - deletedate IS NULL.
So these records need to be exlcuded no matter which role the user...
^{“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

September 22, 2008 at 9:53 am

#875105
RE: Populating an IDENTITY column

Andrew Gothard (9/22/2008)
Or, have an NMR Number, plus an Identity column.
From moving over to the new system, NMR = the Identity column for all new values with the Identity column...
^{“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

September 22, 2008 at 9:29 am

#875076
RE: Conditional logic in a view

Surely this is all you need?
[font="Courier New"]SELECT t.*
FROM TABLE t
WHERE (IS_MEMBER('role') = 0 AND t.deletedate IS NULL) OR IS_MEMBER('role') = 1
--OR ID IN (SELECT ID FROM table WHERE field...
^{“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

September 22, 2008 at 9:28 am

#875073

Viewing 15 posts - 9,526 through 9,540 (of 10,143 total)