June 23, 2011 at 2:33 pm
I am creating a report using SSRS and feeding in two dates using paramaters. My table has IssueDate (date field) and CertificateNumber (integer). I want a query that gives the max and min CertificateNumber between two given dates.
SELECT COUNT(DISTINCT CertificateNumber) AS NumberIssued, MIN(DISTINCT CertificateNumber) AS Expr1, MAX(DISTINCT CertificateNumber) AS Expr2
FROM AuthCertificateTracking
WHERE (IssueDate BETWEEN @Date1 AND @Date2)
is giving the correct count but the max and min values are falling outside of the dates. Seems like the query does not filter the CirtificateNumber between the dates
June 23, 2011 at 4:02 pm
AFAIK the WHERE clause is evaluated before the SELECT column list so what you're describing makes no sense to me. If you can reproduce the situation please post sample DDL, DML and your SELECT statement.
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'AuthCertificateTracking')
AND type IN (N'U') )
DROP TABLE AuthCertificateTracking ;
GO
CREATE TABLE AuthCertificateTracking
(
CertificateNumber INT,
IssueDate DATE
) ;
INSERT INTO dbo.AuthCertificateTracking
(CertificateNumber, IssueDate)
VALUES (1, '2011-06-20'),
(6, '2011-06-21'),
(7, '2011-06-22'),
(9, '2011-06-25'),
(10, '2011-06-28') ;
SELECT COUNT(DISTINCT CertificateNumber) AS NumberIssued,
MIN(DISTINCT CertificateNumber) AS Expr1,
MAX(DISTINCT CertificateNumber) AS Expr2
FROM AuthCertificateTracking
WHERE (IssueDate BETWEEN '2011-06-21' AND '2011-06-25') ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply