ChrisM@Work

Forum Replies Created

Viewing 15 posts - 8,941 through 8,955 (of 10,143 total)

RE: Adding running number on groups of records in a table

Had to Google it Seth, movies take a while to cross the pond...but "a butler who takes an odd interest in feet" - GF might find that very amusing:)
^{“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 8, 2009 at 9:06 am

#923418
RE: Adding running number on groups of records in a table

Garadin (1/8/2009)
john g (1/8/2009)
LOL, it has been quite a day so far - 2 bad responses on my part...it has definitely taught me to read the OP carefully - 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

January 8, 2009 at 8:56 am

#923404
RE: How to populate 1 row multi field from 3 rows single field

Integrate it into the body of your query, something like this:[font="Courier New"]SELECT DISTINCT
   view_leveranciers.LEV_ID,
   view_leveranciers.ADR_NAAM,
   view_leveranciers.ADR_TEL,
   view_leveranciers.PAR_NED AS FirmaNaam,
   view_leveranciers.ADR_ID,
   view_leveranciers.ADS_ID,
   TOEDETAIL.TSD_ARTNR_LEV,
  ...
^{“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 8, 2009 at 8:54 am

#923401
RE: Adding running number on groups of records in a table

john g (1/8/2009)
Lookup the ROW_NUMBER function in BOL. It should do the trick for you.
jg
Might take a while to find it in BOL for SQL2k!
This looks like a candidate...
^{“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 8, 2009 at 8:14 am

#923356
RE: Date Storage, Date Comparison

Your assumptions were spot on, AL.
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

January 8, 2009 at 7:45 am

#923327
RE: Date Storage, Date Comparison

DECLARE @Startdate DATETIME, @Enddate DATETIME
SELECT @Startdate = DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0), -- 2009-01-08 00:00:00.000
@Enddate = DATEADD(dd, 1, @Startdate) ...
^{“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 8, 2009 at 7:36 am

#923311
RE: iff in SQL server

Look up CASE in BOL.
^{“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 8, 2009 at 7:20 am

#923297
RE: How to populate 1 row multi field from 3 rows single field

eddy (1/8/2009)
SELECT tsd_id,
SUM(CASE WHEN Code = 101 THEN verkoop ELSE 0 END) AS Price1,
...
^{“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 8, 2009 at 6:27 am

#923258
RE: How to populate 1 row multi field from 3 rows single field

Try this, eddy:SELECT tsd_id,
SUM(CASE WHEN Code = 101 THEN verkoop ELSE 0 END) AS Price1,
SUM(CASE WHEN Code = 102 THEN verkoop ELSE 0 END) AS Price2,
SUM(CASE WHEN Code =...
^{“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 8, 2009 at 5:39 am

#923219
RE: display values with two precision values

nabajyoti.b (1/8/2009)
DECLARE @columnx FLOAT, @columny nvarchar(2) SELECT @columnx = 0.05, @columny = 'A'
SELECT @columny + STR(@columnx*100, 6,2) + '%'
displays A 5.00% which is having 2 spaces 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

January 8, 2009 at 4:35 am

#923189
RE: Incorrect Default Date

Well done Richard, top work.
^{“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 8, 2009 at 4:12 am

#923179
RE: display values with two precision values

DECLARE @columnx FLOAT, @columny nvarchar(2)
SELECT @columnx = 1, @columny = 'A'
SELECT @columny + convert(varchar(5),@columnx*100.00) + '%',
@columny + STR(@columnx*100, 6,2) + '%'
Output:
-------- ---------
A100% A100.00%
^{“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 8, 2009 at 4:01 am

#923176
RE: writing function fordate

if @asofdate = @priormonthdate and TimClosestatus = 'C'
What's 'TimClosestatus'? Where does it come from?
^{“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 8, 2009 at 2:50 am

#923160
RE: Incorrect Default Date

Richard, what are the results of the following two queries?
SELECT book_date as Job_Date, COUNT(*)
FROM dbo.Job
WHERE book_date IS NULL OR book_date = '1899-12-30 00:00:00.000'
GROUP BY book_date
SELECT [datetime] as Manifest_Date, COUNT(*)
FROM dbo.Job
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

January 8, 2009 at 2:37 am

#923153
RE: Grouping rows together

anu (1/7/2009)
Sorry Jon I am not an expert in SQL 🙁
I didn't understand how to use sub-query for my query.
Can you help.
Also, the above query doesn't get me the desired...
^{“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 7, 2009 at 2:30 pm

#922927

Viewing 15 posts - 8,941 through 8,955 (of 10,143 total)