October 1, 2019 at 6:51 pm
I have a simple table where I need to exclude any dates that weren't within 3 days of the max date. I can't use aggregates in WHERE or HAVING clauses, so looking for an easy to way to exclude a date when it's more than 3 days diff from max date. Here's some sample data:
create table #temp_Samples (
Guid uniqueidentifier not null,
LastDate datetime not null)
INSERT INTO #temp_Samples VALUES ('D87BF755-44C2-415A-A241-1512409F52E3', '2019-07-10 20:09:32.000')
INSERT INTO #temp_Samples VALUES ('929DF755-44C2-415A-A241-1512409F52E3', '2019-09-17 20:19:01.000')
INSERT INTO #temp_Samples VALUES ('42A2F755-44C2-415A-A241-1512409F52E3', '2019-09-17 16:22:25.000')
INSERT INTO #temp_Samples VALUES ('84A3F755-44C2-415A-A241-1500009F52E3', '2019-09-17 16:38:16.000')
INSERT INTO #temp_Samples VALUES ('E522F855-44C2-415A-A241-1512409F52E3', '2019-09-17 20:14:41.000')
INSERT INTO #temp_Samples VALUES ('3E42F855-44C2-415A-A241-1512409F52E3', '2019-09-17 18:08:24.000')
INSERT INTO #temp_Samples VALUES ('6E60F855-44C2-415A-A241-1512409F52E3', '2019-09-17 20:03:55.000')
INSERT INTO #temp_Samples VALUES ('836EF855-44C2-415A-A241-1512409F52E3', '2019-09-17 14:25:02.000')
select * from #temp_Samples
drop table #temp_Samples
Many thanks for any assistance.
Regards,
October 1, 2019 at 7:11 pm
Like this?
DROP TABLE IF EXISTS #temp_Samples;
CREATE TABLE #temp_Samples
(
Guid UNIQUEIDENTIFIER NOT NULL
,LastDate DATETIME NOT NULL
);
INSERT #temp_Samples
VALUES
('D87BF755-44C2-415A-A241-1512409F52E3', '2019-07-10 20:09:32.000')
,('929DF755-44C2-415A-A241-1512409F52E3', '2019-09-17 20:19:01.000')
,('42A2F755-44C2-415A-A241-1512409F52E3', '2019-09-17 16:22:25.000')
,('84A3F755-44C2-415A-A241-1500009F52E3', '2019-09-17 16:38:16.000')
,('E522F855-44C2-415A-A241-1512409F52E3', '2019-09-17 20:14:41.000')
,('3E42F855-44C2-415A-A241-1512409F52E3', '2019-09-17 18:08:24.000')
,('6E60F855-44C2-415A-A241-1512409F52E3', '2019-09-17 20:03:55.000')
,('836EF855-44C2-415A-A241-1512409F52E3', '2019-09-17 14:25:02.000');
WITH MinDate
AS (SELECT MinDate = DATEADD(DAY, -3, MAX(ts.LastDate))
FROM #temp_Samples ts)
SELECT s.Guid
,s.LastDate
FROM #temp_Samples s
CROSS JOIN MinDate d
WHERE s.LastDate >= d.MinDate;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 1, 2019 at 8:37 pm
This appears to perform better, because it only requires reading the table once. You should probably perform a more thorough analysis with a larger data set.
WITH window AS
(
SELECT
ts.Guid
,ts.LastDate
,DATEADD(DAY, -3, MAX(ts.LastDate) OVER()) AS CutoffDate
FROM #temp_Samples AS ts
)
SELECT w.Guid, w.LastDate
FROM window AS w
WHERE w.LastDate >= w.CutoffDate;
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 2, 2019 at 7:08 pm
Thanks for that, guys! That does work for getting the results I need from that part of my query. I'm joining these results into a larger data set via an INNER JOIN. It appears you can't use CTE queries in the joined select. I keep getting an error:
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'WITH'.
Msg 319, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 20
Incorrect syntax near ')'.
Any further thoughts on that?
October 2, 2019 at 7:13 pm
If you don't post your code, we can't find syntax errors in it.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 2, 2019 at 7:14 pm
Thanks for that, guys! That does work for getting the results I need from that part of my query. I'm joining these results into a larger data set via an INNER JOIN. It appears you can't use CTE queries in the joined select. I keep getting an error:
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'WITH'.
Msg 319, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 20
Incorrect syntax near ')'.
Any further thoughts on that?
Make sure that the last statement before the WITH, is terminated with a semi-colon ";"
October 2, 2019 at 7:48 pm
SELECT
A.Context
, A.CName
, A.OutsideId
, DATEADD(hour,-5,A.CompletedOn) AS PostedDate
FROM Audit A
INNERJOIN
(WITH PostedHistory AS
(
SELECT
a.DealId
, MAX(a.CompletedOn) as LastDate
, DATEADD(DAY, -3, MAX(a.CompletedOn) OVER()) AS CutoffDate
FROMAudit a
WHERE a.DisplayName = 'TestFloor'
AND a.Status = 'Complete'
AND a.Client = 'TestClient'
AND a.Context = 'TestContext'
GROUP BY a.DealId, a.CompletedOn
)
SELECT w.DealId, w.LastDate
FROM PostedHistory AS w
WHERE w.LastDate >= w.CutoffDate
) LastAudit ON A.DealId = LastAudit.DealId
WHERE
A.CompletedOn = LastAudit.LastDate
AND A.AuditTypeDisplayName = 'TestFloor'
AND A.Status = 'Complete'
ORDER BY a.Context
October 2, 2019 at 8:25 pm
You cannot define a CTE inside of a subquery as you are trying to do. The correct syntax is more like the following, although it's untested because you haven't supplied sample data.
WITH PostedHistory AS
(
SELECT
a.DealId
,MAX(a.CompletedOn) AS LastDate
,DATEADD(DAY, -3, MAX(a.CompletedOn) OVER ()) AS CutoffDate
FROMAudit AS a
WHERE a.DisplayName = 'TestFloor'
AND a.Status = 'Complete'
AND a.Client = 'TestClient'
AND a.Context = 'TestContext'
GROUP BY a.DealId
,a.CompletedOn
)
SELECT
A.Context
,A.CName
,A.OutsideId
,DATEADD(HOUR, -5, A.CompletedOn) AS PostedDate
FROMAudit AS A
INNER JOIN PostedHistory AS w
ON A.DealId = w.DealId
WHERE A.CompletedOn = w.LastDate
AND A.AuditTypeDisplayName = 'TestFloor'
AND A.Status = 'Complete'
AND w.LastDate >= w.CutoffDate
ORDER BY A.Context;
Also, if you had given us the full problem instead of giving it to us piecemeal, we would have come up with a better solution than using the join. Again, since you haven't posted sample data and expected results, this is untested. It should be close to the optimal solution.
WITH PostedHistory AS
(
SELECT
a.DealId
,a.Context
,a.CName
,a.OutsideId
,a.CompletedOn
,MAX(a.CompletedOn) OVER(PARTITION BY a.DealID) AS LastDate
,DATEADD(DAY, -3, MAX(a.CompletedOn) OVER ()) AS CutoffDate
FROMAudit AS a
WHERE a.DisplayName = 'TestFloor'
AND a.Status = 'Complete'
AND a.Client = 'TestClient'
AND a.Context = 'TestContext'
GROUP BY a.DealId
,a.CompletedOn
)
SELECT
A.Context
,A.CName
,A.OutsideId
,DATEADD(HOUR, -5, A.CompletedOn) AS PostedDate
FROM PostedHistory AS w
WHERE w.CompletedOn = w.LastDate
AND A.AuditTypeDisplayName = 'TestFloor'
AND w.LastDate >= w.CutoffDate
ORDER BY A.Context;
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply