when I run the following query it returns me a zero, although the column is a datetime
type.
SELECT IVSubmitted
FROM vw_queue_ready_for_agencyReview
WHERE agencyid = 1399
ORDER BY lastName ASC, firstName ASC
this is the code for the view:
IF OBJECT_ID('[dbo].[vw_queue_ready_for_agencyReview]') IS NOT NULL
DROP VIEW [dbo].[vw_queue_ready_for_agencyReview]
GO
CREATE VIEW [dbo].[vw_queue_ready_for_agencyReview]
AS
SELECT t1.*, t2.activityDate as IVSubmitted
FROM vw_applicant_queue_output t1
LEFT JOIN applicationActivity t2 on t1.applicationID = t2.applicationID
AND t2.activityTypeID = 48
AND t2.activityDate IN (SELECT min(activityDate)
FROM applicationActivity
WHERE applicationID = t2.applicationID
AND activityTypeID = 48)
WHERE applicationReviewStatus = 3
AND status = 'OLA'
the other view:
CREATE VIEW [dbo].[vw_applicant_queue_output]
AS
SELECT a.firstName,
a.lastName,
a.birthDate,
a.emailAddress,
dbo.ftn_age_calculation(a.birthDate) AS Age,
datediff( day, a.createDate, getdate() ) As DaysSinceRegistration,
a.applicantID,
ap.applicationID,
ap.status,
ad.availabilityEarliestDate,
ad.availabilityLatestDate,
ap.programID,
uvs.lastLogin,
uvs.lastAction,
ct.firstName AS InterviewerFirstName,
ct.lastName AS InterviewerLastName,
asd.applicationFormStatus,
asd.hostFamilyLetterStatus,
asd.childcareExperienceStatus,
asd.applicationReviewStatus,
asd.uploadStatus,
asd.interviewReportStatus,
asd.referenceCheckStatus,
ap.agencyID,
ap.interviewerID,
ap.sendBrochure,
ap.brochureSent,
ac.applicationForm,
ac.childcareExperience,
ac.hostFamilyLetter,
ac.uploads,
ac.interviewReport,
ac.telephoneReferenceCheck,
ac.agency_applicationForm,
ac.agency_childcareExperience,
ac.agency_hostFamilyLetter,
ac.agency_uploads,
ac.agency_interviewReport,
ac.agency_telephoneReferenceCheck,
ct.branchID,
ct.isBranchManager,
a.requiresCallback,
a.calledBackDate,
a.archive,
a.leadType,
a.grading,
ag.countryCode AS 'agencyCountryCode'
FROM applicant AS a
JOIN application AS ap ON ap.applicantID = a.applicantID AND ap.isActive = 1
JOIN applicationData AS ad ON ad.applicationID = ap.applicationID
LEFT JOIN agency AS ag ON ag.agencyID = ap.agencyID
LEFT JOIN applicationStatus AS asd ON asd.applicationID = ap.applicationID
LEFT JOIN contact AS ct ON ct.contactID = ap.interviewerID AND ct.interviewer = 1
LEFT JOIN userVisitSummary AS uvs ON uvs.uniqueUserID = ap.applicationID AND uvs.recordType = 'APP'
LEFT JOIN applicationChecklist AS ac ON ac.applicationID = ap.applicationID
WHERE ap.status NOT IN ('ARC', 'REJ' )
the table dbo.applicationActivity
, which contains the column in question activityDate
:
CREATE TABLE [dbo].[applicationActivity] (
[applicationActivityID] INT IDENTITY(1,1) NOT NULL,
[applicationID] INT NOT NULL,
[activityTypeID] INT NOT NULL,
[activityBy] VARCHAR(100) NOT NULL,
[activityDate] DATETIME NULL CONSTRAINT [DF__applicati__activ__685E57C7] DEFAULT (getdate()),
[objectTypeID] INT NULL,
[objectType] VARCHAR(10) NULL,
[sessionID] VARCHAR(100) NULL,
CONSTRAINT [PK__applicationActiv__676A338E] PRIMARY KEY CLUSTERED ([applicationActivityID] asc),
CONSTRAINT [FK_applicationActivity_application] FOREIGN KEY ([applicationID]) REFERENCES [application]([applicationID]),
CONSTRAINT [FK_applicationActivity_activityType] FOREIGN KEY ([activityTypeID]) REFERENCES [activityType]([activityTypeID]))
GO
CREATE NONCLUSTERED INDEX [idx_activityDate]
ON [dbo].[applicationActivity] ([activityDate] desc, [activityBy] asc)
INCLUDE ([applicationID], [activityTypeID], [objectTypeID], [objectType])
CREATE NONCLUSTERED INDEX [idx_applicationid]
ON [dbo].[applicationActivity] ([applicationID] asc)
CREATE NONCLUSTERED INDEX [idx_objectTypeID]
ON [dbo].[applicationActivity] ([objectTypeID] asc, [objectType] asc)
WHERE ([objectType] IS NOT NULL)
The query plan for the original query
BUT when I copy the code of the first view into the query and run it again:
SELECT IVSubmitted FROM
(
SELECT t1.*, t2.activityDate as IVSubmitted
FROM vw_applicant_queue_output t1
LEFT JOIN applicationActivity t2 on t1.applicationID = t2.applicationID
AND t2.activityTypeID = 48
AND t2.activityDate IN (SELECT min(activityDate)
FROM applicationActivity
WHERE applicationID = t2.applicationID
AND activityTypeID = 48)
WHERE applicationReviewStatus = 3
AND status = 'OLA'
) r
WHERE agencyid = 1399 ORDER BY lastName ASC, firstName ASC
It then gives me the expected result (a datetime
):
the query plan for this new query.
when running the following query:
select @@version as [my sql server],
d.compatibility_level
from sys.databases d
where d.database_id = db_id()
my sql server
Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Datacenter 6.3 (Build 9600: ) (Hypervisor)compatibility_level
100
What is causing this behaviour and how can I return the datatime
?