ChrisM@Work

Forum Replies Created

Viewing 15 posts - 4,216 through 4,230 (of 10,143 total)

RE: Need help with TSQL

Since the sortorder column isn't particularly useful, why don't you populate it with the sort order that you actually want?
^{“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 7, 2013 at 7:20 am

#1639359
RE: Table data in tabular form

-- sample data
DROP TABLE #LeaveEntry
CREATE TABLE #LeaveEntry(
[LeaveId] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,
[LeaveTypeName] [varchar](100) NOT NULL,
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL
)
SET DATEFORMAT YMD
INSERT INTO #LeaveEntry VALUES
(1, 'A','OUT','2013-08-12 00:00:00.000','2013-08-14 00:00:00.000'),
(2, 'B','LON','2013-08-13 00:00:00.000','2013-08-14...
^{“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 7, 2013 at 5:12 am

#1639323
RE: Splitting Comma Separated Values into Rows

Use APPLY to "encapsulate" a splitter function - in this case DelimitedSplit8k[/url];
DECLARE @t Table
(
AreaID int,
AreaName nvarchar(100),
Responsible nvarchar(100)
)
Insert Into @t
Select 1, 'Finance',Null
Union All
Select 2, 'IT','Internal, External'
Union All
Select 3, 'Audit, Security', 'Internal'
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 7, 2013 at 1:55 am

#1639244
RE: select DISTINCT cost too high?

ananda.murugesan (8/6/2013)
ok.. I will update you once getting from Dev.Team for the all details..
thanks
ananda
Here's a guess at the business logic:
Where a set, partitioned on GA_Drg_NO, Rev_NO and Mark_No,
contains at...
^{“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 6, 2013 at 9:26 am

#1638992
RE: select DISTINCT cost too high?

Yes of course, but folks will need a sample data script and a decent description of what the query is supposed to do.
^{“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 6, 2013 at 6:24 am

#1638884
RE: Subquery returned more than 1 value error message

declare @Locality varchar(max)
set @Locality = 'Locality Yate'
SELECT
T.Locality,
[Specialty] = GS2.CodeDescription,
t.[Status],
[Number] = Count(T.ClientID)
FROM (
SELECT
REF.ClientID,
REF.ReferralNumber,
SpecialtyReferredTo,
ServiceTeam,
Locality = ISNULL((
SELECT TOP 1 GS.CodeDescription
FROM dbo.vwSGReferrals As REF2 -- You don't need this reference here;...
^{“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 6, 2013 at 6:06 am

#1638875
RE: Table data in tabular form

Not many folks will want to click on the link you posted. Can you post up a picture of the desired layout instead?
^{“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 6, 2013 at 5:45 am

#1638862
RE: Query Challenge

;WITH OrderedData AS (SELECT *, seq = ROW_NUMBER() OVER(ORDER BY DateS) FROM #LOGTABLE)
SELECT
l.DateS,
l.Value,
x.GroupColumn
FROM OrderedData l
CROSS APPLY (
SELECT TOP 1 GroupColumn = ROW_NUMBER() OVER(ORDER BY nr.seq)
FROM OrderedData tr
INNER...
^{“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 6, 2013 at 5:21 am

#1638855
RE: Query Challenge

Koen Verbeeck (8/6/2013)
The difference between the two rows in group 2 is 39 seconds.
You'll need to establish clear rules on how to divide the groups.
Around 20 seconds doesn't work 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

August 6, 2013 at 4:42 am

#1638850
RE: Query Challenge

Bala' (8/6/2013)
... need to group based on the time ...
Please elaborate on your group definition, including an explanation of how group 2 is derived.
^{“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 6, 2013 at 3:38 am

#1638839
RE: Confused with my procedure

Oracle765 (8/5/2013)
is there a way around this the as the variables
@columnname1 and @columnname2 could be named anything when they are passed in as this is selected from a dynamically imported...
^{“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 6, 2013 at 1:40 am

#1638818
RE: Query Help - Sum by Month

ET DATEFORMAT MDY
SELECT
DateRange = RIGHT('0'+CAST(MONTH([Date]) AS VARCHAR(2)),2)+'/'+
CAST(YEAR([Date]) AS VARCHAR(4)),
SUMAmount = SUM(Amount)
FROM (
SELECT '01/03/2012', $300 UNION ALL
SELECT '01/12/2012', $250 UNION ALL
SELECT '02/05/2012', $200 UNION ALL
SELECT '02/07/2012', $300 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

August 5, 2013 at 9:06 am

#1638635
RE: Convertion from Varchar to Datetime

SELECT CONVERT(DATETIME,RIGHT(MyDate,4)+LEFT(MyDate,4),112)
FROM (SELECT MyDate = '12252013') d
^{“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 5, 2013 at 6:23 am

#1638577
RE: Are the posted questions getting worse?

Brandie Tarvin (8/2/2013)
How many people (besides Jeff and myself) have written SQL Spackle articles?
How many SQL Spackle articles are there?
Yes, I have a reason for asking. But I want to...
^{“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 5, 2013 at 12:47 am

#1638519
RE: Help with Group ID Every X rows query

SELECT
DENSE_RANK() OVER(ORDER BY reference, collection_day, (rnum-1)/6 ),
*
FROM (
select
row_number() OVER (PARTITION BY reference, collection_day ORDER BY sort_date) AS rnum
,collection_day, reference, sort_date
from #temp
) d
order by reference, sort_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

August 2, 2013 at 4:40 am

#1638115

Viewing 15 posts - 4,216 through 4,230 (of 10,143 total)