ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,796 through 3,810 (of 10,143 total)

RE: sort by serialcode

Jeff Moden (1/4/2014)
sayedkhalid99 (1/3/2014)
what change shall i bring in here so that if the serial don't have any decimal point say , it gives error in left function.
DECLARE @Result 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

January 6, 2014 at 2:21 am

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

cs_source (1/3/2014)
okay, wow well thank you all for your queries, i learnt something new with each one, the last query outputs the data in the format i'm exactly looking for...
^{“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 3, 2014 at 9:59 am

#1677367
RE: SUM of COUNT

It's a DB2 error, Laurie 😀
^{“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 3, 2014 at 8:59 am

#1677331
RE: sort by serialcode

DECLARE @Result TABLE (Serial nvarchar(10),Title varchar(20))
INSERT INTO @Result
SELECT '1.1','a' UNION ALL
SELECT '1.2.1','b' UNION ALL
SELECT '1.2.2','C' UNION ALL
SELECT '1.2.3.4','G' UNION ALL
SELECT '1.11','B' UNION ALL
SELECT '1.2.33.4.5','Extra row' UNION ALL
SELECT '2.3','B' UNION ALL
SELECT...
^{“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 3, 2014 at 8:45 am

#1677321
RE: Return random records in a table-valued function?

Jeff Moden (1/3/2014)
ChrisM@Work (1/3/2014)
Jeff Moden (1/2/2014)
You can also just create a view called (for example) ANewID that generates a GUID as a single column, single row result and simply 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

January 3, 2014 at 8:29 am

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

;WITH
[4] AS (SELECT n = 0 FROM (VALUES (0), (0), (0), (0)) d (n)),
[16] AS (SELECT n = 0 FROM [4] a, [4] b),
[256] AS (SELECT n = 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 3, 2014 at 8:27 am

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

cs_source (1/3/2014)
Thank you dwain.c, i'm working through the query now to under stand it (pretty complicated 🙂
hi Chris.m below is the sample data as well as my expected output (sorry...
^{“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 3, 2014 at 8:01 am

#1677294
RE: T SQL QUERY TO GROUPING SEQUENTIAL DATES IN SQL SERVER.

You're welcome, 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 3, 2014 at 7:16 am

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

cs_source (1/2/2014)
Hi salamex,
Thank you, this query is very similar to my first query and it works great if the car is in "service/shop" on the same day, the problem i...
^{“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 3, 2014 at 7:11 am

#1677260
RE: T SQL QUERY TO GROUPING SEQUENTIAL DATES IN SQL SERVER.

Thanks.
-- try this
SELECT a.EID, a.RATING, a.AbsenceStartDate,
AbsenceEndDate = ISNULL(b.AbsenceEndDate, a.AbsenceEndDate)
FROM @COMP_RATINGS a
LEFT JOIN @COMP_RATINGS b
ON b.EID = a.EID
AND b.RATING = a.RATING
AND b.AbsenceStartDate = a.AbsenceEndDate
WHERE NOT EXISTS (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 3, 2014 at 6:42 am

#1677240
RE: sort by serialcode

What's the maximum number of elements you can have in a "serial"? Your sample data shows 3, can it be more? There are two good ways of splitting this 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

January 3, 2014 at 6:32 am

#1677234
RE: T SQL QUERY TO GROUPING SEQUENTIAL DATES IN SQL SERVER.

Source data
(769,'b+','2011-06-30','2011-09-30'),
Required output
5364914 b+ 2011-09-30 00:00:00.0000000 2011-12-31 00:00:00.0000000
If the required output is incorrect then please correct it.
^{“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 3, 2014 at 6:27 am

#1677231
RE: T SQL QUERY TO GROUPING SEQUENTIAL DATES IN SQL SERVER.

Thanks.
Please explain the result for rating = 'b+'.
There's only one row with this value in the sample data, and it's completely different in the required output.
^{“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 3, 2014 at 6:11 am

#1677221
RE: T SQL QUERY TO GROUPING SEQUENTIAL DATES IN SQL SERVER.

kbhanu15 (1/3/2014)
HI MITESH,
THANK YOU FOR YOUR HELP.
THE QUERY IS SATISFYING THE SAMPLE GIVEN DATA.
BUT IT IS NOT SOLVING MY ORIGINAL PROBLEM.
THE ORIGINAL DATA IN THE TABLE:
5364914b+2011-09-30 00:00:00.00000002011-12-31 00:00:00.0000000
5364914bb2011-06-30 00:00:00.00000002011-09-30 00:00:00.0000000
5364914bb2011-12-31...
^{“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 3, 2014 at 5:26 am

#1677214
RE: T SQL QUERY TO GROUPING SEQUENTIAL DATES IN SQL SERVER.

Please stop shouting.
^{“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 3, 2014 at 4:15 am

#1677194

Viewing 15 posts - 3,796 through 3,810 (of 10,143 total)