ChrisM@Work

Forum Replies Created

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

RE: Selecting all data from employee where forign key = null

--suppose i need to show all data from Employee table (NationalityName,BranchName,JobName) where
--NationalityID =NULL OR BranchID=NULL OR JobID=NULL
--WHAT I DO
-- Use outer joins on those columns, like this:
SELECT
n.NationalityName, e.DriverName, e.DriverID,...
^{“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

September 9, 2014 at 1:59 am

#1744032
RE: Selecting all data from employee where forign key = null

Here's your original query reformatted and using table aliases:
SELECT
n.NationalityName, e.DriverName, e.DriverID, e.NationalityID, e.ResidentNo, c.CountryName,
e.CountryID, b.BranchName, e.BranchID, e.JoinDate, e.ResignDate, e.HealthCarNo, e.JobID,
j.JobName, d.DepartmentName, j.DepartmentID, e.PlaceIssue, e.Deduction, e.ExpireDateMedical,
e.PolicyNumber, e.[Owner],...
^{“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

September 9, 2014 at 1:54 am

#1744031
RE: Selecting all data from employee where forign key = null

SELECT NationalityName, BranchName, JobName
FROM Employee
WHERE NationalityID IS NULL OR BranchID IS NULL OR JobID IS NULL
^{“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

September 9, 2014 at 1:44 am

#1744027
RE: SQL import data when both source and destination has data and identity column

IT researcher (9/8/2014)
I am using Import and export data option to import data from one database to another database. Both database have similar structure of tables. All the tables...
^{“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

September 8, 2014 at 10:22 am

#1743920
RE: Not Cast?

DECLARE @Code VARCHAR(4)
DROP TABLE #SampleData
SELECT Code = CAST('20' AS VARCHAR(4)) INTO #SampleData UNION ALL
SELECT '0020' UNION ALL
SELECT '786' UNION ALL
SELECT '0786'
SET @Code = '20'
SELECT *
FROM #SampleData
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

September 8, 2014 at 8:59 am

#1743888
RE: Today's Random Word!

Ed Wagner (9/8/2014)
crookj (9/8/2014)
Ed Wagner (9/7/2014)
TomThomson (9/5/2014)
Ed Wagner (9/5/2014)
Sean Lange (9/5/2014)
Capsize
Expensive
Dear
Yes, Dear
Honey-Do
Perpetual
Peripatus
^{“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

September 8, 2014 at 7:37 am

#1743862
RE: cost of clustered index insert is 100%

charipg (9/8/2014)
actually it inserting simple single record.
we estimated time should be milli seconds, but its taking 2min.
need to find what might be causing this. not getting any clue.
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

September 8, 2014 at 6:47 am

#1743843
RE: Data from one Table to Two Columns

SELECT *
FROM D_DRIVER_LOGGED_HISTORY i
OUTER APPLY (
SELECT TOP 1 LOGGED_DATETIME
FROM D_DRIVER_LOGGED_HISTORY o
WHERE o.DRIVER_ID = i.DRIVER_ID
AND o.IS_LOGGED_IN = 0
AND o.LOGGED_DATETIME > i.LOGGED_DATETIME
ORDER BY LOGGED_DATETIME
) oa
WHERE i.IS_LOGGED_IN = 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

September 5, 2014 at 6:53 am

#1743502
RE: Data from one Table to Two Columns

patelmohamad (9/5/2014)
Hi Koen Verbeeck,
I have tried it but no luck, I have 81 Records for that particular driver but it is multiplying the records and the output value is 6561...
^{“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

September 5, 2014 at 6:04 am

#1743488
RE: Adding a number to a string to create series

Eirikur Eiriksson (9/5/2014)
ChrisM@Work (9/5/2014)
Meatloaf (9/3/2014)
Hello,
I trying to figure out the logic to add a number to the end of an ID to create a series.
For example, I have an EventID...
^{“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

September 5, 2014 at 5:29 am

#1743475
RE: Adding a number to a string to create series

Meatloaf (9/3/2014)
Hello,
I trying to figure out the logic to add a number to the end of an ID to create a series.
For example, I have an EventID that may have...
^{“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

September 5, 2014 at 4:03 am

#1743462
RE: View results not displaying nil columns

Try this:
SELECT
c.cltSortName AS ClientName,
a4.ainTVal AS ClientPartner,
a422.ainDVal AS [Date Added],
ai.ainTVal AS Inudstry
FROM dbo.cdbClient c
LEFT OUTER JOIN dbo.cdbObject o
ON c.cltCategoryID = o.objID
LEFT OUTER JOIN dbo.vcltAttrib4 a4...
^{“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

September 5, 2014 at 2:01 am

#1743428
RE: Help with the query

Not really sure what you are trying to do here - but this is a simple way to assign numbers to the Cycles in your data set:
;WITH CycleEnds AS (
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

September 4, 2014 at 7:02 am

#1743185
RE: Trying to explain LEFT OUTER JOIN

-- Simplify the query and examine the unaggregated result of the INNER JOIN
SELECT
a.[status], a.batch,
b.[status], b.batch
FROM AP4_packing_leg1_HSM_status a
INNER JOIN AP4_packing_leg1_HSM_Faults b
ON a.[status] = b.[status]
AND a.batch = 105
--group by...
^{“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

September 4, 2014 at 3:40 am

#1743127
RE: Can a JOIN be used here instead of a UNION?

david.dartnell (9/3/2014)
Hi ChrisM@Work,
Thank you for your suggestions. I have now managed to find the line in my query which is returning NULL values.
(T0.TotalSales - T0.StockValue) / NULLIF(T0.StockValue, 0) AS 'Gross...
^{“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

September 4, 2014 at 2:00 am

#1743111

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