April 28, 2007 at 3:11 am
Hello everyone, i need us help i m unablt to make A SP thats i used for reporting
i want to get the Total Projects, Planned Current year, Completed Projects , UnderConstruction projects, for this i have these tables
Table : Projects
ProjectID int
ProjectCode char(13) (impp 9 digits are used as 1 digit for Province, 1 for District, 1 for Tehsil, 2 for UnionCouncils, 2 for Villages, 1 for sector, 1 for subsector and other 4 are used for
0000 for 1 code 0001 for 2nd so on but 9 are fixed used for these.)
PartWorkPlan char(9) (used to store Planned year i.e. 2006-2007)
InitialCost
Now 2nd table: Activities
ActivityID int
ActivityName varchar(150)
3rd table: ActivitiesProgress
ProjectID
ActivityID
StartDate
EndDate
now the problem is i want to show the TotalProjects in the Database and the Completed and Undersonstruction Projects but my queries not shows the correct result as if i have 1 complete proj then my query returns the 1 as total projects also . Plz help me who can i write or modify my queries to get the required result. plz i m in trouble and still not ablt to write correct query plz help me.Thanx in Advance.
my query :
select Count(Projects.ProjectID)as Total, PartWorkPlan as 'Planned [current Year]',Count(Projects.projectID) as Completed
FROM dbo.Projects INNER JOIN
dbo.ActivityProgress ON dbo.Projects.ProjectID = dbo.ActivityProgress.ProjectID INNER JOIN
dbo.Activities ON dbo.ActivityProgress.ActivityID = dbo.Activities.ActivityID
where substring(ProjectCode,2,1)=1 and substring(ProjectCode,3,1)=1 and substring(ProjectCode,4,2)=02 and
substring(ProjectCode,8,1)='A' and Activities.ActivityID=30
group by PartWorkPlan
here is some data of my tables also :
Activities Table data
AvtivityID ActivityName
10 Project Identified
20 Implementing Agency Confirmed and Funds Committed
30 PC1 Prepared
160 Project Final Audit and Closure
ActivityProgress table data
ProjectID ActivityID StartDate EndDate
27596 10 2007-04-24 10:02:42.090 NULL
27596 20 2007-04-24 10:02:56.107 NULL
27596 30 2007-04-24 10:03:00.623 NULL
20695 40 2007-04-25 00:00:00.000 2007-04-26 00:00:00.000
20695 50 2007-04-25 00:00:00.000 2007-04-26 00:00:00.000
Projects table data
ProjectID ProjectName ProjectCode InitialCost PartWorkPlan
27596 GPS 1103000BB0001 1.26 2006-2007
27598 XYZ 1112400BC0002 1.0 2007-2008
27599 ABC 1210700BA0003 0.5 2007-2008
27600 DEF 1210800BA0004 1.1 2007-2008
April 30, 2007 at 9:48 am
Let me take a shot at this. If I understand your question correctly, you want to show the total projects in the database. But your query is filtering for a particular project. I created table variables to duplicate your data and ran a query to return what it is I think you are looking for.
select
count(sq.ProjectID) as 'Total', sq.PartWorkPlan as 'Planned [current Year]',
sum(case when Status = 'Not Started' then 1 else 0 end) as 'Not Started',
sum(case when Status = 'In Progress' then 1 else 0 end) as 'In Progress',
sum(case when Status = 'Completed' then 1 else 0 end) as 'Completed'
from (select distinct p.ProjectID, p.PartWorkPlan,
case when StartDate is NULL then 'Not Started'
when StartDate is not NULL and EndDate is NULL then 'In Progress'
when StartDate is not NULL and EndDate is not NULL then 'Completed'
end as Status
from @Projects p
left join @ActivityProgress ap on p.ProjectID = ap.ProjectID) sq
group by sq.PartWorkPlan
This returned:
Total Planned [current Year] Not Started In Progress Completed
1 2006-2007 0 1 0
3 2007-2008 3 0 0
I started building this query at the most broad level (i.e. list all projects and the associated progress) and then narrowed it down from there to get to this. I hope this is what you are looking for.
April 30, 2007 at 10:35 am
You have ActivityID of 40 & 50 in ActivityProgress that are not in Activities Table
You have ActivityID of 160 in Activities Table that is not referenced in ActivityProgress
What constitutes a 'Completed' project and a 'UnderConstruction' (Not Completed) project ?
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply