October 7, 2008 at 12:14 pm
Hello All,
I have 3 tables. Projects, ProjectStatus, and ProjectStatusHistory.
Project contains all information about a project except for the status. ProjectStatus is a list of possible status codes (Open, Closed, Pending, etc...), and ProjectStatusHistory contains information about status changed, for example...
If I get a recordset of all rows w/ a ProjectID = 5, I might get 5 rows, showing the project being opened, changes made, then finally closed. The rows are...
ClientID
ProjectID
StatusID
CreatedOn (Time stamp)
What I'm trying to do is find out how many open projects I have related to a client.
So I want the Count(*) FROM ProjectStatusHistory WHERE ClientID = @ClientID, but I don't know how to specify that the result should only include the TOP records sorted in DESC order.
I have a feeling that this might be confusing, but I hope someone can help me out.
Thanks in advance,
Stephen
October 7, 2008 at 12:27 pm
If you just want the number of open projects for a specific clientID why not use somethign like the follwoing.
SELECT Count(*), ClientID
FROM ProjectStatusHistory
WHERE ClientID = @ClientID
AND Status = 'Open' --or whatever id that equates to
GROUP BY clientID
October 7, 2008 at 12:58 pm
It's possible for one project to have a Status of "Open" multiple times, for example, it's it's Open, then closed, and reopened for upgrades. In that case, it would show me 2. I only want the query to return the current status.
This is also a sub query of a larger query.
October 7, 2008 at 1:11 pm
Sorry, I think I might have been misleading. Here are the table structures...
[Clients]
ID
Client
[Projects]
ID
Name
ClientID - Relates to Clients.ID
[ProjectStatus]
ID
Status
[ProjectStatusHistory]
ProjectID - Relates to Projects.ID
ProjectStatus - Relates to ProjectStatus.ID
CreatedOn (When the status was updated)
October 7, 2008 at 1:22 pm
Stephen Lee (10/7/2008)
It's possible for one project to have a Status of "Open" multiple times, for example, it's it's Open, then closed, and reopened for upgrades. In that case, it would show me 2. I only want the query to return the current status.This is also a sub query of a larger query.
So instead of count(*) use count(distinct ProjectID)
October 7, 2008 at 10:14 pm
I don't believe that will work. Did you see the updated the list of columns in the respective tables?
Stephen
October 8, 2008 at 8:06 am
Yup I looked at them and frankly dismissed them because it seemed like you were missing the columns that we'd need to provide any other type of solution... You spoke of a CreatedOn datetime column in your original post. Where does this exist? I'm making a few assumptions in the following query, it should give you a list of Clients, Projects and Current Status. From there I would imagine you'd be able to add counts wherever you need them and/or modify the idea to your actual columns.
SELECT c.NAME AS ClientName,
p.NAME AS ProjectName,
ps.Status AS Currentstatus --Should all be "open" at this point
FROM Clients C
INNER JOIN Projects P
ON c.ID = p.clientID
INNER JOIN ProjectStatusHistory PSH
ON p.ID = psh.ProjectID
INNER JOIN (SELECT ProjectID, MAX(CreatedON) AS CurrentStatusDate
FROM ProjectStatusHistory
GROUP BY [ProjectID]) AS foo
ON psh.ProjectID = foo.ProjectID--This join could be simplified by an ID column in
AND psh.CreatedON = foo.CurrentStatusDate --Projectstatushistory if one exists
INNER JOIN ProjectStatus PS
ON PSH.ProjectStatus = ps.ID
AND ps.tStatus = 'Open' --This could be placed in other parts of the query to optimize index usage perhaps...
October 8, 2008 at 9:20 am
Thanks for all of your help so far. I updated the table definitions to reflect the CreatedOn column.
I'm able to get the records that I need to determine the data, however, I'm not able to get the count into my subquery. Here's the query that I'm running.
SELECT c.ID, Client, City, State = s.Name,
OpenProjects = 'NYI',
TotalProjects = (SELECT Count(*) FROM Projects WHERE ClientID = c.ID AND IsDeleted = 0)
FROM Clients c
INNER JOIN State s ON c.State = s.Abbreviation
WHERE c.IsDeleted = 0
ORDER BY c.Client, c.CreatedOn
I'm trying to get the count to go into the "OpenProjects" field.
Thanks again,
Stephen
October 8, 2008 at 9:31 am
October 8, 2008 at 9:51 am
I've attached the complete table structure and some test data.
Stephen
October 8, 2008 at 11:25 am
This should work for you, but you'll need to add your state table back in. There was no data in it and I was too lazy to add it.
SELECT c.ID,
c.Client,
c.City,
c.[State],
COUNT(DISTINCT currentPS.ProjectID) AS OpenProjects,
COUNT(DISTINCT P.id) AS [TotalProjects]
FROM Clients c
INNER JOIN [Projects] p
ON p.[ClientID] = c.[ID]
AND p.IsDeleted = 0
LEFT JOIN (SELECT psh.[ProjectID], psh.[ProjectStatus]
FROM ProjectStatusHistory PSH
inner JOIN (SELECT ProjectID, MAX(CreatedON) AS CurrentStatusDate
FROM ProjectStatusHistory
GROUP BY [ProjectID]) AS foo
ON psh.ProjectID = foo.ProjectID
AND psh.CreatedON = foo.CurrentStatusDate
WHERE psh.[ProjectStatus] = 1 /*open */) AS currentPS
ON p.ID = currentPS.ProjectID
WHERE c.IsDeleted = 0
GROUP BY c.ID,
c.Client,
c.City,
c.[State]
ORDER BY c.Client
October 8, 2008 at 12:45 pm
Amazing, thanks so much for your help.
The solution works perfect, I just don't quite understand it, but I don't really need to.
Stephen
October 8, 2008 at 12:53 pm
Don't just write it off as not understanding it... take a moment or two and work from the inner most query (the one tabbed all the way to the edit:right) out...
First we know we need to get the current status of the project(probably something you'll need to do more often...)
All this does is get the date of the most recent createdon date for each Project ID. Theoretically, this should always be the most recent date...
SELECT ProjectID, MAX(CreatedON) AS CurrentStatusDate
FROM ProjectStatusHistory
GROUP BY [ProjectID]
then we join to that the rest of the history table so that we can get the status that that belongs to that record,
Once we have the projectID and the current status of that project, we can filter out any projects that are not open and join back to the rest of the tables to get your client name address and such.
October 8, 2008 at 1:10 pm
Ahh, when I broke it down like that and only ran parts of a query one at a time it made more sense.
Thanks again, I've been working on that single query for days...
Stephen
October 8, 2008 at 1:20 pm
Yup, that's what it's all about break it down into workable chunks. Pick a piece of the puzzle and work that then the next piece etc.
The real take away is given the DDL and accurate sample data it becomes much easier to get you what you really need.
😉
-Luke.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply