August 4, 2015 at 7:32 am
Hi
I have a query that displays a bunch of fields, specifically a createdon field and closedate field.
I need to find the diff in days between date1 and date2 and only disiplay those results.
For example: where the NumDays = a certain value. For example, 81.
I have the NumDays displaying in the query, but I don't know how to reference these values
in the where clause.
SELECT
TBU.businessunitidnameAS 'BU Name',
LEADS.statecodenameAS 'Status',
LEADS.statuscodeAS 'Status Code',
LEADS.accountidnameAS 'Account Name',
LEADS.parentcontactidnameAS 'Parent Contact ID Name',
LEADS.descriptionAS 'Description',
LEADS.createdonAS 'Actual Close Date',
DATEDIFF(day, '2015/01/01', LEADS.createdon) AS 'Days'
FROM dbo.FilteredLeadAS LEADS
INNER JOIN dbo.FilteredUserSettings AS TBU ON @BusinessUnitID = TBU.businessunitid
WHERE @BusinessUnitID= TBU.businessunitid
AND @TheUser= LEADS.owneridname
AND LEADS.statuscode= 3
/*
AND DATEDIFF(day, '2015/01/01', LEADS.createdon) <= @ClosedWithin
*/
August 4, 2015 at 8:05 am
The WHERE clause is processed before the SELECT statement, therefore any aliases assigned in SELECT statements are not available for their corresponding WHERE clauses. I have gotten around this by using a CROSS APPLY.
SELECT
TBU.businessunitidnameAS [BU Name],
LEADS.statecodenameAS [Status],
LEADS.statuscodeAS [Status Code],
LEADS.accountidnameAS [Account Name],
LEADS.parentcontactidnameAS [Parent Contact ID Name],
LEADS.descriptionAS [Description],
LEADS.createdonAS [Actual Close Date],
d.[Days]
FROM dbo.FilteredLeadAS LEADS
INNER JOIN dbo.FilteredUserSettings AS TBU ON @BusinessUnitID = TBU.businessunitid
CROSS APPLY(VALUES(DATEDIFF(day, '2015/01/01', LEADS.createdon))) AS d([Days])
WHERE @BusinessUnitID= TBU.businessunitid
AND @TheUser= LEADS.owneridname
AND LEADS.statuscode= 3
AND d.[Days] <= @ClosedWithin
I would also recommend using square brackets rather than single quotes for quoting your field aliases. It helps to distinguish between data strings and field names.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 4, 2015 at 8:23 am
Thank you so much
It works just great
August 4, 2015 at 8:54 am
Is '2015/01/01' a fixed value for the query or is it a value that can change from one row to another?
If it's a scalar value, you should change your query to something SARGable.
Here's a quick example.
DECLARE @ClosedWithin int = 81;
--Non SARGable
WITH LEADS AS(
SELECT DATEADD(DD, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, '20150101 12:33:42') createdon
FROM master.sys.all_columns
)
SELECT *
FROM LEADS
CROSS APPLY(VALUES(DATEDIFF(day, '20150101', LEADS.createdon))) AS d([Days])
WHERE d.[Days] <= @ClosedWithin;
--SARGable
WITH LEADS AS(
SELECT DATEADD(DD, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, '20150101 12:33:42') createdon
FROM master.sys.all_columns
)
SELECT *
FROM LEADS
WHERE createdon >= '20150101'
AND createdon < DATEADD( DD, @ClosedWithin + 1, '20150101');
August 4, 2015 at 11:27 am
Luis Cazares (8/4/2015)
Is '2015/01/01' a fixed value for the query or is it a value that can change from one row to another?If it's a scalar value, you should change your query to something SARGable.
Here's a quick example.
DECLARE @ClosedWithin int = 81;
--Non SARGable
WITH LEADS AS(
SELECT DATEADD(DD, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, '20150101 12:33:42') createdon
FROM master.sys.all_columns
)
SELECT *
FROM LEADS
CROSS APPLY(VALUES(DATEDIFF(day, '20150101', LEADS.createdon))) AS d([Days])
WHERE d.[Days] <= @ClosedWithin;
--SARGable
WITH LEADS AS(
SELECT DATEADD(DD, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, '20150101 12:33:42') createdon
FROM master.sys.all_columns
)
SELECT *
FROM LEADS
WHERE createdon >= '20150101'
AND createdon < DATEADD( DD, @ClosedWithin + 1, '20150101');
Actually, those two queries are not equivalent. The first query allows dates that are before '20150101', whereas the second does not. While it's highly likely that the OP only wants dates after 2015-01-01, it's not necessarily true in this case.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 4, 2015 at 11:33 am
drew.allen (8/4/2015)
Actually, those two queries are not equivalent. The first query allows dates that are before '20150101', whereas the second does not. While it's highly likely that the OP only wants dates after 2015-01-01, it's not necessarily true in this case.
Drew
Yes, sorry, I got excited thinking on what the actual requirement might look like (not returning the whole history). It's easily corrected removing the first condition. 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply