December 3, 2009 at 11:41 am
Hi all - here's my situation:
SELECT ID, COUNT(*)
FROM User
JOIN project ONID = UserID
AND DATEDIFF(d, datecreated, MAX(datecreated)) < 31
GROUP BY ID
Basically, what I'm trying to get is a list of all the users, and their project count - but only counting projects that were created within 31 days of their most recent project.
Now this query is going to give an error if you try to run it, because I'm trying to aggregate datecreated, but datecreated isn't present in the GROUP BY field. However, I don't want to group by datecreated, because that wouldn't make any sense at all.
My solution for this problem was to change it into a subquery:
SELECT ID, COUNT(*)
FROM User
JOIN project p1 ONID = p1.UserID
AND DATEDIFF(d, datecreated, (SELECT MAX(p2.datecreated) FROM project p2 WHERE p2.userID = ID)) < 31
GROUP BY ID
Is there another solution? I feel like the subquery is going to make things perform in an unoptimal way, but I can't find another solution to the problem.
December 3, 2009 at 12:27 pm
i think this will work however hard to test w/o actual tables and data
select u.id
, count(distinct piprogId)
from user u
join (
select userid
, max(datecreated) mxProjDate
from project
group by userid
) mx
on u.id = mx.userId
join project p
on p.userid = mx.userId
where DATEDIFF(d, p.datecreated, mxProjDate) < 31
group by u.id
December 3, 2009 at 12:31 pm
if u send the table schemas i can give it w proper syntax
i did notice the count(distinct p.progId) had a typo in it. fixed below
select u.id
, count(distinct p.progId)
from user u
join (
select userid
, max(datecreated) mxProjDate
from project
group by userid
) mx
on u.id = mx.userId
join project p
on p.userid = mx.userId
where DATEDIFF(d, p.datecreated, mxProjDate) < 31
group by u.id
December 3, 2009 at 1:55 pm
Unfortunately that doesn't really change the solution much - instead of a subquery happening in the WHERE clause, you're running a subquery to populate a JOIN clause. I'm trying to avoid using a subquery altogether.
December 3, 2009 at 3:12 pm
Please provide the DDL for the tables (CREATE TABLE statements), sample data in a readily consummable format (We should be able to simple cut/paste/execute to load the tables), expected results BASED on the provided sample data.
If you need help with this, please read the first article I reference below in my signature block.
Also, be sure to fully test all your code before posting.
If you provide the requested info, you will get tested code in return. In addition, you may even get several different ways of accomplishing the same task.
December 3, 2009 at 3:32 pm
I understand all that Lynn 😛 this was more of a theoretical question than one that needed to be applied on a specific case. But if you wish,
CREATE TABLE #User
(
ID INT IDENTITY PRIMARY KEY,
SomeRandomField INT
)
CREATE TABLE #Project
(
ProjectID INT IDENTITY PRIMARY KEY,
UserID INT,
DateCreated SMALLDATETIME
)
INSERT INTO #User (SomeRandomField)
SELECT 1
UNION ALL
SELECT 1
UNION ALL
SELECT 1
UNION ALL
SELECT 1
UNION ALL
SELECT 1
UNION ALL
SELECT 1
INSERT INTO #Project (UserID, DateCreated)
SELECT 1, GETDATE()
UNION ALL
SELECT 1, GETDATE()-15
UNION ALL
SELECT 2, GETDATE()-15
UNION ALL
SELECT 2, GETDATE()-20
UNION ALL
SELECT 2, GETDATE()-21
UNION ALL
SELECT 3, GETDATE()-35
UNION ALL
SELECT 4, GETDATE()-40
UNION ALL
SELECT 4, GETDATE()-80
UNION ALL
SELECT 4, GETDATE()-100
SELECT * FROM #User
SELECT * FROM #Project
Query which doesn't work:
SELECT ID, COUNT(*)
FROM #User
JOIN #Project ONID = UserID
AND DATEDIFF(d, datecreated, MAX(datecreated)) < 31
GROUP BY ID
Query which does, that I'd like to see done without using a subquery, if possible:
SELECT ID, COUNT(*)
FROM #User
JOIN #Project p1 ONID = p1.UserID
AND DATEDIFF(d, datecreated, (SELECT MAX(p2.datecreated) FROM #Project p2 WHERE p2.userID = ID)) < 31
GROUP BY ID
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply