July 17, 2016 at 1:52 pm
In a t-sql 2012 table, I have a table that contains the following table definition:
CREATE TABLE [dbo].[TranscriptCourse](
[transcriptID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[personID] [int] NOT NULL,
[startYear] [smallint] NULL,
[endYear] [smallint] NULL)
In this TranscriptCourse table, there can be lots of records for each personID. This personID can have lots of different values for endYear. I need to have a query by personID where there is not a record for endYear = 2013.
Thus can you show me the sql to solve this issue?
July 17, 2016 at 1:59 pm
There are several approaches to the problem. Here's the one I would pick:
SELECT personid
FROM dbo.TranscriptCourse
EXCEPT
SELECT personid
FROM dbo.TranscriptCourse
WHERE EndYear = 2013;
July 17, 2016 at 4:30 pm
What other sql options are you thinking that would work?
I did not include all my question since I was trying to determine what was wrong with my original sql. I thought the too many rows was causing my problem.
Here is my original sql:
declare @gradYear int = 2017
SELECT distinct graduation.personID
into #gradPersonID
FROM [TEST].[dbo].[Graduation] graduation
where graduation.gradyear = @gradYear
SELECT distinct personID
FROM #gradPersonID graduation
where personID NOT in
(select distinct TranscriptCourse.personID from #gradPersonID graduation
join [TEST].[dbo].[TranscriptCourse] TranscriptCourse
on TranscriptCourse.personID = graduation.personID
where TranscriptCourse.grade between '09' and '12'
and @gradYear - 4 = TranscriptCourse.endYear)
DROP table #gradPersonID
July 17, 2016 at 8:14 pm
This would be a much clearer question if you included sample data (create table scripts, insert scripts) and expected results.
July 17, 2016 at 11:13 pm
Ed Wagner (7/17/2016)
There are several approaches to the problem. Here's the one I would pick:
SELECT personid
FROM dbo.TranscriptCourse
EXCEPT
SELECT personid
FROM dbo.TranscriptCourse
WHERE EndYear = 2013;
Quick thought, this is a very effective solution on narrow sets, preferably single column. If the set gets wider then the cost of the Hash Match Aggregate goes through the roof.
A better option in such cases would be to collect the exclusion set's key in a CTE or subquery and use NOT IN.
😎
July 18, 2016 at 12:20 am
wendy elizabeth (7/17/2016)
What other sql options are you thinking that would work?I did not include all my question since I was trying to determine what was wrong with my original sql. I thought the too many rows was causing my problem.
Here is my original sql:
declare @gradYear int = 2017
SELECT distinct graduation.personID
into #gradPersonID
FROM [TEST].[dbo].[Graduation] graduation
where graduation.gradyear = @gradYear
SELECT distinct personID
FROM #gradPersonID graduation
where personID NOT in
(select distinct TranscriptCourse.personID from #gradPersonID graduation
join [TEST].[dbo].[TranscriptCourse] TranscriptCourse
on TranscriptCourse.personID = graduation.personID
where TranscriptCourse.grade between '09' and '12'
and @gradYear - 4 = TranscriptCourse.endYear)
DROP table #gradPersonID
Quick thoughts, this is quite a different problem from the one you originally posted, can I ask why you didn't post the full problem up front? This way you are wasting everyone's time.
Further, the solution you posted is not very efficient, lots of unnecessary IO, table scans and is far too complicated for such a simple task.
😎
Here is a quick suggestion towards a solution, cannot say anything else as I don't know if the problem we are trying to solve is going to change yet again.
USE TEEST;
GO
SET NOCOUNT ON;
--/*
IF OBJECT_ID(N'dbo.TranscriptCourse') IS NOT NULL DROP TABLE dbo.TranscriptCourse;
CREATE TABLE dbo.TranscriptCourse
(
transcriptID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TranscriptCourse_transcriptID PRIMARY KEY CLUSTERED
,personID int NOT NULL
,startYear smallint NULL
,endYear smallint NULL
,grade CHAR(2) NULL
)
IF OBJECT_ID(N'dbo.Graduation') IS NOT NULL DROP TABLE dbo.Graduation;
CREATE TABLE dbo.Graduation
(
GraduationID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_GRADUATION_GRADUATIONID PRIMARY KEY CLUSTERED
,personID int NOT NULL
,gradyear INT NULL
)
-- SAMPLE DATA GENERATION dbo.TranscriptCourse
;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))AS X(N))
, NUMS(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4)
,PERSON_AND_START_YEAR AS
(
SELECT
ABS(CHECKSUM(NEWID())) % 1000 AS PERSON_ID
,(ABS(CHECKSUM(NEWID())) % 100) + 1950 AS START_YEAR
,CONVERT(VARCHAR(2),(ABS(CHECKSUM(NEWID())) % 12) + 1,0) AS GRADE
FROM NUMS NM
)
INSERT INTO dbo.TranscriptCourse(personID,startYear,endYear,grade)
SELECT
PASY.PERSON_ID
,PASY.START_YEAR
,PASY.START_YEAR + (ABS(CHECKSUM(NEWID())) % 100) AS END_YEAR
,STUFF('00',3 - LEN(PASY.GRADE),LEN(PASY.GRADE),PASY.GRADE) AS GRADE
FROM PERSON_AND_START_YEAR PASY;
-- SAMPLE DATA GENERATION dbo.Graduation
;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))AS X(N))
, NUMS(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4)
,PERSON_GRADE AS
(
SELECT
ABS(CHECKSUM(NEWID())) % 1000 AS PERSON_ID
,CONVERT(VARCHAR(2),(ABS(CHECKSUM(NEWID())) % 12) + 1,0) AS GRADE
FROM NUMS NM
)
INSERT INTO dbo.Graduation(personID,gradyear)
SELECT
PEGR.PERSON_ID
,(ABS(CHECKSUM(NEWID())) % 100) + 1954 AS GRAD_YEAR
FROM PERSON_GRADE PEGR;
CREATE NONCLUSTERED INDEX NCLIDX_DBO_TRANSCRIPTCOURSE_ENDYEAR_GRADE_INCL_PERSONID ON dbo.TranscriptCourse(endYear ASC,grade ASC) INCLUDE (personID);
CREATE NONCLUSTERED INDEX NCLIDX_DBO_GRADUATION_GRADYEAR_PERSONID ON dbo.Graduation(gradyear ASC,personID);
--*/
declare @gradYear int = 2017
RAISERROR ('CTE SOLUTION',0,0) WITH NOWAIT;
SET STATISTICS IO,TIME ON;
;WITH EXCLUSION_SET AS
(
SELECT
TC.personID
FROM dbo.TranscriptCourse TC
WHERE TC.endYear = @gradYear - 4
AND TC.grade IN ('09','10','11','12')
)
SELECT
GR.personID
FROM dbo.Graduation GR
WHERE GR.gradyear = @gradYear
AND GR.personID NOT IN (SELECT ES.personID FROM EXCLUSION_SET ES)
GROUP BY GR.personID;
SET STATISTICS TIME,IO OFF;
RAISERROR ('TEMP TABLE SOLUTION',0,0) WITH NOWAIT;
SET STATISTICS IO,TIME ON;
SELECT distinct graduation.personID
into #gradPersonID
FROM dbo.Graduation graduation
where graduation.gradyear = @gradYear
SELECT distinct personID
FROM #gradPersonID graduation
where personID NOT in
(select distinct TranscriptCourse.personID from #gradPersonID graduation
join dbo.TranscriptCourse TranscriptCourse
on TranscriptCourse.personID = graduation.personID
where TranscriptCourse.grade between '09' and '12'
and @gradYear - 4 = TranscriptCourse.endYear)
DROP table #gradPersonID
SET STATISTICS TIME,IO OFF;
Statistics for the CTE query
CTE SOLUTION
Table 'Graduation'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TranscriptCourse'. Scan count 4, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 16 ms, elapsed time = 1 ms.
Statistics for the temp table query
TEMP TABLE SOLUTION
SQL Server parse and compile time: CPU time = 8 ms, elapsed time = 8 ms.
Table 'Graduation'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 4 ms.
SQL Server parse and compile time: CPU time = 44 ms, elapsed time = 44 ms.
Table '#gradPersonID_______________________________________________________________________________________________________00000000000D'. Scan count 2, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TranscriptCourse'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 3 ms.
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 2 ms.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply