ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,251 through 2,265 (of 10,143 total)

RE: OUTER JOIN Not Producing Non Existent Record with IS NULL

serviceaellis (8/18/2015)
Please see attached.
The worksheet is the result that needs to be produced with the highlighted row.
in the Access file the two tables are:
1. PersonMembership - with all the membership...
^{“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 19, 2015 at 1:27 am

#1820953
RE: Help with T-Sql

maxlezious (8/18/2015)
Thank you so much for the updated query Chris. That's is exactly what I was after, really appreciate your time and help with the query.
You're welcome. Sample data, a...
^{“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 19, 2015 at 1:25 am

#1820952
RE: Help with T-Sql

Sure - try this:
SELECT
h.[hours],
a.ActType,
Activities = COUNT(a.ActType),
Points = COUNT(*) * x.points
FROM #tblActivities a
CROSS APPLY (
SELECT
[hours] = CASE
WHEN a.ActType = 'Motion' THEN DATEPART(HOUR, a.isoTimeStamp)
WHEN a.ActType =...
^{“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 18, 2015 at 10:28 am

#1820817
RE: Help with T-Sql

Here's a halfway point:
SELECT
a.*,
x.[hours],
Points = COUNT(*) OVER(PARTITION BY a.ActType, x.[hours]) * x.points
FROM #tblActivities a
CROSS APPLY (
SELECT
[hours] = CASE
WHEN a.ActType = 'Motion' THEN DATEPART(HOUR, a.isoTimeStamp)
WHEN...
^{“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 18, 2015 at 8:58 am

#1820784
RE: Help with T-Sql

Excellent job on putting together sample data. Can you post up what you expect your output to look like? Cheers.
^{“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 18, 2015 at 8:42 am

#1820777
RE: OUTER JOIN Not Producing Non Existent Record with IS NULL

serviceaellis (8/18/2015)
It is showing but showing 2014 and 2015.
It should show 2015 AND a null row like ClubNo 8004
So neither solutions are showing ClubNo 5305 null row like ClubNo 8004.
Only...
^{“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 18, 2015 at 8:31 am

#1820775
RE: OUTER JOIN Not Producing Non Existent Record with IS NULL

serviceaellis (8/18/2015)
Thank you both ...
however ...
ChrisM@Work,
yours is showing 2014.
and both are not showing ClubNo 5305 with a null membership info like ClubNo 8004.
NOTE: 5305 has memberships in 2015 and prior...
^{“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 18, 2015 at 8:10 am

#1820755
RE: Today's Random Word!

djj (8/18/2015)
Ed Wagner (8/17/2015)
Luis Cazares (8/17/2015)
whereisSQL? (8/17/2015)
Eirikur Eiriksson (8/17/2015)
Ed Wagner (8/17/2015)
eccentricDBA (8/17/2015)
whereisSQL? (8/17/2015)
DonlSimpson (8/17/2015)
Optimizer
Cost-based
low-cost provider strategy
Low Quality
WYPIWYG
Consultant
Contractor
Temp
Intern
Affairs
^{“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 18, 2015 at 7:58 am

#1820741
RE: Long running Select Query

What datatype is te.TripEndTime?
^{“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 18, 2015 at 7:01 am

#1820697
RE: Long running Select Query

Try this:
SELECT
TE.JobID,
TotalDistanceTravelled as Distance,-- what table is this from?
TotalTripFare AS TotalFare -- what table is this from?
FROM [sqlcdscluster\sqlcds].cdsbusiness.dbo.Tbljobbooking MW
INNER remote JOIN dbo.tbltripenddetail TE
ON MW.JobID = TE.jobID
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

August 18, 2015 at 6:59 am

#1820696
RE: Help Needed in Relational Logic

born2achieve (8/18/2015)
Hi Chris,
Sorry, Am not sure whats wrong in the test data. Please help me on understanding what wrong in test data.
Hi Eirik,
Thanks for your sample.
You say "@Process table data,...
^{“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 18, 2015 at 6:08 am

#1820679
RE: Help Needed in Relational Logic

born2achieve (8/18/2015)
Hi Chris,
Thanks for your reply and @Process and at @steps are two tables.
select ProcessName,S.Stepname as parent,S.stepname as child from @Process P join @steps S.......
any suggestions and sample please
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

August 18, 2015 at 5:56 am

#1820675
RE: Help Needed in Relational Logic

born2achieve (8/18/2015)
Hi,
Sample data to play with,
declare @Process table(ProcessId int identity(1,1) primary key,ProcessName varchar(100),ParentStep int, ChildStep int);
Insert into @Process(ProcessName,ParentStep,ChildStep)
select 'Process1',1,2 union all
select 'Process2',1,3 union all
select 'Process3',1,6 union all
select 'Process4',4,2 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 18, 2015 at 5:39 am

#1820667
RE: Bizarre Divide by Zero behaviour

-- This has become more or less the gold standard method
-- for avoiding the division by zero error:
SELECT
i.CPSP35,
i.VINT35,
ISNULL(i.CPSP35 / NULLIF(i.VINT35,0),0) AS High
FROM dbo.SSCCWorderExtract e
INNER JOIN DEV.dbo.INP35 i
ON...
^{“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 18, 2015 at 2:02 am

#1820637
RE: OUTER JOIN Not Producing Non Existent Record with IS NULL

-- Note the use of aliases to reduce noise and make your query more clear
-- avoid right joins, most humans can't read them
-- don't put a column from an outer-joined...
^{“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 18, 2015 at 1:56 am

#1820635

Viewing 15 posts - 2,251 through 2,265 (of 10,143 total)