ChrisM@Work

Forum Replies Created

Viewing 15 posts - 9,646 through 9,660 (of 10,143 total)

RE: pivoting data

Duplicate post, see http://www.sqlservercentral.com/Forums/Topic559341-338-1.aspx
^{“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

August 28, 2008 at 12:53 am

#864148
RE: Require assistance in Constructing a View

GSquared (8/27/2008)
Why build a function for it? That's row-by-row processing. The join I gave you should do what you need. I've used that type of construct many...
^{“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

August 27, 2008 at 8:14 am

#863763
RE: Require assistance in Constructing a View

Andrew Peterson (8/27/2008)
The main issue here is to get the previous odometer readings, given the current vehicle ID and reading date. So, first I would setup a function that...
^{“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

August 27, 2008 at 7:29 am

#863723
RE: Rows display

Chandu (8/27/2008)
Exscellent.. Selcet * from t1 has worked.. Thanks to all for working out on the query.
Gald to be of assitsance, Chnadu!
^{“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

August 27, 2008 at 7:23 am

#863716
RE: t-sql help lpease

Each BEGIN should have one corresponding END.
This is also likely to cause some problems...
SELECT distinct
...
^{“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

August 27, 2008 at 7:16 am

#863709
RE: convert different rows ( dynamic no.of rows ) into different columns

Hi Chris, thanks for pointing this out, though I agree that the OP should do some of the work here! My solution will work where there are dupes in 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

August 27, 2008 at 6:44 am

#863677
RE: convert different rows ( dynamic no.of rows ) into different columns

Here is another possible solution:
[font="Courier New"]-- Create sample data
CREATE TABLE #Table ([no] INT, [name] CHAR(3))
INSERT INTO #Table ([no], [name])
SELECT 1, 'aaa' UNION ALL
SELECT 1, 'bbb' UNION ALL
SELECT 1, 'ccc' UNION...
^{“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

August 27, 2008 at 5:59 am

#863647
RE: Rows display

Chandu (8/27/2008)
How do i display all the values in all 23 available columns of a table t1 when i don't know the number of rows available.
This should do the trick:
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

August 27, 2008 at 5:27 am

#863625
RE: updatestatement

kumar99ms (8/27/2008)
Employee table
EmployeeID Employeename Employeecode Passportcode
1 aaaa ...
^{“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

August 27, 2008 at 3:43 am

#863585
RE: Require assistance in Constructing a View

[font="Courier New"]-- make some sample data
SET DATEFORMAT MDY
DROP TABLE #Temp
CREATE TABLE #Temp (FuelLogID INT, VehicleID INT, [Date] DATETIME, FuelQty INT, EndOdometer INT)
INSERT INTO #Temp (FuelLogID, VehicleID, [Date], FuelQty, EndOdometer)
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

August 27, 2008 at 3:37 am

#863582
RE: TVF with Cursor - is there a better way?

Sure Jack!
Multiply the decimal fraction by 4,
0.26 becomes 1.04
0.24 becomes 0.96
then Cast as INT to truncate
0.26 becomes 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

August 22, 2008 at 9:22 am

#861982
RE: TVF with Cursor - is there a better way?

Whatever did we do without tally tables?
[font="Courier New"]DROP TABLE #ADPTimeDetail
CREATE TABLE #ADPTimeDetail (EmpID INT, [IN_Punch] DATETIME, [OUT_Punch] DATETIME, Job VARCHAR(3), [Punch_RecID] INT)
INSERT INTO #ADPTimeDetail (EmpID, [IN_Punch], [OUT_Punch], Job, [Punch_RecID])
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

August 22, 2008 at 8:56 am

#861962
RE: TSQL Message Filter

Good point Ryan:
[font="Courier New"]DECLARE @Delimiter VARCHAR(4)
SET @Delimiter = ':61:'
SELECT * FROM (
   SELECT SUBSTRING([Message]+@Delimiter, number-LEN(@Delimiter),
           LEN(@Delimiter)+CHARINDEX(@Delimiter, [Message]+@Delimiter, number) - number) AS MESSAGE
   FROM Numbers, #t1
   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

August 22, 2008 at 7:34 am

#861882
RE: TSQL Message Filter

Using a few changes to the ever-popular uftSplitString function,
[font="Courier New"]
CREATE TABLE #t1
(
MESSAGE VARCHAR(8000)
)
INSERT INTO #t1
SELECT '
{1:F01PARBESMXFXXX0074301877}
{2:I950MLILGB4LXKSIN}
{3:{108:MMD0192030369000}}
{4:
:20:GL0710250013
:25:0000110405
:28C:00211/00001
:60F:C071024EUR778881,80
:61:0710221025DR108,84NDIVAJCD506148ES01//1AJCD506148
ES0111845014 AC.ABERTIS INFRAE
:61:0710251025CR1,31NDIVCD879992ES01//1CD879992
ES0109427734 AC. ANTENA 3
:61:0710251025CR1,31NDIVCD880006ES01//1CD880006
ES0109427734 AC. ANTENA 3
:61:0710251025CR1,31NDIVCD880008ES01//1CD880008
ES0109427734 AC. ANTENA 3
:61:0710251025CR1,31NDIVCD880025ES01//1CD880025
ES0109427734 AC. ANTENA 3
:61:0710251025CR1,31NDIVCD880029ES01//1CD880029
ES0109427734...
^{“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

August 22, 2008 at 5:44 am

#861807
RE: How to use column name inlike clause in SQL server

Maroti, this would be a heck of a lot easier to solve if the query was nicely formatted, as follows:
[font="Courier New"]
FROM maintSubStrategySecurityType
RIGHT JOIN tblBlackrockPositionsByFund AS Positions
ON maintSubStrategySecurityType.[Sub...
^{“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

August 22, 2008 at 4:24 am

#861762

Viewing 15 posts - 9,646 through 9,660 (of 10,143 total)